How to Update Date Field which is NULL

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

How to Update Date Field which is NULL

Post by Zero-G. » Thu 10 Apr 2008 13:45

Hey

I creaty dynamicly a Script with Insert Statements. This looks like:

Code: Select all

scrFile.ScriptText &= "Delete From IproHead where manufacturer_code='" & rHead(9).Feldwert & "';"
        scrFile.ScriptText &= String.Format("INSERT INTO `voptneu`.`iprohead` (`version`, " & _
        "`software_id`, `comment`, `uid_manufacturer`, `uid_postedit`, `valid_from`, " & _
        "`valid_until`, `country`, `language`, `manufacturer_code`, `manufacturer_subcode`, " & _
        "`manufacturer_name`, `manufacturer_subname`, `manufactuer_name_1`, " & _
        "`manufacturer_name_2`, `street`, `zip_code`, `city`, `po_box_zip_code`, " & _
        "`po_box_text`, `phone`, `fax`, `phone_order`, `fax_order`, `mail`, `url`, " & _
        "`pricedefinition_cylinder`, `cylindergroup_base`, `cylindergroup_1`, " & _
        "`cylindergroup_2`, `prismgroup_1`, `prismgroup_2`, `prismgroup_3`, " & _
        "`prismgroup_4`, `prismgroup_5`, `currencydescription`, " & _
        "`currencydescription_decimals`, `pricefield_01`, `pricefield_02`, `pricefield_03`, " & _
        "`pricefield_04`, `pricefield_05`, `pricefield_description_01`, " & _
        "`pricefield_description_02`, `pricefield_description_03`, `pricefield_description_04`, " & _
        "`pricefield_description_05`, `characterset`) VALUES (" & _
        "'{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', " & _
        "'{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', " & _
        "'{21}', '{22}', '{23}', '{24}', '{25}', '{26}', '{27}', '{28}', " & _
        "'{29}', '{30}', '{31}', '{32}', '{33}', '{34}', '{35}', '{36}', '{37}', '{38}', " & _
        "'{39}', '{40}', '{41}', '{42}', '{43}', '{44}', '{45}', '{46}', '{47}';)", _
        rHead(0).Feldwert, rHead(1).Feldwert, rHead(2).Feldwert, rHead(3).Feldwert, rHead(4).Feldwert, _
        rHead(5).Feldwert, rHead(6).Feldwert, rHead(7).Feldwert, rHead(8).Feldwert, rHead(9).Feldwert, _
        rHead(10).Feldwert, rHead(11).Feldwert, rHead(12).Feldwert, rHead(13).Feldwert, rHead(14).Feldwert, _
        rHead(15).Feldwert, rHead(16).Feldwert, rHead(17).Feldwert, rHead(18).Feldwert, rHead(19).Feldwert, _
        rHead(20).Feldwert, rHead(21).Feldwert, rHead(22).Feldwert, rHead(23).Feldwert, rHead(24).Feldwert, _
        rHead(25).Feldwert, rHead(26).Feldwert, rHead(27).Feldwert, rHead(28).Feldwert, rHead(29).Feldwert, _
        rHead(30).Feldwert, rHead(31).Feldwert, rHead(32).Feldwert, rHead(33).Feldwert, rHead(34).Feldwert, _
        rHead(35).Feldwert, rHead(36).Feldwert, rHead(37).Feldwert, rHead(38).Feldwert, rHead(39).Feldwert, _
        rHead(40).Feldwert, rHead(41).Feldwert, rHead(42).Feldwert, rHead(43).Feldwert, rHead(44).Feldwert, _
        rHead(45).Feldwert, rHead(46).Feldwert, rHead(47).Feldwert)
The two fields Valid From & Valid Until can have NO Data this means in the Values Statement are the fields like = '', ''
But there I get an error. - How to solve this problem? - What di I have to insert, that the Date can be NULL?

The field on the server has Allow Null activated.

THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Thu 10 Apr 2008 14:02

What exception do you receive?
Please post the table definition script.
Check in debug mode, that you have all valid data when inserting is being made.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Fri 11 Apr 2008 13:34

Hey

The error is: Incorrect date value: ' ' for column 'valid_until' at row 1
The Definition of the table is:

Code: Select all

- Tabelle: iprohead

-- DROP TABLE `iprohead`;

CREATE TABLE `iprohead` (
  `version`                       varchar(10),
  `software_id`                   varchar(40),
  `comment`                       varchar(200),
  `uid_manufacturer`              varchar(50) NOT NULL,
  `uid_postedit`                  varchar(50),
  `valid_from`                    date,
  `valid_until`                   date,
  `country`                       varchar(2),
  `language`                      varchar(2),
  `manufacturer_code`             varchar(3),
  `manufacturer_subcode`          varchar(3),
  `manufacturer_name`             varchar(40),
  `manufacturer_subname`          varchar(40),
  `manufactuer_name_1`            varchar(40),
  `manufacturer_name_2`           varchar(40),
  `street`                        varchar(40),
  `zip_code`                      varchar(8),
  `city`                          varchar(40),
  `po_box_zip_code`               varchar(8),
  `po_box_text`                   varchar(40),
  `phone`                         varchar(40),
  `fax`                           varchar(40),
  `phone_order`                   varchar(40),
  `fax_order`                     varchar(40),
  `mail`                          varchar(40),
  `url`                           varchar(40),
  `pricedefinition_cylinder`      varchar(1),
  `cylindergroup_base`            int(1),
  `cylindergroup_1`               int(1),
  `cylindergroup_2`               int(1),
  `prismgroup_1`                  int(2),
  `prismgroup_2`                  int(2),
  `prismgroup_3`                  int(2),
  `prismgroup_4`                  int(2),
  `prismgroup_5`                  int(2),
  `currencydescription`           varchar(3),
  `currencydescription_decimals`  varchar(3),
  `pricefield_01`                 int(2),
  `pricefield_02`                 int(2),
  `pricefield_03`                 int(2),
  `pricefield_04`                 int(2),
  `pricefield_05`                 int(2),
  `pricefield_description_01`     varchar(40),
  `pricefield_description_02`     varchar(40),
  `pricefield_description_03`     varchar(40),
  `pricefield_description_04`     varchar(40),
  `pricefield_description_05`     varchar(40),
  `characterset`                  int(2),
  `id`                            bigint AUTO_INCREMENT NOT NULL,
  /* Schlüssel */
  PRIMARY KEY (`id`)
) ENGINE = InnoDB
  CHARACTER SET `utf8` COLLATE `utf8_general_ci`;
THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 11 Apr 2008 14:06

I am running the following script with MyDirect without such error:

Code: Select all

create table a(
id int auto_increment primary key,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');
Do you have an exception running this script?
Please post the version and edition of MyDirect .NET, MySQL server.
My testing configuration is:
MyDirect .NET Professional 4.50.26.0
MySQL server 5.1.11

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Fri 11 Apr 2008 14:17

The whole Exception =
CoreLab.MySql.MySqlException was unhandled by user code
ErrorCode=-2147467259
Message="Incorrect date value: ' ' for column 'valid_until' at row 1"
Source="CoreLab.Data"
StackTrace:
bei CoreLab.Common.DbScript.ExecuteNext(IDataReader& reader)
bei CoreLab.Common.DbScript.Execute()
bei Ipro_v6.Form1.bWorker_DoWork(Object sender, DoWorkEventArgs e) in C:\Dokumente und Einstellungen\Administrator\Desktop\VS2005\IPRO\Ipro v6\Ipro v6\Form1.vb:Zeile 532.
bei System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
bei System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

my MyDirect.NET = Professional 4.50.26.0
my Server = MySQL 5.0.45-community-nt
my Client = 5.1.11

I tried throug a 3rd Party programm. - There I get an error with your script:
The Create Statement = OK
The first Statement (with null) = OK
The second statement (with '') = ERROR
The third statement(with ' ') = ERROR

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 11 Apr 2008 14:23

Do you recieve the error running this script with MySQLScript component?

Code: Select all

create table a(
id int auto_increment primary key,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Fri 11 Apr 2008 14:26

Yes, the same as in the 3rd Party programm

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 11 Apr 2008 14:39

The problem is in MySQL server.
I've been testing the SQL script with MySQL Command Line Client
on MySQL 4.1.18 and MySQL 5.1.11 - no problem.
But on MySQL server 5.0.26 it throws the mentioned exception.
I suppose it happens with all MySQL 5.0.* versions.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Fri 11 Apr 2008 14:41

It seems. - I gonna download a newer version.

THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Fri 11 Apr 2008 14:43

Please let us know if the problem is solved.

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Tue 15 Apr 2008 18:59

Hey

I do use Server 5.1.23 now. - But the error is still there when I try to update a DateField with Value = '' or Value = ' '

THX

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 16 Apr 2008 08:05

Are you able to run such SQL script on the newer server with MySQL Command Line Client?

Code: Select all

create table a(
id int,
date_in date
);
INSERT INTO a VALUES(1,null);
INSERT INTO a VALUES(2,'');
INSERT INTO a VALUES(3,' ');

Zero-G.
Posts: 398
Joined: Sat 09 Dec 2006 12:20

Post by Zero-G. » Wed 16 Apr 2008 08:13

Hey

No, I have tried on my third Party Programm which is designed for the mySQL. -

This sayed, that the query with null = OK, and the other two: You have an error in your mysql syntax...

Alexey.mdr
Posts: 729
Joined: Thu 13 Dec 2007 10:24

Post by Alexey.mdr » Wed 16 Apr 2008 08:39

Ok. So basically the issue is on the server side.
As a solution, I would suggest to check the date for empty values ('', '_', '__' ,...)
before insert, and replace them with null
if you'd prefer to stick to this version of MySQL server.
This additional check might slow down the application if it makes numerous inserts.

Post Reply