tov’s Blog

Just took me 2 hours of Googling…

I found:
http://stackoverflow.com/questions/246983/informix-how-to-get-an-id-of-the-last-inserted-record

[…]The value of the last SERIAL insert is stored in the SQLCA record, as the second entry in the sqlerrd array. Brian’s answer is correct for ESQL/C, but you haven’t mentioned what language you’re using.
If you’re writing a stored procedure, the value can be found thus:
LET new_id = DBINFO(‚sqlca.sqlerrd1‘);
It can also be found in $sth->{ix_sqlerrd}[1] if using DBI
There are variants for other languages/interfaces, but I’m sure you’ll get the idea.
[…]

I got the idea and have created the following procedure (obsolete… Please see below)

CREATE PROCEDURE lastid ()
RETURNING integer;
DEFINE lastid integer;
LET lastid = DBINFO(‘sqlca.sqlerrd1’);
RETURN lastid;
END PROCEDURE

So to get the latest column you just use

select lastid() from table(set{1})

btw.: If you want to make use of this in Rails, I’d some success with:

class InformixConnect < ActiveRecord::Base
self.abstract_class = true
establish_connection :development_informix

def save
super
self.id = self.connection.select_value(“select lastid() from table(set{1})”)
end

end

so a model like

class Customer < InformixConnect

end

would be able to connect to Informix and to get correct values from the DB without the
usage of sequences!

ERROR
Unfortunately the stuff above (overwriting the save method) does not work for some reasons.

I have patched

class InformixAdapter < AbstractAdapter

def prefetch_primary_key?(table_name = nil)
false
end
….

def insert(sql, name= nil, pk= nil, id_value= nil, sequence_name = nil)
execute(sql)
select_value(“select lastid() from table(set{1})”)
end
…..

This works!

HEUREKA

Found another solution that works also with serial8 columns:
INFORMIX

CREATE PROCEDURE lastid_both ()
RETURNING integer;
DEFINE lastid_1 integer;
DEFINE lastid_8 integer;
LET lastid_1,lastid_8 = DBINFO('sqlca.sqlerrd1'),DBINFO('serial8');
IF lastid_1 == 0 THEN
return lastid_8;
END IF
RETURN lastid_1;
END PROCEDURE


Ruby/Rails


class InformixConnect < ActiveRecord::Base

else
ret = super
end
ret
end