Reconnect database through delphi windows service

Posted on 2011-10-14
Last Modified: 2013-11-23

I have a Delphi windows service which uses ADOConnection to connect to SQL Server 2005 database.
Some times the database need to be restarted, in this case we have to restart our service.
Now we need to avoid restarting the service , instead handle this through code.
The service code should reestablish connection with the database itself.
But we do not want to use timer or thread. Whenever a user senda a request from client side
then the service should check connection with the database and then reestablish it if not connected.

I tried this by doing

Fired an arbitary query to database, if it fails then handle the exception by doing below:

What this is doing is not serving the request that the client sent, but does it fine from
the next time but only if a select is fired. Here I do not need to restart the service.

Also if there is an update or insert fired from the client side, this does not work. Please let me know how can I restablish connection with database through service code and serve all kind of request insert update and delete.

Also there is a problem by this solution. A new user is not able to connect to the service
 through the client.
Question by:PallaviSaxena
    LVL 36

    Accepted Solution

    you have created a service and you don't want to use threads ?

    if you have created a service, then the user request is handled inside a thread
    just catch the exception on the open, examine what error it is
    and when it is a connection error, attempt the open again

    here is a more or less pseudo code sample
      ConnectionErrors: string = '03113,03114,03135,12519,12545,12152,12154,12156,12571,12560';
    function ConnectionError(ErrorMessage: string): boolean;
      OraError, Temp: string;
      OraError := '';
      Result := False;
      if AnsiContainsStr(ErrorMessage, 'ORA-') then
        Temp := Copy(ErrorMessage, Pos('ORA-', ErrorMessage) + 4, Length(ErrorMessage));
        while (Temp <> '') and (Temp[1] in ['0'..'9']) do
          OraError := OraError + Temp[1];
          Delete(Temp, 1, 1);
        if (OraError <> '') and (Pos(OraError, ConnectionErrors) > 0) then
          Result := True;
    procedure ServiceThread.RunQuery;
    var IsConnectionError: Boolean;
      IsConnectionError := False;
          on E: Exception do 
            // Examine exception message here
            // Depends on your database
            // Example oracle connection errors: 
            //   ConnectionErrors: string = '03113,03114,03135,12519,12545,12152,12154,12156,12571,12560';
            if ConnectionError(E.Message) then 
              IsConnectionError := True;
      until not IsConnectionError;

    Open in new window

    LVL 36

    Expert Comment

    by:Geert Gruwez
    you didn't post any code, so it's all guessing
    LVL 21

    Expert Comment

     I have been in this situation myself.  The problem is not happening when the open statement is called, but is happening while the connection is open and the database is restarted.  This leaves the connection in a state where it will report that it is connected when the connection has been broken.

      It is not good practice to leave your database connection open like this.  I would, instead, change the code to always close the connection when the user call finishes and always assume to open the connection when they make a new request.  This way there is a very narrow window where it would matter.  The database could be restarted and you would only notice it if it happens in the middle of the call.  Any other time it should be able to make a new connection with each call.

    Author Comment

    @Geert_Gruwez: Do i need to open connection differently on the basis of request i.e. if a select is fired or is an update is fired.

    Please can you provide some piece of code.
    LVL 36

    Expert Comment

    by:Geert Gruwez
    a select is no problem,
    a delete, update, insert etc depends on the whole transaction

    it is always best to wrap separate deletes, inserts, updates into 1 transaction to keep data consistent

    you can only open a connection and catch the errors in an exception handler
    the query executing the code can also produce errors, so you need to catch them too

    i once posted my whole unit with all this reconnect stuff built around the devart odac components

    maybe it helps you to find the ins and outs of reconnect within a thread :)
    LVL 22

    Expert Comment

    ...stored procedure on the server itself perhaps?
    LVL 100

    Expert Comment

    I've requested that this question be deleted for the following reason:

    This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

    Author Comment

    Sorry for being absent for so many days. Geert has solved my queriesto quite an extent. Thanks. Just want to know that if I accept a solution then would I be able to view it afterwards.

    Thanks to all the experts.....

    Author Comment

    Please do not close... I want to accept Geert's solution.

    Author Closing Comment

    Thanks to all experts...

    Author Comment

    Please can you let me know what will happen if it is a multiuser environment....

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    The viewer will learn how to use NetBeans IDE 8.0 for Windows to connect to a MySQL database. Open Services Panel: Create a new connection using New Connection Wizard: Create a test database called eetutorial: Create a new test tabel called ee…
    THe viewer will learn how to use NetBeans IDE 8.0 for Windows to perform CRUD operations on a MySql database.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now