Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

getGeneratedKeys()

24 views
Skip to first unread message

Henrik Andersson

unread,
Dec 30, 2003, 1:16:14 PM12/30/03
to
Is getGeneratedKeys() implemented? I am using the package in Debian
Unstable but I am getting: "org.postgresql.util.PSQLException: This
method is not yet implemented."

--
Henrik


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org

Kris Jurka

unread,
Dec 30, 2003, 3:30:03 PM12/30/03
to

On Tue, 30 Dec 2003, Henrik Andersson wrote:

> Is getGeneratedKeys() implemented? I am using the package in Debian
> Unstable but I am getting: "org.postgresql.util.PSQLException: This
> method is not yet implemented."

This method has not been implemented and most likely will not be
implemented anytime soon. First it is unclear to me what it means by
auto-generated keys. Does it mean just things like serial or anything
that a before insert trigger does to modify the row? Secondly what comes
back in the ResultSet? Just the generated keys? How is that useful
without anything to tie it to? Finally the real problem is that the
backend has no way of reporting this information to us. Possibly for a
single insert we could do a getLastOID() and assume it was unique in the
table to look it up, but this is obviously fragile in the presence of
duplicate oids, tables without oids, anything other than insert
statements, and statements that operate on more than one row.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

henr...@dsv.su.se

unread,
Dec 30, 2003, 4:55:22 PM12/30/03
to

> This method has not been implemented and most likely will not be
> implemented anytime soon. First it is unclear to me what it means by
> auto-generated keys. Does it mean just things like serial or anything
> that a before insert trigger does to modify the row?

In my case it's a serial. I want to get the id-number that my insert
generates.
MySQL's JDBC-driver has implemented the method.

John Sidney-Woollett

unread,
Dec 30, 2003, 5:47:41 PM12/30/03
to
Can you do the insert using a function instead, and return the currval on
the underlying sequence?

John


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)

henr...@dsv.su.se

unread,
Dec 30, 2003, 6:18:47 PM12/30/03
to
> Can you do the insert using a function instead, and return the currval
> on the underlying sequence?
>
> John
>

I'm not sure what you mean. A stored procedure?

The good thing about getGeneratedKeys() is that you don't have to write
DB-specific code.
--
Henrik

---------------------------(end of broadcast)---------------------------

Marcus Andree S. Magalhaes

unread,
Dec 30, 2003, 6:28:21 PM12/30/03
to

Guys,

I am too dumb or a simple currval() would be useful?
We solved this by sending two queries in JDBC. The first one inserts
a single record in the db. The other issues a currval on the desired
sequence.
There was a posting a few weeks ago, describing a clever method,
issuing both queries on the same connection by using the ";" operator
to separate the queries.
hope this helps.


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly

Paul Thomas

unread,
Dec 30, 2003, 6:56:23 PM12/30/03
to

On 30/12/2003 23:18 henr...@dsv.su.se wrote:
> > Can you do the insert using a function instead, and return the currval
> > on the underlying sequence?
> >
> > John
> >
>
> I'm not sure what you mean. A stored procedure?
>
> The good thing about getGeneratedKeys() is that you don't have to write
> DB-specific code.

I always thought that auto-generated keys _are_ db specific. Where are
they defined in SQL92/99?

Back to your problem: Does your app have to support different DBMSs or is
it PostgreSQL-only?


--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

henr...@dsv.su.se

unread,
Dec 30, 2003, 7:08:14 PM12/30/03
to
> I always thought that auto-generated keys _are_ db specific. Where are
> they defined in SQL92/99?
>
> Back to your problem: Does your app have to support different DBMSs or
> is it PostgreSQL-only?
>

Different DBMSs.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Kris Jurka

unread,
Dec 30, 2003, 9:38:51 PM12/30/03
to

On Tue, 30 Dec 2003 henr...@dsv.su.se wrote:

>
> > This method has not been implemented and most likely will not be
> > implemented anytime soon. First it is unclear to me what it means by
> > auto-generated keys. Does it mean just things like serial or anything
> > that a before insert trigger does to modify the row?
>
> In my case it's a serial. I want to get the id-number that my insert
> generates.
> MySQL's JDBC-driver has implemented the method.
>

They have implemented the method, but they have not answered any of the
questions I have raised. Their getGeneratedKeys call just wraps
getLastInsertID(). This would be the equivalent of us providing a currval
like function that didn't take a sequence argument. It is largely useless
in the face of after insert triggers calling another sequence's nextval
and tables with multiple serial columns. This doesn't even get into the
before trigger case.

Also with mysql's timestamp type auto-updating itself, shouldn't that be
returned as well?

Does anyone know of any documention other than the API javadoc? The
DatabaseMetaData functions that return ResultSets list the columns and
their contents, but this Statement function does no such thing.

Kris Jurka


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

John Sidney-Woollett

unread,
Dec 31, 2003, 2:53:20 AM12/31/03
to
If you want a database neutral solution, then create a function to insert
the record. Have the function returns the ID (PK) of the record it just
inserted.

Here's an example:

CREATE OR REPLACE FUNCTION WEBDAV.CreateFolder (integer, varchar) RETURNS
integer AS '
-- creates a new folder (if it does not exist)
-- and returns the WDResource ID of the newly
-- created folder. If the folder already exists
-- or could not be created then -1 is returned

DECLARE
pFolderID ALIAS FOR $1;
pFolderName ALIAS FOR $2;

vUserID integer;
vCount integer;
vURL varchar(255);

BEGIN
-- get the parent folder information
select WDUserID, URL into vUserID, vURL
from WEBDAV.WDResource
where WDResourceID = pFolderID;

if vUserID is null then
return -1;
end if;

-- check that the folder does not exist
select count(*) into vCount from WEBDAV.WDResource where URL = vURL
||''/''|| pFolderName;
if (vCount > 0) then
return -1;
end if;

-- create the new folder resource
insert into WEBDAV.WDResource (WDPARENTID, WDUSERID, WDRESTYPEID, URL,
FILENAME, LASTMODIFIED)
values (pFolderID, vUserID, 1, vURL||''/''||pFolderName, pFolderName,
now());

return currval(''WEBDAV.SEQ_WDResource'');
END;
' LANGUAGE 'plpgsql';


The important things to note are that this function can be written for
Postgres, Oracle or any other RDBMS - you only change the internals of the
function. The function returns either -1 if no record is created, or the
PK value of the newly inserted record.

Does that help you?

John


henr...@dsv.su.se said:
>> Can you do the insert using a function instead, and return the currval
>> on the underlying sequence?
>>
>> John
>>
>
> I'm not sure what you mean. A stored procedure?
>
> The good thing about getGeneratedKeys() is that you don't have to write
> DB-specific code.
> --
> Henrik
>
>
>


---------------------------(end of broadcast)---------------------------

Marcus Andree S. Magalhaes

unread,
Dec 31, 2003, 3:19:49 AM12/31/03
to

Ahhh.... yes.... good and old "multiplatform code compatibility"...

I remember that from '95, when version Beta 2.0 arrived and I started
to write Java code in applets...

There was a cool hack around to use Netscape to compile code and
generate a class file under Linux 1.2...

The promiss of "write once, run everywhere". Frames used AWT back
then and all have the same cup of cofee when iconized. After all,
the mac wasn;t able to iconize frames. Well, some flexibility was
trade off in the name of compatibility. Fine...

Then, we had to deal with threading issues in Solaris and Windows...
After that, JDBC arrived... True, multiplatform db access.
A query as complex as "select gettime()" could run in SQL Server
and Postgres (or it was called PostgreSQL at that time ?)
and return the same kind of data: a timestamp-like data in SQL Server
and an error in Postgres.

Since then, I never believed in platform independent coding. And it
was a long time ago....

0 new messages