ADO handling disconnections

[markphsd]

Hi,

I would like to know how to determine if the connection object is valid, before using it.

Invalid wouldn't necessarily mean, adStateClosed, because i can turn off my wireless device on my computer and ado won't recognize this. Then when i try to open a recordset i get an error.

I believe need some kind of withevents for a change of the connection objects state, it doesn't seem to recognize shutting off the wireless as a change in state. Also, the object is still not nothing after turning off the device. So I don't know!

HELP
thanks

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[genomon]

Probably isn't this simple, but can you try openeing the connection first and trapping the error it throws if it's no good?

I wondered why the baseball was getting bigger.
Then it hit me.

 

[markphsd]

Thanks for pitching in an idea.

What i have is a global connection object. It's left through most of the lifetime of the application instance. Opening and closing is slow, so i leave it open.

My problem is verifying it's valid once it's open.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[genomon]

the object is still not nothing after turning off the device

The connection object still occupies a memory address, but connects to "nothing". If the source of the connection is lost, the object is still in the open state. Just want to make sure I understand. There may be some ADO code floating around to test for this using the command object & ActiveConnection property but not familiar with the syntax;
probably some API calls as well. Just floating a couple thoughts, as I want to keep track of this thread & see what you find. Sorry can't be of more help, but too busy to research more right now. Good Luck! I'll see if I can do some more homework & check back again tomorrow...




I wondered why the baseball was getting bigger.
Then it hit me.

 

[markphsd]

I can do something outside of the Connection object to reasonably accommodate for a broken connection, such as api call. However I was hoping that there would be a method within the connection object. It would reduce my work, and probably make for a more stable application.

Obviously .state does not work for what I'm dealing with.
thanks

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[BobRodes]

What's the back end database you're using, specifically what OLE DB provider are you using?

Bob

 

[ArtieChoke]

According to my ADO book, there is no way within ADO to detect a dropped connection. His suggestion was to send a "select 1" command and see if it was successful, since it doesn't take a lot of bandwidth.

Another way is to create a class to handle the recordset and always check for connectivity and reconnect if desired.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[markphsd]

thanks,

I'm not only concerned about bandwidth, but also speed.

I need the check to be fast, so I'd rather not be checking all the time even with a SELECT 1 recordset. However, that could be a good option for users on VPN, I'll just force it to check the connection object before using it.

The other side would be wireless networks, which are very fast now with the application. However, they do sometimes suffer from connection breaks. Additional checks are a lag that customers wouldn't be happy with.

database is SQL Server

thanks.

Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[ArtieChoke]

As long as you trap the recordset error you could check if you're disconnected, then reconnect at that point. I don't recall what the error code is, but it would be easy enough to set up your connection, then kill it at the database and see what error you get back.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[JoeAtWork]

If you have a centralized error handler, you can check for the specific error numbers that indicate the global connection is lost. At that point, you can attempt to reconnect, and if that doesn't work handle the "lost database connection" error appropriately.

 

[BobRodes]

I did a little looking around in the OLE DB classes. Maybe ADO can't detect it, but perhaps the SQL OLE DB provider can raise an error?

 

[JoeAtWork]

I would like to know how to determine if the connection object is valid, before using it

I don't think that's an optimum strategy. As per my previous post, I think you will get the most efficient code by always assuming the connection object is valid, and then just trap the disconnection error(s) in the individual procedures that use the connection object.

What I often have is a public function like ReconnectToDB() where it attempts to reopen the connection. The function returns True if the reconnection was successful, otherwise False.

Any type of code that verifies the connection is still open every time you use it is going to add unnecessary overhead to your code. Let's say the connection is valid 90% of the time, that means 90% of the time you are making unneccessary extra calls to the database.

A good error handling strategy is your best option, in my opinion.

 

[BobRodes]

I agree with Joe's strategy. It's more efficient to use error traps than it is to check connections. Connections are one of the most expensive resources out there.

 

[genomon]

My first thought as well!

I wondered why the baseball was getting bigger.
Then it hit me.

 

[markphsd]

I do have a global error handler, and had always thought that would be my next best solution.

I could put in a response, however I hadn't yet found out if there were various responses that I would have accomadate for.

I was hoping someone would know of a WithEvents code snippet for the connection object. That would be the most convenient option. But since the object doesn't have any properties to test for, I guess there would be no snippet.

I use a function that gets the connection GetCon its like this kind of: So the reconnect would be the same, if i would just know when to set the cn = nothing

Private cn as adodb.connection

Public function GetCon() as adodb.connection
if cn is nothing then
set cn = new adodb.connection
cn.connectionstring = GetConString
cn.open
end if
if not cn is nothing then
set getcon = cn
end if
end function



Mark P.

http://www.cafecartel.com

Providing Low Cost Powerful Point of Sale Solutions.

 

[BobRodes]

Well, you could check the disconnect event of the Connection object, but from what you're saying, you may well find that it doesn't fire because the object thinks it's still connected.

 

[JoeAtWork]

I really doubt there would be an Event that would notify you of a disconnection. The only way it could do that is if it constantly queried the database to see if the connection was still valid. I doubt that even a Microsoft programmer would add such huge overhead to provide this "convenience" for the UI programmer.

 

[BobRodes]

<I really doubt there would be an Event that would notify you of a disconnection.

Try this:

Option Explicit
Dim WithEvents cn As ADODB.Connection

Private Sub cn_Disconnect(adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox "disconnected"
End Sub

Private Sub Form_Load()
Dim strCn As String
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\Microsoft Visual Studio\VB98\nwind.mdb"
Set cn = New ADODB.Connection
cn.Open strCn
cn.Close
End Sub

Now, I know that you're talking about something a bit different, Joe. But my point is that the provider can notify the consumer that the connection is down, if they are set up to do it. (I know that in my code the consumer context closed the connection, but the event doesn't fire until the connection actually closes.) For example, if you have a connection object in, say, an MTS/COM+ wrapper, that is holding open a connection to a database on another machine, if the DBA manually closes that connection the object will raise the Disconnect event.

So, I suspect that the wireless connection isn't something that the provider context is aware of, so it isn't aware that the connection is actually closed when the wireless drops its connection.

However, as I mentioned before, the OP could attempt to use the Disconnect event and see what happens.

Bob

 

[JoeAtWork]

Doing a 15 minute Google search on the Disconnect event gave me surprisingly very little information. Most of the articles gave a short one-line explanation along the lines of "occurs when disconnecting from the database". The scarcity of information on this event suggests it doesn't have much practical value (otherwise, one would expect it to show up in "best practices" articles and the like).

Even if it did detect unplanned breaks from the database, I think you would still need to back that up with an error handler.

 

[SBerthold]

This same problem exists with removable devices holding flat data files or local databases and using an ADODB Connection object on them.

With these you can usually just use the connection's WillExecute event to to capture the mentioned ADODB connection errors, or even first verify in the event if the path (DIR/FSO) is still valid, and then cancel the calling object's request (using the adStatus argument) if the device (and file name) is not available.

The advantage of using this event is that all executions (connection.execute, recordset opens, Command object executes) will go through this events.

If the source is not available, the Conn.Errors listing and it's error number or SQLState property should shows this prior to actually performing the Exectue/Open.

After cancelling the action using adStatus, you will also need an error handler in the Recordset.Open routine (should be one there anyways) to capture the simple error that is raised because the execution action was cancelled.

Maybe this will trigger some other ideas for you.


(Side note: This usually works simple and easy.
But another problem may arise, with removable devices and ADODB connections: After the actual device connection is lost and re-established, the ADODB connection object its self may still need to be closed and re-opened, I guess because the device ID may have changed.)