Page 1 of 1
Mysql with ADO
Posted: Fri Oct 26, 2018 6:34 am
by 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 | Expand
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
Re: Mysql with ADO
Posted: Sun Oct 28, 2018 4:13 pm
by 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.
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 4:34 am
by 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.
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 4:37 am
by nageswaragunupudi
How do you check if the connection is live or not?
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 5:12 am
by 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
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 9:00 am
by cnavarro
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 ) ?
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 9:32 am
by anserkk
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
Re: Mysql with ADO
Posted: Mon Oct 29, 2018 3:11 pm
by nageswaragunupudi
Suggested function:
Code: Select all | Expand
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 | Expand
if IsCnActive( oCn )
< ANY ACTION WITH ADO >
else
? "connection lost. Check your connection and try again"
endif
Welcome comments and suggestions.
Re: Mysql with ADO
Posted: Tue Oct 30, 2018 4:12 am
by anserkk
This looks like a good solution.
Re: Mysql with ADO
Posted: Tue Oct 30, 2018 7:06 am
by Maurizio
Thanks Rao , Anser,Navarro
I try and let you know .
Maurizio
Re: Mysql with ADO
Posted: Tue Oct 30, 2018 8:26 am
by nageswaragunupudi
Another approach
Code: Select all | Expand
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 ) } )
Re: Mysql with ADO
Posted: Fri Aug 11, 2023 10:28 am
by leandro
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: Select all | Expand
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
