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
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!
