Store Procedures: output parameters and multiple resultsets

[JC]

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

[PetarP]

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.

[Michael Luna]

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.

[PetarP]

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.

[Michael Luna]

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.

[PetarP]

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.

[emmanuel vichi]

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!

[Ady]

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.