Handling NULL's in Fields having default values set in DB

[Wild_Pointer]

I'm using zeos 6.6.5-stable with delphi 2006 to connect to postgresql (8.3.3). Theres a problem I'd like to discuss - how to insert a record with a null value if corresponding database column has default value? Thats wat I have:
1. In DB
CREATE TABLE test
(
id serial NOT NULL,
"value" integer DEFAULT 0,
CONSTRAINT test_pkey PRIMARY KEY (id)
)

2. In program:
...
ZQuery1.SQL.Text := 'SELECT id, value FROM test';
ZQuery1.Open;
ZQuery1.Insert;
ZQuery1.FieldByName('value').AsVariant := NULL;
ZQuery1.Post;
..

I get record with new id and 0 in "value". It's not correct I think - because if I wright INSERT INTO test("value") VALUES(NULL); in SQL editor - I get record with new id and NULL in "value".

Is there a way to tell the component not to use database default value on inserts? (Or any other work around)

[mdaems]

Did you try using an TZUpdateSql component?
Did you also have a look at the output from a TZSQLMonitor, so you could check what's actually sent to the server? Is the field actuallyposted with the default value in the sql statement, or is the field just skipped because it contained a null value?

Mark

[Wild_Pointer]

Hello,

yes, I have used TZUpdateSQL component in the past, and I'm sure if I use it in my current application it will solve my problem (inserting NULL instead of default). I haven't looked at the output from a TZSQLMonitor, but I traced down to the place the SQL statement is formed in Zeos code - it is with the default value, not the NULL.
I don't know if thats a feature or a bug, but I certainly would like to know if that behavior is permanent or it is going to change in future versions of Zeos

[Wild_Pointer]

Hello,

so how about the status of this behavior. How would you address this issue - bug or a feature? If it's considered to be a bug, then what should be the way to fix it. I guess some flag "changed" should be set on field when somebody assigns value to it. Not assigned values should not be passed I guess.. This is not trivial fix. The question - is the current behavior should be fixed or left as is?...

[mdaems]

I've been thinking about WHY this behaviour exists... The answers is this, I believe : zeoslib can't know what you mean by not filling the field. Do you really want a null, or are you effectively ignoring the field so the null value will be posted.?

What do you propose?

[Wild_Pointer]

Hello, mdaems.

From my point of view the bahavior should be similar to SQL's. That is if the field doesn't get the value of some sort it should by treated as unassigned. Lets say I have table foo(id integer, value varchar(20) default 'hello'. If I add some value to field "value" then on post it should generate SQL with that field (insert into foo(id, value) values (1, 'some_value')), but if I don't - It should generate SQL without mentioning that fieldname (insert into foo(id) values(1)).
Thats the - idea. I don't know what to do for backwards compatibility - maybe an option should be introduced on connection component to change the SQL formation rules. Anyway its wrong to insert default value when the programer orders NULL to be inserted into the field.

[mdaems]

So, you would like to make a difference between the field left blank in a component and the field set explicitly to null by the programmer? Not sure if this is possible with standard TField an TDataset structures (as these are used by zeoslib).

Seems like we're close to another discussion we had on this forum.
Have a look here. What do you think?

Mark

[Wild_Pointer]

Hello, Mark

Would you be more specific what discussion you have in mind? I tried searching, but with no results.

[mdaems]

Sorry, I forgot to paste the link

I was talking about this thread. http://zeos.firmos.at/viewtopic.php?t=2227
(last 2 posts in the thread are not relevant)
It's not exactly the same case anyway.

Mark

[Wild_Pointer]

Ok - I read it. Generaly fixing one would fix the other. I'll try to investigate the possibility of setting "changed" or "assigned" flag on field. Even if its possible it should be intrusive. I'd have to change data assignment to field process, SQL generation (using just the right fields) and filling it with the right field values.
Anyway - if you think that would be beneficial for ZeosLib - I'm willing to give it a try.

[mdaems]

I hope you investigation has a positive result.

Mark

[EmheMah]

I got the same problem here with Access database. Using a TZTable, insert, primary key is autoinc and I don't assign any value.

On theTable.Post : "You tried to assign the Null value to a variable that is not a Variant data type"

I tried the modify the procedure TZGenericCachedResolver.DefineInsertColumns as suggested in http://zeos.firmos.at/viewtopic.php?t=2227 without success.

I'll use an easy workaround for now (manually generate an insert) but I would be glad to help fix this bug.

[Wild_Pointer]

Hello,

I've investigated TField class and found no method to distinguish field with no value from one with value of NULL in it... Do you know if dataset gets noticed when the value is assigned to the field? If so - maybe theres a way to keep list of changed fields on dataset ?...

[mdaems]

No idea. You'll have to check dat n the Delphi/Fpc code. That would involve keeping an extra array of changed/unchanged switches. Quite heavy, isn't it?

Mark

[Wild_Pointer]

Yes, heavy. But if the only possible way to do it is this, then so be it. Its always better to do something than not to do
Right now I'm busy at my day work, but in free moment i'll give one more look at the code. You mention FPC - its free pascal I guess. Zeos supports both?