On Mon, 8 Sep 2003, Chris Gamache wrote:
> 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?
Are you writing in java?
If yes, then implementing a wrapper around Connection would be a way.
>
> CG
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
==================================================================
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
Nikis 4, Glyfada
Athens 16610
Greece
tel: +30-210-8981112
fax: +30-210-8981877
email: achill at matrix dot gatewaynet dot com mantzios at softlab dot ece dot ntua dot gr