zeos tzupdatesql

[3rdshiftcoder]

Hi-

I think there must be a better way to do this. I have a zquery, zconnection, grid, and tzupdate control.
It doesn't seem right that the zquery is readonly false. Is there a way to do this where the zquery is readonly - true?
I thought the whole idea behind tzupdate was that a readonly false wasn't allowed with a complicated query from more than one table.

Here is my code which works if it is a simple query and the zquery is readonly false.

Please check out the code and assist! Thanks for any help- jim

Code: Pascal  [Select][+][-]

1.  
2. procedure TForm1.Button2Click(Sender: TObject);
3. var
4.   Reply, BoxStyle: Integer;
5. begin
6.   ZConnection1.Connected:=true;
7.   ZQuery1.Close;
8.   ZQuery1.UpdateObject := ZUpdateSQL1;
9. ZUpdateSQL1.ModifySQL.text:='update employee set last_name = :ln, first_name =:fn where emp_no =:en';
10.  
11.  
12. ZQuery1.Open;
13. ZQuery1.Append;
14. //ZQuery1.FieldByName('last_name').Value := 'bar';
15. //ZQuery1.FieldByName('first_name').Value := '123';
16. ZUpdateSQL1.Params.ParamByName('fn').AsString:='mrfoo12';
17. ZUpdateSQL1.Params.ParamByName('ln').AsString:='mrsfoo';
18. ZUpdateSQL1.Params.ParambyName('en').AsInteger:=2;
19. ZQuery1.ApplyUpdates;
20. ZQuery1.CommitUpdates;
21. ZQuery1.Close;
22. ZConnection1.Connected:=false;
23.  
24.   end;
25.                              

[Zoran]

If ZQuery1 has UpdateObject set to ZUpdateSQL, then it means that ZQuery1 uses it instead of its default generated SQL commands.

That means:
when you use ZQuery1.Append or ZQuery1.Insert - then ZUpdateSQL.AppendSQL is called.
when you use ZQuery1.Edit - then ZUpdateSQL.ModifySQL is called.
when you use ZQuery1.Delete - then ZUpdateSQL.DeleteSQL is called.

Important: you are NOT expected to provide parameters to ZUpdateSQL, but the parameters should be named according to fields.

All this means, your code line where ZQuery1.ModfiySQL.Text is set does not make sense, but this does:

Code: [Select]

ZUpdateSQL1.ModifySQL.Text := 'update employee set last_name = :last_name, first_name =:first_name where emp_no =:emp_no';

Also, you should NOT use these:

Code: [Select]

ZUpdateSQL1.Params.ParamByName('fn').AsString:='mrfoo12';
ZUpdateSQL1.Params.ParamByName('ln').AsString:='mrsfoo';
ZUpdateSQL1.Params.ParambyName('en').AsInteger:=2;
These parameters are provided automatically, according to field names in ZQuery1.SQL, you should not provide them in code.

However, as you use ZQuery1.Append, not ZQuery1.Edit, this means ModifySQL is ignored, but InsertSQL is used, so you put something like this:

Code: [Select]

ZUpdateSQL1.InsertSQL := 'insert into employee (emp_no, last_name, first_name) (:emp_no, :last_name, :first_name);
Then you can call ZQuery1.Append;

Plus, you do not have to type these statements in your code, do this to build these InsertSQL, ModifySQL and DeleteSQL statements:
Right-click on the ZUpdateSql1 in form designer and from the drop-down menu chose "UpdateSQL Editor...". Play with it...

I hope, this was helpful...

[3rdshiftcoder]

Hi Zoran-

Thanks for the cool tips. I can't wait until 12 hours from now so I can try it out.

Have a cool week,
jim