Bug #27896 Error when ^inserting^ null in a datetime field via ODBC5 driver
Submitted: 17 Apr 2007 18:44 Modified: 6 Jun 2007 11:56
Reporter: Louis Breda van Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:5.00.11, snapshot OS:Microsoft Windows (VISTA64)
Assigned to: Jess Balint
Tags: datetime, null

[17 Apr 2007 18:44] Louis Breda van
Description:
Hello,

I just noticed that my insert query breaks when it tries to insert NULL into a datefield which is alowed to be NULL. Problem occurs when using a MS-ACCESS 2003 insert query.

Please fix that in the next beta.

Sincerely,

Louis

How to repeat:
Create a table containing a datetime field without "not null" restriction. Try to insert data using a msaccess query.
[18 Apr 2007 17:48] Jess Balint
Works in snapshot. Please download and unzip with 7zip.

http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.0/
[18 Apr 2007 17:51] Jess Balint
Access just doesn't use that field in the insert and uses the default value. Shown in general log:

3232 Query       INSERT INTO  `test`  (`id`) VALUES (2)

Table schema is:

create table test (id int not null auto_increment, somedate date, primary key (id));
[18 Apr 2007 19:21] Louis Breda van
Hello Jess,

I am not sure about the test build and your testing so here a reaction:
- first I do not know how to install the nightly build. I assume running update.bat or install.bat !!??? I used install.bat  and do not see the difference
- I should explain a bit more about when I see the problem, in fact I think I notice the same problem in two different querys:
1) a query I use to reset particular fields to NULL  
UPDATE Klachten SET test.egOrderStart = Null, test.egOrderEnd = Null, etc;
2) INSERT INTO test ( ImportDate,  Source, CreationDate)
SELECT somedate AS ImportDate, 'thatsme' AS Source, otherdate
FROM test2 LEFT JOIN Klachten ON test2.MyKey = test.MyKey
WHERE (((test.MyKey) Is Null));

In both situation the query, which used to run OK when using a MS-ACCESS backend, breaks as soon as the to be inserted date happens to be NULL.

Sincerely,

Louis
[18 Apr 2007 20:22] Louis Breda van
Hello,

Two additional remarks:
- table name in given example was not correct, intenstion is clear I think
- i think i how to install the nightly build ( I saw a hint in another report). Among things I did was, remove driver using control panel, uninstall from commandline running as administrator, installing from commandline running as administrator. End result, ODBC driver is not correclty in the registry / does not show up anymore in the windows odbc-administrator tool (controlpanel/systemetc/odbcetc

Louis
[19 Apr 2007 7:26] Tonci Grgin
Hi Louis and thanks for your report.

As for installing snapshot, please take a look at archive 
mysql-connector-odbc-5.0-win32-nightly-YYYYmmDD.zip\Driver\lib\ folder and take whatever is there into SysWOW64 folder overwriting old files (you may wish to backup old files before overwriting). That should do the trick. Inform me please if new driver is in place.
[20 Apr 2007 12:17] Louis Breda van
Tonci,

I installed the latest build (yesterday build). The insert NULL problem is still there.

I also noticed another problem:

Sometimes when I have to to a large number of updates, I build them to a couple of transactions. That in order to force commits every xxxx records.

The code looks about like this:

beginloop
  cn.BeginTrans

  group of updates

  cn.CommitTrans
 
if not ready goto begin loop

with ODBC5 there is an error when reaching the first cn.begintrans
the first time "commands out of sync; you can't run this command now

Should I start another bug report for this one?

Louis
[23 Apr 2007 14:41] Tonci Grgin
Louis, if you can repeat INSERT NULL problem without transactions then open new report. If INSERT NULL is a problem only with transactions then correct the synopsis. Waiting on your feedback.
[23 Apr 2007 17:46] Louis Breda van
Tonci,

Sorry for the confusion. I noticed a couple of problems, as far as I know they are not at all related.

Louis
[1 May 2007 13:34] Tonci Grgin
Hi Louis. Can we deal with this first:
 - If original NULL DateTime problem still exists with latest connector/ODBC please post complete test case exhibiting this problem. If not, we should close this report.
 - For other problems, please open new reports.
[1 May 2007 14:17] Louis Breda van
Tonci,

Durring the latest testrun with the nightly build form I think 20/4 the problem was still there. I do not think it is repaired.

I order not to mix up thinks, I will open another issue related to the transaction problem.

Sincerely,

Louis
[1 May 2007 16:56] Tonci Grgin
Hi Louis. As I don't have office on my Vistax64 yet I tried with XPPro SP2 32bit. What I've found is that MS Access 2003 does not identify DATE field as being "date" but rather of Variant type thus throwing error on update with NULL value. To bypass this problem, and get to what Jess has been testing, you need to define field as being of Date type and nullable in Table designer. After that, Access doesn't use it in update statement when no data is provided:
070501 18:37:08	     18 Query       SELECT `bug27896`.`Id` FROM `bug27896`
070501 18:37:12	     18 Query       SET AUTOCOMMIT=0
		     18 Query       START TRANSACTION
		     18 Query       INSERT INTO  `bug27896`  (`Id`) VALUES (0)
		     18 Query       COMMIT
		     18 Query       SET AUTOCOMMIT=1
		     18 Query       SET AUTOCOMMIT=1
		     18 Query       SELECT `Id`,`NDat`  FROM `bug27896`  WHERE `Id` = 0
...
070501 18:37:25	     18 Quit
[1 May 2007 20:03] Louis Breda van
Tonci,

Since you where testing, I was trying to support you by reconecting my DB towards MySQL again (using ODBC5 snapshot).

However, I have one problem after the other to work arround, just too much. That is also the reason. I stopped using MySQL and certainly ODBC5 a few weeks ago.

I sincerely hope on improved versions soon. I surrely will try again. 

The transaction problem I spoke about two weeks ago, is one of the issues which stopped me. I use transactions on a couple op places in this DB you know. I simply can not work arround them.

Sincerely,

Louis
[2 May 2007 21:16] Jess Balint
Fix committed in rev 898, will be available in 2007-05-03 and lately nightly builds
[6 Jun 2007 11:56] MC Brown
A note has been added to the 5.00.12 changelog.