Обсуждение: getting primary key values for inserted records?

Hallo,

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?

Guido

On Tue, 27 Jan 2004, Guido Fiala wrote:

> Hallo,
>
> 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?
>

Other than reissuing the query, the only real way around this is to
separately issue a nextval call on the sequence and use that value in
rs.updateInt.  There is no way for the JDBC driver to know what the
primary key turned out to be otherwise.

Kris Jurka

> 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

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@wellsgaming.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
>
>
>

Am Dienstag, 27. Januar 2004 22:26 schrieben Sie:
> > 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, ...)";
> >

Yup! That is just fine, many thanks!

On Tue, 27 Jan 2004, Jeremy Buchmann wrote:

> 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

You could always program a before trigger that would not let you directly
insert or update that column, and would only use the sequence.