Default values for not null fields on new record

[adi3000]

This has been discussed on

http://seegernet.koolhost.de/zeosforum/ ... .php?t=298
http://seegernet.koolhost.de/zeosforum/ ... .php?t=362

The problem is : Zeos does not permit a field to be 'nullable' if it's defined as not null even if has an default value. – in fact it is not null when the record arrive in the database.

This is happened only with the new version of Zeos … I use an older version from 2005 that works correctly (if an not null field has an default value it is not required, if has not a default value defined it is required) . What can be changed to have the same functionality. (I use mySQL).

[zippo]

I noticed the same change between 5.x and 6.x, too. Don't know why, but this comlicates a lot the upgrade from older to newer Zeos... I tried almost everything and have the same problem. I had to put a lot of rows like this before posting data:

if Ds.FieldByNAme('FLD').IsNull (or AsString='' .. etc.) then Ds.FieldByNAme('FLD').AsString := xxx

Dvelopers, help..

[zippo]

One more thing - doesn't the server's default values be filled automatically? If yes, then it's definitely a Zeos bug..

Developer, could you please help here, because it is a critical feature/bug/situation?

[btrewern]

As I understand it this is only because the field objects are initialized as Required for Not Null fields. If you change that then everything just works.

Regards,

Ben

[zippo]

It will also fill the default values?

[btrewern]

I use ZeosLib 6.5.1 with PostgreSQL 8.x and everything relating to default values works well.

Regards,

Ben

[adi3000]

Hi,

What zippo sad is not a good idea, we cannot change all the code to test if the field is null or not and if it has a default value to take the appropriate action. What btrewern sad is either not good because if you use an TZQuery with an ‘select * ...’ an you don’t define all fields, because some times I don’t care about what fields are, I cannot set Required option. I heard other idea, to change in the database all fileds to accept null ... but why to change all the database, it is very large and it works before as it is ... for the moment I will keep the version of Zeos what I have working, but I need the new functionality from the newer version and I think we need to resolve this.

I will repeat: the problem I reported I noticed in MySQL, with this I work. I have the following situation: a table :

CREATE TABLE `access` (
`ID` int(11) unsigned NOT NULL auto_increment,
`NAME1` varchar(100) NOT NULL default 'TEST1',
`NAME2` varchar(100) NOT NULL default ‘TEST2’,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

if I do insert into access (NAME2) values (‘DEMO’); I will insert an row with ID=1 NAME1=TEST1, NAME2=DEMO ... OK ? , if I do the same but using an TDBGrid in Delphi , and I introduce ‘DEMO’ in NAME2 field (all the fields appear on grid) the rest are empty , now Zeos must use for the NAME1 ‘TEST1’ and for NAME2 ‘DEMO’ (what I put) for ID – it is auto increment and is working, next I move the cursor down and I get the error : NAME1 must have an value cannot be null ... but it is not ... this is the problem .... I did the same test with an version 6.5.1 from may 2005 and is working .... ??????? any idea ?????????

Regards,

Adrian

[mdaems]

In short this means that ZEOS should NOT raise an error when someone tries to insert null into a NOT NULL column having a default value on the database.
At least not when dealing with Mysql. (I don't know the specifications of other DBs)
For mysql it's not necessary to remove the field from the insert statement, but I can imagine some other DBs don't accept this kind of statement.

Mark

[btrewern]

Adrian,

If you want 'SELECT * FROM ...' to work then try putting something like:

Code: Select all

var
  i: Integer;
begin
  for i := 0 to ZQuery1.FieldCount - 1 do
  begin
    if ZQuery1.Fields[i].Required then
      ZQuery1.Fields[i].Required := False;
  end;
end;

in the AfterOpen event.

Ben.

[adi3000]

I found what is changed between what version I use and the new version: In ZAbstractDataset.pas at line 420 it is an inherited; I belive we need to put:

{$IFNDEF FPC}
inherited;
{$ENDIF}

and this resolve the problem in windows, on delphi - I will check if in lazarus work ...

Adrian

lte: In Lazarus it's working too ...

[plamendp]

This is a pretty old thread but I'd like to add my 0.02:

It might be connected to the AutoGeneratedValue TField property. As far as I can see Zeos does not implement this feature.

Quote form Delphi 10 Help:

TField.AutoGenerateValue Property

Indicates whether the field values can be generated by the server rather than entered by the user.

Class
TField

Syntax


[Delphi] property AutoGenerateValue: TAutoRefreshFlag read FAutoGenerateValue write SetAutoGenerateValue;


Description
Use AutoGenerateValue to indicate when field values can be generated by the database. Such fields include autoincrement fields and fields with default values.

Some database drivers can't determine which fields can have values supplied automatically by the server. This can lead to problems when posting edits. For these cases, applications can provide this information by setting the AutoGenerateValue property. Use this property to indicate whether the field is an autoincrement field (arAutoInc), a field with a default value (arDefault), or a field whose value is not automatically generated (arNone).

AutoGenerateValue is not always required. Fields with an AutoGenerateValue property of arNone may still be updated if the driver supplies metadata that indicates it has a default value or an autoincrement value. However, it is good practice to supply this information because not all drivers supply this metadata.
If you do use AutoGenerateValue for any fields that have values generated by the database, you must set AutoGenerateValue to arAutoInc or arDefault on every field whose value is automatically generated by the database. This is because when records are posted, the application immediately executes a query to fetch the automatically-generated values. If you leave an automatically-generated field with AutoGenerateValue set to arNone, this query looks for a record with a blank value on that field, which will fail to locate the correct record.
Warning:
AutoGenerateValue can fail in some cases when the automatically-generated field value is on a key field. This is because the query that re-fetches the new field value can't always uniquely locate the record that was just posted.

Note:
Trying to change the AutoGenerateValue property when the field's dataset is open generates an exception. Thus, AutoGenerateValue only works with persistent field components, which remain when the dataset is closed.

In short, Zeos should check (using metadata) if the filed would have a default value assigned by the server (be it AutoInc or some default value) and should not raise Exception if taht's the case. If Zeos database driver is unable to determine this, it should consult the user supplied property AutoGeneratedValue. If it is arNone .... wow... too complicated... we need some pseucode here

Code: Select all

IF FIELD VALUE IS NULL:

Can the DB driver figure out if server assigns default value ???

if YES then
   {
      if Server assigns some default value then
         DO NOT CARE about field being NULL
      else
         Raise exception
   }
else 
   {
      Check AutoGeneratedValue supplied by the user
         if arNone  (not defined)
            Raise Exception       
         else 
            Don't care
            // Assume USER knows what he is doing
   }        
         
 

Something like this :-)

Sorry for the long posting. If developers can point me where to look I'd be happy to try to implement this loginc.


Currently I set Required to false to resolve this situation.

Plamen

[mdaems]

I add this to bug report forum. We'll have to look if this still is an issue in the beta release.
A very small example would be nice. Does somebody have a D7 very small example app? (Table def, data and delphi files?) Please add to the bug report with this title.

Mark

[plamendp]

Just to note that Field.Required property should play its role here. I mean at some stage of the logic it is important if that property is True or False.

Honestly, this issue is kinda foggy and must be treated very carefully and must cover every single hypothesis (combination of server+driver+user).

Server: DOES or DOES NOT support DEFAULT values
AFAIK all server DOES

Driver: CAN or CAN NOT provide enough metadata
AFAIK not all Zeos drivers CAN

User: INVOLVED or NOT INVOLVED in the logic
Some users rely on driver (defaults) while others want to control the behaviour (Required and AutoGeneratedValue properties).


Plamen

[fduenas]

Just want to add my two cents for this topic:

Zeos will assign default values for fields only after sending the updates to the server (by calling 'Post' or 'ApplyUpdates') and if the field's value is null and if the 'opCalcDefaults' in TZquery.options is set to true. obviously it works with the table metadata obtained from the server. if the filed has a default value it will assign it, if not it will leave it as null. At least this is the normal behavior for mysql.

For fields that are part of the primary key index of the table, or fields configured as 'not nullable' at database side, when creating at design time the fields (automatically) those fields will have the 'required' property set to true, So if you're using a query with this kind of fields and TZQiery's property 'ChachedUpdates' set to true, then set to false the 'required' property for each field so it won't validate if the field is null when posting the record.

AutoGenerateValue field's property doesn't do anything using zeos.


Maybe a useful function to add should be something like Tzquery.AssignDefaultValues, that will get the default values for fields that have them. This can be called aster inserting a new record or at any time.