ZUpdateSQL issue (updatekind)

[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?

[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

[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;

[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

[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.