Bug #42070 ODBC driver not writing NULL to integer column
Submitted: 13 Jan 2009 4:22 Modified: 24 Feb 2010 14:57
Reporter: avi weiss Email Updates:
Status: Duplicate Impact on me:
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.1.5 OS:Windows
Assigned to: CPU Architecture:Any
Tags: ODBC driver integer NULL

[13 Jan 2009 4:22] avi weiss
ODBC driver not able to write NULL to integer column. if type is allowed in column, then driver should be able to write value with said type to column

Test table: 

DROP TABLE IF EXISTS `devvnsp`.`dummy`;
CREATE TABLE  `devvnsp`.`dummy` (
  `dummy_id` int(11) NOT NULL auto_increment,
  `zone_id` int(11) default NULL,
  `unit_id` int(11) default NULL,
  PRIMARY KEY  (`dummy_id`),
  KEY `zone_id` (`zone_id`),
  KEY `unit_id` (`unit_id`)

vbscript asp code to demonstrate failure:

Dim gnMySQLOptions
Dim sConn
Dim oDBConn

gnMySQLOptions = 1 + 2 + 8 + 32 + 1024 + 2048 + 16384 + 131072	' (See database.asp for references)
sConn = _
	"Driver={MySQL ODBC 5.1 Driver};" & _ 
	"Server=localhost;" & _
	"Port=3306;" & _
	"Database=devvnsp;" & _
	"Uid=temp;" & _
	"Pwd=;" & _
	"Option=" & 3 & ";"

Set oDBConn = Server.CreateObject("ADODB.Connection")
oDBConn.open sConn, "", ""

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.CursorLocation = adUseClient	
objRS.Open "SELECT * FROM dummy where dummy_id=1", oDBConn, adOpenStatic, adLockOptimistic, adCmdText
if objRS.EOF then
end if
objRS("zone_id") = null
objRS("unit_id") = null

How to repeat:
* create table
* ensure DB user specified in connection string in code exists
* run asp test code

Suggested fix:
ensure that driver is able to write all legal types to columns.
[29 Jan 2009 23:19] Jess Balint
Are you receiving an error when running Update()? Please say what is happening when you run your script. I have run your code under IIS and it's working fine.

[30 Jan 2009 5:12] avi weiss

No error received, but apparently large random number is being stored instead of NULL.

Driver is running on win2003 server, IIS 6, ASP.
DB is 5.0.60-Enterprise running under redhat linux.
[2 Feb 2009 20:42] Tonci Grgin
Hi Avi. No way to make this fail on my W2008 x64 server with IIS 7... Back to Jess.
[2 Feb 2009 23:00] avi weiss
ok, looks like the issue only happens when code, driver AND server are running on windows XP / Vista.

driver on win 2003 and server on win 2003: ok
driver on win2003 and server on red hat: unable to test due to live environment.

most likely some difference in how NULL is defined, or some runtime issue difference between XP/vista and win2003
[3 Feb 2009 0:23] Jess Balint
Thanks, will try on WinXP shortly.
[11 May 2009 8:35] Tonci Grgin
Avi, can you please try a snapshot build (http://downloads.mysql.com/snapshots.php) as I think this should have been fixed.
[11 Jun 2009 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[24 Feb 2010 14:57] Tonci Grgin
This is a duplicate of Bug#37872.