mysqldatabasevbscriptadodbmysql-odbc-connector

Variables losing their value

I have the following vbscript code:

hemisphereConnectionString = "Driver={MySQL ODBC 5.1 Driver};" & _
                             "Server=" & hemisphereServer & ";" & _
                             "Database=" & hemisphereDatabase & ";" & _
                             "User=" & hemisphereUsername & ";" & _
                             "Password=" & hemispherePassword & ";"

Set hemisphereConnection = CreateObject("adodb.connection")
hemisphereConnection.Open(hemisphereConnectionString)

hem_sql = "SELECT * FROM hei_vision_update WHERE id IN (SELECT MAX(id) FROM hei_vision_update WHERE done = 'N' GROUP BY name)"

Set hemisphereResult = hemisphereConnection.Execute(hem_sql)

if hemisphereResult.EOF = false then
    hemisphereResult.MoveFirst()
end if
msgbox isnull(hemisphereResult("home_publish").value)
msgbox hemisphereResult("home_publish").value

It is part of a much larger script (too big to post here but these are the key lines) My message boxes display false (ie the field has a value that is not null) and the next message box crashes saying "Invalid use of Null"

Anyone have any Ideas??

I have modified my code to the above, This is all of it (except the server and password details). There are no OERN/OEG0 lines now. The row I am getting from the database has a 'Y' in the home_publish field, hence the first message box displaying false is correct. Just the second one displaying "Invalid use of Null" is the mystery. I am beginning to wonder if there is an issue with the MySQL Driver?

Now this is getting silly:

I changed the last 2 lines to these 3:

msgbox hemisphereResult("home_publish").value
msgbox isnull(hemisphereResult("home_publish").value)
msgbox hemisphereResult("home_publish").value

The first message box displays my value 'Y'.
Now the second message box displays true (which it obviously isn't).
And finally my third message box gives my "Invalid use of null" error.

Anyone else ever experienced variables losing their value just because you have used them?

Solution

  • If hemisphereResult is ADODB.Recordset, then hemisphereResult("home_publish") is ADODB.Field, at which point the following happens.

    IsNull does not try to further investigate the passed object and returns False because the field itself exists as an object in the recordset.

    MsgBox, on contrary, cannot do a meaningful thing to an object, so it calls the default property of that object (the Field object) in order to display it. The default property is .Value, and that is Null.

    So despite the arguments to IsNull and MsgBox look the same in code, they are not actually the same.

    You might want to be explicit in your demands:

    msgbox isnull(hemisphereResult("home_publish").value)

    While the above is true, you might have been affected by a MySQL bug too (44831, 42385 etc.)

    The suggested workaround is to use client-side cursor:

    set hemisphereResult = CreateObject("ADODB.Recordset")
hemisphereResult.CursorLocation = 3 'adUseClient
hemisphereResult.open "select ...", hemisphereConnection