on 2012 May 10 7:59 PM
No value us returned to the parameters after the execute command. Has anyone done this?
Here is the store proc I used.
create or replace procedure jay(out @val int) begin select 25; set @val = 34; return 33; end;
As you can see this does it all. Has an output parameter and a result set and a return value.
I can read the result set but I never have any value but 0 in the paramter set after the execute.
The following is the code I used to test this out.
var connString = ConfigurationManager.ConnectionStrings["dbConnect"]; var conn = new SAConnection(connString.ConnectionString); conn.Open(); var cmd = new SACommand(); string cmdstring = "jay"; cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SAParameter() { ParameterName = "@ret", Direction = ParameterDirection.ReturnValue, SADbType = SADbType.Integer }); cmd.Parameters.Add(new SAParameter() { ParameterName = "@val", Direction = ParameterDirection.Output, SADbType = SADbType.Integer }); cmd.CommandText = cmdstring; var ret = cmd.ExecuteScalar(); conn.Close();
Request clarification before answering.
I don't find a doc reference for that, but I think I remember that when using interfaces like ODBC or ADO with a stored procedure that does return both a result set and output parameters and/or a return value, you can either
but not both at the same time.
I guess the reason is that the result set is returned beforehand and has to be completely fetched before the other values are available, and that is usually not possible with these interfaces.
More on this can be found in Mark's answer on a related topic.
In contrast, ISQL does allow both kind of accesses - and that leads to 2 result sets, as in your sample:
begin declare @MyOutputVal int; declare @MyReturnVal int; @MyReturnVal = call jay(@MyOutputVal); select @MyOutputVal, @MyReturnVal; end;
returns first result set
25
25
and second result set
@MyOutputVal,@MyReturnVal
34,33
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I was not clear, your example code is in ISQL, I have no problem doing as you did there but when calling from a C# application I have the problem. The documentation for ASE ADO access shows and example of how to access parameters. By the way the ADO API controls how this should work, you must always read to the end of the cursor to see the parameter values. I have had no problem doing this in Microsoft SQL server but ASA 12 does not seem to implement this properly. Is there a trick to it is what I want to know.
Obviously, it's a question on ADO.NET, not ADO...
I don't have further hints, but I guess ADO.NET just lets you choose between
I hope a SQL Anywhere expert will confirm that - or will correct me, if there's a way to combine both return pathes in one call.
FWIW, in such cases where I would have preferred a result set plus a return value, I usually have modified my stored procedures by turning return values and output parameters into a (possibly second) result set, by this choosing just one "return path". If you use more than one result set, I guess SADataReader.NextResult() will move to the next result set.
User | Count |
---|---|
52 | |
10 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.