Returning Identity from an InsertFunction

Returning Identity from an InsertFunction

Postby DavePark » Wed 19 Jan 2011 16:35

Using dotConnect for Oracle v6.00.70

Due to various limitation in Entity Framework, I need to use a stored procedure as an InsertFunction for inserting new rows into one of my tables that also happens to have StoreGeneratedPattern="Identity". Unfortunately, I've tried various approaches and have still not had any luck. I'm now looking for guidance on what else to try.

Here's what I've tried so far and the results:

1a) Attempting to return the ID as a ReturnType:
Error 2046: A mapping function binding specifies a function NodeModel.Store.INSERTNODEFREEFORMTEXT that is not supported. Only functions that cannot be composed are supported.
1b) Changing IsComposable from true to false:
Error 137: Functions that cannot be composed must not declare a return type.
1c) Removing the ReturnType:
System.ServiceModel.DomainServices.Client.DomainOperationException: Submit operation failed due to conflicts. Please inspect Entity.EntityConflict for each entity in EntitiesInError for more information.
1d) Adding a RowsAffectedParameter:
The generated ID does not get sent back up the stack through RIA to Silverlight
2) Attempting to use a PIPELINED function:
ORA--14551: cannot perform a DML operation inside a query
3) Attempting to return ID as an OUT parameter:
Error 2047: A mapping function binding specifies a function NodeModel.Store.INSERTNODEFREEFORMTEXT2 with an unsupported parameter: PID. Output parameters may only be mapped through the RowsAffectedParameter property. Use result bindings to return values from a function invocation.

Any assistance would be much appreciated!

Thanks,

Dave
DavePark
 
Posts: 4
Joined: Wed 19 Jan 2011 16:23
Location: Malvern, PA

Got it!

Postby DavePark » Wed 19 Jan 2011 22:04

I looked at how a standard insert works:

Code: Select all
declare
  updatedRowid ROWID;
begin
  insert into T(C1, C2)
  values (:p0, :p1)
  returning ROWID into updatedRowid;
  open :outParameter for select ID from T where ROWID = updatedRowid;
end;


So, I re-coded my stored function to return SYS_REFCURSOR, as follows:

Code: Select all
CREATE OR REPLACE
FUNCTION
   InsertT
(
   pC1 T.C1%type,
   pC2 T.C2%type
)
RETURN
   sys_refcursor
IS
   ID T.Id%type;
   csr sys_refcursor;
BEGIN
   INSERT
   INTO
      T
   (
      C1,
      C2
   )
   VALUES
   (
      pC1,
      pC2
   )
   RETURN Id INTO ID;
   open csr for select ID ID from dual;
   RETURN csr;
END;


This worked perfectly.

Note that I no longer needed the RowsAffectedParameter either.

Also note that I needed "ID" twice in the SELECT clause. Since I am selecting a local variable from dual, that local variable needs an alias.
DavePark
 
Posts: 4
Joined: Wed 19 Jan 2011 16:23
Location: Malvern, PA

Postby AndreyR » Fri 21 Jan 2011 12:49

Thank you for sharing the solution. This is an appropriate workaround.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle