[Lazarus] Handling Firebird connection
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; -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Handling Firebird connection
Le 25/08/2014 09:32, Richard Mace a écrit : 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? 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 -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Handling Firebird connection
Antonia, Many thanks for your reply. I'll re-code my app and do some more testing. Richard On 25 August 2014 09:19, SPRL AFOR afors...@gmail.com wrote: Le 25/08/2014 09:32, Richard Mace a écrit : 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? 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. -- http://www.avast.com/ Ce courrier électronique ne contient aucun virus ou logiciel malveillant parce que la protection Antivirus avast! http://www.avast.com/ est active. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Handling Firebird connection
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 -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Handling Firebird connection
Le [...] 25/08/2014 12:13, Richard Mace a écrit : 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? 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. Thanks Richard -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus --- Ce courrier électronique ne contient aucun virus ou logiciel malveillant parce que la protection avast! Antivirus est active. http://www.avast.com -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
