topic-36268

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Mysql with ADO
Posted: Fri Oct 26, 2018 06:34 AM

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 (fw): Select all Collapse

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

Maurizio
www.nipeservice.com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql with ADO
Posted: Sun Oct 28, 2018 04:13 PM

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.

Regards

G. N. Rao.

Hyderabad, India

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 04:34 AM

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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 04:37 AM

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

Regards

G. N. Rao.

Hyderabad, India

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 05:12 AM

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

Posts: 6755

Joined: Wed Feb 15, 2012 08:25 PM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 09:00 AM

anserkk wrote: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 ) ?

Cristobal Navarro

Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo

El secreto de la felicidad no está en hacer lo que te gusta, sino en que te guste lo que haces

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 09:32 AM

cnavarro wrote: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

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql with ADO
Posted: Mon Oct 29, 2018 03:11 PM

Suggested function:

Code (fw): Select all Collapse

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 (fw): Select all Collapse

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

Welcome comments and suggestions.

Regards

G. N. Rao.

Hyderabad, India

anserkk

Posts: 1335

Joined: Fri Jun 13, 2008 11:04 AM

Re: Mysql with ADO
Posted: Tue Oct 30, 2018 04:12 AM

This looks like a good solution.

Maurizio

Posts: 842

Joined: Mon Oct 10, 2005 01:29 PM

Re: Mysql with ADO
Posted: Tue Oct 30, 2018 07:06 AM

Thanks Rao , Anser,Navarro

I try and let you know .

Maurizio

Maurizio
www.nipeservice.com

nageswaragunupudi

Posts: 10733

Joined: Sun Nov 19, 2006 05:22 AM

Re: Mysql with ADO
Posted: Tue Oct 30, 2018 08:26 AM

Another approach

Code (fw): Select all Collapse

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 ) } )

Regards

G. N. Rao.

Hyderabad, India

leandro

Posts: 1816

Joined: Wed Oct 26, 2005 02:49 PM

Re: Mysql with ADO
Posted: Fri Aug 11, 2023 10:28 AM

Hola buenos días,

Retomando este tema, estamos usando el código que alguna vez publico Mr. Rao, para poder hacer la reconexión remota a las bases de datos de mysql vía ADO. Pero desde hace unos días cuando actualizamos la versión FWH2307, nos esta devolviendo varios errores, los cuales publico aqui para ver si alguien a podido realizar la reconexión con las bases de datos, o si ya existe una función con ADO que se encargue de la reconexión.

Code (fw): Select all Collapse

Function AdoExecute( oCn, bAction ) //Aporte Mr.Rao Foro FW
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
    msginfo("Conexión Perdida, imposible recuperar","Error de conexión")
endif
//Usage:
//oRs      := AdoExecute( oMyConObject, { |oCn| FW_OpenRecordSet( oCn, cSql ) } )
//uResult  := AdoExecute( oMyConObject, { |oCn| oCn:Execute( cSql ) } )
return uRet

Saludos
LEANDRO AREVALO
Bogotá (Colombia)
https://hymlyma.com
https://hymplus.com/
leandroalfonso111@gmail.com
leandroalfonso111@hotmail.com

[ Turbo Incremental Link64 6.98 Embarcadero 7.70 ] [ FiveWin 25.01 ] [ xHarbour 64 bits) ]

FiveTech Support Forums

Continue the discussion