[solved] Firebird Insert Into (Executing SQL)
lazarus / 16-Databases

----------------------------------------------------------------------
caused · 2010-11-19 14:24

Hi everyone =). I'm new to lazarus.

I connected a Firebird Database with the native components. It all
seems to work quite good, except, that I can only do Select Queries..
It's like its ignoring my Insert Into ie. Queries.

Here's the code I'm using to insert Data:
Code: [Select]procedure TForm1.Button4Click(Sender: TObject);
var
  mysqlquery : TSQLQuery;
  mytransaction : TSQLTransaction;
begin
  mysqlquery := TSQLQuery.Create(Form1);

  mytransaction := TSQLTRansaction.Create(Form1);
  mytransaction.DataBase := IBConnection1;
  mytransaction.Active := TRUE;

  mysqlquery.ReadOnly := FALSE;
  with mysqlquery do
    begin
      Database := IBConnection1;
      Transaction := mytransaction;
      SQL.Text:= 'INSERT INTO KUNDEN (KUNDENID, VORNAME, NACHNAME)
VALUES (1526,''VORNAME'',''NACHNAME'')';
      ExecSQL;
      Close;
    end;
  mysqlquery.free;
  mytransaction.Free;
end;            
Hope that you can help me.

caused

----------------------------------------------------------------------
caused · 2010-11-20 13:57

Anyone ?  ..

I'm using Firebird 2.5 btw.

I did also try ZEOSlib which DOES execute the sql... Though the
problem with ZEOS is, that it does not seem to notice external changes
made to the database...

----------------------------------------------------------------------
fredycc · 2010-11-20 17:06

Check what kind of Action is TSQLTransaction, is this case need to be
caCommit, by defaul is caNone; With Zeos you can set AutoCommit to
True, in some cases.


Regards.

----------------------------------------------------------------------
caused · 2010-11-21 14:55

hey,

i tried caCommit, and still no change  . Heres the code...

Code: [Select]procedure TForm1.Button4Click(Sender: TObject);
var
  mysqlquery : TSQLQuery;
  mytransaction : TSQLTransaction;
begin
  mysqlquery := TSQLQuery.Create(Form1);

  mytransaction := TSQLTRansaction.Create(Form1);
  mytransaction.DataBase := IBConnection1;
  mytransaction.Active := TRUE;

  mytransaction.Action := caCommit;

  mysqlquery.ReadOnly := FALSE;
  with mysqlquery do
    begin
      Database := IBConnection1;
      Transaction := mytransaction;
      SQL.Text:= 'INSERT INTO KUNDEN (VORNAME, NACHNAME) VALUES
(''VORNAME'',''NACHNAME'')';
      ExecSQL;
      Close;
    end;
  mysqlquery.free;
  mytransaction.Free;
end;   
With Zeos it works though, the only problem is that it does not seem
to notice external changes on the database until you restart the
programme (kinda weird)...

caused

----------------------------------------------------------------------
Zoran · 2010-11-21 18:03

Quote from: caused on November 21, 2010, 03:55:12 pmWith Zeos it works
though, the only problem is that it does not seem to notice external
changes on the database until you restart the programme (kinda
weird)...

Depending on what is set in your ZConnection's
TransactionIsolationLevel property, you cannot see the changes made by
other transaction if:
1. These external changes are not commited yet.
2. Your zeos connection started its own transaction before -- you are
reading from an still open transaction that had started BEFORE the
other transaction which made those external changes has finished.

And that is the most isolated scenario, it depends actually on
transaction isolation level you are using.

There is more about it in this tutorial from zeos about connecting
with Firebird. Generally, their recomendation is setting it to
tiRepeatableRead, but you might need diferent...

For better understanding of transaction isolation levels, read the
Wikipedia article about it.

----------------------------------------------------------------------
caused · 2010-11-21 20:01

Quote from: Zoran on November 21, 2010, 07:03:42 pmQuote from: caused
on November 21, 2010, 03:55:12 pmWith Zeos it works though, the only
problem is that it does not seem to notice external changes on the
database until you restart the programme (kinda weird)...

Depending on what is set in your ZConnection's
TransactionIsolationLevel property, you cannot see the changes made by
other transaction if:
1. These external changes are not commited yet.
2. Your zeos connection started its own transaction before -- you are
reading from an still open transaction that had started BEFORE the
other transaction which made those external changes has finished.

And that is the most isolated scenario, it depends actually on
transaction isolation level you are using.

There is more about it in this tutorial from zeos about connecting
with Firebird. Generally, their recomendation is setting it to
tiRepeatableRead, but you might need diferent...

For better understanding of transaction isolation levels, read the
Wikipedia article about it.

Changing that TransactionIsolationLevel property worked great =). ALso
thanks for the info.

I guess I'll just stick to ZEOSlib for now.

Thanks

----------------------------------------------------------------------
kostya · 2011-01-18 11:29

i use

Transaction.Commit;

after ExecSql. It works for me