CODEWITHSUNDEEP
sqlinformix
hudi
hudi

Reputation: 16555

Select of last inserted id return always 0 in informix

I’ve followed this example to get the last id of an inserted record, problem is that my application sometimes returns 0. I've tried to test it using sql developer

INSERT INTO ...
execute function sysmaster:yieldn(1); // wait 1 second
select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1;

always returns 0. The 1 second waiting is just to simulate traffic in my application.

Can someone explain me what does dbinfo('sqlca.sqlerrd1') means.

PS:

I've tried the example from IBM site

When I execute:

insert into fst_tab VALUES (0,1);
insert into fst_tab VALUES (0,4);
insert into fst_tab VALUES (0,6);

execute function sysmaster:yieldn(1);
insert into sec_tab values (dbinfo('sqlca.sqlerrd1'));
select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1;

it returns the right id but when I comment the insert line:

insert into fst_tab VALUES (0,1);
insert into fst_tab VALUES (0,4);
insert into fst_tab VALUES (0,6);

execute function sysmaster:yieldn(1);
//insert into sec_tab values (dbinfo('sqlca.sqlerrd1'));
select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1;

then it returns 0.

PS2:

my db:

Upvotes: 1

Views: 1845

Answers (1)

mustaccio
mustaccio

Reputation: 19001

SQLCA (SQL Communication Area) contains information about the very latest executed SQL statement.

In this example: 

INSERT INTO ...
execute function sysmaster:yieldn(1); // wait 1 second
select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1;

SQLCA will contain results from execute function, not the INSERT. To obtain the latest inserted serial you must call dbinfo('sqlca.sqlerrd1') immediately after the INSERT.

Upvotes: 2

Related Questions