[SOLVED] Updating table using UpdateSQL
lazarus / 16-Databases

----------------------------------------------------------------------
madref · 2015-06-16 12:22

I am trying to update a table with he following code:
Code: [Select]
    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' +
IntToStr(Volgorde_Vorige) + ' ' +
                                  'WHERE Divisie_ID = ' +
IntToStr(ID_Huidige) + ';';
    // Zet de juiste volgeorde bij het huidige ID
    TQ_Divisies.UpdateSQL.Clear;
    TQ_Divisies.UpdateSQL.Text := dgs;


    // Zet de juiste volgeorde bij het voorgaande ID
    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' +
IntToStr(Volgorde_Huidige) + ' ' +
                                  'WHERE Divisie_ID = ' +
IntToStr(ID_Vorige) + ';';
    TQ_Divisies.UpdateSQL.Add (dgs);
    TQ_Divisies.Post;
    TQ_Divisies.ApplyUpdates;


    if Form_Information.transRefereeDB.Active then
      Form_Information.transRefereeDB.CommitRetaining;


But i am getting an error: Raised Exception Class 'Unknown'


What is wrong with this code?

----------------------------------------------------------------------
rvk · 2015-06-16 12:51

You are constructing a SQL-update statement with direct values
(Volgorde_Vorige / ID_Huidige). In that case you shouldn't use the
UpdateSQL. The UpdateSQL is for records you retrieve and values you
change after that (with .Edit and .Post). So either your UpdateSQL
should have parameters (Like :volgorde_vorige and :OLD_Id) or you
should just execute the statement directly.

For direct execution:
Code: [Select]  // Zet de juiste volgeorde bij het voorgaande ID
  dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = ' +
IntToStr(Volgorde_Huidige) + ' ' +
                                'WHERE Divisie_ID = ' +
IntToStr(ID_Vorige) + ';';
  TQ_Divisies.SQL.Text := dgs;
  TQ_Divisies.ExecSQL;
  if Form_Information.transRefereeDB.Active then
    Form_Information.transRefereeDB.CommitRetaining;

This will only work if you don't need the TQ_Divisies afterwards
because the SQL.Text is changed here.

But why are you not using the default generated UpdateSQL and just do
this?
Code: [Select]TQ_Divisies.Edit;
TQ_Divisies.FieldByName('Divisie_Volgorde').asInteger :=
Volgorde_Vorige;
TQ_Divisies.Post;
if Form_Information.transRefereeDB.Active then
  Form_Information.transRefereeDB.CommitRetaining;

----------------------------------------------------------------------
madref · 2015-06-16 13:01

Beacuse i am trying to change 2 values at once.
The value that is being pointed to AND the previous one


and i am trying to do it with UPDATESQL instead of the SQL

----------------------------------------------------------------------
rvk · 2015-06-16 13:13

Ah, I now notice the Add in "UpdateSQL.Add (dgs);"

But that won't work. The UpdateSQL (and SQL.Text for ExecSQL) only
accepts one statement.

You can't change two records with one statement.

You can execute them after each other and if one fails you could do a
rollback.

I also wouldn't use the TQ_Divisies for this purpose but would use a
separate TSQLQuery. (You could also use TSQLScript but I think it's
overkill for this situation.)

Code: [Select]var
  dgs: string;
  TempSql: TSQLQuery;
begin
  TempSql := TSQLQuery.Create(nil);
  try
    TempSql.DataBase := TQ_Divisies.Database;
    TempSql.Transaction := TQ_Divisies.Transaction;
    // I take it Transaction of TempSql is the same as
Form_Information.transRefereeDB
    // otherwise you would need:
    // TempSql.Transaction := Form_Information.transRefereeDB;

    dgs := 'UPDATE tbl_Divisie SET Divisie_Volgorde = %d WHERE
Divisie_ID = %d;';
    // only one string because we're using format later on

    TempSql.SQL.Text := format(dgs, [Volgorde_Vorige, ID_Huidige]);
    TempSql.ExecSQL;

    TempSql.SQL.Text := format(dgs, [Volgorde_Huidige, ID_Vorige]);
    TempSql.ExecSQL;

    if TempSql.Transaction.Active then
      TSQLTransaction(TempSql.Transaction).CommitRetaining;

  finally
    TempSql.Free;
  end;

end;