Results 1 to 23 of 23

Thread: How to efficiently check if ADODB.Connection is really open

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    How to efficiently check if ADODB.Connection is really open

    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?

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How to efficiently check if ADODB.Connection is really open

    The connection is not closed after every query - that would be crazy-inefficient.
    I guess I was taught the wrong thing (open, use it, close it). I never tested for efficiency but it saves on having connections dropping issues like you mentioned. I wonder if it is crazy-inefficient on the database to keep connections open all the time.
    Please remember next time...elections matter!

  3. #3
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: How to efficiently check if ADODB.Connection is really open

    It's a generic error code HRESULT, just like VBs error 5.

    Code:
    E_FAIL = 0x80004005, // Unspecified failure

    https://msdn.microsoft.com/en-us/lib...(v=vs.85).aspx
    Last edited by DEXWERX; Feb 22nd, 2018 at 10:19 AM.

  4. #4
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: How to efficiently check if ADODB.Connection is really open

    Quote Originally Posted by TysonLPrice View Post
    I guess I was taught the wrong thing (open, use it, close it). I never tested for efficiency but it saves on having connections dropping issues like you mentioned. I wonder if it is crazy-inefficient on the database to keep connections open all the time.
    The driver should be able to pool connections, so open and close away.

  5. #5
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: How to efficiently check if ADODB.Connection is really open

    Hi,

    I use it like Tyson...open - use -close

    take a look at CursorLocation
    https://dev.mysql.com/doc/connector-...osoft-ado.html

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    Unfortunately the connection pooling feature in Windows "ODBC Data Source Administrator" does not work with the MySQL connector, valid documentation on the subject is nowadays hard to find, and whatever documentation you find does not apply to the MySQL/MariaDB connector so you're left guessing. If you are aware of documentation about connection pooling using the MySQL connector for Windows XP upward and you verified that it actually works, please share.

    Closing and opening connections is slow, bandwidth-heavy wrt overhead, and eventually DOSes the server. Keeping a connection open for this program, which uses it at least once every 10 seconds and at most over a thousand times per second (which is not good and I improve the code whenever I encounter that, but it's a big program and such connection-heavy cases must be handled well), works very well.

    Sample data for 1000 requests with connection closing and reopening:
    Code:
    20624.8847ms
    Server sent:      3 028 670 bytes
    Server received:   457 431 bytes
    Could not test for 10000 queries, the server was DOS'd after 2900 queries.

    Reusing the connection:
    Code:
    1129.8635ms
    Server sent:      2 623 667 bytes
    Server received:   60 430 bytes
    The server is fine even after 10 000 queries:
    Code:
    11569.2835ms
    Server sent:      26 233 141 bytes
    Server received:   600 530 bytes

  7. #7
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: How to efficiently check if ADODB.Connection is really open

    Hi,

    in my Textfiles I found this documentation
    here the Link
    https://mysql-net.github.io/MySqlCon...ction-options/

    that's all I could find, there really isn't much

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  8. #8

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    Thanks, I will try it out and report back!

  9. #9
    PowerPoster
    Join Date
    Jun 2015
    Posts
    2,224

    Re: How to efficiently check if ADODB.Connection is really open

    Have you tried setting
    Code:
    pooled=true
    in the connection string?

  10. #10
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to efficiently check if ADODB.Connection is really open

    I don't think connection pooling is going to help.

    It works by keeping connections in the pool open for long periods of time decoupling clients from their lifetime. It spoofs your connector (ADODB.Connection) object's open/closes, linking and de-linking from pooled connection instances. If a pooled connection loses communication wouldn't that just end up in the same state you are running into without pooling? I doubt you can discard your error handling even with pooling.


    As for the error, have you tried dumping the Connection's Error collection property when this scenario occurs? That's where Provider errors would be found and perhaps a more specific error code to test for might be discovered there.

    In your case you don't have a proper Provider but instead rely on the ODBC-shim Provider (MSDASQL) implicitly. That gets substituted on open when ADO sees that you failed to specify one. Not only does this shim add a layer of inefficiency to all activity, it may well mask or distort errors from any given ODBC Driver. The process gets cleaner and optimized for some SQL Server ODBC Drivers over time as Microsoft refines its Azure support (they want to sell Azure to non-Windows customers who can't support OLEDB) but all bets are off with regard to 3rd Party Drivers.

    When using the MSDASQL Provider it is probably just as much in the dark regarding actual connection state itself. Instead of OLEDB connection pooling ODBC connection pooling gets used at yet another layer of indirection and spoofing.

    It may lose details about the connection state that way. Sort of like tying your shoes wearing three pairs of mittens one over the next.


    This stuff is only really viable for LAN scenarios anyway.

    Ideally if you must operate over a WAN you would rip as much logic out of your program as possible and into a middle tier running next to the database server if not on the same hardware itself. Then connect from your hollowed out front-end to the middle tier using some connectionless protocol such as HTTP.

    This is why Windows DNA came about, and then was replaced by Web Services and .NET Remoting protocols.


    Connection pooling was designed to prop up web server scripts. I don't see many applications for it in normal desktop applications.

  11. #11
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to efficiently check if ADODB.Connection is really open


  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    dilettante thank you for your response, gives something to think about.

    That article was one of the first I read. None of them were very clear about the specific of what depends on what and when.

    I got pooling apparently working now with the MySQL connector when I close the connection but don't destroy the connection object. The bandwidth is only slightly increased, however the processing time for (an unrealistically high but good for a benchmark) 10 000 queries went from ~10s to ~18s. No DOS.

    Now I need to pick one of the two approaches - keeping an open connection or reconnecting with pooling.
    - Keeping an open connection is faster, but I don't think the time difference will matter much in real use. Reconnecting when the connection drops works, but I really don't like the extra error handling code I had to write to keep it reliable.
    - Closing the connection after every query without destroying the connection object so that pooling is possible seems more sturdy, and it allowed me to trim down and simplify the code a little.

  13. #13
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to efficiently check if ADODB.Connection is really open

    It is hard to imagine what pooling might be gaining you here.

    I suppose that it is possible that when you request to be relinked to a pooled open connection the error checking and connection re-establishment is done for you behind the scenes. I can't find a single thing documented to suggest such behavior occurs though.

    I can't imagine why this would be faster than doing this work yourself though. Maybe you were doing something expensive like closing and throwing away the Connection object instance and creating a brand new one to reconnect?


    Setting all of this aside...

    If you are indeed doing something strongly discouraged like trying to open database connections across a WAN then there you face some severe latency problems. It seems quite likely that your commands are just timing out now and then rather than your connections being lost.

    I suspect this even more strongly if you seem to see the problem remedied at all by pooling, which can only add overhead that makes no sense in a desktop client.

    Perhaps all you really need to do is set a larger value for the Connection.ConnectionTimeout Property?

    The default is 15 seconds. Perhaps between LAN congestion, router congestion, Internet congestion, and a tipsy drunken sailor server that is trying to support too many users sometimes 15 seconds just isn't enough?

    I'd double down on looking at that if you are indeed trying to make use of some cheap oversubscribed database service across the Internet.

    Try doubling that to 30 seconds without any pooling or error checking at all.

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    > It is hard to imagine what pooling might be gaining you here.

    Avoiding opening a new connection. You can check by running "SHOW STATUS LIKE '%onn%';" on the server (how do I write inline code in this forum?).

    > Maybe you were doing something expensive like closing and throwing away the Connection object instance and creating a brand new one to reconnect?

    That's possibly what was being done before. I like it when code and docs are explicit. This whole subject is poorly documented and very implicit. It is, after all, MS documentation from the 90s.

    > It seems quite likely that your commands are just timing out now and then rather than your connections being lost. (...) Try doubling that to 30 seconds without any pooling or error checking at all.

    We might have a misunderstanding here as I'm not sure what you're referring to. It works fine now, and it worked fine before when I kept an open connection. It did not work fine when each query opened a new connection, as that caused overhead and all the new connections eventually caused a denial of service.

    I think the solution to prevent it from opening new connections was to run "m_cn.close" every time I finished dealing with the recordset "m_rs" after every query, but to not run "Set m_cn = Nothing" or "Set m_cn As New ADODB.Connection". i.e. it seems that it was destroying the connection object and instantiating a new connection object which made pooling not work before.

    > Ideally if you must operate over a WAN

    It operates over LAN - sometimes wireless, sometimes wired.

  15. #15
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to efficiently check if ADODB.Connection is really open

    Quote Originally Posted by OldClock View Post
    We might have a misunderstanding here as I'm not sure what you're referring to. It works fine now, and it worked fine before when I kept an open connection. It did not work fine when each query opened a new connection, as that caused overhead and all the new connections eventually caused a denial of service.
    If that's the case then I apologize. I obviously failed to read everything closely enough because I didn't think you were doing that at all.

    If you want to use techniques intended to support old ASP logic in a desktop application then I agree that pooling should help a lot. That way you aren't really closing connections at all.

    I'm not sure why you'd ever do that though. Maybe that's where I went off the rails, feeling it was unlikely.


    As for the documentation... well what can you expect? Most of Microsoft's brain was slowly fed the .Net chloroform from 1999 onward. Most of the documentation dating to the 1990s is the best they ever produced and is still the Gold Standard in the industry. If you wanted tutorials instead then you would have been referred to their training programs and MSDN Events, but those are dead and gone now for the most part.

  16. #16

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    > If you want to use techniques intended to support old ASP logic in a desktop application then I agree that pooling should help a lot. (...) I'm not sure why you'd ever do that though.

    I wasn't aware that that's what I was doing. What do you mean, and how else would you suggest retrieving, storing and updating info from MariaDB?

  17. #17
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,045

    Re: How to efficiently check if ADODB.Connection is really open

    Hi OldClock,

    I don't know if this is of any use for you.

    https://www.itworld.com/article/2861...nnections.html

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  18. #18
    PowerPoster
    Join Date
    Feb 2006
    Posts
    24,482

    Re: How to efficiently check if ADODB.Connection is really open

    Quote Originally Posted by OldClock View Post
    I wasn't aware that that's what I was doing. What do you mean, and how else would you suggest retrieving, storing and updating info from MariaDB?
    A desktop program would normally open a single connection and just keep it open as long as you need to use the database.

    An ASP page script, CGI program, etc. only has a lifetime of a single request so they need pooling in order to avoid incredibly high overhead. While ASP scripts can cache objects, early versions of ASP didn't have much support for this and a lot of ASP scripters seemed unaware of the capability. Making pooling the default for ASP got around serious performance problems caused by opening then closing a database connection for each HTTP request.

  19. #19
    New Member
    Join Date
    May 2020
    Posts
    2

    Re: How to efficiently check if ADODB.Connection is really open

    Hello.
    I met with same issue, so I will be grateful if OldClock tells how did he solve it. What approach did you choose at last?

  20. #20

    Thread Starter
    Addicted Member
    Join Date
    Jul 2016
    Posts
    230

    Re: How to efficiently check if ADODB.Connection is really open

    Hey

    I have indeed found a good solution. I know it's good because I have put it through many tests and found no wasted overhead, and also because it's been running for a few years on many networks in a few African countries where the power goes out on a daily basis, problems would have been caught when the power goes back on.

    The important part is this: you instantiate a new connection on program startup, the connection is closed and reopened after each query, but it must not be destroyed throughout the program's session. Opening and closing it does not cause wasted overhead, but destroying it and instantiating a new one would.

    I will provide a (mostly-)complete code example, as it took years of experimentation to reach this stable point.

    Declare this somewhere persistent throughought your program's life, e.g. in your loader module:

    vb Code:
    1. Public g_mhD As MariaHandler

    In your program's startup:

    vb Code:
    1. Set g_mhD = New MariaHandler
    2.     If Not g_mhD.Init Then
    3.         Call MsgBox("Opening a connection to the database failed." & App.ProductName & ".", vbCritical, App.Title)
    4.         Exit Sub
    5.     End If

    Now in the MariaHandler class:

    vb Code:
    1. ' If compiling in Win7 there is a risk that the binary won't run in Vista/XP
    2. ' See this article for the resolution:
    3. ' [url]https://support.microsoft.com/en-us/help/2517589/an-ado-application-does-not-run-on-down-level-operating-systems-after[/url]
    4.  
    5. Private m_cn As ADODB.Connection
    6. Private m_rs As ADODB.Recordset
    7. Private m_cmd As ADODB.Command
    8.  
    9. Public Function Init() As Boolean
    10.     Dim success As Boolean
    11.     success = initConn
    12.     Init = success
    13. End Function
    14.  
    15. ' Instantiate and test new connection.
    16. Private Function initConn() As Boolean
    17.     Dim connStr As String
    18.     Dim success As Boolean
    19.  
    20.     On Error GoTo Fail
    21.  
    22.     success = False
    23.  
    24.     Set m_cn = New ADODB.Connection
    25.  
    26.     ' Configure connection
    27.     connStr = g_cfgD.dbConnDriver _
    28.         & ";SERVER=" & g_cfgD.dbServer _
    29.         & ";DATABASE=" & g_cfgD.dbName _
    30.         & ";UID=" & g_cfgD.dbUser _
    31.         & ";PWD=" & g_cfgD.dbPwd _
    32.         & ";PORT=" & g_cfgD.dbPort _
    33.         & ";POOLING=true" _
    34.         & ";OPTION=3"
    35.  
    36.     m_cn.ConnectionString = connStr
    37.     m_cn.CommandTimeout = 30
    38.  
    39.     ' Test that the connection works
    40.     success = openConn
    41.  
    42.     Call closeConn
    43.  
    44.     initConn = success
    45.     Exit Function
    46.  
    47. Fail:
    48.     initConn = False
    49.     Call failHandler("initConn", "", err, "Initializing a connection to the database failed!")
    50. End Function
    51.  
    52. Private Function openConn() As Boolean
    53.     On Error GoTo Fail
    54.  
    55.     If m_cn.State <> adStateOpen Then
    56.         ' An error is raised here if any of the details in m_cn.ConnectionString are wrong
    57.         Call m_cn.Open
    58.     End If
    59.  
    60.     If m_cn.State = adStateOpen Then
    61.         openConn = True
    62.     Else
    63.         openConn = False
    64.     End If
    65.  
    66.     Exit Function
    67.  
    68. Fail:
    69.     openConn = False
    70.     Call failHandler("openConn", "", err, "Opening a connection to the database failed!")
    71. End Function
    72.  
    73. Public Sub destroyConn()
    74.     On Error Resume Next
    75.  
    76.     Set m_cmd.ActiveConnection = Nothing
    77.     Call m_rs.Close
    78.     Set m_rs.ActiveConnection = Nothing
    79.     Call m_cn.Close
    80.  
    81.     Set m_cmd = Nothing
    82.     Set m_rs = Nothing
    83. End Sub
    84.  
    85. Public Sub closeConn()
    86.     ' [url]https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado[/url]
    87.     ' [url]https://msdn.microsoft.com/en-us/library/ms810829.aspx[/url]
    88.     ' [url]https://mysql-net.github.io/MySqlConnector/connection-options/[/url]
    89.     ' Note: you cannot access m_rs once m_cn.Close is called
    90.     If m_cn.State <> adStateClosed Then
    91.         Call m_cn.Close
    92.     End If
    93. End Sub
    94.  
    95. ' Called after every query is executed and m_rs no longer needed
    96. Private Sub rsCleanup()
    97.     Call closeConn
    98.  
    99.     If Not m_rs Is Nothing Then
    100.         If m_rs.State <> adStateClosed Then ' Closed when executing an INSERT or UPDATE, open on SELECT
    101.             Call m_rs.Close
    102.         End If
    103.         Set m_rs = Nothing
    104.     End If
    105.  
    106.     Set m_cmd = Nothing
    107. End Sub
    108.  
    109. ' Executes a given query and sets the modular m_rs object
    110. Private Function executeQuery(ByVal sqlQuery As String, ByVal callerName As String)
    111.     On Error GoTo Fail
    112.  
    113.     Call executeCommand(sqlQuery)
    114.  
    115.     Exit Function
    116.  
    117. Fail:
    118.     ' The error is re-raised for the calling function to handle.
    119.     err.Raise err.number, err.Source, err.description, err.HelpFile, err.HelpContext
    120. End Function
    121.  
    122. ' Tries to execute ADODB.Command.
    123. Private Function executeCommand(ByVal sqlQuery As String) As Boolean
    124.     Dim recordsAffected As Long
    125.     Dim success As Boolean
    126.  
    127.     success = False
    128.     On Error GoTo Fail
    129.  
    130.     If Not m_cn Is Nothing Then
    131.  
    132.         ' Parameterized queries require an ad-hoc m_cmd, which must be destroyed once used
    133.         ' so that the next query does not encounter old parameters. m_cmd is destroyed at
    134.         ' the end of this function. Non-parameterized queries do not instantiate a new m_cmd,
    135.         ' so that is done here:
    136.         If m_cmd Is Nothing Then ' Non-parameterized query
    137.             Set m_cmd = New ADODB.Command
    138.             m_cmd.CommandType = adCmdText
    139.         End If
    140.  
    141.         If Not m_cmd Is Nothing Then
    142.             If openConn Then
    143.                 m_cmd.ActiveConnection = m_cn
    144.                 m_cmd.CommandText = sqlQuery
    145.                 Set m_rs = m_cmd.Execute(recordsAffected)
    146.                 success = True
    147.                 ' Do not close connection or destroy m_cmd yet as that would prevent access
    148.                 ' to m_rs, needed not only for SELECT statements but also for retrieving
    149.                 ' error number from stored procedures.
    150.             End If
    151.         End If
    152.     End If
    153.  
    154.     executeCommand = success
    155.     Exit Function
    156.  
    157. Fail:
    158.     err.Raise err.number, err.Source, err.description, err.HelpFile, err.HelpContext
    159. End Function
    160.  
    161. Private Sub Class_Terminate()
    162.     Call destroyConn
    163. End Sub
    164.  
    165. ' Optional, makes error messages more human-friendly
    166. Private Sub failHandler( _
    167.     ByVal procName As String, _
    168.     ByVal queryStr As String, _
    169.     ByVal err As ErrObject, _
    170.     Optional ByVal leadDesc As String)
    171.  
    172.     Dim desc As String
    173.     Dim descPrefix As String
    174.     Dim errDscr As String
    175.     Dim errNbr As Long
    176.     Dim errSrc As String
    177.     Dim ll As Long
    178.     Dim p As Parameter
    179.     Dim msg As String
    180.     Dim regex As RegExp
    181.     Dim res As Integer
    182.  
    183.     ' Handle errors in this error handler.
    184.     ' "On Error..." resets the ErrObject, so backup the error info first:
    185.     errDscr = err.description
    186.     errNbr = err.number
    187.     errSrc = err.Source
    188.     On Error Resume Next
    189.  
    190.     Set regex = New RegExp
    191.  
    192.     If procName = "openConn" Then
    193.         ' m_cn.Open failed, precise cause is in errDscr
    194.         ' e.g.
    195.         ' [MySQL][ODBC 5.3(w) Driver]Access denied for user 'foo'@'localhost' (using password: YES)
    196.  
    197.         regex.Pattern = "\]([^\]]+)$" ' Match last ']'
    198.         descPrefix = regex.Replace(errDscr, "]")
    199.  
    200.         regex.Pattern = ".*\]([^\]]+)$" ' Match everything after last ']'
    201.         desc = regex.Replace(errDscr, "$1")
    202.  
    203.         msg = "Opening a connecton to the database failed:" & vbNewLine _
    204.             & desc & vbNewLine _
    205.             & vbNewLine _
    206.             & "Check the database connection settings in" & vbNewLine _
    207.             & Environ("ALLUSERSPROFILE") & "" & App.ProductName & ".ini" & vbNewLine _
    208.             & vbNewLine _
    209.             & "Error number: " & errNbr & vbNewLine _
    210.             & "Source: " & descPrefix & vbNewLine _
    211.             & "Source: " & errSrc & vbNewLine _
    212.             & "Procedure: " & procName
    213.  
    214.     Else
    215.  
    216.         ' Split errDscr to make it more readable and to make the dialog less wide
    217.         regex.Pattern = "\]([^\]]+)$" ' Match last ']'
    218.         desc = regex.Replace(errDscr, "]" & vbNewLine & "$1")
    219.  
    220.         If LenB(leadDesc) = 0 Then
    221.             msg = "There was an error while communicating with the database." & vbNewLine & vbNewLine
    222.         Else
    223.             msg = leadDesc & vbNewLine & vbNewLine
    224.         End If
    225.  
    226.         msg = msg & "Error number: " & errNbr & vbNewLine _
    227.             & "Source: " & errSrc & vbNewLine _
    228.             & "Procedure: " & procName & vbNewLine _
    229.             & vbNewLine _
    230.             & "Description: " & vbNewLine _
    231.             & desc & vbNewLine
    232.  
    233.         If LenB(queryStr) > 0 Then
    234.             ' Replace ? with parameters
    235.             If Not m_cmd Is Nothing Then
    236.                 If m_cmd.Parameters.Count > 0 Then
    237.                     For ll = 1 To m_cmd.Parameters.Count
    238.                         Set p = m_cmd.Parameters(ll - 1)
    239.                         queryStr = Replace(queryStr, "?", "'" & p.Value & "'", 1, ll)
    240.                     Next
    241.                 End If
    242.             End If
    243.             msg = msg & vbNewLine & queryStr
    244.         End If
    245.  
    246.     End If
    247.  
    248.     Debug.Print msg
    249.     Call MsgBox(msg, vbExclamation, App.Title)
    250.  
    251.     Set m_cmd = Nothing
    252.  
    253.     res = MsgBox("Would you like to close " & App.ProductName & "?" & vbNewLine & _
    254.         "Press ""Yes"" to close it ""No"" to continue using it.", vbQuestion + vbYesNo + vbDefaultButton2, App.Title)
    255.  
    256.     If res = vbYes Then
    257.         Call destroyConn
    258.         Call unloadForms
    259.         End
    260.     End If
    261. End Sub

    Helper utils:

    vb Code:
    1. Public Const MYSQL_DATETIME_LENGTH As Integer = 19 ' "yyyy-mm-dd hh:mm:ss"
    2. Public Const MYSQL_DATETIME_FORMAT As String = "yyyy-mm-dd hh:mm:ss"
    3. Public Const MYSQL_DATE_LENGTH As Integer = 10 ' "yyyy-mm-dd"
    4. Public Const MYSQL_DATE_FORMAT As String = "yyyy-mm-dd"
    5.  
    6. Public Function getMysqlDate(ByVal v As Variant, doIncludeTime As Boolean) As String
    7.     Dim str As String
    8.  
    9.     If v = 0 Then
    10.         ' Format$(0, MYSQL_DATETIME_FORMAT) returns 1899-12-30 00:00:00
    11.         If doIncludeTime Then
    12.             str = "0000-00-00 00:00:00"
    13.         Else
    14.             str = "0000-00-00"
    15.         End If
    16.     Else
    17.         If doIncludeTime Then
    18.             str = Format$(v, MYSQL_DATETIME_FORMAT)
    19.         Else
    20.             str = Format$(v, MYSQL_DATE_FORMAT)
    21.         End If
    22.     End If
    23.  
    24.     getMysqlDate = str
    25. End Function
    26.  
    27. Public Function getDateTimeNull(ByVal v As Variant) As Date
    28.     If IsDate(v) Then
    29.         getDateTimeNull = v
    30.     Else
    31.         getDateTimeNull = 0
    32.     End If
    33. End Function

    And finally, example functions for storing and selecting data, using parameterized queries:

    vb Code:
    1. ' The id is set by MariaDB:
    2. '   `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
    3. '   PRIMARY KEY (`id`)
    4. ' It is then retrieved after the insert, so that your program can refer to fooD.id
    5. Public Function storeFooData(ByVal fooD As FooData) As Boolean
    6.     Dim queryStr As String
    7.  
    8.     On Error GoTo Fail
    9.  
    10.     queryStr = "INSERT INTO foo (" _
    11.         & "item_name, " _
    12.         & "expiry, " _
    13.         & "stock, " _
    14.         & "is_deleted)" & vbNewLine _
    15.         & "VALUES (?, ?, ?, ?);"
    16.  
    17.     Set m_cmd = New ADODB.Command
    18.     m_cmd.Parameters.Append m_cmd.CreateParameter(, adVarWChar, adParamInput, 100, fooD.name)
    19.     m_cmd.Parameters.Append m_cmd.CreateParameter(, adVarChar, adParamInput, MYSQL_DATE_LENGTH, getMysqlDate(fooD.expiry, False)) ' Gotcha
    20.     m_cmd.Parameters.Append m_cmd.CreateParameter(, adUnsignedInt, adParamInput, 10, fooD.stock)
    21.     m_cmd.Parameters.Append m_cmd.CreateParameter(, adBoolean, adParamInput, 1, fooD.isDeleted)
    22.  
    23.     Call executeQuery(queryStr, "storeFooData")
    24.     Call executeQuery("SELECT LAST_INSERT_ID();", "storeFooData")
    25.     If Not m_rs Is Nothing Then
    26.         If Not m_rs.EOF Then
    27.             fooD.id = m_rs("LAST_INSERT_ID()")
    28.         End If
    29.     End If
    30.  
    31.     storeFooData = True
    32.     Call rsCleanup
    33.     Exit Function
    34.  
    35. Fail:
    36.     storeFooData = False
    37.     Call failHandler("storeFooData", queryStr, err)
    38. End Function
    39.  
    40. Public Function retrieveFooDataById(ByVal fooId As Long) As FooData
    41.     Dim fooD As FooData
    42.     Dim queryStr As String
    43.  
    44.     On Error GoTo Fail
    45.  
    46.     queryStr = "SELECT id, item_name, expiry, stock, is_deleted" & vbNewLine _
    47.         & "FROM foo" & vbNewLine _
    48.         & "WHERE id = ?;"
    49.  
    50.     Set m_cmd = New ADODB.Command
    51.     m_cmd.Parameters.Append m_cmd.CreateParameter(, adUnsignedBigInt, adParamInput, 10, fooId)
    52.  
    53.     Call executeQuery(queryStr, "retrieveFooDataById")
    54.  
    55.     If Not m_rs.EOF Then
    56.         Set fooD = New FooData
    57.         fooD.id = m_rs("id")
    58.         fooD.name = m_rs("item_name")
    59.         fooD.expiry = getDateTimeNull(m_rs("expiry")) ' Gotcha
    60.         fooD.stock = m_rs("stock")
    61.         fooD.isDeleted = CBool(m_rs("is_deleted"))
    62.     End If
    63.  
    64.     Set retrieveFooDataById = fooD
    65.     Call rsCleanup
    66.     Exit Function
    67.  
    68. Fail:
    69.     Call failHandler("retrieveFooDataById", queryStr, err)
    70. End Function

  21. #21
    New Member
    Join Date
    May 2020
    Posts
    2

    Re: How to efficiently check if ADODB.Connection is really open

    I am so grateful to you!

  22. #22
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: How to efficiently check if ADODB.Connection is really open

    Noticably transaction handling is missing from this DAL.

    I was about to investigate how is this MsgBox lurking in the code prevented from being displayed while an outstanding transaction is neither comitted nor rolled back yet. (Been there, done that :-))

    Always closing cached connection on error is the way to go IMO.

    cheers,
    </wqw>

  23. #23
    Lively Member
    Join Date
    Feb 2006
    Posts
    92

    Re: How to efficiently check if ADODB.Connection is really open

    Quote Originally Posted by OldClock View Post
    Hey
    I have indeed found a good solution. I know it's good because I have put it through many tests and found no wasted overhead, and also because it's been running for a few years on many networks in a few African countries where the power goes out on a daily basis, problems would have been caught when the power goes back on.
    [/highlight]
    Can you post a working project?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width