Zeoslib Portal

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

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

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.

You can always use the UpdateSQL component

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.

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

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.

technobot wrote: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

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

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?

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

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);';

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

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