ZQuery and ZUpdateSQL blues!!!

[JD]

Hi there everyone,

I need a fresh pair of eyes for a problem I'm having. I'm trying to update remote tables using a ZQuery and a ZUpdateSQL component. I want to use one ZQuery and a ZUpdateSQL for each of the tables that I want to update. I have 8 tables to update.

The code snippet for updating one of the tables is shown below:

Code: Select all

with qryWrite do
    begin
        //
        SQL.Clear;
        try
           UpdateObject := updFoyerTable;
           if LFoyerRecord[0].ID = 0 then
              UpdateObject.SQL[ukInsert].Text := SQLInsertFoyerRecord
           else
              UpdateObject.SQL[ukModify].Text := SQLModifyFoyerRecord;
           ApplyUpdates;
           Connection.Commit;
           // set the operation results to 'success'
           strOperationFlag := strServerUpdateRecordSuccess;
        except
             // rollback the transaction because it failed!
            Connection.Rollback;
            // set the operation results to 'failure'
            strOperationFlag := strServerUpdateRecordFailure;
        end;
    // close the dataset
    Close;
end;

The insert and modify SQL statements are as shown below:

Code: Select all

  SQLInsertFoyerRecord =  'INSERT INTO Foyers' +
                          '(Nom, Adresse, VilleCodePostal, Telephone, Contact)' +
                          'VALUES (:Nom, :Adresse, :VilleCodePostal, :Telephone, :Contact)';
  SQLModifyFoyerRecord =  'UPDATE Foyers SET' +
                          'Nom = :Nom, Adresse = :Adresse, VilleCodePostal = :VilleCodePostal, Telephone = :Telephone, Contact = :Contact' +
                          'WHERE pk_id_foyer = :ID';

The code runs without problems and it tells me the update operations was a success BUT I never see the modifications when I open the table!

What am I doing wrong? Any insight will be highly appreciated.

JD

[EgonHugeist]

JD,

Seems like you're a Lazarus user and you decided to use Zeos now. Which transacton mode did you set and did you easiely tryed ZQuery.Post instead of ApplyUpdates?

Michael

[tkszeos]

Try to insert a Post before ApplyUpdates. This should be the correct sequence.

Nicola

[JD]

Hi there,

Inserting POST before APPLY UPDATES results in a EDatabase error: operation not allowed, dataset "qryWrite" is not in an edit or insert state.

This is understandable because I did not issue an OPEN command earlier. If I add OPEN, the error becomes "SQL query is empty". I actually thought that using pure SQL commands would free me from using commands like EDIT, OPEN, POST, DELETE etc. What do I do now?

@EgonHugiest
How did you know I'm a Lazarus user? In any case, I actually use Zeos on both Lazarus & Delphi (2006 & 2010). The TransactIsolationLevel for the connection is set to tiReadCommitted.

Thanks,

JD

[EgonHugeist]

JD,

How i know this? that's the typical Lazarus code you've poster here. The sequence you use Zeos is exactly the same ire the lcl db components are working.

Now to your issue. Did you assign your parameters? I mean from which origin do you assign the chnaged vaues? Are you sure you need that UpdateComponent in this case? What i understand is you want to have only one Update components but the sequence how that components is used is everywhere the same. You need an edit or insert on your ZQuery, assign the values and post them. I miss the part where the Query will be prepared for updates and where the values which you want to send to the server are comming from. ApplyUpdates is not needed for your transaktion mode.

I hope that helps you..

Michael

[JD]

@EgonHugeist

A) "Do I need that UpdateComponent"
I added the ZUpdateSQL component when I noticed that the ParamCount property of the ZQuery was giving faulty values. For example, I added 6 parameters to a ZQuery but ParamCount returned 0! ParamCount worked OK for ZUpdateSQL though. Is this a bug? I'm using the svn version of Zeos of June 1, 2012.

BTW, how does a ZQuery with an assigned ZUpdateSQL component know which SQL statement (Insert, Modify, Delete) to execute. I noticed that there is something called UpdateKind but thus far, I have had no success calling UpdateKind from code.

I've tried to do the following:

Code: Select all

  if LFoyerRecord[0].ID = 0 then
    UpdateObject.SQL[ukInsert].Text := SQLInsertFoyerRecord
  else
    UpdateObject.SQL[ukModify].Text := SQLModifyFoyerRecord;

B) I assigned the parameters as follows:

After retrieving the values from a stream sent by a client machine, I do

Code: Select all

  // set the parameter values
 for intCount := 0 to updFoyer.ParamCount - 1 do
    if intCount = 0 then
       updFoyer.Params[intCount].AsInteger := StrToInt(LFoyerRecord[0].PropertyString[intCount])
    else
       updFoyer.Params[intCount].AsString := LFoyerRecord[0].PropertyString[intCount];

And this code snippet works because I tested/verified the contents of the parameters.

Thanks,

JD

[EgonHugeist]

JD,

property of the ZQuery was giving faulty values. For example, I added 6 parameters to a ZQuery but ParamCount returned 0! ParamCount worked OK for ZUpdateSQL though. Is this a bug? I'm using the svn version of Zeos of June 1, 2012.

Hmm i've looked to our ZQuery and that component does not export the ParamCount property. Is it possible that you've tryed a code like this?:

with ZQuery do ... for i := 0 to ParamCount do begin ???? In that case your using the ParamCount of the System unit instead of !! ZQuery.Params.Count !!

BTW, how does a ZQuery with an assigned ZUpdateSQL component know which SQL statement (Insert, Modify, Delete) to execute. I noticed that there is something called UpdateKind but thus far, I have had no success calling UpdateKind from code.

Which SQL-statement the component is using will be decided from the UpdateKind. The UpdateKind is logical set from the kind of update you want to do now. Just execute either ZQuery.Edit(ukUpdate), ZQuery.Insert/Append(ukInsert) or ZQuery.delete(ukDelete). If you execute ZQuery.Post then the ZQuery choose the right SQL ,adds the Parameters of that statements and sends it to the server. So you can't change the UpdateKind by your selves manually like UpdateKind := ukInsert.. Use ZQuey1.Insert instead.

I hope again this will help you.

Michael