MySQL Bugs: #37872: Null not allowed when inserting via ODBC

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:	None
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

Description:
Hello,

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.

Sincerely,

Louis

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.AddNew

rs1!field1 = True
rs1!field2 = rs2!fieldy
rs1!field3 = rs2!fieldz assume this field = null

On Error GoTo cancel_update
rs1.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;

Sorry, I was using odbc 5.14 not 3.25. (do not kow if it makes a difference)

Louis

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.

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
   SQLBindParameter

My C test case inserts NULL data correctly providing SQL_NULL_DATA indicator is properly set. Verdict - not a bug in MyODBC.

Bogdan,

Thanks for the investigation.

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

Louis

Louis,

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.

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.

Hello,

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.

Sincerely,

Louis

Louis,

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...

Bogdan,

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.

Sincerely,

Louis

Bogdan, bug#37649 is fixed, what about this one?

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

Bug#42070 was marked as duplicate of this report.

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