[Lazarus] Is there a way to reconnect to postgresql if the connection fails?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list
Hello,

I'm trying to devise a strategy for when the connection to the server fails.
I'm using zeos but I also tried with sqldb and I couldn't find a way.
I open a connection to the server, then I stop the server and subsequent
operations (e.g. refreshing a query) fail (obviously since the
connection has been severed).
However, for the same reason, I cannot close the connection (with sqldb
by setting PQConnection.connected:=false or with zeos by calling
ZConnection.Disconnect), since it raises an exception :-(
With zeos I tried to get hold of the underlying handle and call PQReset
before closing the connection.
The PQreset works, but the connection cannot be closed because it tries
to release prepared statements that don't exist in the new connection so
postgresql returns an error.....

Is there a way to force the closing of a connection in this case so I
can try and reopen it again?

TIA
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list


On Tue, 15 May 2018, Luca Olivetti via Lazarus wrote:

> Hello,
>
> I'm trying to devise a strategy for when the connection to the server fails.
> I'm using zeos but I also tried with sqldb and I couldn't find a way.
> I open a connection to the server, then I stop the server and subsequent
> operations (e.g. refreshing a query) fail (obviously since the
> connection has been severed).
> However, for the same reason, I cannot close the connection (with sqldb
> by setting PQConnection.connected:=false or with zeos by calling
> ZConnection.Disconnect), since it raises an exception :-(
> With zeos I tried to get hold of the underlying handle and call PQReset
> before closing the connection.
> The PQreset works, but the connection cannot be closed because it tries
> to release prepared statements that don't exist in the new connection so
> postgresql returns an error.....
>
> Is there a way to force the closing of a connection in this case so I
> can try and reopen it again?

Try using forcedclose in SQLDb:

TheDatabase.Close(True);

Michael.
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list
El 15/05/18 a les 20:44, Michael Van Canneyt ha escrit:

>>
>> Is there a way to force the closing of a connection in this case so I
>> can try and reopen it again?
>
> Try using forcedclose in SQLDb:
>
> TheDatabase.Close(True);

Project project1 raised exception class 'EPQDatabaseError' with message:
PQConnection1: connection pointer is NULL
(PostgreSQL:)

In file 'fcl-db/src/sqldb/postgres/pqconnection.pp' ad line 725:
raise E;


TDatabase.DoDisconnect calls CloseDatasets which in turn calls
Unprepare (in TCustomSQLQuery.SetActive)->
FStatement.Unprepare->
DoUnprepare->
Database.UnPrepareStatement(FCursor)->
TPQConnection.UnprepareStatement which eventually fails.

Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list


On Tue, 15 May 2018, Luca Olivetti wrote:

> El 15/05/18 a les 20:44, Michael Van Canneyt ha escrit:
>
>>>
>>> Is there a way to force the closing of a connection in this case so I can
>>> try and reopen it again?
>>
>> Try using forcedclose in SQLDb:
>>
>> TheDatabase.Close(True);
>
> Project project1 raised exception class 'EPQDatabaseError' with message:
> PQConnection1: connection pointer is NULL
> (PostgreSQL:)
>
> In file 'fcl-db/src/sqldb/postgres/pqconnection.pp' ad line 725:
> raise E;
>
>
> TDatabase.DoDisconnect calls CloseDatasets which in turn calls
> Unprepare (in TCustomSQLQuery.SetActive)->
> FStatement.Unprepare->
> DoUnprepare->
> Database.UnPrepareStatement(FCursor)->
> TPQConnection.UnprepareStatement which eventually fails.

I will need to check this, but the purpose of Close(True) is exactly to
ignore errors while closing for a reconnect.
It was introduced and tested in the case of MySQL, but
normally should work on any database.

Can you please post a small sample program in the bugtracker ?

Michael.
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list
El 15/05/18 a les 21:11, Michael Van Canneyt via Lazarus ha escrit:

> I will need to check this, but the purpose of Close(True) is exactly to
> ignore errors while closing for a reconnect. It was introduced and
> tested in the case of MySQL, but
> normally should work on any database.
>
> Can you please post a small sample program in the bugtracker ?

Done

https://bugs.freepascal.org/view.php?id=33737

Thank you

--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus
Reply | Threaded
Open this post in threaded view
|

Re: [Lazarus] Is there a way to reconnect to postgresql if the connection fails?

Free Pascal - Lazarus mailing list
El 15/05/18 a les 21:43, Luca Olivetti via Lazarus ha escrit:

> El 15/05/18 a les 21:11, Michael Van Canneyt via Lazarus ha escrit:
>
>> I will need to check this, but the purpose of Close(True) is exactly to
>> ignore errors while closing for a reconnect. It was introduced and
>> tested in the case of MySQL, but
>> normally should work on any database.
>>
>> Can you please post a small sample program in the bugtracker ?
>
> Done
>
> https://bugs.freepascal.org/view.php?id=33737

Using the same project as in the bug report, almost every time I set the
"Active" property of the SQLQuery1 component to true in the object
inspector I get an "Access violation" and the selection tree in the
object inspector gets messed up.
It doesn't happen if I use zeos.
I tried both with the original fcl-db and the one modified as my last
message in the bug report (I rebuild lazarus every time I changed fcl-db).
Fpc 3.0.4/lazarus 1.8.2, win32 and linux (64 bits)/qt

The access violation happens inside
TComponentTreeView.UpdateComponentsNodesValues, UpdateComponentNode,
line "if APersistent is TComponent", when Anode is the first
TSQLDBFieldDef in the sqlquery


Bye
--
Luca Olivetti
Wetron Automation Technology http://www.wetron.es/
Tel. +34 93 5883004 (Ext.3010)  Fax +34 93 5883007
--
_______________________________________________
Lazarus mailing list
[hidden email]
https://lists.lazarus-ide.org/listinfo/lazarus