MySQL Bugs: #42070: ODBC driver not writing NULL to integer column

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:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	ODBC driver integer NULL

Description:
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`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

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
    objRS.AddNew
end if
objRS("zone_id") = null
objRS("unit_id") = null
objRS.Update
objRS.Close
%>

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.

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

Jess

Jess;

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.

Hi Avi. No way to make this fail on my W2008 x64 server with IIS 7... Back to Jess.

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

Thanks, will try on WinXP shortly.

Avi, can you please try a snapshot build (http://downloads.mysql.com/snapshots.php) as I think this should have been fixed.

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

This is a duplicate of Bug#37872.