Re: getting primary key values for inserted records?

On Jan 27, 2004, at 11:35 AM, Andrew Nelson wrote:

> I've always wondered if something like this is totally safe. What if
> someone somewhere else outside of my code ran a insert or update that
> didn't use the sequence at all but changed the id. And that id turned
> out to be the same as the id collect from the select
> nextval('sequence');
>
> So I guess you would need to add a lock of some sorts.
> Just wondering because this is something I have fought with for a long
> time.

Well, it's only safe if you use the sequence. If you don't, you're on
your own.

Cheers,
Jeremy

>
>>>> Jeremy Buchmann <jeremy(at)wellsgaming(dot)com> 01/27/04 10:16AM >>>
>> I'am currently at the point, that after i insert a new Record in a
>> ResultSet
>> using insertRow() and using a serial for the primary key of the target
>> table.
>>
>> Of course i do not call rs.updateInt(primaryKey), as the sequence does
>> get me
>> the new value automatically.
>>
>> However - even using rs.refreshRow() doesn't get me the values, just a
>> requery
>> of the ResultSet.
>>
>> What is the way to go here?
>
> If you don't mind running two statements, you can select the next value
> from the sequence and then put it in your insert statement. Like this:
>
> $pkey = "SELECT nextval('sequence_name')";
> "INSERT INTO table (id, ...) VALUES ($pkey, ...)";
>
> It's psuedo-code, but you get the idea. This ensures that you know the
> primary key of the row you inserted, and the number came from the
> sequence so it's safe.
>
> Hope that helps,
> Jeremy
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>