From: | "John Sidney-Woollett" <johnsw(at)wardbrook(dot)com> |
---|---|
To: | henr-and(at)dsv(dot)su(dot)se |
Cc: | pgsql-jdbc(at)postgresql(dot)org |
Subject: | Re: getGeneratedKeys() |
Date: | 2003-12-31 07:53:20 |
Message-ID: | 2491.192.168.0.64.1072857200.squirrel@mercury.wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
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(at)dsv(dot)su(dot)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
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Marcus Andree S. Magalhaes | 2003-12-31 08:19:49 | Re: getGeneratedKeys() |
Previous Message | Kris Jurka | 2003-12-31 02:38:51 | Re: getGeneratedKeys() |