?
Solved

Reconnect database through delphi windows service

Posted on 2011-10-14
13
Medium Priority
?
1,660 Views
Last Modified: 2013-11-23
Hi,

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:
ADOConnection.Close;
ADOConnection.Open;

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.
0
Comment
Question by:PallaviSaxena
11 Comments
 
LVL 38

Accepted Solution

by:
Geert Gruwez earned 2000 total points
ID: 36968335
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
 
const
  ConnectionErrors: string = '03113,03114,03135,12519,12545,12152,12154,12156,12571,12560';

function ConnectionError(ErrorMessage: string): boolean;
var
  OraError, Temp: string;
begin
  OraError := '';
  Result := False;
  if AnsiContainsStr(ErrorMessage, 'ORA-') then
  begin
    Temp := Copy(ErrorMessage, Pos('ORA-', ErrorMessage) + 4, Length(ErrorMessage));
    while (Temp <> '') and (Temp[1] in ['0'..'9']) do
    begin
      OraError := OraError + Temp[1];
      Delete(Temp, 1, 1);
    end;
    if (OraError <> '') and (Pos(OraError, ConnectionErrors) > 0) then
      Result := True;
  end;
end;

procedure ServiceThread.RunQuery;
var IsConnectionError: Boolean;
begin
  IsConnectionError := False;
  repeat
    try
      AdoConnection.Open
    except
      on E: Exception do 
      begin
        // 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;
      end;
    end;
  until not IsConnectionError;
end;

Open in new window

0
 
LVL 38

Expert Comment

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

Expert Comment

by:developmentguru
ID: 36973852
 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:PallaviSaxena
ID: 36977636
@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.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 36985375
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
http://delphi.veerle-en-geert.be/uRoot.pas

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

Expert Comment

by:senad
ID: 37036150
...stored procedure on the server itself perhaps?
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37190798
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.
0
 

Author Comment

by:PallaviSaxena
ID: 37190790
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.....
0
 

Author Comment

by:PallaviSaxena
ID: 37190799
Please do not close... I want to accept Geert's solution.
0
 

Author Closing Comment

by:PallaviSaxena
ID: 37190802
Thanks to all experts...
0
 

Author Comment

by:PallaviSaxena
ID: 37190860
Please can you let me know what will happen if it is a multiuser environment....
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Here is a helpful source code for C++ Builder programmers that allows you to manage and manipulate HTML content from C++ code, while also handling HTML events like onclick, onmouseover, ... Some objects defined and used in this source include: …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.

864 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