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

[Luca Olivetti via Lazarus]

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

[Michael Van Canneyt via Lazarus]

Post by Luca Olivetti via Lazarus
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.
--

[Luca Olivetti via Lazarus]

Post by Michael Van Canneyt via Lazarus

Post by Luca Olivetti via Lazarus
Is there a way to force the closing of a connection in this case so I
can try and reopen it again?

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

[Michael Van Canneyt via Lazarus]

Post by Luca Olivetti via Lazarus

Post by Michael Van Canneyt via Lazarus

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

TheDatabase.Close(True);

PQConnection1: connection pointer is NULL
(PostgreSQL:)
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.
--

[Luca Olivetti via Lazarus]

Post by Michael Van Canneyt via Lazarus
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 via Lazarus]

Post by Luca Olivetti via Lazarus

Post by Michael Van Canneyt via Lazarus
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