Zeos components & TZUpdateSQL
lazarus / 16-Databases

----------------------------------------------------------------------
warcode · 2010-08-05 20:24

Hi all i have some trouble to make working the TZUpdateSQL, im usin
zeos version 7.0.0-dev and i'm using mssql for now ...

if i understand correcly TZUpdateSQL depend on a  TZQuery
you need to set UpdateObject propriety to your TZUpdateSQL

and this is my code ...

  tmp_sql := 'insert into tReceptions
(fkFournisseur,PO,DatePrevu,NbPalettePrevu,NbPaletteRecu,Note) values
( ' + QuotedStr(four) + ' , ' + QuotedStr(po) + ' , ' +
QuotedStr(start_date) + ' , ' + QuotedStr(pal_prevu) + ' , ' +
QuotedStr(pal_recu) + ' , ' + QuotedStr(note) + ' )';


ZQuery.UpdateObject := Zup;
ZUp.InsertSQL.Text := tmp_sql;

ZQuery.Open;
ZQuery.ExecSQL;

ZQuery.ApplyUpdates;
ZQuery.CommitUpdates;

ZQuery.Close;      


When i run this it crash with a error EdatabaseError : Operation
connot be performed on an incative dataset
BTW when i dont use ZUp (TZUpdateSQL) and pass my insert sql direcly
to ZQuery.SQL.text and run it its work (insert a new record ) but give
me a error : Con not open a Resultset.

Some one can help my with this
Thanks for any help
Christian F

----------------------------------------------------------------------
Marc · 2010-08-05 22:48

this is not the way you are supposed to use a TZUpdateSQL.

You only need to provide a TZUpdateSQL object to a TZQuery, when the
TZQuery itself is not updatable (like a select from a view or from
multiple tables).

When you want to execute a insterstatement directly, assign this
statement to the TZQuery.SQL and use TZQuery.ExecSQL.
IIRC you can in this case also use TZConnection.ExecuteDirect('your
statmement')

A usage of TZUpdateSQL is more like:Code: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  ZQuery.SQL
:= 'select fieldname1, fieldname2, ... from foo';ZQuery.UpdateObject
:= Zup;ZUp.InsertSQL.Text := 'insert into foo (fieldname1, fieldname2,
...) values (:fieldname1, :fieldname2, ...)'; ZQuery.Open;ZQuery.Appen
d;ZQuery.FieldByName('fieldname1').Value :=
'bar';ZQuery.FieldByName('fieldname2').Value :=
'123';....ZQuery.Post;ZQuery.Close;     
BTW, In this case using a TZQuery on a form/da]"]>Blockeddule and
using persistent fields is a more usefull approach.

----------------------------------------------------------------------
warcode · 2010-08-06 12:48

Hi Marc thanks for the help, i dont understand the difference in this
ZUp.InsertSQL.Text := 'insert into foo (fieldname1, fieldname2, ...)
values (:fieldname1, :fieldname2, ...)'; 

and

# ZQuery.FieldByName('fieldname1').Value := 'bar'; 
# ZQuery.FieldByName('fieldname2').Value := '123';

anyway i try it ... it giving me no error but dont add the new record
so i use the plan b with

TZConnection.ExecuteDirect('my insert sql')

and its work perfecly .. any disadvantage of using TZConnection
direcly ?

Christian F

----------------------------------------------------------------------
marcov · 2010-08-06 13:15

When I was playing with Zeos, I thought it was really useful to have a
sql monitor somewhere to see what is actually sent to the DB.

For me tzupdatesqls worked fine (designtime even), except that you
then need to set some event to copy values from the query (selected
row) to the params of the tzupdatesql component. (typically pk's and
fk's)

----------------------------------------------------------------------
Marc · 2010-08-08 12:03

Quote from: warcode on August 06, 2010, 02:48:01 pm
anyway i try it ... it giving me no error but dont add the new record
so i use the plan b with

TZConnection.ExecuteDirect('my insert sql')

and its work perfecly .. any disadvantage of using TZConnection
direcly ?

there is no disadvantage since IIRC, internally, after a TZquey is
parsed etc, zeas uses the same