We help IT Professionals succeed at work.

Using Delphi EX2, How can I monitor the availability of SQL server connection from my application

1,005 Views
Last Modified: 2012-02-14
My application must have an on-line and an off-line mode.  I want to use a timer that call a function (attached) which checks for the connectivity of an SOL server every 15 seconds.  If the server is or becomes available for connection, the connection must be made to the SQL server, a small panel must change to green and the online mode must become active and if the server becomes unavailable the panel must turn red and the application must go in off line mode.

Problem 1
Using my attached function, when the application starts and the QSL server is not available, e.g. the user is out of the office, then the application hangs while  
Try
      ADOConnection1.Connected := True;
tries to establish the connection, instead of just carrying on and setting the panel to Red.

Problem 2
If the server is initially available, the connection is made flawlessly.  However when the server then gets disconnected, the program remains in Online mode and the panel remains green.  Naturally then if the program executes a query, an exception is raised.

How can I do this differently to get it to work as described? Thanks
SQL-Connect-Function.txt
Comment
Watch Question

Commented:
Try setting TADOConnection.ConnectionTimeOut to a lower value.

Any changes in behavior????

If not, then try to install latest Microsoft MDAC (Microsoft Data Access Components)...
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
We seem to be moving the right direction but there are still problems.  I initially got an OLeException that SYSDATE is not a recognised built in function name.  I replace SYSDATE with GETDATE which works.

When the server is disconnected, the first time the timer triggers, the panel goes red as expected, but on the second time the timer triggers I get the error message as per attachment.  Upon singel step through the program, I find the when the steps get to:
 Try
      ADOConnection1.Connected := True;
      if ADOConnection1.Connected = True Then ...

the program breaks out of the timer procedure and the exception is not handled and the mentioned error message is raised.
SQL-Error.png

Commented:
Are you running your program from outside or inside the IDE?

Try...except without exception message only works when program is started from outside the IDE!

When running your program from inside the IDE, error message is displayed, execution is paused and Delphi begins to debug the program where the exception occured.

When you press Run again the program will continue running at the point it had stopped and that should include the except clause to be executed...

By the way, there is a little error in my code. Certainly after line ADOQuery1.ExecSQL it must be clLime not clRed:

ADOQuery1.ExecSQL;
SQLConnceted.Color := clLime;
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Oh yes, I forgot to mention that I saw and fixed the clRed meant to be clLime.

Yes, I tried the program outside the IDE and it does exactly the same, hence when running inside the IDE I thus get two errors, the first being the Debugger Exception Notification and on clicking the Continue button the same error as outside the IDE and as per my previous screen print pops up.

Commented:
Can you send me your connection string???
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
Further comments
1. You should consider using a Thread instead of a TTimer
2. Set the connection string and sql query once, no need to keep reassigning values that never change

Author

Commented:
ewangoya, thanks for your comment.  I think I am a bit confused with the new Experts Exchange as I was under the impresion that I already awarded the points to Thommy and closed the question, but now it appears as if the Close Request is pending.

However, I do agree with setting the connection string once and have done so for the connetion string.  As far as the SQL Query goes, I was under the impresion that one has to actually excecute the query every time in order to test for the presence of the SQL server.

I like your idea of a Thread instead of a Timer but I havn't muchexperience in using threads.  Hence, is there a asimple way to set up the additional thread to look after the SQL connection check in place of the simple timer?  If there is, I will raise a separate question for this.

Author

Commented:
Thommy, thank you for all your input which helped me get my mind towards the solution.  The procedure became a bit clumsy so I reconstructed it as shown below and it is now working 100%.  I think the final trick was to encaptulate the entire process inside the Try Except block.

procedure TMainFrm.SQLConnectTimerTimer(Sender: TObject);
begin
  Try
    if ADOConnection1.Connected = False Then
    Begin
      ADOConnection1.ConnectionString := SQLConncetionStr;
      ADOConnection1.Connected := True;
    End
    else
    Begin
      ADOQuery1.Connection:=ADOConnection1;
      ADOQuery1.Close;
      ADOQuery1.SQL.Text := 'SELECT GETDATE()';
      ADOQuery1.ExecSQL;
      SQLConnceted.Color := clLime;
    End;
  Except
    ADOConnection1.Connected := False;
    SQLConnceted.Color := clRed;
  End;
end;

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.