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

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!