Обсуждение: Serial key
Hi, I have a table in which the primary key is of type SERIAL. I insert a record and then I want the generated id because of this, so that I can use the id to insert records in child table. I know about a solution in which generate a id before hand and then use the id to insert in table and also in child tables. The other stuff which I read in JDK 1.4 is of getGeneratedKeys(). The problem is I have to use JDK 1.3.1. Is there any solution for this problem? Thanks Bhushan ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
Bhushan Bhangale wrote:
> I have a table in which the primary key is of type SERIAL. I insert a
> record and then I want the generated id because of this, so that I
> can use the id to insert records in child table.
>
> I know about a solution in which generate a id before hand and then
> use the id to insert in table and also in child tables.
IMO, that's definitely the best way to go.
> The other stuff which I read in JDK 1.4 is of getGeneratedKeys().
>
> The problem is I have to use JDK 1.3.1.
>
> Is there any solution for this problem?
Incidentally, using 1.4 wouldn't help you because the PostgreSQL JDBC driver
doesn't implement getGeneratedKeys anyway. The only other approach is to use
some unique set of columns in the table other than your numeric key, and
immediately follow your insert statement with a select. This only works if
your records are unique without regard to the generated key. As an example,
you'd do:
insert into mytable (name, department, position, hiredate)
values('jdoe', 'eng', 'manager', DATE '10-12-1994');
select empid from mytable where
name = 'jdoe' AND
department = 'eng' AND
position = 'manager' AND
hiredate = DATE '10-12-1994';
--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
Thanks Chris!
Just wanted to confirm what all I can do.
I will ofcourse go with the best way of generating id beforehand.
-----Original Message-----
From: Chris Smith [mailto:cdsmith@twu.net]
Sent: 13 February 2004 17:46
To: Bhushan Bhangale; pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Serial key
Bhushan Bhangale wrote:
> I have a table in which the primary key is of type SERIAL. I insert a
> record and then I want the generated id because of this, so that I
> can use the id to insert records in child table.
>
> I know about a solution in which generate a id before hand and then
> use the id to insert in table and also in child tables.
IMO, that's definitely the best way to go.
> The other stuff which I read in JDK 1.4 is of getGeneratedKeys().
>
> The problem is I have to use JDK 1.3.1.
>
> Is there any solution for this problem?
Incidentally, using 1.4 wouldn't help you because the PostgreSQL JDBC driver
doesn't implement getGeneratedKeys anyway. The only other approach is to
use
some unique set of columns in the table other than your numeric key, and
immediately follow your insert statement with a select. This only works if
your records are unique without regard to the generated key. As an example,
you'd do:
insert into mytable (name, department, position, hiredate)
values('jdoe', 'eng', 'manager', DATE '10-12-1994');
select empid from mytable where
name = 'jdoe' AND
department = 'eng' AND
position = 'manager' AND
hiredate = DATE '10-12-1994';
--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.
Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Chris Smith said:
The only other approach is to
> use
> some unique set of columns in the table other than your numeric key, and
> immediately follow your insert statement with a select. This only works
> if
> your records are unique without regard to the generated key. As an
> example,
> you'd do:
>
> insert into mytable (name, department, position, hiredate)
> values('jdoe', 'eng', 'manager', DATE '10-12-1994');
>
> select empid from mytable where
> name = 'jdoe' AND
> department = 'eng' AND
> position = 'manager' AND
> hiredate = DATE '10-12-1994';
>
Alternatively, if you could use a sequence object for the primary key
value (instead of a serial column), and you switched autocommit off, you
should be able to determine the last allocated PK id AFTER creating the
record by querying the currval on the sequence.
John Sidney-Woollett
On Fri, 13 Feb 2004, John Sidney-Woollett wrote: > Alternatively, if you could use a sequence object for the primary key > value (instead of a serial column), and you switched autocommit off, you > should be able to determine the last allocated PK id AFTER creating the > record by querying the currval on the sequence. > autocommit has nothing to do with this, sequence state via currval is session specific, not transaction specific. Kris Jurka
Yes,
the easiest and best way to do this is to append select
curval('sequencename') to your inserts
ie insert into foo .....; select curval('sequence');
and then use the execute command
Dave
On Fri, 2004-02-13 at 12:23, Bhushan Bhangale wrote:
> Hi,
>
> I have a table in which the primary key is of type SERIAL. I insert a record
> and then I want the generated id because of this, so that I can use the id
> to insert records in child table.
>
> I know about a solution in which generate a id before hand and then use the
> id to insert in table and also in child tables.
>
> The other stuff which I read in JDK 1.4 is of getGeneratedKeys().
>
> The problem is I have to use JDK 1.3.1.
>
> Is there any solution for this problem?
>
> Thanks
> Bhushan
>
> ________________________________________________________________________
> This e-mail has been scanned for all viruses by Star Internet. The
> service is powered by MessageLabs. For more information on a proactive
> anti-virus service working around the clock, around the globe, visit:
> http://www.star.net.uk
> ________________________________________________________________________
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
--
Dave Cramer
519 939 0336
ICQ # 14675561
Kris Jurka said: > autocommit has nothing to do with this, sequence state via currval is > session specific, not transaction specific. You're right. Apologies. John Sidney-Woollett