Error: Field ... must have a value

[tam]

I just have installed ZEOSLIB_TESTING_REV329.zip (alos happens with ZEOSDBO-6.6.2-rc.zip) and the following error happens:

Default values are not fetched correctly from the table when I use the following example:

Code: Select all

  With q1 do begin
    Close;
    Sql.Clear;
    Sql.Add('drop table if exists test');
    ExecSQL;

    Sql.Clear;
    Sql.Add('create table test (');
    Sql.Add('  id int unsigned not null auto_increment primary key,');
    Sql.Add('  s1 varchar(20) not null,');
    Sql.Add('  s2 varchar(120) not null,');
    Sql.Add('  s3 varchar(40) not null');
    Sql.Add(');');
    ExecSQL;

    Sql.Clear;
    Sql.Add('insert into test (id) values (0);');
    ExecSQL;

    Sql.Clear;
    Sql.Add('select * from test');
    Open;
    Append;
    q1.FieldByName('id').AsInteger := 0;
    Post;
  end;

The 'insert into' is ok, but the Post Command raises a "Field 's1' must have a value" - I think this is because Zeos wants to insert a NULL for s1 which is not allowed.

Edit: 6.6.1-beta works ok

[mdaems]

Do you mean this behaviour changed between 6.6.1 and 6.6.2?

As I see this : this behaviour is correct. What did 6.6.1 beta insert when the post was executed?
Please, if you can test it use a TZSQLMonitor, check the issue statements.

Why the insert statement works : Mysql uses defaults, even if not specified at table creation time. This is not standard sql behaviour. If you set mysql sql_mode parameter to a more 'standard sql' mode, this behaviour woulds work either, I think. I know no other databases that would allow this insert for this table definition.

Why the append doesn't : s1 (and s2, s3) are defined on the database as required. Zeos notices this and sets the required property to true for these fields. If you want these to be posted as null values nevertheless, set the required field property to false 'manually'.

Mark

[tam]

Hi Mark,

Do you mean this behaviour changed between 6.6.1 and 6.6.2?

Yes.

As I see this : this behaviour is correct. What did 6.6.1 beta insert when the post was executed?

INSERT INTO test.test (id,s1,s2,s3) VALUES (0,'','','')

[gto]

Hello tam!

Well, you're creating a table with four fields, and all of them are NOT NULL, which means you must fill a value for them!

Am I right?

[edit] It look's like Zeos changed the way it manage nulls. If in a previous version it was posting '', which is not null, for null fields, it was wrong.

[tam]

Well, you're creating a table with four fields, and all of them are NOT NULL, which means you must fill a value for them! Am I right?

You're right. But plain MySQL also works, and because it worked with Zeos also since latest rc I would have to change quite a lot of stuff.

[mdaems]

Did you try setting default values on the table? Not sure if zeoslib takes those into account, however.
What about dropping the not null constraint? As mysql is 'ignoring' or fixing it anyway it doesn't make much sense in your case.

Also : don't depend on this default behaviour for future mysql versions. As mysql grows toward better compatibility with standards using this feature may become 'deprecated' (but somehow supported for backward compatibility at first).

My advise : "change your program setting required to false for these fields and add defaults on your columns" or "change your database structure allowing null's for these fields". Both solutions are less dependent on this mysql "feature" and should also work with other database servers (firebird,postgres) if you ever decide to support these as well.

Mark

[tam]

Did you try setting default values on the table? Not sure if zeoslib takes those into account, however.

Also, default values are ignored. At least these should be used by Zeos.

What about dropping the not null constraint? As mysql is 'ig
noring' or fixing it anyway it doesn't make much sense in your case.

I have serveral hunderds of them.

Ok, I would appreciate if the new Zeos would work like the 6.6.1-beta, but at least given default values from the tables should be used.

Example, Post gives a i1 must have a value error:

Code: Select all

  With q1 do begin
    Close;
    Sql.Clear;
    Sql.Add('drop table if exists test');
    ExecSQL;

    Sql.Clear;
    Sql.Add('create table test (');
    Sql.Add('  id int unsigned not null auto_increment primary key,');
    Sql.Add('  i1 smallint(6) NOT NULL default 0');
    Sql.Add(');');
    ExecSQL;

    Sql.Clear;
    Sql.Add('insert into test (id) values (0);');
    ExecSQL;

    Sql.Clear;
    Sql.Add('select * from test');
    Open;
    Append;
    q1.FieldByName('id').AsInteger := 0;
    Post;
  end;

[gto]

Ok, I would appreciate if the new Zeos would work like the 6.6.1-beta, but at least given default values from the tables should be used.

Example, Post gives a i1 must have a value error:

I agree you! This is a bug, and should be solved.

Please open a bug in Mantis, then I can help you resolving it

[tam]

Please open a bug in Mantis, then I can help you resolving it

Done. See http://zeosbugs.firmos.at/view.php?id=90

[mdaems]

tam, gto,

I have a problem...
I've been looking into your problem. Build a small project using the code above. Found a 'solution'.
in procedure TZAbstractRODataset.InternalInitFieldDefs change this line of code:

Code: Select all

Required := IsWritable(I) and (IsNullable(I) = ntNoNulls) ***and Not HasDefaultValue(I)*** ;

This causes a new problem, however...
Just check this extention to your sample

Code: Select all

With q1 do begin
    Close;
    Sql.Clear;
    Sql.Add('drop table /*!if exists*/ test');
    ExecSQL;

    Sql.Clear;
    Sql.Add('create table test (');
    Sql.Add('  id int unsigned not null auto_increment primary key');
    Sql.Add('  ,i1 smallint(6) default 23');
    Sql.Add('  ,i2 varchar(6) NOT NULL default ''az''');
    Sql.Add('  ,i3 timestamp NOT NULL default CURRENT_TIMESTAMP ');
    Sql.Add(');');
    ExecSQL;

    Sql.Clear;
    Sql.Add('insert into test (id) values (0);');
    ExecSQL;

    Sql.Clear;
    Sql.Add('select * from test');
    Open;

    Edit;
    q1.FieldByName('id').Clear;
    Post;

    Append;
    q1.FieldByName('id').AsInteger := 0;
    Post;

    first;
    Edit;
    q1.FieldByName('i1').Clear;
    q1.FieldByName('i3').Clear;
    Post;

  end;

This gives following log output :

Code: Select all

2008-02-03 01:08:57 cat: Connect, proto: mysql-5, msg: CONNECT TO "test" AS USER "root"
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: drop table /*!if exists*/ test

2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: create table test (
  id int unsigned not null auto_increment primary key
  ,i1 smallint(6) default 23
  ,i2 varchar(6) NOT NULL default 'az'
  ,i3 timestamp NOT NULL default CURRENT_TIMESTAMP 
);

2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: insert into test (id) values (0);

2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: select * from test

2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: SHOW TABLES FROM test LIKE 'test'
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: SHOW COLUMNS FROM test.test LIKE '%'
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: SHOW KEYS FROM test.test
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: UPDATE test.test SET id=NULL WHERE id=1
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: SELECT 23,'az',CURRENT_TIMESTAMP
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: INSERT INTO test.test (id,i1,i2,i3) VALUES (0,23,'az','2008-02-03 01:08:57')
2008-02-03 01:08:57 cat: Execute, proto: mysql-5, msg: UPDATE test.test SET i1=23,i3=CURRENT_TIMESTAMP WHERE id IS NULL 
2008-02-03 01:08:57 cat: Disconnect, proto: mysql-5, msg: DISCONNECT FROM "test"

You'll notice all inserts work like expected. However, the edits allow serious errors.
id field : can be set null (resulting in a 0 value) (This is a bug right now already)
i1 field : can't be set to null!!
i3 field : setting to null causes an update of the timestamp. However, default values shouldn't have an impact on updates...

Conclusion :
I don't like this solution. Now it looks like the AutoIncrement implementation isn't perfect either...

Instead of the change in the zeos code the programmer should add

Code: Select all

    q1.FieldByName('i1').Required := false;
    q1.FieldByName('i2').Required := false;
    q1.FieldByName('i3').Required := false;

after opening the query. This causes exactly the same behaviour, but forces the programmer to take this behaviour into account.

Your opinion?

[tam]

Your opinion?

6.6.1-beta is doing fine. Isn't it possible to restore it's behaviour into the new Zeos?

[mdaems]

Yes, it's possible.
No, I'm not really willing to do it. (But you can patch your version using the change above).

I'm now looking at a way to fix the evaluation of what fields are required and which are not.

Probably I'll end up overwriting the TDataSet.InternalPost method. (From fpc and Delphi vcl) Because this method treats inserts the same as updates, which is not the same in the case of NOT null or autoincrement columns...
In D7 this InternalPost only checks for RequiredFields but using a private(!) method. So we'll have to overwrite the whole function.

To be continued...

[tam]

Yes, it's possible.
No, I'm not really willing to do it. (But you can patch your version using the change above).

Ok, I just spent a couple of hours to change our database, so the only problem remaining is the default value stuff. If you're going to fix this, everything is fine again.

[gto]

Some results of my research:

The solution proposed my mdaems seems correct but not complete. Zeos isn't telling the fields the correct required property, so it must be changed, something in the way you did. In my tests I only put two () more:

Code: Select all

Required := (IsWritable(I) and (IsNullable(I) = ntNoNulls)) and not HasDefaultValue(I);

This do the trick for that problem with some oddities:

- If you add the component to a form, double-click it, right-click the blank field list and select "Add All", they show up with wrong required properties. By the way, if you leave the list empty and access it only in runtime (using FieldByName), the required is filled correctly. It may be because I'm not installing the whole Zeos when changing the source, but only recompiling the ZComponent package. Whatever.

- The HasDefaultValue property, which is filled in TZMySQLDatabaseMetadata.GetColumns, lie when the field type is CHAR, BLOB, TEXT or SET. By the line 1229 of dbc\ZDbcMySqlMetadata.pas you can find the following comment:

// MySQL bizarity 2:
// For CHAR, BLOB, TEXT and SET types, '' either means: default value is '' or: no default value
// There's absolutely no way of telling when using SHOW COLUMNS FROM,
// the correct information can /only/ be discerned by using information_schema.
// TODO: For now, just use '' as default value for these types, but this should really be fixed to use information_schema.
// For ENUM types, '' means: default value is first value in enum set
// For other types, '' means: no default value

Holly mother crap, we're fucked up.

By the way, mdaems, when I've changed my source, I can post using the following SQL:

Code: Select all

   ZQuery1.Append;
   ZQuery1.FieldByName('i2').Value := 'dio';
   ZQuery1.Post;

Which means I can post without filling i1 neither id, so they were null on the SQL (I think) and the result line in the database showed i1 = 23 and id = 5 (The correct sequence). Remembering that I can only post successfully that way when I don't fill up the field list at the query in design-time, but only access then in runtime, using FieldByName.


Now I think we must follow two paths:
- Do this change in ZAbstractRODataset and deal with the problems it may produce.
- Do the change on posting routines to fix up the problems mdaems related. (About TIMESTAMP and AutoIncrement Fields).

To be continued!

[mdaems]

Try the attached patched files. In this file all not null columns are mandatory, unless
- you are inserting : then autoinc and default values are used
- you set the required property false : then you can set every field to null

Mark