Mysql with ADO

[Maurizio]

Hello Rao

I use Mysql via ADO with the functions of adofuncs.prg

when the program stays idle for a while, with the reuse I have this error

Error FW_OpenRecordSet(440)

Code: Select all

function FW_OpenRecordSet( oCn, cSql, nLockType, nCursorType, nMaxRecords, nOpt )
.
.

CATCH
         if ! Empty( oCn )
            FW_ShowAdoError( oCn )       <-------- Line 440
         endif 
         oRs   := nil
      END

I think the problem is with the timeout of Mysql .
I saw that this problem was solved with MariaDB ,
Is it possible to solve this problem in ADO (adofuns.prg ) ?

Thanks
Maurizio

[nageswaragunupudi]

Our friend Mr. Anser also uses MySql with ADO. He developed his own technique to handle the timeout issues. It would be better if Mr. Anser explains his approach.

[anserkk]

Yes, we will definitely will face this issue at one point of time, especially when we access the database via Internet. At times, the internet connectivity in the client PC may break. It may be for few seconds or for few minutes, or hours and the internet connectivity is resrored automatically.

There are few methods, like increasing the wait_timeout session variable etc.

I followed the following method which is working fine for me for many years

All my database queries passes thru a single function that I have created. This function will check the present connectivity status, If the connectivity is fine, then proceed with the query, OR else it will reconnect to the database and then process the query. Then return the result set.

I use Stored procedures extensively for insert/update/delete etc. I make use of the above said function to handle such situations too.

[nageswaragunupudi]

How do you check if the connection is live or not?

[anserkk]

I check whether the connection object is NIL or not
if not NIL, then I check whether the DefaultDatabase property (available in the connection object) is empty or NOT, if empty, then I understand that for some reason, the connection with the database was lost in-between and need to reconnect. Then I call the function to Connect to the Database

[cnavarro]

I check whether the connection object is NIL or not
if not NIL, then I check whether the DefaultDatabase property (available in the connection object) is empty or NOT, if empty, then I understand that for some reason, the connection with the database was lost in-between and need to reconnect. Then I call the function to Connect to the Database

Hello Anserk
And, it is not valid and sufficient to verify that oCn:State > 0 ( or oRs:State > 0 ) ?

[anserkk]

Hello Anserk
And, it is not valid and sufficient to verify that oCn:State > 0 ( or oRs:State > 0 ) ?

If I am not wrong, oCn:State was giving me wrong results even if the internet was disconnected. Internet may get disconnected and get connected again (especially low cost broadband connections), may be in few seconds it gets reconnected again without any user intervention, but if you try to do some operation on the database, even after the connectivity is re-established, it was giving problems. May be the MySQL server will not allow any operation by a client that got disconnected in between

[nageswaragunupudi]

Suggested function:

Code: Select all

function IsCnActive( oCn )

   if oCn:State == 0 .or. Empty( oCn:Properties( "Current Catalog" ):Value )
      oCn:Close()
      oCn:Open()
   endif

return oCn:State > 0
 

This function returns if the connection is still active or not. In case connection is lost for some reason, this function tries to reconnect before returning the result.

Usage:

Code: Select all

if IsCnActive( oCn )
    < ANY ACTION WITH ADO >
else
    ? "connection lost. Check your connection and try again"
endif
 

Welcome comments and suggestions.

[anserkk]

This looks like a good solution.

[Maurizio]

Thanks Rao , Anser,Navarro

I try and let you know .

Maurizio

[nageswaragunupudi]

Another approach

Code: Select all

function AdoExecute( oCn, bAction )

   local uRet
   
   if oCn:State == 0 .or. Empty( oCn:Properties( "Current Catalog" ):Value )
      oCn:Close()
      oCn:Open()
   endif
   if oCn:State > 0
      TRY
         uRet  := Eval( bAction, oCn )
      CATCH
         FW_ShowAdoError( oCn )
      END
   else
      ? "Connection Lost. Check your connection"
   endif

return uRet


//Usage:

oRs      := AdoExecute( oMyConObject, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )
uResult  := AdoExecute( oMyConObject, { |oCn| oCn:Execute( cSql ) } )