Re: JDBC keygen select - Mailing list pgsql-jdbc

Mike,

Apparently there is a patch floating around that will implement
INSERT ... RETURNING

So I'd say that it is more likely that number 1 gets implemented as
it would seem to me easier to do if we know exactly which column to
return.

Dave
On 2-Feb-06, at 9:07 AM, Mike Clements wrote:

> When Postgres eventually does support keygen as you mentioned,
> which form is more likely to be supported?
>
> ps = itsDBConnection.prepareStatement(sql, {"myKeyColumn"});
>
> OR
>
> ps = itsDBConnection.prepareStatement(sql,
> Statement.RETURN_GENERATED_KEYS);
>
> In the first, the caller declares which columns he wants back so
> there is no possibility of any ambiguity. In the second, the caller
> relies on the database to decide which column will be returned, and
> we hope the DB knows which one the caller wants (most likely the
> primary key column).
>
>> -----Original Message-----
>> From: pgsql-jdbc-owner@postgresql.org
>> [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Mike Clements
>> Sent: Monday, October 24, 2005 12:51 PM
>> To: Dave Cramer
>> Cc: pgsql-jdbc@postgresql.org
>> Subject: Re: [JDBC] JDBC keygen select
>>
>> Dave,
>>
>> Thanks for the info. Right now I'm issuing a select
>> currval('foo') after the insert to get the generated PK. This
>> works but the extra SQL round trip slows down insert
>> performance quite a bit.
>>
>> Mike
>>
>>> -----Original Message-----
>>> From: Dave Cramer [mailto:pg@fastcrypt.com]
>>> Sent: Monday, October 24, 2005 11:20 AM
>>> To: Mike Clements
>>> Cc: pgsql-jdbc@postgresql.org
>>> Subject: Re: [JDBC] JDBC keygen select
>>>
>>> Mike,
>>>
>>> Well, until we get insert returning implemented (allegedly
>>> soon), the
>>> protocol doesn't support returning any values without
>> another round
>>> trip to the db
>>>
>>> So you have two choices here.
>>>
>>> 1) get the ID before and insert it ie select
>>> nextval('sequence_name')
>>> and insert the value explicitly. If you cache sequences you
>>> can cache
>>> them on the connection too ( more work )
>>> 2) get the ID after using currval('sequence_name')
>>>
>>> P.S. Using PostGRE is frowned upon the name is either Postgres, or
>>> PostgreSQL
>>>
>>> Dave
>>> On 22-Oct-05, at 4:57 PM, Mike Clements wrote:
>>>
>>>> Hello,
>>>>
>>>> I'm a newbie on PostGRE but have experience using SQL
>>> Server & Oracle
>>>> via JDBC. Something that works fine on these does not work in
>>>> PostGRE so
>>>> I'm looking for advice.
>>>>
>>>> In my schema all primary keys are integers and the DB
>> automatically
>>>> assigns values (using sequences or identities). When I
>> insert into
>>>> these
>>>> tables via JDBC I do not specify any value for the primary
>>> key, and I
>>>> use the RETURN_GENERATED_KEYS flag so the generated key is
>>> provided in
>>>> the RecordSet returned from the insert command - something
>>> like this:
>>>>
>>>> pStmt = itsDbConn.prepareStatement(
>>>>     "insert into tbl (col1) values (?)",
>>>>     Statement.RETURN_GENERATED_KEYS);
>>>> pStmt.setString(1, "foo");
>>>> count = pStmt.executeUpdate();
>>>> if(count > 0)
>>>> {
>>>>     rs = pStmt.getGeneratedKeys();
>>>>     rs.next();
>>>>     pk = rs.getLong(1);
>>>> }
>>>>
>>>> This works on SQL Server & Oracle but *not* on PostGRE SQL. In the
>>>> latter, it throws an exception in prepareStatement()
>> saying "this
>>>> method
>>>> is not yet implemented".
>>>>
>>>> So my question is, how does one do this? This keygen approach is
>>>> important for performance, scalability and robustness. Launching a
>>>> separate SQL command to fetch the generated key has performance
>>>> problems. Self-generating the keys has problems with concurrency
>>>> across
>>>> multiple clients.
>>>>
>>>> Thanks
>>>>
>>>> Michael R. Clements
>>>> Principal Architect, Actional Corp.
>>>> mclements@actional.com
>>>> FREE! Actional SOAPstation Developer Version
>>>> Web services routing, security, transformation and versioning
>>>> http://www.actional.com/sstdownload
>>>>
>>>> ---------------------------(end of
>>>> broadcast)---------------------------
>>>> TIP 4: Have you searched our list archives?
>>>>
>>>>                http://archives.postgresql.org
>>>>
>>>>
>>>
>>>
>>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>                http://www.postgresql.org/docs/faq
>>
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org
>