Hi,

My task is to call a stored procedure with output parameters.
I use auto detection methods to obtain the parameter list.

I had a working solution with ActiveX ODBC. I tried to convert it into NET.

No success, after a lot of attempts.

Then, I tried NET SQL Server classes. I had success, but I had to change one of autodetected directions.

So, my last solution works for me. However, I wonder whether it is really impossible to use ODBC in NET.


More details.

The procedure (it is very long, only parameter definitions copied).
CREATE PROCEDURE insertKSESSION
@UserName LONGSTRING = NULL,
@ClientWorkStation LONGSTRING = NULL,
@ServerHostName LONGSTRING = NULL,
@Expirable BOOLEAN = 0,
@UserPrincipalName LONGSTRING = NULL,
@SessionID INTEGER OUTPUT,
@UserID INTEGER = NULL OUTPUT,
@GUID SYSTEMID = NULL OUTPUT,
@ConnectionType ENUM = 0,
@userRealName LONGSTRING = NULL OUTPUT
AS
SET NOCOUNT ON
BEGIN
....
END

You can notice that @SessionID differs from the rest OUTPUT parameters: it has no default value.
The user defined type LONGSTRING is VARCHAR(255) with a specific collation. It is correctly
recognized as DbType=AnsiString and OdbcType/SqlDbType=VarChar during the parameter detection.
Please, do not recommend me to change anything in the procedure. It is a part of a large 3rd
party system running on multiple servers.

My working solution in ADO:
var cn,cmd;
var adCmdStoredProc = 4;
cn = new ActiveXObject('ADODB.Connection');
cn.Open('DSN=MyOdbc;UID=sa;PWD=asas');
cmd = new ActiveXObject('ADODB.Command');
cmd.ActiveConnection = cn;
cmd.CommandText = 'insertKSESSION';
cmd.CommandType = adCmdStoredProc;
cmd.Parameters.Refresh; // now, I have 11 parameters
cmd.Parameters("@UserName") = MyUserName;
cmd.Parameters("@ClientWorkStation") = MyClientWorkStation;
cmd.Parameters("@ServerHostName") = MyServerHostName;
cmd.Parameters("@Expirable") = 1;
cmd.Parameters("@UserPrincipalName") = "";
cmd.Execute();
// And now I have all required output values in cmd.Parameters

My working solution with System.Data.SqlClient:
var cn:SqlConnection, cmd:SqlCommand;
cn = new SqlConnection('Server=MyServer;Database=MyDatabase ;User ID=sa;Password=asas');
cn.Open();
cmd= new SqlCommand();
cmd.Connection = cn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
cmd.CommandText = 'insertKSESSION';
SqlCommandBuilder.DeriveParameters(cmd); // now, I have 11 parameters
cmd.Parameters("@SessionID").Direction = ParameterDirection.Output; // !!!
cmd.Parameters("@UserName").Value = MyUserName;
cmd.Parameters("@ClientWorkStation").Value = MyClientWorkStation;
cmd.Parameters("@ServerHostName").Value = MyServerHostName;
cmd.Parameters("@Expirable").Value = 1;
cmd.Parameters("@UserPrincipalName").Value = "";
cmd.ExecuteNonQuery();

One problem: if I try to do it without assignment for
cmd.Parameters("@SessionID").Direction, I get
Procedure 'insertKSESSION' expects parameter '@SessionID', which was not supplied.

Really, DeriveParameters return InputOutput instead of Output in all cases.
However, Refresh in ADO had the same problem, but it worked.


I will not give the text for my attempts with System.Data.Odbc.
The main version is an exact copy of the previous text, with all Sql changed into Odbc.
I performed a large number of additional tests.

I never could go over the exception
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'insertKSESSION' expects parameter '@SessionID', which was not supplied.

Then, I tried to change procedure parameter definition into
@SessionID INTEGER = NULL OUTPUT
(as I mentioned, the change is not acceptable for work, I did it only for testing purposes).
Now, the procedure was entered. But the first parameter, @UserName, was incorrect,
and the procedure failed.

I definitely wonder whether anybody has success in NET ODBC using stored procedures with output parameters.

Thank you,

Mahris

Against SQL Server, all OUTPUT parameters are really input/output
parameters.

With OdbcParameter.Value == null, /* which represents DEFAULT */ against the
server, it will complain about no value which is why it worked why you
tweaked the procedure to have a default value. System.DBNull.Value
represents NULL.

on the client, try to add the following line

For the second issue, with Odbc parameters are by ordinal position, not by
name. i.e. the odbc syntax of { CALL x(?,?,?) }
Make sure your OdbcParameter collection is in the correct ordinal position
for the server.

SqlClient is different by doing everything by name (position doesn't
matter).

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Maris Janis Vasilevskis" <mahris (AT) myself (DOT) com> wrote