Lazarus, can't get DB Update working. How can I preview whats being submitted?

Hi I have a form setup using TMySQL51Connection, TSQLTransaction & TSQLQuery modules, it retrieves info from the DB without issue but I'm having problems with updates.

The DB is pretty large so I'm starting with just the 1st couple of rows, once they're working I'll extend the query.

My TSQLQuery.UpdateSQL is as follows

UPDATE table SET
ContactFirst = :ContactFirst,
ContactSur = :ContactSur
WHERE AccountID = :AccountID

Then I have a button in my form, the onCLick event contains:

begin
    accSelect.Edit;
    accSelect.Post;
    accSelect.ApplyUpdates;
    dbTransaction.CommitRetaining;
    sqlbl1.Caption := accSelect.UpdateSQL;
end;

Clicking the buttons does precisely nothing with the DB, it generates no error messages, just updates the caption on sqlbl1. Is there anyway to preview what Lazarus is sending to the DB with values included so I can track down whats going wrong?

I tried adding a logging event to the SQLConnection but couldn't work out how to get it generating logs.

Solution

From my experience, the Edit, Post and ApplyUpdates methods are used in conjunction with a TClientDataSet. In that case you would simply assign new values to the fields in the CDS (after navigating to the record you want to Edit) then Post those changes (no need for an Update SQL statement). Something like this...

MyCDS.Edit;
MyCDS.FieldByName('ContactFirst').Value := sContactFirstName;
MyCDS.FieldByName('ContactSur').Value := sContactSurname;
MyCDS.Post;
MyCDS.ApplyUpdates;

Alternatively, you could use a Query component, write the UPDATE SQL into the SQL property, and use ExecSQL or whatever method has been implemented for that component (there are many database access components that each have their own idiosyncrasies)

Here's some code off the top of my head. Please excuse me but I'm not familiar with Lazarus specifics - it's basically Delphi code.

MySQLQuery.SQL.Clear;
MySQLQuery.SQL := 'UPDATE MyTable 
                   SET    ContactFirst = :ContactFirst,
                          ContactSur = :ContactSur
                   WHERE AccountID = :AccountID';
MySQLQuery.Params.ParamByName('ContactFirst').Value := sContactFirstName;
MySQLQuery.Params.ParamByName('ContactSur').Value := sContactSurname;
MySQLQuery.Params.ParamByName('AccountID').Value := iAccountID;
try
  MySQLQuery.ExecSQL;
  ShowMessage('Update succeeded');
except on e: Exception do
  ShowMessage(e.Message);
end;

So it might be that you've ended up using half of both methods and neither of them completely.