Update database (firebird)

[wdn]

 This is my code:

{insert into table driver}

   SQLCommand := 'insert into driver (lastname,firstname) values (_LN, _FN )';
     //showmessage(sqlcommand);
  try
   SQLQuery1.Close;
   SQLQuery1.SQL.Text:= SQLCommand;
   SQLQuery1.params.ParamByName('P_LN').AsString := LN;
   SQLQuery1.Params.ParamByName('P_FN').AsString:= FN;
   DBConnection.Connected := true;
   SQLTransaction1.Active:= true;
   SQLQuery1.ExecSQL;
   SQLTransaction1.Commit;
   except
   on e: edatabaseerror do
   b

No data transferred to database (isql & other tools)
Strange:
- the BI trigger, used to "auto increment" the PK, seems to be fired: value generator is incremented

[exdatis]

SQLquery.ApplyUpdates
http://wiki.freepascal.org/SQLdb_Tutorial1
http://wiki.freepascal.org/SQLdb_Tutorial2
http://wiki.freepascal.org/SQLdb_Tutorial3
http://www.freepascal.org/docs-html/fcl/sqldb/tsqlquery.html

[exdatis]

You can use xxConnection to execute sql directly(non selectable sql)
http://wiki.freepascal.org/SqlDBHowto

[wdn]

After a lot of experimenting, I have the following result:
- data must be somewhere in a firebird buffer
- only after an explicit commit in firebird (isql) they show up in the table(s) 
and now .......
-the same pascal procedure against MySQL/MariaDB RDBMS : NO PROBLEM AT ALL! 
?????????

[rvk]

- only after an explicit commit in firebird (isql) they show up in the table(s) 

Did you start isql before or after the commit of the transaction in your application?

If you started isql before commiting the transaction in your app it could be that isql doesn't see the commited updated until you start a new transaction in isql.

Edit:
According to this when you start isql it is started with a transaction in SNAPSHOT mode.

In isql, a transaction is begun as soon as the utility is started. The transaction is begun in SNAPSHOT isolation, with a lock resolution set to WAIT.

So if you started isql before you committed in your app everything you committed is not yet available in isql (until committing and starting a new transaction).

You can also read more about this here and see how to set a transaction with a different isolation level.

[mangakissa]

This example I use for inserting a record into a (embedded) firebird database. For update is really the same.

Code: [Select]

procedure TDMcontacten.DBSaveBufToTable;
var MyQuery     : TSQLQuery;
    SQLTransAction : TSQLTransaction;
begin
  MyQuery     := TSQLQuery.create(nil);
  SQLTransAction := TSQLTransaction.create(nil);
  try
    try
      SQLTransAction.DataBase := DBConnection;
      MyQuery.DataBase     := DBConnection;
      MyQuery.Transaction  := SQLTransaction;
      MyQuery.SQL.Add('INSERT INTO kaartverkoop');
      MyQuery.SQL.Add('(soortverkoop, datumverkoop, lidnummer, aantal,');
      MyQuery.SQL.Add(' bedrag, usernr_create, usernr_update, date_create,');
      MyQuery.SQL.Add(' date_update) values');
      MyQuery.SQL.Add('(:soortverkoop, :datumverkoop, :lidnummer, :aantal,');
      MyQuery.SQL.Add(' :bedrag, :usernr_create, :usernr_update, :date_create,');
      MyQuery.SQL.Add(' :date_update)');
      MyQuery.Params[0].AsInteger  := BufKaartverkoop.FieldByName('soortverkoop').AsInteger;
      MyQuery.Params[1].AsDateTime := BufKaartverkoop.FieldByName('datumverkoop').AsDateTime;
      MyQuery.Params[2].AsString   := BufKaartverkoop.FieldByName('lidnummer').AsString;
      MyQuery.Params[3].AsInteger  := BufKaartverkoop.FieldByName('aantal').AsInteger;
      MyQuery.Params[4].AsFloat    := BufKaartverkoop.FieldByName('bedrag').AsFloat;
      MyQuery.Params[5].AsInteger  := uGebruiker.ID;
      MyQuery.Params[6].AsInteger  := uGebruiker.ID;
      MyQuery.Params[7].AsDateTime := now;
      MyQuery.Params[8].AsDateTime := now;
      MyQuery.ExecSQL;
      SQLTransAction.Commit;
    except
      SQLTransAction.Rollback;
    end;
  finally
    MyQuery.free;
    SQLTransAction.free;
  end;
end;                           


[wdn]

The isql trick did the job 
I spent hours and hours trying to isolate the problem in the Lazarus application. 

Thanks

Wim