I'm using sequences and currval() to retrieve the last inserted row in a table.
If currval() is undefined, as it is when a connection is made, then I know no
rows were inserted in that table and can take a different action. This is
problematic when using a connection pooling library, as the value of currval()
for any given sequence could possibly be set from a previous "connection".
One (theoretical) workaround would be to issue some sort of command to the
back-end database to wipe all values of currval() when a "new" connection is
made. I've done some digging in the system tables and source code, and can't
find an obvious solution. Perhaps one you you gurus can suggest a SQL statement
to do such a thing.
Alternately, if there is a better way to retrieve the last inserted row for any
given table, I'd be very grateful for the tip. It would need to be independent
of the connection history, and undefined if there has not been a row inserted
to the table during a definable interval of time (drop anchor when the
"connection" begins, raise anchor when the "connection" ends), and be
independant of the other connections inserting rows to the same table.
Any idaeas?
CG
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com