Hi all,

I have the following code that I use to connect to a Firebird database, which works fine, however, if the Firebird server becomes unavailable, fIBConnection.Connected always returns true. What's the best way of checking to make sure that the Firebird server is still available before I attempt to connect to the database?

Thanks in advance

Richard

  fOnline := True;
  try
    if fIBConnection.Connected then
      Exit;
        with fIBConnection do
          begin
            HostName     := 'hostname';
            DatabaseName := 'location';
            UserName     := 'username';
            Password     := 'password';
          end;
      end;
    fIBConnection.Transaction  := fSQLTransaction;
    with fSQLQuery do
      begin
        DataBase := fIBConnection;
        Transaction := fSQLTransaction;
      end;
    //Finally switch on connection
    fSQLTransaction.DataBase := fIBConnection;
    fIBConnection.Connected := True;
  except
    fOnline := False;
  end;
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140825/c2e44f11/attachment.html>

Le 25/08/2014 09:32, Richard Mace a ?crit :

AFAIK the TIbConnection is based upon TSQLConnection itself based upon the TDatabase component. In all this chain the Connected property is defined in the latest component, the TDatabase. And the Connected property has a read function wich sends a boolean variable content (FConnected variable). That's why once the first connection has been established with the Firebird server (whether is succeeds or not), the connection status is kept thru all component life until next disconnect or re-connect operation takes place.
To solve the situation when the link to the databse server is broken I rely on the transaction mechanism. In fact I always control the transaction state allowing any query to be executed, as the Firebird server requires that any database access has to be done under the control of a transaction. This means thar when the MyTrans.StartTransaction fails I know (most of the times) that the link to the FB server is broken (omitting programmatic errors which can be controlled or checkced in another way). This does not protect your code from a connection exception between two consecutive database access (two consecutive calls to the fbclient library anyway). When I need a fine tuned control over a database access, after a initial MyDB.Connected := True, I always use the same sequence:

     try
         MyDatabase.Connedted := True;
     except
         on ...(exception process) do ... connection fault
     end
     [...] prepare data for the database
     try
         MyTransaction;StartTransaction
         try
             do whatever has to be done with the database
         finally
             MyTransaction.Commit
         end
     except
         do whatever needed when the exception rises: check whether its
is an ordinary exception or a server link fault
         if MyTransaction.InTransaction then
             MyTransaction.RollBack;
     end;

This sequence does not protect against the server disconnection between two consecutive database calls. I easily can imagine that the same process occurs with any other database server hosted in a remote machine. Even when the database server is hosted in the same machine of the applicaton, TCP/IP communication applies.

Antonio.

---
Ce courrier ?lectronique ne contient aucun virus ou logiciel malveillant parce que la protection avast! Antivirus est active. http://www.avast.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140825/a2c2e111/attachment.html>

Antonia,
Many thanks for your reply.
I'll re-code my app and do some more testing.

Richard

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140825/d916c754/attachment.html>

?Hi Antonia?,

This sequence does not protect against the server disconnection between two consecutive database calls.

?Would you mind giving me an example of this?

Thanks

Richard

?
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140825/02984fd3/attachment.html>

Le
[...] 25/08/2014 12:13, Richard Mace a ?crit :

What I mean is that the physical TCP/IP connection between the aplication (the client) and the firebird server is not systematically tested on every function call to the library accessing the firebirs server. If you assume the following sequence of operations:

    MySql.Database := MyDatabase;
    [...]
    MyDatabase.Connect;
    (1)
    Mysql.Sql.text := 'select any from thedatabase';
    MyTransaction.StartTransaction;
    (2)
    Mysql.Open:
    (3)
    while Mysql.EOF = False do begin
         // do whatever to do with database records
         Mysql.Next
    (4)
    end;
    (5)
    Mysql.close;
    (6)
    MyTransaction.Commit;
    (7)
    MyDatabase.Connected := False;

After each statement noted (1) thru (7) the TCP/IP link initiated by the MyDatabase.Connect can be broken anywhere betwenn (1) and (7) unless you code something to test it. This can be any function looking something like a:
telnet host_name(or IP address) 3050 (or whatever firebird port is used in host) issued in in a command line.
In a Lazarus program you would need something like Indy ping component (any other is also OK) wich will test the firebird server awakeness. Its is really a heavy workloa anyway.
I usually keep going with my former exemple. When the server connection is critical I fill in the except part some code to figure out witch is the failing part using either the exception code or the exception text itself. I can also specialize the different exception sources using the On MyException:Exception do
In the top example of code I'll insert try..except's (or finally's if I don't care about exceptions) around the critical places I can't miss. But this depends on the application as well

Antonio.

---
Ce courrier ?lectronique ne contient aucun virus ou logiciel malveillant parce que la protection avast! Antivirus est active. http://www.avast.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lazarus.freepascal.org/pipermail/lazarus/attachments/20140825/19ffec80/attachment.html>