NewsForumsCodeLogsGallerySpecsSearch

Database connectivity

BlitzMax Forums/Brucey's Modules/Database connectivity

Retimer(Posted 2009) [#1]
Sometimes on my servers the database connection will time out, or simply disconnect (on the remote ones anyways).

For this I tried the following to prevent problems:

If Not MyDB.isOpen() Then ConnectSql()


Which, if isOpen() returned 0 when the connection was lost, would reconnect the server to the database. I'm not finding this to work though, as once connected, isOpen 'always' returns 1, even if the connection is broken it would seem.

The query commands still work, but return nothing instead. Is there any way to have the database modules check the connection more 'lively'?


DavidDC(Posted 2009) [#2]
Which database are you using Retimer? It is possible that Brucey hasn't written the IsOpen routine for it.

I say this because I had exactly the same issue with PostgreSQL until I asked Brucey to have a look under the bonnet. He implemented the missing routine and all is sweet now.

My routine looks like this and (in production) works like a charm
	Method WaitForOpenConnection()
	
		In("WaitForOpenConnection")
		
		'	If the connection to the database is down (*the* most likely db error user will face due to network failures)	
		If Not DBConnection.IsOpen()
		
			'	Show a busy dialog, counting down to a reconnect attempt
			If BusyDialog And BusyDialog.ShowGauge(RetryDelayInMilliseconds, MaxRetryAttempts) = wxID_OK
				
				'	Re-attempt to reconnect manually
				Reconnect()
				WaitForOpenConnection()
					
			EndIf
	
		EndIf
		
		Out("WaitForOpenConnection")
		
	End Method



Retimer(Posted 2009) [#3]
MySQL

Perhaps I should take a look at the postgresql method of it.

Thanks David


DavidDC(Posted 2009) [#4]
Yes it looks like the MySql mod is missing the equiv of:

int bmx_pgsql_PQstatus(const PGconn * handle) {
return PQstatus(handle);
}

which is called from:

	Method isOpen:Int()
		If _isOpen Then
			' really check that the database is open
			If bmx_pgsql_PQstatus(handle) Then
				_isOpen = False
			End If
		End If
		
		Return _isOpen
	End Method


What about:

int mysql_ping(MYSQL *mysql)

That might work.


Description

Checks whether the connection to the server is working. If the connection has gone down and auto-reconnect is enabled an attempt to reconnect is made. If the connection is down and auto-reconnect is disabled, mysql_ping() returns an error.

Auto-reconnect is enabled by default.

mysql_ping() can be used by clients that remain idle for a long while, to check whether the server has closed the connection and reconnect if necessary.




Retimer(Posted 2009) [#5]
Yes it looks like the MySql mod is missing the ...

Beat me to it ;)

Thanks for bringing up the ping function, i'll have a go with it this week when I have the time and see what comes of it. Hopefully, if it works, it'll be worthy of an official update when brucey gets back.

Cheers


Brucey(Posted 2009) [#6]
isOpen() should now actually check to see if the connection is open, and return False is not.


Retimer(Posted 2009) [#7]
Ah, so that function was the trick then, thanks brucey/david.