Thread: getGeneratedKeys()
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
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
> 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. -- Henrik
Can you do the insert using a function instead, and return the currval on the underlying sequence? John henr-and@dsv.su.se said: > >> 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. > > -- > Henrik > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
> 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
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. >> 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)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
On 30/12/2003 23:18 henr-and@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 | +------------------------------+---------------------------------------------+
> 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.
On Tue, 30 Dec 2003 henr-and@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
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-and@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
>
>
>
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.... >> 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)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org