-
Feb 22nd, 2018, 09:50 AM
#1
Thread Starter
Addicted Member
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?
-
Feb 22nd, 2018, 10:05 AM
#2
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!
-
Feb 22nd, 2018, 10:16 AM
#3
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.
-
Feb 22nd, 2018, 10:17 AM
#4
Re: How to efficiently check if ADODB.Connection is really open
Originally Posted by TysonLPrice
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.
-
Feb 22nd, 2018, 10:29 AM
#5
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.
-
Feb 22nd, 2018, 10:37 AM
#6
Thread Starter
Addicted Member
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
-
Feb 22nd, 2018, 10:52 AM
#7
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.
-
Feb 22nd, 2018, 11:01 AM
#8
Thread Starter
Addicted Member
Re: How to efficiently check if ADODB.Connection is really open
Thanks, I will try it out and report back!
-
Feb 22nd, 2018, 11:05 AM
#9
Re: How to efficiently check if ADODB.Connection is really open
Have you tried setting in the connection string?
-
Feb 22nd, 2018, 12:40 PM
#10
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.
-
Feb 22nd, 2018, 02:13 PM
#11
Re: How to efficiently check if ADODB.Connection is really open
-
Feb 22nd, 2018, 04:36 PM
#12
Thread Starter
Addicted Member
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.
-
Feb 22nd, 2018, 05:05 PM
#13
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.
-
Feb 22nd, 2018, 05:45 PM
#14
Thread Starter
Addicted Member
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.
-
Feb 22nd, 2018, 10:41 PM
#15
Re: How to efficiently check if ADODB.Connection is really open
Originally Posted by OldClock
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.
-
Feb 23rd, 2018, 03:52 AM
#16
Thread Starter
Addicted Member
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?
-
Feb 23rd, 2018, 04:03 AM
#17
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.
-
Feb 23rd, 2018, 09:52 AM
#18
Re: How to efficiently check if ADODB.Connection is really open
Originally Posted by OldClock
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.
-
May 25th, 2020, 06:36 PM
#19
New Member
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?
-
May 26th, 2020, 05:05 AM
#20
Thread Starter
Addicted Member
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:
Public g_mhD As MariaHandler
In your program's startup:
vb Code:
Set g_mhD = New MariaHandler
If Not g_mhD.Init Then
Call MsgBox("Opening a connection to the database failed." & App.ProductName & ".", vbCritical, App.Title)
Exit Sub
End If
Now in the MariaHandler class:
vb Code:
' If compiling in Win7 there is a risk that the binary won't run in Vista/XP
' See this article for the resolution:
' [url]https://support.microsoft.com/en-us/help/2517589/an-ado-application-does-not-run-on-down-level-operating-systems-after[/url]
Private m_cn As ADODB.Connection
Private m_rs As ADODB.Recordset
Private m_cmd As ADODB.Command
Public Function Init() As Boolean
Dim success As Boolean
success = initConn
Init = success
End Function
' Instantiate and test new connection.
Private Function initConn() As Boolean
Dim connStr As String
Dim success As Boolean
On Error GoTo Fail
success = False
Set m_cn = New ADODB.Connection
' Configure connection
connStr = g_cfgD.dbConnDriver _
& ";SERVER=" & g_cfgD.dbServer _
& ";DATABASE=" & g_cfgD.dbName _
& ";UID=" & g_cfgD.dbUser _
& ";PWD=" & g_cfgD.dbPwd _
& ";PORT=" & g_cfgD.dbPort _
& ";POOLING=true" _
& ";OPTION=3"
m_cn.ConnectionString = connStr
m_cn.CommandTimeout = 30
' Test that the connection works
success = openConn
Call closeConn
initConn = success
Exit Function
Fail:
initConn = False
Call failHandler("initConn", "", err, "Initializing a connection to the database failed!")
End Function
Private Function openConn() As Boolean
On Error GoTo Fail
If m_cn.State <> adStateOpen Then
' An error is raised here if any of the details in m_cn.ConnectionString are wrong
Call m_cn.Open
End If
If m_cn.State = adStateOpen Then
openConn = True
Else
openConn = False
End If
Exit Function
Fail:
openConn = False
Call failHandler("openConn", "", err, "Opening a connection to the database failed!")
End Function
Public Sub destroyConn()
On Error Resume Next
Set m_cmd.ActiveConnection = Nothing
Call m_rs.Close
Set m_rs.ActiveConnection = Nothing
Call m_cn.Close
Set m_cmd = Nothing
Set m_rs = Nothing
End Sub
Public Sub closeConn()
' [url]https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/close-method-ado[/url]
' [url]https://msdn.microsoft.com/en-us/library/ms810829.aspx[/url]
' [url]https://mysql-net.github.io/MySqlConnector/connection-options/[/url]
' Note: you cannot access m_rs once m_cn.Close is called
If m_cn.State <> adStateClosed Then
Call m_cn.Close
End If
End Sub
' Called after every query is executed and m_rs no longer needed
Private Sub rsCleanup()
Call closeConn
If Not m_rs Is Nothing Then
If m_rs.State <> adStateClosed Then ' Closed when executing an INSERT or UPDATE, open on SELECT
Call m_rs.Close
End If
Set m_rs = Nothing
End If
Set m_cmd = Nothing
End Sub
' Executes a given query and sets the modular m_rs object
Private Function executeQuery(ByVal sqlQuery As String, ByVal callerName As String)
On Error GoTo Fail
Call executeCommand(sqlQuery)
Exit Function
Fail:
' The error is re-raised for the calling function to handle.
err.Raise err.number, err.Source, err.description, err.HelpFile, err.HelpContext
End Function
' Tries to execute ADODB.Command.
Private Function executeCommand(ByVal sqlQuery As String) As Boolean
Dim recordsAffected As Long
Dim success As Boolean
success = False
On Error GoTo Fail
If Not m_cn Is Nothing Then
' Parameterized queries require an ad-hoc m_cmd, which must be destroyed once used
' so that the next query does not encounter old parameters. m_cmd is destroyed at
' the end of this function. Non-parameterized queries do not instantiate a new m_cmd,
' so that is done here:
If m_cmd Is Nothing Then ' Non-parameterized query
Set m_cmd = New ADODB.Command
m_cmd.CommandType = adCmdText
End If
If Not m_cmd Is Nothing Then
If openConn Then
m_cmd.ActiveConnection = m_cn
m_cmd.CommandText = sqlQuery
Set m_rs = m_cmd.Execute(recordsAffected)
success = True
' Do not close connection or destroy m_cmd yet as that would prevent access
' to m_rs, needed not only for SELECT statements but also for retrieving
' error number from stored procedures.
End If
End If
End If
executeCommand = success
Exit Function
Fail:
err.Raise err.number, err.Source, err.description, err.HelpFile, err.HelpContext
End Function
Private Sub Class_Terminate()
Call destroyConn
End Sub
' Optional, makes error messages more human-friendly
Private Sub failHandler( _
ByVal procName As String, _
ByVal queryStr As String, _
ByVal err As ErrObject, _
Optional ByVal leadDesc As String)
Dim desc As String
Dim descPrefix As String
Dim errDscr As String
Dim errNbr As Long
Dim errSrc As String
Dim ll As Long
Dim p As Parameter
Dim msg As String
Dim regex As RegExp
Dim res As Integer
' Handle errors in this error handler.
' "On Error..." resets the ErrObject, so backup the error info first:
errDscr = err.description
errNbr = err.number
errSrc = err.Source
On Error Resume Next
Set regex = New RegExp
If procName = "openConn" Then
' m_cn.Open failed, precise cause is in errDscr
' e.g.
' [MySQL][ODBC 5.3(w) Driver]Access denied for user 'foo'@'localhost' (using password: YES)
regex.Pattern = "\]([^\]]+)$" ' Match last ']'
descPrefix = regex.Replace(errDscr, "]")
regex.Pattern = ".*\]([^\]]+)$" ' Match everything after last ']'
desc = regex.Replace(errDscr, "$1")
msg = "Opening a connecton to the database failed:" & vbNewLine _
& desc & vbNewLine _
& vbNewLine _
& "Check the database connection settings in" & vbNewLine _
& Environ("ALLUSERSPROFILE") & "" & App.ProductName & ".ini" & vbNewLine _
& vbNewLine _
& "Error number: " & errNbr & vbNewLine _
& "Source: " & descPrefix & vbNewLine _
& "Source: " & errSrc & vbNewLine _
& "Procedure: " & procName
Else
' Split errDscr to make it more readable and to make the dialog less wide
regex.Pattern = "\]([^\]]+)$" ' Match last ']'
desc = regex.Replace(errDscr, "]" & vbNewLine & "$1")
If LenB(leadDesc) = 0 Then
msg = "There was an error while communicating with the database." & vbNewLine & vbNewLine
Else
msg = leadDesc & vbNewLine & vbNewLine
End If
msg = msg & "Error number: " & errNbr & vbNewLine _
& "Source: " & errSrc & vbNewLine _
& "Procedure: " & procName & vbNewLine _
& vbNewLine _
& "Description: " & vbNewLine _
& desc & vbNewLine
If LenB(queryStr) > 0 Then
' Replace ? with parameters
If Not m_cmd Is Nothing Then
If m_cmd.Parameters.Count > 0 Then
For ll = 1 To m_cmd.Parameters.Count
Set p = m_cmd.Parameters(ll - 1)
queryStr = Replace(queryStr, "?", "'" & p.Value & "'", 1, ll)
Next
End If
End If
msg = msg & vbNewLine & queryStr
End If
End If
Debug.Print msg
Call MsgBox(msg, vbExclamation, App.Title)
Set m_cmd = Nothing
res = MsgBox("Would you like to close " & App.ProductName & "?" & vbNewLine & _
"Press ""Yes"" to close it ""No"" to continue using it.", vbQuestion + vbYesNo + vbDefaultButton2, App.Title)
If res = vbYes Then
Call destroyConn
Call unloadForms
End
End If
End Sub
Helper utils:
vb Code:
Public Const MYSQL_DATETIME_LENGTH As Integer = 19 ' "yyyy-mm-dd hh:mm:ss"
Public Const MYSQL_DATETIME_FORMAT As String = "yyyy-mm-dd hh:mm:ss"
Public Const MYSQL_DATE_LENGTH As Integer = 10 ' "yyyy-mm-dd"
Public Const MYSQL_DATE_FORMAT As String = "yyyy-mm-dd"
Public Function getMysqlDate(ByVal v As Variant, doIncludeTime As Boolean) As String
Dim str As String
If v = 0 Then
' Format$(0, MYSQL_DATETIME_FORMAT) returns 1899-12-30 00:00:00
If doIncludeTime Then
str = "0000-00-00 00:00:00"
Else
str = "0000-00-00"
End If
Else
If doIncludeTime Then
str = Format$(v, MYSQL_DATETIME_FORMAT)
Else
str = Format$(v, MYSQL_DATE_FORMAT)
End If
End If
getMysqlDate = str
End Function
Public Function getDateTimeNull(ByVal v As Variant) As Date
If IsDate(v) Then
getDateTimeNull = v
Else
getDateTimeNull = 0
End If
End Function
And finally, example functions for storing and selecting data, using parameterized queries:
vb Code:
' The id is set by MariaDB:
' `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT
' PRIMARY KEY (`id`)
' It is then retrieved after the insert, so that your program can refer to fooD.id
Public Function storeFooData(ByVal fooD As FooData) As Boolean
Dim queryStr As String
On Error GoTo Fail
queryStr = "INSERT INTO foo (" _
& "item_name, " _
& "expiry, " _
& "stock, " _
& "is_deleted)" & vbNewLine _
& "VALUES (?, ?, ?, ?);"
Set m_cmd = New ADODB.Command
m_cmd.Parameters.Append m_cmd.CreateParameter(, adVarWChar, adParamInput, 100, fooD.name)
m_cmd.Parameters.Append m_cmd.CreateParameter(, adVarChar, adParamInput, MYSQL_DATE_LENGTH, getMysqlDate(fooD.expiry, False)) ' Gotcha
m_cmd.Parameters.Append m_cmd.CreateParameter(, adUnsignedInt, adParamInput, 10, fooD.stock)
m_cmd.Parameters.Append m_cmd.CreateParameter(, adBoolean, adParamInput, 1, fooD.isDeleted)
Call executeQuery(queryStr, "storeFooData")
Call executeQuery("SELECT LAST_INSERT_ID();", "storeFooData")
If Not m_rs Is Nothing Then
If Not m_rs.EOF Then
fooD.id = m_rs("LAST_INSERT_ID()")
End If
End If
storeFooData = True
Call rsCleanup
Exit Function
Fail:
storeFooData = False
Call failHandler("storeFooData", queryStr, err)
End Function
Public Function retrieveFooDataById(ByVal fooId As Long) As FooData
Dim fooD As FooData
Dim queryStr As String
On Error GoTo Fail
queryStr = "SELECT id, item_name, expiry, stock, is_deleted" & vbNewLine _
& "FROM foo" & vbNewLine _
& "WHERE id = ?;"
Set m_cmd = New ADODB.Command
m_cmd.Parameters.Append m_cmd.CreateParameter(, adUnsignedBigInt, adParamInput, 10, fooId)
Call executeQuery(queryStr, "retrieveFooDataById")
If Not m_rs.EOF Then
Set fooD = New FooData
fooD.id = m_rs("id")
fooD.name = m_rs("item_name")
fooD.expiry = getDateTimeNull(m_rs("expiry")) ' Gotcha
fooD.stock = m_rs("stock")
fooD.isDeleted = CBool(m_rs("is_deleted"))
End If
Set retrieveFooDataById = fooD
Call rsCleanup
Exit Function
Fail:
Call failHandler("retrieveFooDataById", queryStr, err)
End Function
-
May 26th, 2020, 07:59 AM
#21
New Member
Re: How to efficiently check if ADODB.Connection is really open
-
May 26th, 2020, 10:16 AM
#22
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>
-
Dec 6th, 2021, 09:25 AM
#23
Lively Member
Re: How to efficiently check if ADODB.Connection is really open
Originally Posted by OldClock
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|