• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1789
  • Last Modified:

Reconnect database through delphi windows service

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
PallaviSaxena
Asked:
PallaviSaxena
1 Solution
 
Geert GOracle dbaCommented:
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
 
Geert GOracle dbaCommented:
you didn't post any code, so it's all guessing
0
 
developmentguruPresidentCommented:
 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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
PallaviSaxenaAuthor Commented:
@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
 
Geert GOracle dbaCommented:
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
 
senadCommented:
...stored procedure on the server itself perhaps?
0
 
mlmccCommented:
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
 
PallaviSaxenaAuthor Commented:
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
 
PallaviSaxenaAuthor Commented:
Please do not close... I want to accept Geert's solution.
0
 
PallaviSaxenaAuthor Commented:
Thanks to all experts...
0
 
PallaviSaxenaAuthor Commented:
Please can you let me know what will happen if it is a multiuser environment....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now