ZQuery and ZUpdateSQL blues!!!
lazarus / 16-Databases

----------------------------------------------------------------------
JD · 2012-05-30 13:13

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.

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

Code: [Select]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]  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

----------------------------------------------------------------------
goodname · 2012-05-30 16:32

Use SQLdb but think it will be the same fix.

First do a Post. This makes changes to the local record set.

Next do ApplyUpdates. This sends local changes to the database.

Then do commit.

Usually put the applyUpdates and commit in the afterPost event.