Excel VBA ODBC reading field changes it to Null

Question

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.com/downloads/connector/odbc

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

Field:	meta_id	post_id	meta_key	meta_value
Type:	BIGINT UNSIGNED	BIGINT UNSIGNED	VARCHAR	TEXT
Char Set:	binary	binary	utf8mb4	utf8mb4
First Row:	21875	100	total_sales	0

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:

This is what I was expecting:

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.com/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?

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
– Tim Williams, Commented Apr 2 at 16:16

mbillion · Accepted Answer · 2025-04-02 21:12:28Z

1

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.com/doc/connector-odbc/en/connector-odbc-configuration-connection-parameters.html

answered Apr 2 at 21:12

mbillion

741 silver badge12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Noodle_Soup Apr 3 at 5:12

Hmm, could be related, but hasn't worked for me. I've tried setting it using the "ODBC Data Source Administrator (64-bit)" GUI and adding it to the connection string like: "AUTO_IS_NULL=1;". Looking at the value in the MySQL workbench, querying it there, and querying it from python, I'm getting 0

mbillion Apr 3 at 16:04

in any event looks like you are on the hunt above as well - I have a strong inkling this is a driver setting or DB setting, some default behavior. MySql workbench will give the true database value uncorrupted by the driver, its all running local so python should give you the same result as the workbench - I would expect those to be the same as you are running both locally. To test you would need to pull something using the driver, thats where the break is occuring

Noodle_Soup · Accepted Answer · 2025-04-03 08:29:19Z

1

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.

answered Apr 3 at 8:29

Noodle_Soup

1231 silver badge6 bronze badges

Collectives™ on Stack Overflow

Excel VBA ODBC reading field changes it to Null

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related