Bug #37872 Null not allowed when inserting via ODBC
Submitted: 4 Jul 2008 13:59 Modified: 6 Dec 2010 5:14
Reporter: Louis Breda van Email Updates:
Status: Closed Impact on me:
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.1.4 OS:Windows (VISTA 64, XP 32/64)
Assigned to: Lawrenty Novitsky CPU Architecture:Any
Tags: ADODB, myisam, null

[4 Jul 2008 13:59] Louis Breda van

When there is no / not yet a value for a field, I always make that field NULL.

There are at least three variants to do this:
- default value in table in null (it should always be IMHO)
- assign null to a field myrecordset!fieldx = Null
- myrecordset!fieldx = anotherrecordset!fieldy being null

All of the three variants should of course work.

However recently I defined a few tables based on the MyISAM engine, 
and at least myrecordset!fieldx = anotherrecordset!fieldy did not work.

Error message saying it is not allowed to assign null to a non variant field.
Nonsence ! 

When using ODBC query browser, I could change the field to NULL without a problem.



How to repeat:
- using msaccess, odbc 3.25, server 5.1.25
- on a vista 64 machine
- define in mysql two MyISAM-tables more ore less identical fields
- define in access two recordsets
- add rs2 to rs1 like this

    rs1!field1 = True
    rs1!field2 = rs2!fieldy
    rs1!field3 = rs2!fieldz  assume this field = null
    On Error GoTo cancel_update

- e.g. in the situation that rs1!field3 of type tinyint (should be boolean, but that is another problem) and rs2!fieldz = null there is an error when reaching this code line (not allowed to assign null if not of type variant or something like that)
- I did never gave problems when using INNODB
- it works when using the MySQL query browser e.g. like this
- UPDATE schema.`mytable` SET `mytable`.field3 = Null;
[5 Jul 2008 11:31] Louis Breda van
Sorry, I was using odbc 5.14 not 3.25. (do not kow if it makes a difference)

[16 Jul 2008 12:54] Bogdan Degtyariov
Louis, thank you for the report.
I verified the bug in XP with MyODBC 5.1.5-pre-release.
MyODBC 3.51 works well. 
When debugging I see that the parameter added by SQLBindParameter function is not interpreted correctly by SQLExecDirectW.
Trying to fix that.
[11 Aug 2008 14:43] Bogdan Degtyariov
ADO library uses extended functionality in MyODBC 5.1 and actively works not with the direct pointers to the data, but with SQL_ATTR_PARAM_BIND_OFFSET_PTR and offsets from the base address.

It is a bug in ADO when working through SQL_ATTR_PARAM_BIND_OFFSET_PTR. 
ADO works as follows:

 - ADO binds buffers for getting data (SQLBindCol)
 - ADO calls SQLExtendedFetch to retrieve data
 - MyODBC fills bound buffers (SQL_NULL_DATA indicator is set 
   for NULL values)
 - ADO prepares INSERT query, but does not set SQL_NULL_DATA for

My C test case inserts NULL data correctly providing SQL_NULL_DATA indicator is properly set. Verdict - not a bug in MyODBC.
[11 Aug 2008 18:46] Louis Breda van

Thanks for the investigation.

But where do you assume the error, given the fact that odbc 3 is working correct??

[12 Aug 2008 5:25] Bogdan Degtyariov

ADO works with MyODBC 3.51 in another way than with MyODBC 5.1, otherwise there would be no issue when NULL value cannot be inserted.

The main difference is: 

For MyODBC 3.51 ADO mounts data buffers directly: the address in memory where MyODBC has to read the data for storing in the database and where to write the result from the database.

The situation is different when ADO calls MyODBC 5.1 functions. It does not provide the direct addresses for the buffers. ADO passes the base address at the beginning and all other addresses that go after that into SQLBindCol/SQLBindParameter functions are to be considered as offsets from this base address.

SQLBindCol() function binds the buffer for reading the result from the database. MyODBC returns SQL_NULL_DATA identifier when the value is NULL.

Consequently, when inserting data into another table (as it was in your example) this SQL_NULL_DATA (-1) identifier has to be passed to SQLBindParameter function which sets the buffers for input data for INSERT query. However, instead of (-1) which means SQL_NULL_DATA, the buffer given by ADO contains 0 which means empty string or zero value for numeric column.

MyODBC gives the correct result, but receives wrong data back from ADO when inserting a new record.
[12 Aug 2008 7:58] Bogdan Degtyariov
The bug occurs since SQL_API_SQLSETDESCFIELD functionality has been enabled in MyODBC 5.1 and ADO library uses it to set base addresses and offsets instead of direct pointers to the data buffers. Just for testing I disabled SQLSetDescField() function in MyODBC 5.1. VB6 script worked exactly as with MyODBC 3.51 and copied NULL values successfully.

This confirms my conclusions about the bug in ADO.
[21 Aug 2008 10:08] Louis Breda van

I just installed 5.15, in order to see if that one works for me.

Guess what happend within a minut the machine hangs because a record
field was (completely legal) updated with "Null". 

So appart from the fact who to "blame" (microsoft or mysql), I simply can not use 5.15. Again have to fall back on 3.25

For info I was using 5.13 in combination with special build 5.1.25.
But intoduction of 5.1.26 (in general good release), forced me to step back
to odbc 3.25. The combination with 5.1.26 with 5.14 just did give me lots of troubles.

So, IMHO you need to fix this with or without the help of microsoft.


[21 Aug 2008 10:28] Bogdan Degtyariov

I am working with the issue where NULL is inserted for not-NULL values (bug#37649). They seem to be connected. Re-opening this bug. Fix is in progress...
[9 Apr 2009 8:47] Louis Breda van

I know you are bussy but could you please fix this bug on short terms. It has been open for a far too long period now.

At this moment I am using a kind of "undefined" mix of ODBC3 a and ODBC5 connections, since some times I need to use ODBC3 because ODBC5 does not work and sometimes I need to use ODBC5.

I want to get rid of this situation, but I can not as long as ODBC5 has this bug.


[24 Feb 2010 13:31] Tonci Grgin
Bogdan, bug#37649 is fixed, what about this one?
[24 Feb 2010 14:55] Tonci Grgin
More to NULL story:
Bug#45142  	Recordset("Field") = NULL Results in Empty String if CursorLocation=adUseClient

Bug#42070  	ODBC driver not writing NULL to integer column
[24 Feb 2010 14:57] Tonci Grgin
Bug#42070 was marked as duplicate of this report.
[6 Dec 2010 5:14] Bogdan Degtyariov
Both Connector/ODBC 5.1.8 and 3.51.27 drivers worked well with the test VB script:

5.1.4: inserted 0 (wrong)
3.51.27: inserted NULL (correct)
5.1.8: inserted NULL (correct)

Closing the bug