How can I check if connection is active?

[Soner]

Hello,
in my program I have active TZConnection with Firebird datebase, then I connect with TFBAdmin to the Database server to change password from other user. When I do this TZConnection looses the connection, but the program does not know about it, ZConnection1.Connected is true and ZConnection1.Ping is also true. Now when I execute a sql command then TZConnection gives error:
"SQL Error: Invalid database handle (no active connection)..."
How can I check success full if then connection is really active?





I use Lazarus 1.6.4, fpc 3.0.2, Zeos 7.2.1-Svn 3951, Firebird 2.5 Database

[marsupilami]

Hello Soner,

hmmm - currently the Firebird driver doesn't support pinging the server. But usually you should get an exception telling you that this operation is not supported...

But: Changing the password of a user should not kill other connections. Maybe it is a good idea to use Zeos for changing the password using the corresponding SQL from Firebird 2.5 (See corresponding part of Firebird 2.5 Language Ref. Update):

ALTER USER username
[PASSWORD 'password']
[FIRSTNAME 'firstname']
[MIDDLENAME 'middlename']
[LASTNAME 'lastname']
[{GRANT|REVOKE} ADMIN ROLE]

-- At least one of the optional parameters must be present.
-- GRANT/REVOKE ADMIN ROLE is reserved to privileged users.

With best regards,

Jan

[Soner]

This problem is resolved with version 7.2.4.
If someone wants to check if firebird-server is available then use ping-funcktion like this:

Code: Select all

 if ZConnection1.Ping then
   Writeln('Connection exists');
 //ZConnection1.Connected  is true, even when connection lost. 
   

(Only tested with Firebird 2.5, Windows)

[aehimself]

Just keep in mind that not all mainstream database engines support the .Ping function. (yet?)
When I was developing an application which had to be able to communicate with ANY type of SQL server I built my own keepalive (NOOP) function by executing a simple query:
SELECT 1;
If an exception is raised - no connection. I agree that .Ping would be easier (and feels less hacky!) but the custom noop will always produce accurate results, regardless of the backend.

[marsupilami]

Hello,

select 1; will also not work on all databases. Firebird for example will expect a table name. Oracle does the same if I remember correctly. This is why the ping function in Zeos needs to be implemented in a driver specific fashion. It is just that for some drivers nobody cared to implement it. Contributions are welcome.
Best regards,

Jan

[Fr0sT]

"SELECT 1" is non-standard and every DBMS has its own syntax for selecting a constant. Moreover this dummy select consumes more resources (starts/uses a transaction, creates resultset etc) than probably other driver-specific pinging methods. F.ex., FB pings by requesting DB info which doesn't require transaction and creating a resultset.

[aehimself]

Moreover this dummy select consumes more resources (starts/uses a transaction, creates resultset etc) than probably other driver-specific pinging methods. F.ex., FB pings by requesting DB info which doesn't require transaction and creating a resultset.

This. I wish I'd have this knowledge about all databases; what action will be the quickest / smallest impact! If I would; I would have written a solution before, not a workaround

The app I made was only used on MySQL, MSSQL and PostgreSQL at the end and all seemed to support the constant query just fine. Maybe it was not the most resource-friendly solution; but fortunately I implemented a timer which was reset after any database interaction. This way the dummy only had to execute when nothing had anything to do with the database for 2-3 minutes.

[Fr0sT]

If your solution is executed from time to time that's just fine; resource consumption is very low . Dummy select as a concept is the most simple and general solution though on the implementation level it will require different syntax for different drivers. That's why ZConnection.Ping is implemented personally for each driver.