Computed Column problem

[kerino]

I'm rather new to Zeos & Firebird.
I create a new database and a table as follows:-
ZQuery1.SQL.Text := 'CREATE TABLE Table1 (';
ZQuery1.SQL.Add(db_Index+' INTEGER PRIMARY KEY,');
ZQuery1.SQL.Add(db_Name+' VARCHAR(10),');
ZQuery1.SQL.Add(db_Num+' DECIMAL (10,2),');
ZQuery1.SQL.Add(format('%s FLOAT COMPUTED BY (%s / 3)',[db_Comp1,db_Num]));
ZQuery1.SQL.Add(')');
ZQuery1.ExecSQL;

ZTable1.Active := true;

I then try to insert a new record as follows:-
ZTable1.Edit;
ZTable1.Append;
ZTable1[db_Index] := 1;
ZTable1[db_Name] := 'Name1';
ZTable1[db_Num] := 123.456;
ZTable1.CommitUpdates;

On the commitupdates, I get an exception saying "attempted read of read only column. Error code -151. This column cannot be updated because it is derived from an SQL function or expression. The SQL: INSET INTO TABLE1 (DINDEX,DNAME,DNUMBER,COMP1) VALUES(?,?,?,?);"
It looks like Zeos is trying to put a value into the COMP1 column, when it knows it's a computed column.

I thought I might be able to fix it by setting the FieldKind to fkCalculated, but when I try
ZTable1.FieldByName(db_Comp1).FieldKind := fkCalculated;
it says it cannot perform this on an open dataset, and if I set the table to inactive, it says it cannot find the field (catch 22)

Can anyone tell me what I should do to make this work please?
Many thanks

[mdaems]

Did you try using a ZQuery component instead of the rather simple ZTable component? ZTables are only advised for simple/small tables.

There's also an option UpdateMode. Make sure it's set to umUpdateChanged.

Mark

[kerino]

Hi Mark,
Thanks for replying.
I just tried:-
ZQuery1.SQL.Text := 'SELECT * FROM Table1';
ZQuery1.Open;

ZQuery1.Edit;
ZQuery1.Append;
ZQuery1[db_Index] := 1;
ZQuery1[db_Name] := 'Name1';
ZQuery1[db_Num] := 123.456;
ZQuery1.CommitUpdates;
and made sure the UpdateMode was set to umUpdateChanged, and it made no difference. I still get the same error.

[gto]

You can always use the UpdateSQL component

[technobot]

Looks to me like a bug in the calculated fields handling of TZTable. But in the meantime, kerino, you can use the a Query and UpdateSQL components instead of the table, as gto suggested.

[kerino]

Thanks for the advice.
I have never used ZUpdateSQL before, and after spending a pleasant couple of hours with Google, I've been unable to unearth information on how to use this component.
I guess I do something like:-
ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1';
ZUpdateSQL1.InsertSQL.Add(format('%s,%s)',[db_Name,db_Num]));
ZUpdateSQL1.InsertSQL.Add(format('VALUE (%s,%f)',['Name1',123.456]));
but I don't know what the next line should be.
Can anyone help me out on how to use this component please?
Many thanks - Kerin

[technobot]

I don't remember 100%, but iirc it goes smt like this:

You need to associate the UpdateSQL with the Query (there's a property in Query for that). Then you set up the UpdateSQL sql properties like so (for example):

ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1 (DINDEX,DNAME,DNUMBER) VALUES(:DINDEX,:DNAME,:DNUMBER);';

where :DINDEX,:DNAME,:DNUMBER are the column names as the Query knows them (the ":" means "take current value from column ...").

Then you just use the Query as a regular dataset.

[gto]

I don't remember 100%, but iirc it goes smt like this:

You need to associate the UpdateSQL with the Query (there's a property in Query for that). Then you set up the UpdateSQL sql properties like so (for example):

ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1 (DINDEX,DNAME,DNUMBER) VALUES(:DINDEX,:DNAME,:DNUMBER);';

where :DINDEX,:DNAME,:DNUMBER are the column names as the Query knows them (the ":" means "take current value from column ...").

Then you just use the Query as a regular dataset.

exaclty

The UpdateSQL component is specially deisgned for queries that cannot be updated, like selecting from two tables or doing joins

[kerino]

Thanks for helping, but I'm still not there yet.
I added a ZUpdateSQL component, and set the ZQuery1.UpdateObject := ZUpdateSQL1 in the properties list.
Then I do
ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1 (DINDEX,DNAME,DNUMBER) VALUES(:DINDEX,:DNAME,:DNUMBER);';

and then

ZQuery1.SQL.Text := 'SELECT * FROM Table1';
ZQuery1.Open;

ZQuery1.Edit;
ZQuery1.Append;
ZQuery1[db_Index] := 1;
ZQuery1[db_Name] := 'Name1';
ZQuery1[db_Num] := 123.456;
ZQuery1.CommitUpdates;

on the commitupdates I get a 'Token unknown' error on column 43 VALUE.
The SQL: INSERT INTO Table1 (dIndex,dName,dNumber) VALUE (?,?,?);

Is there something else I should be doing?
Many thanks - Kerin

[technobot]

Possibly the column names don't match. If the update query says ":xxxx", then xxxx should be the same as what you get by reading Query.Fields[index].FieldName. I believe in your case, these are the values of the db_Index etc constants?

[kerino]

Well, I used db_Index etc. constants to avoid the possibility of getting the names wrong, so I don't think thats the problem.
The error message is saying it cant recognise the token VALUE.

I've now greatly simplified the program, and here it is:-

// First create the table
ZQuery1.SQL.Text := 'CREATE TABLE Table1 ';
ZQuery1.SQL.Add('(INDX INTEGER PRIMARY KEY, COMP1 FLOAT COMPUTED BY (INDX + 2))');
ZQuery1.ExecSQL;

// Set up the UpdateSQL
ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1 (INDX) VALUE (:INDX);';

// Insert the record
ZQuery1.SQL.Text := 'SELECT * FROM Table1';
ZQuery1.Open;

ZQuery1.Edit;
ZQuery1.Append;
ZQuery1['INDX'] := 1;
ZQuery1.CommitUpdates;

and is still goes wrong at the same place i.e. cannot recognise VALUE token.
Obviously something is still wrong, but I can't see it.
Can any of you eagle eyed experts see what's wrong?
Many thanks

[technobot]

oh, right - it should be "VALUES" (mind the S)

strange though - didn't you write earlier that you did (quote):

ZUpdateSQL1.InsertSQL.Text := 'INSERT INTO Table1 (DINDEX,DNAME,DNUMBER) VALUES(:DINDEX,:DNAME,:DNUMBER);';

[kerino]

Well spotted - I've been looking for ages, but sometimes you just can't see it!

Now everything has sprung into life, so I would like to thank you all for being such a help.
It's the first time I've used this forum, but I'm sure it won't be the last.

Thanks again - Kerin

[lumoraes]

REQUEST DATABASE DRIVE WAS NOT FOUND

Update Zeos Version 6.5.1 to 6.6.2
The connection (ZConnection using driver PostGres8)
But Internal ZEOS Set driver PostGres74