We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to check whether database connection is dropped?

muntasirrahman
on
Medium Priority
386 Views
Last Modified: 2010-05-02
I am using adodb (to connect my program with SQL server DB )as follows
……..
Public ConnectDatabase As New ADODB.Connection

Public Sub ConnectCurrent()
Dim ConnectionString As String
On Error GoTo errdesc

    ConnectionString = "uid=sa;pwd=;dsn=NPIL"
    ConnectDatabase.Open ConnectionString
    ConnectDatabase.CursorLocation = adUseClient
    Exit Sub
errdesc:
    MsgBox Err.Description, vbInformation, "Information"
    If ConnectDatabase.State = 1 Then ConnectDatabase.Close
    End
End Sub
…….

While running my program NT/Database connection may break, so I would like to test where database connection  is dropped or not. If dropped then again connect it.
 I have tried as follows

                  If ConnectDatabase.State = 1 Then
                    ConnectDatabase.Close
                    Call ConnectCurrent
                   End If

But after successful connection it alwas shows 1,though it has been  disconnected from database.
How to do that one, can any one help me…
Comment
Watch Question

Head of Software Services
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
State should not show 1 if when you close your database you close it like this

ConnectDatabase.Close
Set ConnectDatabase = Nothing

Author

Commented:
jmwheeler,
you are right but When it is disconnected from Db then it also shows 1
TimCotteeHead of Software Services

Commented:
jmwheeler, as I said, that is an indication of the state locally. You have changed the state by closing the database.  As the questioner rightly noted this is not the case if the connection is broken as the .state property is only refreshed when an action is taken on the client and not otherwise. There are a number of approaches to this problem, some people like to think that it should be the server's responsibility to announce that it has terminated the connection - of course that is a nonsense for practical purposes especially as we are talking about a database access layer here (ADO) which can be using everything from oracle or sql server to a flat text file as the database. So logically there is no consistent event that can be raised back to each client to inform them that a break has occured from the server's perspective. Equally the break in connection maybe a physical network layer issue that neither the client or server will know about at that time. The only way to know for certain whether a connection is valid (not just open) is to use it and trap any error that occurs.

Author

Commented:
hi TimCottee,
               You are absolutely right, I have been using your technique … .  In my own point of view, passing a query to test the connectivity is a bad practice, as I have to test it several times in each second.  
If there is no improve way to do this … I have to continue with this concept ….
Thanks a lot
TimCotteeHead of Software Services

Commented:
The only alternative is to have a "heartbeat" process that runs lets say once every ten seconds. This can be used to test connectivity in much the same way as a query would (in fact you would do exactly the same sort of thing) but as it runs only once every few seconds you can then trap the error and close the connection. This would allow you to use the .State with a little more chance of it being adStateClosed when a connection is not valid. This is of course a compromise and cannot be used as a substitute for adequate error handling in all your calls to the database.

Author

Commented:
Thx! would you mind give this idea to my open question

https://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/VB_Databases/Q_20944813.html#10774027


so that i could give you credit ...
 
 
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.