Description:
The MyODBC 5.1.5 driver is not able to write NULL values to a column of type datetime, varchar or int when using a CLIENTside cursor (adUseClient) on MySQL 5.1.34.
- Trying to add a null value to a datetime field results in an error
- Trying to add a null value to an int field results in random values
- Trying to add a null value to a varchar field results in an empty string instead of NULL
Using a SERVERside cursor (adUseServer) has no problems. But for our project using server side cursors is not an option because of performance issues and because server side cursors cannot handle updates on joined tables.
How to repeat:
Step 1: Create a table, named 'table1', containing a datetime, int and varchar field:
CREATE TABLE `table1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`datefield` datetime DEFAULT NULL,
`intfield` int(11) DEFAULT NULL,
`strfield` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
Step 2: Create some test-code in Classic ASP using Jscript and save this to a file called test.asp:
------------------------------------------------------------
<SCRIPT RUNAT="server" LANGUAGE="JScript">
//---- CursorLocationEnum Values ----
adUseServer = 2
adUseClient = 3
//---- CursorTypeEnum Values ----
adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3
//---- LockTypeEnum Values ----
adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4
conn = Server.CreateObject("ADODB.Connection");
conn.ConnectionString = " DRIVER={MySQL ODBC 5.1 Driver};" +
" SERVER=localhost;" +
" DATABASE=test;" +
" UID=root;PWD=******; OPTION=3; PORT=3306";
conn.Open;
rs = Server.CreateObject("ADODB.RecordSet");
rs.CursorLocation = adUseClient;
rs.Open ("SELECT id, datefield, intfield, strfield FROM table1", conn, adOpenDynamic, adLockOptimistic);
rs.AddNew();
//Trying to add a null value to column 1 (datefield) results in an error
//on update statement
rs.fields(1).value = null;
//Trying to add a null value to column 2 (intfield) results in a random
//value beeing stored in the database
rs.fields(2).value = null;
//Trying to add a null value to column 3 (strfield) results in an empty
//string, (not a NULL value !) beeing stored in the database
rs.fields(3).value = null;
rs.Update;
rs.Close;
conn.Close;
Response.Write("update done");
</SCRIPT>
------------------------------------------------------------
Description: The MyODBC 5.1.5 driver is not able to write NULL values to a column of type datetime, varchar or int when using a CLIENTside cursor (adUseClient) on MySQL 5.1.34. - Trying to add a null value to a datetime field results in an error - Trying to add a null value to an int field results in random values - Trying to add a null value to a varchar field results in an empty string instead of NULL Using a SERVERside cursor (adUseServer) has no problems. But for our project using server side cursors is not an option because of performance issues and because server side cursors cannot handle updates on joined tables. How to repeat: Step 1: Create a table, named 'table1', containing a datetime, int and varchar field: CREATE TABLE `table1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `datefield` datetime DEFAULT NULL, `intfield` int(11) DEFAULT NULL, `strfield` varchar(45) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; Step 2: Create some test-code in Classic ASP using Jscript and save this to a file called test.asp: ------------------------------------------------------------ <SCRIPT RUNAT="server" LANGUAGE="JScript"> //---- CursorLocationEnum Values ---- adUseServer = 2 adUseClient = 3 //---- CursorTypeEnum Values ---- adOpenForwardOnly = 0 adOpenKeyset = 1 adOpenDynamic = 2 adOpenStatic = 3 //---- LockTypeEnum Values ---- adLockReadOnly = 1 adLockPessimistic = 2 adLockOptimistic = 3 adLockBatchOptimistic = 4 conn = Server.CreateObject("ADODB.Connection"); conn.ConnectionString = " DRIVER={MySQL ODBC 5.1 Driver};" + " SERVER=localhost;" + " DATABASE=test;" + " UID=root;PWD=******; OPTION=3; PORT=3306"; conn.Open; rs = Server.CreateObject("ADODB.RecordSet"); rs.CursorLocation = adUseClient; rs.Open ("SELECT id, datefield, intfield, strfield FROM table1", conn, adOpenDynamic, adLockOptimistic); rs.AddNew(); //Trying to add a null value to column 1 (datefield) results in an error //on update statement rs.fields(1).value = null; //Trying to add a null value to column 2 (intfield) results in a random //value beeing stored in the database rs.fields(2).value = null; //Trying to add a null value to column 3 (strfield) results in an empty //string, (not a NULL value !) beeing stored in the database rs.fields(3).value = null; rs.Update; rs.Close; conn.Close; Response.Write("update done"); </SCRIPT> ------------------------------------------------------------