null and not null field

[bibopp]

Hi everybody

I'am using zeoslib 6.6.5 and Delphi7, my program read from sqlserver 2005 express database and write to mysql 5.1 database.

All works fine, zeoslib are great and faster on mysql side.

Testing my software I found a strange behaviour. The scenario is this:
table on mysql with 'field1' not null and 'field2' null allowed, then the first insert on table just created fails with message: 'Field field1 is required, but not supplied.'.

I'am using TZQuery with TZUpdateSQL associated, all my insert query are parametric so, for the example, query is: 'insert into table (field1, field2) values(:pfield1, pfield2)'.

Note that if I alter table and set nulls allowed on field1 all works fine (this is obvious) but all works also after I reset nulls on field1 exactly it must be. This is the behaviour I can't explain.

Any idea.

Thanks a lot.

[MYSQL889]

Dear Bibopp!

First, excuse my English, I use a translator from Spanish to English!

It's true!, Reported the problem does not occur with MySQL versions below 5.1. A solution that solves the problem is to indicate the default when creating the table:

Code: Select all

CREATE TABLE `articles` (
    `CODE` varchar (40),
    `Article` varchar (60),
    `Color` varchar (30) NOT NULL default '',
    PRIMARY KEY ( `CODE`)
) Type = InnoDB;

If you put this same code and makes an insert in MySQL 5.1 does not include any data in the color field, will have no error.

However, if you use this sql code (note that this does not include the default reference), you get an error message [Field ... is required, but not supplied]. if it does not insert a value for the COLOR field.:

Code: Select all

CREATE TABLE `articles` (
    `CODE` varchar (40),
    `Article` varchar (60),
    `Color` varchar (30) NOT NULL,
    PRIMARY KEY ( `CODE`)
) Type = InnoDB;

Using a version of MySQL 4.1 or 5.0 ... it is not necessary to include Default, not if the difference is MySql Zeos or ...??

Greetings,

Sebas

Note: Test conducted with a DBGrid in Delphi 7, Zeos 6.6.6 and MySQL 5.1.36. With version 6.6 5 and Alpha 7 the behavior is identical.

Hi everybody

I'am using zeoslib 6.6.5 and Delphi7, my program read from sqlserver 2005 express database and write to mysql 5.1 database.

All works fine, zeoslib are great and faster on mysql side.

Testing my software I found a strange behaviour. The scenario is this:
table on mysql with 'field1' not null and 'field2' null allowed, then the first insert on table just created fails with message: 'Field field1 is required, but not supplied.'.

I'am using TZQuery with TZUpdateSQL associated, all my insert query are parametric so, for the example, query is: 'insert into table (field1, field2) values(:pfield1, pfield2)'.

Note that if I alter table and set nulls allowed on field1 all works fine (this is obvious) but all works also after I reset nulls on field1 exactly it must be. This is the behaviour I can't explain.

Any idea.

Thanks a lot.

[mdaems]

Hi,

This is a little strange.
Seems to me like mysql might return different values from the SHOW TABLES FROM %s LIKE '%s' statement depending on server version. Can you compare this? Here's the code in Zeoslib raising this error.

Code: Select all

  procedure Checkrequired;

  var
    I: longint;
    columnindex : integer;

  begin
    For I:=0 to Fields.Count-1 do
      With Fields[i] do
        Case State of
         dsEdit:
          if Required and not ReadOnly and (FieldKind=fkData) and IsNull then
            raise EZDatabaseError.Create(Format(SNeedField,[DisplayName]));
         dsInsert:
          if Required and not ReadOnly and (FieldKind=fkData) and IsNull then
            begin
           // allow autoincrement and defaulted fields to be null;
              columnindex := Resultset.FindColumn(Fields[i].FieldName);
              if (Columnindex = 0) or
                 (not Resultset.GetMetadata.HasDefaultValue(columnIndex) and
                  not Resultset.GetMetadata.IsAutoIncrement(columnIndex)) then
                raise EZDatabaseError.Create(Format(SNeedField,[DisplayName]));
            end;
        End;
  end;

This indicates for me 'HasDefaultValue' returns false for mysql5.1 and not for versions below.
If you can send the create table statement I could try to check this myself easily.

Mark

[MYSQL889]

Hi Mark!

Excuse my English ....

I could not execute SHOW TABLES FROM% s LIKE '% s' in the MySQL server as you asked me, I am beginner with mysql and I have not done it well ... Anyway, I tried to do another test in case it is of some help:

We have the following table with the color field declared as NOT NULL: Note the table has been created with the following SQL code from a server PhpMyAdmin MYSQL 5.1.36

Code: Select all

CREATE TABLE `TESTNULL` (
  `CODE` int(6) AUTO_INCREMENT,
  `Article` varchar(30),
  `Color` varchar(20) NOT NULL,
  PRIMARY KEY (`CODE`)
) ENGINE=InnoDB

Since Delphi 7 and zeos 6.6.6, connecting the table to a field DBGRID and property required color = FALSE, COLOR left the field without data entry. We get an error Column 'Color' can not be null

LOGSQL BY ZEOS:

2010-01-29 00:13:06 cat: Execute, proto: mysql-5, msg: INSERT INTO probe.testnull (CODE,Article,Color) VALUES (NULL,'Racquet',NULL), errcode: 1048, error: Column 'Color' cannot be null


The same table doing an insert with the same data but from MYphpAdmin throws the following SQL, which is different and does not generate error:

INSERT INTO `probe`.`testnull` (`CODE`, `Article`, `Color`) VALUES (NULL, 'Racquet ', '');


NULL Zeos try to put the color field, and leave it blank phpMyAdmin Wampserver.

Test Zeos 6.6.6 / Delphi 7 on Windows 7 ---- and Wampserver 2.0i http://www.wampserver.com/ [php + apache mysl +] over a local mysql server.


Sebas.

Hi,

This is a little strange.
Seems to me like mysql might return different values from the SHOW TABLES FROM %s LIKE '%s' statement depending on server version. Can you compare this? Here's the code in Zeoslib raising this error.

Code: Select all

  procedure Checkrequired;

  var
    I: longint;
    columnindex : integer;

  begin
    For I:=0 to Fields.Count-1 do
      With Fields[i] do
        Case State of
         dsEdit:
          if Required and not ReadOnly and (FieldKind=fkData) and IsNull then
            raise EZDatabaseError.Create(Format(SNeedField,[DisplayName]));
         dsInsert:
          if Required and not ReadOnly and (FieldKind=fkData) and IsNull then
            begin
           // allow autoincrement and defaulted fields to be null;
              columnindex := Resultset.FindColumn(Fields[i].FieldName);
              if (Columnindex = 0) or
                 (not Resultset.GetMetadata.HasDefaultValue(columnIndex) and
                  not Resultset.GetMetadata.IsAutoIncrement(columnIndex)) then
                raise EZDatabaseError.Create(Format(SNeedField,[DisplayName]));
            end;
        End;
  end;

This indicates for me 'HasDefaultValue' returns false for mysql5.1 and not for versions below.
If you can send the create table statement I could try to check this myself easily.

Mark

[mdaems]

Well,

Actually, I believe this problem is really related to mysql behavior.
Have a look at the mysql documentation.
I have the impression strict mode is enabled on the new server and it wasn't on the old server.
Unfortunately zeoslib can't be forced to send an empty string instead of null to the server when no data has been entered.

Mark