Mark Ashton
9/17/2005 1:12:00 AM
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
> cmd.Parameters("@SessionID").Value = DBNull.Value;
If that isn''t enough, try changing direction from Output to InputOutput.
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@myself.com> wrote in message
news:%23gU3xsCuFHA.2540@TK2MSFTNGP09.phx.gbl...
> 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