Print Page | Close Window

Stored Procedure Mapping

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1356
Printed Date: 22-Sep-2025 at 4:22am


Topic: Stored Procedure Mapping
Posted By: skingaby
Subject: Stored Procedure Mapping
Date 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


Replies:
Posted By: kimj
Date 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&KW=stored+procedure - http://www.ideablade.com/forum/forum_posts.asp?TID=1259  and http://msdn.microsoft.com/en-us/library/bb399203.aspx - http://msdn.microsoft.com/en-us/library/bb399203.aspx

Posted By: skingaby
Date 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 - http://www.ideablade.com/forum/forum_posts.asp?TID=1363

Posted By: kimj
Date 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.)


Print Page | Close Window