Page 1 of 2
Check Connection
Posted: Thu May 14, 2015 11:21 pm
by Da_Elf
Ok so i wanted to connect to my database to do all the stuff i want to do (as below it connects as needed)
But then i wanted that when i do anything that needed to connect to the database i would check if the connection still exists and if not to open a connection before doing anything. However when i forced my databse to drop the connection then tried to run something it doesnt like me using select if there is no connection. So i assume that one of the revDatabase commands that would be best to use to check if there is a connection and if not do something but not sure which is the best to use.
Code: Select all
on something
checkServer
do what needs to be done
end something
Code: Select all
on checkServer
put "SELECT COUNT(id) FROM connecttest WHERE testid ='1' " into trSQL
put revDataFromQuery(tab, cr, gConnectionID, trSQL) into trData
if trData <> 1 then
dbaseConnect
end if
end checkServer
Code: Select all
on dbaseConnect
put "xxxxx" into tDatabaseAddress
put "xxxxx" into tDatabaseName
put "xxxxx" into tDatabaseUser
put "xxxxx" into tDatabasePassword
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
if tResult is a number then
put tResult into gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database"
end if
end dbaseConnect
Re: Check Connection
Posted: Fri May 15, 2015 12:05 am
by Da_Elf
Grrrr. neither of these works.
basically when i kill the connection and put a debug stop point on the checkServer and mouse over revOpenDatabases it says "revOpenDatabases :revOpenDatabases" which means its not empty which means it didnt run the dbaseConnect script to reconnect
Code: Select all
on checkServer
if revOpenDatabases is empty then dbaseConnect
end checkServer
Code: Select all
on dbaseConnect
if revOpenDatabases is empty then
put "xxxxx" into tDatabaseAddress
put "xxxxx" into tDatabaseName
put "xxxxx" into tDatabaseUser
put "xxxxx" into tDatabasePassword
put revOpenDatabase("MySQL", tDatabaseAddress, tDatabaseName, tDatabaseUser, tDatabasePassword) into tResult
if tResult is a number then
put tResult into gConnectionID
else
put empty into gConnectionID
answer error "Unable to connect to the database"
end if
end if
end dbaseConnect
Re: Check Connection
Posted: Fri May 15, 2015 11:27 am
by robl
I approach this a bit differently. My apps do not attempt maintain a database connection for a long period of time. Many times a database server will have a relatively short timeout period to close the connections, which is done to conserve resources (and on commercial database servers where you paid based on the number of concurrent connections, conserve on licensing cost).
My basic app flow is to open the connection; check that it is valid; run a series of queries that are immediately needed; close the connection. The user will then interact which triggers a script to open the connection again; check; query; close. This works well. With most modern computers, servers, and networks, the connection process is very quick, so it should have a minimal impact on your app performance.
Re: Check Connection
Posted: Fri May 15, 2015 9:12 pm
by Da_Elf
hmm. maybe i should try this approach.
Re: Check Connection
Posted: Fri May 15, 2015 10:37 pm
by ghettocottage
Maybe use your global variable to check the connection
Code: Select all
--check if already connected
if gConnectionID is empty then
do whatever...
end if
Re: Check Connection
Posted: Sat May 16, 2015 3:13 am
by Da_Elf
that wouldnt work if im cut off from the server. Once ive opened a connection gConnectionID is set. if im cut off how does livecode know there is no connection to reset gConnectionID to empty
Re: Check Connection
Posted: Sat May 16, 2015 8:29 am
by ghettocottage
doh!
what about:
Code: Select all
on checkServer
put "SELECT COUNT(id) FROM connecttest WHERE testid ='1' " into trSQL
put revDataFromQuery(tab, cr, gConnectionID, trSQL) into trData
if item 1 of trData = "revdberr" then
dbaseConnect
end if
end checkServer
Re: Check Connection
Posted: Sat May 16, 2015 12:02 pm
by Da_Elf
i used to use a similar way but it will crash on the revData line since gConnectionID wont be valid is its not connected
Re: Check Connection
Posted: Sat May 16, 2015 1:07 pm
by Simon
hmmm try;
Code: Select all
on checkServer
if revOpenDatabases() is empty then dbaseConnect
end checkServer
revOpenDatabases()
Simon
Re: Check Connection
Posted: Sat May 16, 2015 3:17 pm
by dave.kilroy
Another way to check connectivity (which also works for an initial connection as well as checking for continued internet connection) is use a function to connect to a .lc file on the remote server, which if everything connects properly returns the seconds - and if the function returns as expected I then carry on and do my CRUD operation with the database
I should think it would be faster to check for connectivity from google.com (or I guess a ping?) but at least doing it this way I know for sure whether my server is working (my web service is currently on on-rev so I always check first...)
Re: Check Connection
Posted: Sat May 16, 2015 9:37 pm
by Da_Elf
im pretty sure i tried the revOpenDatabases with and without the () will have to try again but ive more or less decided to just close the connection after each executed search rather than keep it open and invite timeout disconnections
Re: Check Connection
Posted: Sat May 16, 2015 10:05 pm
by ghettocottage
Just out of curiosity: is this a remote server that you are connecting to a MySQL database?
Re: Check Connection
Posted: Sat May 16, 2015 11:42 pm
by Da_Elf
its usually on an internal network
Re: Check Connection
Posted: Sun May 17, 2015 1:35 am
by ghettocottage
have you tried using Livecode server as an intermediary?
Re: Check Connection
Posted: Sun May 17, 2015 1:55 am
by Da_Elf
what do you mean by livecode server?