Hi,
i want to write a stored procedure that inserts into a table
which has primary key of type SERIAL8, and then retrieves
this new key (how?) and returns it as returned value.
I cannot use SELECT...WHERE statement since some of the fields
are TEXT etc. Is it possible to get the newly inserted key from DBINFO?
Can I use ROWID perhaps to get primary key?

thnx,
bran

You have to use the dbinfo() function for
this.

create procedure p1() returning int;

define my_serial int;

insert into t_serial values(0);
let my_serial = dbinfo("sqlca.sqlerrd1");

return my_serial;

end procedure


HTH.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************

I tried this but unfortunatelly on my system (Informix DS 9.3)
DBINFO( "sqlca.sqlerrd1" ) returns 0 always, i.e. stored procedure
inserts correct SERIAL8 primary key but DBINFO always returns 0?!

I traced this with following statements to confirm:

SET DEBUG FILE TO '/tmp/trace';
TRACE OFF;
TRACE 'primary key = ' || DBINFO("sqlca.sqlerrd1");


I dont know what could be wrong??

thanks,,

Hi,

this was my fault.

You need to used dbinfo('serial8') instead of
dbinfo('sqlca.sqlerrd1').

I didn't recognize that it was a serial8, which has a larger
range of values than a normal serial.

Sorry.

Best regards

Eric
--
IT-Consulting Herber
WWW: http://www.herber-consulting.de
Email: eric@herber-consulting.de

***********************************************
Download the IFMX Database-Monitor for free at:
http://www.herber-consulting.de/BusyBee
***********************************************