Page 1 of 1
ZUpdateSQL issue (updatekind)
Posted: 16.06.2022, 11:00
by wsian
Hi,
Just started to switch over to Zeoslib. Everything works fine.
I have a question regarding ZUpdateSQL.
In BDE or (SQLdirect), I'm able to trigger the updatesql to do an insert using
(Query1.UpdateObject as TUpdateSQL).Apply(ukInsert);
example below:
Code: Select all
procedure TForm1.Query1AfterEdit(DataSet: TDataSet);
begin
if Query1.FieldByName('REASON').IsNull and
Query1.FieldByName('CAUSE').IsNull and
then
begin
(Query1.UpdateObject as TUpdateSQL).Apply(ukInsert);
end;
end;
Is there an equivalent method to do this with Zeoslib?
Re: ZUpdateSQL issue (updatekind)
Posted: 16.06.2022, 14:39
by marsupilami
Hello wsian,
I have to admit, that I don't understand the use case. Usually Zeos will call the relevant SQL from TZUpdateSQL object automatically?
What do you want to achieve?
Best regards,
Jan
Re: ZUpdateSQL issue (updatekind)
Posted: 16.06.2022, 16:20
by wsian
I have a joined query, table A left join table B display on single dbgrid.
table A data are fixed data and table B is blank initially and pending for user to update.
By using updatesql, I'm able to insert / modify table B with the statement in insertSQL and modifySQL.
Example with following code, after user edit data in dbgrid, it will be checked whether this is new data. This will trigger the Insert action with the updatesql object.
Code: Select all
procedure TForm1.Query1AfterEdit(DataSet: TDataSet);
begin
if Query1.FieldByName('REASON').IsNull and
Query1.FieldByName('CAUSE').IsNull and
then
begin
(Query1.UpdateObject as TUpdateSQL).Apply(ukInsert);
end;
end;
Re: ZUpdateSQL issue (updatekind)
Posted: 17.06.2022, 11:50
by marsupilami
Hello wsian,
Unfortunately TZUpdateSQL doesn't support that use case directly. It automatically chooses the SQL to run and executes it. Depending on your database you might want to have a look at using
upsert statements in the InsertSQL and ModifySQL of TZUpdateSQL. This way your database decides on what to do.
Unfortunately the exact statement to use depends on the database. In Firebird it is
UPDATE OR INSERT, PostgreSQL has insert ... on conflict, MSSQl seems to use MERGE, MySQL seems to have REPLACE INTO.
Best regards,
Jan
Re: ZUpdateSQL issue (updatekind)
Posted: 17.06.2022, 16:52
by wsian
Thanks for the hint. I'm converting from old sql statement and overlook the merge statement in oracle.
I shall look into this again.
Thank you once again.