Summary; The first time I read a data field, I get 0, every time after, the same record field reads Null. This changes if I debug, step through and watch the values in the locals/watchlist, I get Null every time.

This is in Excel VBA, using ADODB.Connection to a MySQL server on the same machine. I'm using the latest driver that I downloaded and installed from dev.mysql/downloads/connector/odbc

Here's the table fields and the first example row:

Summary; The first time I read a data field, I get 0, every time after, the same record field reads Null. This changes if I debug, step through and watch the values in the locals/watchlist, I get Null every time.

This is in Excel VBA, using ADODB.Connection to a MySQL server on the same machine. I'm using the latest driver that I downloaded and installed from dev.mysql/downloads/connector/odbc

Here's the table fields and the first example row:

Sub test()
    Const sConnection As String = _
        "DRIVER={MySQL ODBC 9.2 Unicode Driver};" & _
        "SERVER=127.0.0.1;" & _
        "DATABASE=wp_woocommercedb;" & _
        "USER=root;" & _
        "PASSWORD=CorporateAnxiety;" & _
        "CharSet=utf8;"
    Const sSQLQuery = "SELECT * FROM `wp_postmeta` WHERE `post_id` = '100';"
    
    Dim conn As New ADODB.Connection
    conn.Open sConnection
    
    Dim rs As New ADODB.Recordset
    rs.Open sSQLQuery, conn
    
    Debug.Print rs.Fields("meta_id")
    Debug.Print rs.Fields("meta_value")
    Debug.Print rs.Fields("meta_id")
    Debug.Print rs.Fields("meta_value")
    
    rs.Close
    Set rs = Nothing
    conn.Close
    Set conn = Nothing
End Sub

This is what it prints if I just run it:

 21875
0
 21875
Null

This is what I was expecting:

 21875
0
 21875
0

So I started poking around in the watch/Locals window. But when I step through looking at the value I get:

 21875
Null
 21875
Null

I've included screenshots of the value changing, because it's just so bizarre.

I've also tried having a look through connectionstrings/mysql which documents the connection options we're passing as text at the start. But no luck there.

To my mind the value shouldn't be changing. I can work around this if it's small and isolated. But I need to know if there's more that's glitching out. Any ideas what's going on?

• excel
• vba
• odbc
• mysql-connector
• adodb

• 4 Odd things can happen when debugging using the Watch window - for example we've seen questions here about issues with Dictionary object, but I don't recall this specific issue coming up. However if the issue only happens when the Watch window is open then I think you can safely disregard it. – Tim Williams Commented 2 days ago

2 Answers 2

When AUTO_IS_NULL is not set the driver changes between zeros and nulls, I think you need to configure the ODBC

"When AUTO_IS_NULL is set, the driver does not change the default value of sql_auto_is_null, leaving it at 1, so you get the MySQL default, not the SQL standard behavior.

When AUTO_IS_NULL is not set, the driver changes the default value of SQL_AUTO_IS_NULL to 0 after connecting, so you get the SQL standard, not the MySQL default behavior.

Thus, omitting the flag disables the compatibility option and forces SQL standard behavior.

See IS NULL. Added in 3.51.13."

Youre seeing zero and thinking its a 0 but its really a default value

https://dev.mysql/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

Got it figured. Just had to change:

rs.Open sSQLQuery, conn

to:

rs.Open sSQLQuery, conn, CursorType:=adOpenStatic

So the third argument is a CursorTypeEnum. This defaults to adOpenForwardOnly. I've got a conjecture of what's going on under the hood. This 'forward cursor' is supposed to have the quickest read speed. I suspect that when the last field is read it's in some way incrementing to the next record set. Even viewing the last field in the Local/Watch window must count as a read, so the field was incrementing before I got to it.