New Posts New Posts RSS Feed: Stored Procedure Mapping
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Stored Procedure Mapping
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
skingaby View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 23-Apr-2008
Location: United States
Posts: 146 		Post Options Post Options   Quote skingaby Quote  Post ReplyReply Direct Link To This Post Topic: Stored Procedure Mapping
    Posted: 02-Jul-2009 at 8:36am
I have an Oracle Stored Proc:

CREATE OR REPLACE PROCEDURE GetNextSequenceValue(
    sequence_name IN VARCHAR,
    next_val OUT NUMBER)
IS
    select_stmt varchar(200);
BEGIN
    EXECUTE IMMEDIATE concat('select ' , concat(sequence_name , '.NEXTVAL from dual'))
        INTO next_val;
END;

I have mapped it in the EDMX:

<Function Name="GETNEXTSEQUENCEVALUE" IsComposable="false" BuiltIn="false" Aggregate="false" NiladicFunction="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="GCSD9" StoreFunctionName="GETNEXTSEQUENCEVALUE">
<Parameter Name="SEQUENCE_NAME" Type="VARCHAR2" Mode="In" />
<Parameter Name="NEXT_VAL" Type="int64" Mode="Out" />
</Function>       

<FunctionImport Name="GetNextSequenceValue">
<Parameter Name="SEQUENCE_NAME" Mode="In" Type="String" />
<Parameter Name="NEXT_VAL" Mode="Out" Type="Int64" />
</FunctionImport>

<FunctionImportMapping FunctionImportName="GetNextSequenceValue" FunctionName="GcsModel.Store.GETNEXTSEQUENCEVALUE" />

When I save this model, DevForce notices the EDMX has changed and tries to regenerate the servermodel, but it fails and leaves this message in the designer.cs file:

    #region GetNextSequenceValue StoredProcQuery
    /// <summary>
    /// Constructs and executes the <see cref="T:IbEm.StoredProcQuery"/>
    /// associated with the given stored procedure.
    /// </summary>
    public

*** Error occurred in template processing ***Eval error: Unable to evaluate either XPath: '@ibcg:ClientReturnType' or Attribute: ''

Is this a bug in RC3 or is this a problem with my implementation? How should I implement this? Can I?
Thanks,
Simon
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 03-Jul-2009 at 10:15am
The Entity Framework, and by extension DevForce, supports only a collection of entities as a return type from a stored procedure.  You can probably get this to work if you create a dummy Entity wrapping the return result.  Here's a little more info - http://www.ideablade.com/forum/forum_posts.asp?TID=1259 and http://msdn.microsoft.com/en-us/library/bb399203.aspx
Back to Top
skingaby View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 23-Apr-2008
Location: United States
Posts: 146 		Post Options Post Options   Quote skingaby Quote  Post ReplyReply Direct Link To This Post Posted: 08-Jul-2009 at 7:30am
I figured out a way to execute the stored proc using a custom ADO Helper class and the RdbKey from the EdmKey in the DevForce config. I posted it to the forum here:
http://www.ideablade.com/forum/forum_posts.asp?TID=1363
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 08-Jul-2009 at 7:52am
Thanks for posting your workaround!
 
(The reason the AdoHelper was configured for SQL Server is because that's the fallback when the data provider is unknown.  The recognized data providers are System.Data.OleDb, System.Data.OracleClient and System.Data.SqlClient.  Another workaround might have been to extend the existing OracleProviderHelper class, but since, as you say, this is largely undocumented, that might have proven more difficult than your very nice solution.)
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down