Database Connection State

[Zeal]

Hi

I am inserting data to my Database which is MS Access.

Using following code I open database connection,

dbase2_A1 = db.OpenEx("Driver={Microsoft Access Driver (*.mdb)};Dbq="+Registry.strPath_A1[0]+".mdb;Uid=Admin;Pwd=;")

Registry.strPath_A1[0] is string of my database path.

db.Execute(dbase2_A1,"INSERT INTO Final2(Test_ID) values ( '"+ C1_[0] +"')")
 

 it's working fine.

Now would like to check database connection ether it is open or close. 

 

[AzeoTech]

There is no way to tell if the connection is open or closed until you try a query or other command.  If that command fails, and you don't have malformed SQL, then the connection closed.  You could always do a simply query like "select now()", then catch any errors:

function checkDBConnection(dbHandle)
   try
      db.execute(dbHandle, "select now();")
      return(1)
   catch()
      return(0)
   endcatch
endfunction

This will just return 1 if the connection is active, and 0 if it fails.  Now() is a MySQL function.  I am assuming it is also available in Access, but you get the idea.  Notice how I didn't bother with using Query() as that would require DAQFactory to actually process the result.  Since we just want to check the connection we can just use execute() with a super simply query that does not depend on database schema and ignore the result of the query.

[Zeal]

Thank You.

However we are new to the use of function. So, could you explain a bit elaborately. Also, after  taking use of  the mention code, couldn't figure out where would the 0 or 1 be obtained. Also, there is no mention of the use of function in the user guide pdf.

[AzeoTech]

This is a function I just wrote to solve your problem.  It won't be in the user's guide, but everything in it is in the user's guide.  Creating a function in DAQFactory from a sequence is section 5.17.   So is the return() statement. Try/catch is described in 5.19.  db.execute() you are already familiar with, but is in 9.5.  All I am doing is calling the simplest of SQL commands (the db.execute() line).  If it is successful, the next line will run which will return a 1 from the function.  If it fails, an error is thrown which causes the script to jump to the catch() line, which returns a 0 from the function.  So, anytime you call checkDBConnection() you will get a 1 if the connection is good, and 0 if it not.  What you then do with that is up to you.  I do not recommend displaying a message box if it fails if you are checking in a loop or the operator will never be able to dismiss it.  A better solution would be to close the existing connection handle and open a new connection.