ZUpdateObject example

[mtournay]

Hi

I try to use TZQuery and TZUpdateSQL with lazarus and it gives me headache.

the situation is : a datamodule with TZquery and TZUpdateSQL
TZQuery.UpdateObject is set to TZUpdateSQL component
TZQuery have a select query

Code: Select all

SELECT libelle, CodeCIP FROM t0

TZUpdateSQL have an insert query

Code: Select all

INSERT INTO t0 (article_id, Libelle, CodeCIP) VALUES (:article_id, :Libelle, :CodeCIP)

My code for insert is :

Code: Select all

query.Insert;
query.paramByName('article_id').asInteger := FarticleId;
query.paramByName('Libelle').asString := EditLibelle.Text;
query.paramByName('CodeCIP').asString := EditCodeCIP.Text;
query.post

I get an exception because 'article_id' is unknow

So I try another method

Code: Select all

query.Insert;
update.Params.paramByName('article_id').asInteger := FarticleId;
update.Params.paramByName('Libelle').asString := EditLibelle.Text;
update.Params.paramByName('CodeCIP').asString := EditCodeCIP.Text;
query.post

Insert fail because all parameters are NULL (I can see complete request in zmonitor file)

What am I missing !!! (i'm sure component is not buggy, just not documented !!)

regards

PS : article_id is not the primary key

[marsupilami]

Hello mtournay,

did you have a look at our Zeos Documentation Collection that can be found on our Sourceforge files section? I know that this manual is not that good but it is there.

To answer you question:
Paramaters in TZUpdateSQL are automatically filled in by the TZQuery object. current field values are inserted into parameters that have tha same values as the field currently has. Old values will be inserted into parameters that are prepended by old_.

So you should change your example. Add article_id to your query:

Code: Select all

SELECT article_id, libelle, CodeCIP FROM t0

Keep the InsertSQL property of the TZUpdateObject as ist is:

Code: Select all

INSERT INTO t0 (article_id, Libelle, CodeCIP) VALUES (:article_id, :Libelle, :CodeCIP)

Use your query as usual:

Code: Select all

query.Insert;
query.FieldByName('article_id').asInteger := FarticleId;
query.FieldByName('Libelle').asString := EditLibelle.Text;
query.FieldByName('CodeCIP').asString := EditCodeCIP.Text;
query.post

Usually you should be able to use TDBEdit and the like with this, so your code might not be needed.

Best regards,

Jan

[mtournay]

Hi Jan

Do I have to understand that parameters in update/insert have to be present in original select ?

I will update to 7.2.4 via OPM and make some test....

regards

[mtournay]

Hi

Sorry to bother you, but it's still not working.
I forget to mention that in the list query, there is a join

Code: Select all

SELECT T0.id,
  T0.article_id,
  T0.CodeCIP, 
  T0.Libelle, 
  T0.DateCreation,
  T0.BLM_ID,
  T0.DateRappel,
  T0.Commentaire,
  BLM.BLM_NOM 
FROM retraits AS T0
LEFT JOIN BLOCAGE_LOT_MOTIF BLM ON BLM.BLM_ID = T0.BLM_ID
ORDER BY T0.CodeCIP

Code: Select all

INSERT INTO retraits
  (article_id, CodeCIP, DateCreation, BLM_ID, Commentaire, DateRappel, Libelle)
VALUES
  (:article_id, :CodeCIP, :DateCreation, :BLM_ID, :Commentaire, :DateRappel, :Libelle)

Code: Select all

    if FarticleId = 0 then
      DM.ZRappelListe.paramByName('article_id').Value := Null
    else
      DM.ZRappelListe.paramByName('article_id').asInteger := FarticleId;
    DM.ZRappelListe.paramByName('Libelle').asString := EditLibelle.Text;
    DM.ZRappelListe.paramByName('CodeCIP').asString := EditCodeCIP.Text;
    DM.ZRappelListe.paramByName('BLM_ID').AsInteger := DBLookupComboBox1.ListSource.DataSet.FieldByName(DBLookupComboBox1.KeyField).AsInteger;
    DM.ZRappelListe.paramByName('DateRappel').AsDate := DTDateRappel.Date;
    DM.ZRappelListe.paramByName('Commentaire').asString := MemoCommentaire.Text;

Usually you should be able to use TDBEdit and the like with this, so your code might not be needed.

I know, but i'm not at the beginning of the project so I have to manage things like this....

did you have a look at our Zeos Documentation Collection that can be found on our Sourceforge files section?

I did before trying to use zupdate component I'll take a look again this afternoon

regards

[marsupilami]

Hello mtournay,

Do I have to understand that parameters in update/insert have to be present in original select ?

Erm - no. Your SQL statements in the TZUpdateSQL component need to use parameters with the names of the fields in your query. You did this correctly in your insert statement. Normally TZQuery and TZUpdateSQL will work together to update your database then. You don't need any more code for this to work. So I don't know what your second code listing does. It simply should not be necessary. TZQuery and TZUpdateSQL should do all the work for you. Maybe we need some kind of example program for this... Hmmm ....

Best regards,

Jan