Re: SQL-question: returning the id of an insert querry

Scott Chapman wrote:

>On Monday 10 November 2003 08:23, David Green wrote:
>
>
>>Are X & Y two different connections?
>>If you execute 2 statements on the same connection and then get
>>currval() it will give the last generated id.
>>
>>Ex.
>>On 1 connection:
>>INSERT INTO A (fld) VALUES (val); -- id generated = 1
>>INSERT INTO A (fld) VALUES (val2); -- id generated = 2
>>SELECT currval('SA');
>>2
>>
>>
>
>Thanks for the clarification. With web applications and connection
>pooling, it would appear that it's quite easy to get incorrect values
>back. This is what I thought.
>
Huh? My web application has connection pooling and it goes like this:

<receive a request to do something>
Get a handle from the pool.
Do your insert.
Do your currval select.
Do whatever else you need to do...return data to user maybe.
Put the handle back in pool.
<wait for more requests to do something>

Nobody can grab my database handle til I am done with it. I can use it
as much as I like before I put it back. It is put back by default at
the end of the function if not explicitly put back.

You will never get "incorrect values" if you call currval immediately
after an insert while using the same handle.

I would not use a web application that got a new handle for every sql
statement executed.