Hello

I have two questions: one about a better way of checking whether a connection really is open, and one about an error numner.

I found that if an ADODB.Connection is opened, and then subsequently it is dropped for whatever external reason (e.g. server is disconnected, or the machine goes to sleep and then later is woken up), the connection object's status property remains adStateOpen, so you cannot use it to check whether the connection is really open - it tells lies.

Some greatly simplified code to give a rough idea:
Code:
Private m_cn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_cmd As ADODB.Command

Private Sub openConn()
    Set m_cn = New ADODB.Connection
    m_cn.CommandTimeout = 30
    m_cn.ConnectionString = "DRIVER={MySQL ODBC 5.3 UNICODE Driver};SERVER=192.168.1.42;DATABASE=db;UID=user;PWD=pass;PORT=3306;OPTION=3"

    Call m_cn.Open

    Set m_cmd = New ADODB.Command
    m_cmd.ActiveConnection = m_cn
    m_cmd.CommandType = adCmdText
End Sub

Public Function executeQuery(sql As String) As Boolean
    If Not m_cn Is Nothing Then
        If m_cn.State = adStateOpen Then ' Cannot be trusted to really be open
            If Not m_cmd Is Nothing Then
                m_cmd.CommandText = sql
                Set m_rs = m_cmd.Execute ' Will fail here if anything goes
                <error handling here, closing the connection and opening a new one if needed>
            End If
        End If
    End If
End Function

Public Function getFoo(id As Integer) As String
    Call executeQuery("SELECT blabla id blabla")
    If Not m_rs.EOF Then
        getFoo = m_rs("bar")
    End If
End Function
The connection is not closed after every query - that would be crazy-inefficient. The connection is reused as long as possible, which typically means for the whole day, unless the connection is broken for external reasons.

Question 1: How can one check whether the connection really is up, without wasting time and bandwidth, without unnecessarily opening new connections?

The way I currently do it is that I have an "On Error Resume Next" before "m_cmd.Execute", and if it catches error -2147467259 (0x80004005, "Lost connection to MySQL server during query" and "MySQL server has gone away") then it calls closeConn() and openConn() which reestablishes a new connection. It's good - the connection is reused as long as possible, a new one is opened only when necessary. It's light, it's fast, it can send thousands of queries per second. What I don't like is that I'm relying on a specific error number, and I don't know whether that number is unique to a broken connection, or whether it could mask other errors which I would like to be able to distinguish, e.g. unhandled returned NULL values, or errors in SQL, etc.

Question 2: What is error -2147467259 0x80004005?
I googled and couldn't find an answer - it seems that that number is used for a wide variety of unrelated problems. What's special about that number?