Update database (firebird)
lazarus / 16-Databases

----------------------------------------------------------------------
wdn · 2014-08-30 12:04

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 · 2014-08-30 15:59

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 · 2014-08-30 16:04

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

----------------------------------------------------------------------
wdn · 2014-09-01 17:54

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 · 2014-09-01 18:40

Quote from: wdn on September 01, 2014, 07:54:34 pm- 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.
QuoteIn 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 · 2014-09-02 06:53

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 · 2014-09-02 10:30

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

Thanks

Wim