Thread: Serial key

Serial key

From

Bhushan Bhangale

Date:

13 February 2004, 16:24:20

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
________________________________________________________________________

Re: Serial key

From

"Chris Smith"

Date:

13 February 2004, 16:46:05

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

Re: Serial key

From

Bhushan Bhangale

Date:

13 February 2004, 16:50:36

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
________________________________________________________________________

Re: Serial key

From

"John Sidney-Woollett"

Date:

13 February 2004, 17:28:54

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

Re: Serial key

From

Kris Jurka

Date:

13 February 2004, 17:39:27

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

Re: Serial key

From

Dave Cramer

Date:

13 February 2004, 17:53:16

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

Re: Serial key

From

"John Sidney-Woollett"

Date:

13 February 2004, 18:03:36

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