Recent

Author Topic: [solved] Firebird Insert Into (Executing SQL)  (Read 11031 times)

caused

  • New Member
  • *
  • Posts: 36
[solved] Firebird Insert Into (Executing SQL)
« on: November 19, 2010, 03:24:20 pm »
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
« Last Edit: November 21, 2010, 09:02:00 pm by caused »

caused

  • New Member
  • *
  • Posts: 36
Re: Firebird Insert Into (Executing SQL)
« Reply #1 on: November 20, 2010, 02:57:50 pm »
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

  • Sr. Member
  • ****
  • Posts: 264
Re: Firebird Insert Into (Executing SQL)
« Reply #2 on: November 20, 2010, 06:06:26 pm »
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

  • New Member
  • *
  • Posts: 36
Re: Firebird Insert Into (Executing SQL)
« Reply #3 on: November 21, 2010, 03:55:12 pm »
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

  • Hero Member
  • *****
  • Posts: 1611
    • http://wiki.lazarus.freepascal.org/User:Zoran
Re: Firebird Insert Into (Executing SQL)
« Reply #4 on: November 21, 2010, 07:03:42 pm »
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

  • New Member
  • *
  • Posts: 36
Re: Firebird Insert Into (Executing SQL)
« Reply #5 on: November 21, 2010, 09:01:47 pm »
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

  • Newbie
  • Posts: 1
Re: [solved] Firebird Insert Into (Executing SQL)
« Reply #6 on: January 18, 2011, 12:29:45 pm »
i use

Transaction.Commit;

after ExecSql. It works for me

 

TinyPortal © 2005-2018