Re: getGeneratedKeys()

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
>
>
>