Store Procedures: output parameters and multiple resultsets

8 posts, 0 answers
  1. JC
    JC avatar
    5 posts
    Member since:
    May 2005

    Posted 23 Jun 2009 Link to this post

    I have just started to work on OpenAccess and did the QuickStart.
    My problem now is with Stored Procedures (SQL Server 2005) and I can't find an example which look like my requirements:
    - One of my parameter is an Output parameter
    - My stored procedure returns 4 resultsets

    My code is:
    IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope();

    IQuery resultQuery = scope.GetSqlQuery("ip_ListSearchRequirements ", null, "VARCHAR psProcedureName, INTEGER pnUserIdentityId, VARCHAR psCulture, INTEGER pnQueryContextKey, INTEGER pnQueryKey, VARCHAR ptXMLSelectedColumns, INTEGER pnReportToolKey, VARCHAR psPresentationType, BOOLEAN pbCalledFromCentura, BOOLEAN pbUseDefaultPresentation, BOOLEAN pbIsExternalUser");

    IQueryResult result = resultQuery.Execute(new object[] {"csw_ListCase", 26, "en-AU", 12, null, null, null, null, false, false, false});

    int count = result.Count;


    Questions:
    - when I run this code, I get an error "Telerik.OpenAccess.RT.sql.SQLException: Type not supported for setObject: 16". This is not very explicit and I don't know what to look for. Do you know what the problem is?
    - the first parameter "psProcedureName" is an output parameter. How do you get the result?
    - how do you get the 4 resultsets?
    - is there an alternative (ie better way) to the code above?

    Thanks
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 29 Jun 2009 Link to this post

    Hi JC,
    I suggest that we continue our discussion in the support thread that you have opened.

    Greetings,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Check out the tips for optimizing your support resource searches.
  3. DevCraft banner
  4. Michael Luna
    Michael Luna avatar
    9 posts
    Member since:
    May 2007

    Posted 28 Sep 2009 Link to this post

    Thanks a lot Petar.  You've taken away the value of seeing such a question in a forum, which is that others can benefit from the conversation and solution.
  5. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 29 Sep 2009 Link to this post

    Hi Michael Luna,

    We decided to continue the conversation in the support thread mainly because it is private, thus allowing the customer to share more details. If anyone faces the same problem he can easily "revive" this thread and he will most certainly get an answer. Here is the one I provided to JC. I hope you will find it useful.

    1. You should try and replace the boolean with bit values in the string where you pass the parameters.
    2. You will need to define the type a little bit different. When you want to use an output parameter the "out." prefix is needed. Here is an example:
    var query = scope.GetSqlQuery("Myproc ?,?"null"decimal unitPrice, out.integer outParam");  
    In your case you will need to use out.Varchar. When you do this your output parameter will be placed in a dictionary and its name will be used for key. If we would like to retrieve the output parameter from the above stored procedure call we would need code similar to this:
     int myParam = (int)queryResult.OutParameter["outParam"]; 
    3. If your result set contains the items of only one table you can map the result type of the stored procedure to the class that represents this table. This will enable you to interact with the result as a set of class objects. If you however map the return type of a procedure to an object array than the result will be a set of object arrays and each array will be representing a row from your query result.
    Multiple results sets returned by a single stored procedure are currently not supported.
    4. Unfortunately this is the only way around so far.

    Sincerely yours,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  6. Michael Luna
    Michael Luna avatar
    9 posts
    Member since:
    May 2007

    Posted 29 Sep 2009 Link to this post

    Can you tell me why, when I reverse-engineer a stored procedure with OUTPUT parameters, the keyword "OUTPUT" is not found anywhere in the display of the procedure code up on top?  This has the effect of forcing me to go in after the calling methods have been generated into "StoredProcedure.cs" and physically type "out." as a prefix to each of the output parameters, and I also find myself removing those parameters from the method, too, since I would just pass NULL anyway.  Now that I know this, I'm on the lookout for it.  However it's curious that the ORM doesn't do it for me automatically.  If I were to re-generate those sproc caller methods, I would have to re-do those fixes every time.
  7. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 30 Sep 2009 Link to this post

    Hello Michael Luna,

    The problem here is that the reverse mapping wizard does not support stored procedures with output parameters. So unfortunately as for now you will have to manually specify the "out" keyword.
    When editing the generated method you don't really need to remove the parameters from its definition. Instead you can mark them as out thus allowing the procedure to assign values to the parameters that you pass to the method. For example if you have a stored procedure that returns some result and and integer for the OrderId as output parameter than you should change the generated method to look something like this:
     
    public static IQueryResult OrderAndOrderID(IObjectScope scope,out int? OrderID) 
            { 
                IQuery query = scope.GetSqlQuery("[ProductFiles_InsertFromProductFileId] ?",null 
                    ,"OUT.INTEGER OrderID");             
                IQueryResult res = query.Execute(new object[] {OrderID}); 
                int a = res.Count;//Actually executes the query 
                OrderID = (int)res.OutParameter["OrderID"];             
               return res; 
            } 
    This way when you pass a parameter to your method and upon execution of the method this parameter will acquire the value of your output parameter.

    All the best,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  8. emmanuel vichi
    emmanuel vichi avatar
    1 posts
    Member since:
    Sep 2009

    Posted 07 Oct 2009 Link to this post

    What if I have an oracle sp that returns a cursor... somthing like this:

    create or replace PROCEDURE SelectArbol(ArbolIdParam IN ARBOL.CAMPO_ID%TYPE, ArbolCursor  OUT SYS_REFCURSOR) IS  
    BEGIN
        IF (ArbolIdParam IS NOT NULL) THEN
            OPEN ArbolCursor FOR
                SELECT *
                  FROM ARBOL C 
                 WHERE C.PADRE_ID = ArbolIdParam;
        ELSE
               OPEN ArbolCursor FOR
                SELECT *
                  FROM ARBOL C ;    
        END IF;
    end SelectArbol;

    How can I do to obtain a set of Arbol objects?

    Thanks!
  9. Ady
    Admin
    Ady avatar
    589 posts

    Posted 09 Oct 2009 Link to this post

    Hello emmanuel vichi,

     You can use the Reverse mapping wizard to generate a static method for this procedure.
    1. Run the Reverse mapping wizard and select the stored procedure under the 'Stored Procedures' node.Set the 'Generate method' property to 'true' and select the class for the ARBOL table in the 'Result' property.
    2. In the generated method modify the GetSqlQuery call as follows: specify 'OUT.CURSOR ARBOLCURSOR' in the second string parameter.
    3. Pass null as the parameter value in the call to ExecuteEnumerable.
    4. The return value of the query will be a list of arbol objects.
    5. Please have a look at this page for a similar example.

     You could also LINQ to obtain persistent objects instead of a stored procedure, if the procedure just returns instances and does nothing more.

    Greetings,
    Ady
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
Back to Top
DevCraft banner