|Subject||Re: [IBO] INSERT ... RETURNING vs AutoCommit|
I don't think it's anything to do with Autocommit. A couple if things I see are ...
1. You create a Query component with a nil parent and never assign a session, database connection or Transaction to it. I would normally do something linke
Query := TIBOQuery.Create(IB_Connection.IB_Session);
Query.IB_Connection := IB_Connectionl
Query.IB_Session := IB_Connection.IB_Session;
//Assign a transaction if you are not using the default
2. You are inserting a record so you should not .Open the query, you should .ExecSQL. You can still get the returning value :)
Give that a go and see if it makes a difference :)
On 10/06/2016 11:02 AM, tvdien@... [IBObjects] wrote:
I have the following database structure:
CREATE GENERATOR S_FILES_ID;
CREATE TABLE FILES (
ID BIGINT NOT NULL,
CONTENT BLOB SUB_TYPE 0 NOT NULL
ALTER TABLE FILES ADD CONSTRAINT PK_FILES PRIMARY KEY (ID);
SET TERM ^ ;
CREATE OR ALTER TRIGGER BI_FILES FOR FILES
ACTIVE BEFORE INSERT POSITION 0
NEW.ID = NEXT VALUE FOR S_FILES_ID;
SET TERM ; ^
Now, when I insert into it with the following code:
function InsertFile: Int64;
LQuery := TIBOQuery.Create(nil);
LQuery.SQL.Text := 'INSERT INTO FILES (CONTENT) VALUES (:CONTENT) RETURNING ID';
Result := LQuery.FieldByName('ID').AsLargeIn t;
and a TIBODatabase elsewhere that has AutoCommit enabled, the insert does not seem to be automatically committed to the database. Only when I close the (demo) application, the inserted record shows up in another transaction with ReadCommitted isolation. When I remove the RETURNING clause, the commit is instant.
I've tested this with IBO 5.7.7 2340. I did not see any changes in the log that indicate anything related to this changed in the few versions since then. Am I doing something wrong, or is this a bug?
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2016.0.7639 / Virus Database: 4598/12391 - Release Date: 06/09/16