Check Connection

[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

[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

[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.

[Da_Elf]

hmm. maybe i should try this approach.

[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
      

[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

[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

[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

[Simon]

hmmm try;

Code: Select all

on checkServer
   if revOpenDatabases() is empty then dbaseConnect
end checkServer

revOpenDatabases()

Simon

[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...)

[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

[ghettocottage]

Just out of curiosity: is this a remote server that you are connecting to a MySQL database?

[Da_Elf]

its usually on an internal network

[ghettocottage]

have you tried using Livecode server as an intermediary?

[Da_Elf]

what do you mean by livecode server?