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