Subject Re: [IBO] INSERT ... RETURNING vs AutoCommit
Author Robert Martin
Post date 2016-06-19T21:18:28Z
Hi

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 :)
Cheers
Rob

On 10/06/2016 11:02 AM, tvdien@... [IBObjects] wrote:

Hello,


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
AS   
BEGIN
  NEW.ID = NEXT VALUE FOR S_FILES_ID;
END^

SET TERM ; ^


Now, when I insert into it with the following code:


function InsertFile: Int64;
var
  LQuery: TIBOQuery;
begin
  LQuery := TIBOQuery.Create(nil);
  try
    LQuery.SQL.Text := 'INSERT INTO FILES (CONTENT) VALUES (:CONTENT) RETURNING ID';
    LQuery.ParamByName('CONTENT').LoadFromFile('C:\...', ftBlob);
    LQuery.Open;
    Result := LQuery.FieldByName('ID').AsLargeIn t;
    LQuery.Close;
  finally
    LQuery.Free;
  end;
end;


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?


Thanks!

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