[solved] Firebird Insert Into (Executing SQL)

[caused]

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]

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]

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]

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]

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)...

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]

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)...

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]

i use

Transaction.Commit;

after ExecSql. It works for me