problem retrieving output value from StoredProc

I am trying to use parameters for a storedproc with C# and MySQL and I
am getting a null return value.

when I try to run the following snippet I find that the output value is
null. If I run the storedproc in the mysql query app I get a valid
return value. Is there something wrong with the code. btw I also have
found that parameters must be prefixed with '?' instead of '@'.

statement = "my_sqrt";
using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
command.CommandType = CommandType.StoredProcedure;

MySqlParameter param = new MySqlParameter();
param.ParameterName = "?input_number";
param.MySqlDbType = MySqlDbType.Int32;
param.Value = 9;
param.Direction = ParameterDirection.Input;
command.Parameters.Add(param);

param = new MySqlParameter();
param.ParameterName = "@l_sqrt";
param.MySqlDbType = MySqlDbType.Int32;
param.Direction = ParameterDirection.Output;
command.Parameters.Add(param);

command.ExecuteNonQuery();
if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
Console.WriteLine("null");
}

thanks for the help

john

Paul
Thu Aug 07 13:29:57 CDT 2008

On Thu, 07 Aug 2008 08:10:16 -0400, John Coltrane <tendengarci@yahoo.com> wrote:

¤ I am trying to use parameters for a storedproc with C# and MySQL and I
¤ am getting a null return value.
¤
¤ when I try to run the following snippet I find that the output value is
¤ null. If I run the storedproc in the mysql query app I get a valid
¤ return value. Is there something wrong with the code. btw I also have
¤ found that parameters must be prefixed with '?' instead of '@'.
¤
¤ statement = "my_sqrt";
¤ using ( MySqlCommand command = new MySqlCommand(statement, conn) ) {
¤ command.CommandType = CommandType.StoredProcedure;
¤
¤ MySqlParameter param = new MySqlParameter();
¤ param.ParameterName = "?input_number";
¤ param.MySqlDbType = MySqlDbType.Int32;
¤ param.Value = 9;
¤ param.Direction = ParameterDirection.Input;
¤ command.Parameters.Add(param);
¤
¤ param = new MySqlParameter();
¤ param.ParameterName = "@l_sqrt";
¤ param.MySqlDbType = MySqlDbType.Int32;
¤ param.Direction = ParameterDirection.Output;
¤ command.Parameters.Add(param);
¤
¤ command.ExecuteNonQuery();
¤ if ( command.Parameters[ "?l_sqrt" ].Value == null ) {
¤ Console.WriteLine("null");
¤ }
¤

You may want to try making the output parameter the first parameter in the Parameters collection. I
seem to remember that this is a requirement.

Yes, MySQL uses somewhat different notation for parameter names.


Paul
~~~~
Microsoft MVP (Visual Basic)

john
Thu Aug 07 14:44:56 CDT 2008

On Aug 7, 2:29=A0pm, Paul Clement
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> On Thu, 07 Aug 2008 08:10:16 -0400, John Coltrane <tendenga...@yahoo.com>=
wrote:
>
> =A4 I am trying to use parameters for a storedproc with C# and MySQL and =
I
> =A4 am getting a null return value.
> =A4
> =A4 when I try to run the following snippet I find that the output value =
is
> =A4 null. If I run the storedproc in the mysql query app I get a valid
> =A4 return value. Is there something wrong with the code. btw I also have
> =A4 found that parameters must be prefixed with '?' instead of '@'.
> =A4
> =A4 statement =3D "my_sqrt";
> =A4 using ( MySqlCommand command =3D new MySqlCommand(statement, conn) ) =
{
> =A4 =A0 =A0 =A0command.CommandType =3D CommandType.StoredProcedure;
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0MySqlParameter param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "?input_number";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Value =3D 9;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Input;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "@l_sqrt";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Output;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0command.ExecuteNonQuery();
> =A4 =A0 =A0 =A0 =A0 =A0if ( command.Parameters[ "?l_sqrt" ].Value =3D=3D =
null ) {
> =A4 =A0 =A0 =A0 =A0 =A0 =A0Console.WriteLine("null");
> =A4 =A0 =A0 =A0 =A0 =A0}
> =A4
>
> You may want to try making the output parameter the first parameter in th=
e Parameters collection. I
> seem to remember that this is a requirement.
>
> Yes, MySQL uses somewhat different notation for parameter names.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

I moved the output parameter to the first parameter and now I get the
following exception:

"A MySqlParameter with ParameterName '?l_sqrt' is not contained by
this MySqlParameterCollection."

john
Thu Aug 07 14:49:51 CDT 2008

On Aug 7, 2:29=A0pm, Paul Clement
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> On Thu, 07 Aug 2008 08:10:16 -0400, John Coltrane <tendenga...@yahoo.com>=
wrote:
>
> =A4 I am trying to use parameters for a storedproc with C# and MySQL and =
I
> =A4 am getting a null return value.
> =A4
> =A4 when I try to run the following snippet I find that the output value =
is
> =A4 null. If I run the storedproc in the mysql query app I get a valid
> =A4 return value. Is there something wrong with the code. btw I also have
> =A4 found that parameters must be prefixed with '?' instead of '@'.
> =A4
> =A4 statement =3D "my_sqrt";
> =A4 using ( MySqlCommand command =3D new MySqlCommand(statement, conn) ) =
{
> =A4 =A0 =A0 =A0command.CommandType =3D CommandType.StoredProcedure;
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0MySqlParameter param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "?input_number";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Value =3D 9;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Input;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "@l_sqrt";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Output;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0command.ExecuteNonQuery();
> =A4 =A0 =A0 =A0 =A0 =A0if ( command.Parameters[ "?l_sqrt" ].Value =3D=3D =
null ) {
> =A4 =A0 =A0 =A0 =A0 =A0 =A0Console.WriteLine("null");
> =A4 =A0 =A0 =A0 =A0 =A0}
> =A4
>
> You may want to try making the output parameter the first parameter in th=
e Parameters collection. I
> seem to remember that this is a requirement.
>
> Yes, MySQL uses somewhat different notation for parameter names.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

correction, the exception was caused by a typo in my code. So having
the output parameter first still yields a null output value. I posted
this problem in the MySQL group and didn't get a response. grrrr!

thanks

john
Thu Aug 07 15:57:40 CDT 2008

On Aug 7, 2:29=A0pm, Paul Clement
<UseAdddressAtEndofMess...@swspectrum.com> wrote:
> On Thu, 07 Aug 2008 08:10:16 -0400, John Coltrane <tendenga...@yahoo.com>=
wrote:
>
> =A4 I am trying to use parameters for a storedproc with C# and MySQL and =
I
> =A4 am getting a null return value.
> =A4
> =A4 when I try to run the following snippet I find that the output value =
is
> =A4 null. If I run the storedproc in the mysql query app I get a valid
> =A4 return value. Is there something wrong with the code. btw I also have
> =A4 found that parameters must be prefixed with '?' instead of '@'.
> =A4
> =A4 statement =3D "my_sqrt";
> =A4 using ( MySqlCommand command =3D new MySqlCommand(statement, conn) ) =
{
> =A4 =A0 =A0 =A0command.CommandType =3D CommandType.StoredProcedure;
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0MySqlParameter param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "?input_number";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Value =3D 9;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Input;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0param =3D new MySqlParameter();
> =A4 =A0 =A0 =A0 =A0 =A0param.ParameterName =3D "@l_sqrt";
> =A4 =A0 =A0 =A0 =A0 =A0param.MySqlDbType =3D MySqlDbType.Int32;
> =A4 =A0 =A0 =A0 =A0 =A0param.Direction =3D ParameterDirection.Output;
> =A4 =A0 =A0 =A0 =A0 =A0command.Parameters.Add(param);
> =A4
> =A4 =A0 =A0 =A0 =A0 =A0command.ExecuteNonQuery();
> =A4 =A0 =A0 =A0 =A0 =A0if ( command.Parameters[ "?l_sqrt" ].Value =3D=3D =
null ) {
> =A4 =A0 =A0 =A0 =A0 =A0 =A0Console.WriteLine("null");
> =A4 =A0 =A0 =A0 =A0 =A0}
> =A4
>
> You may want to try making the output parameter the first parameter in th=
e Parameters collection. I
> seem to remember that this is a requirement.
>
> Yes, MySQL uses somewhat different notation for parameter names.
>
> Paul
> ~~~~
> Microsoft MVP (Visual Basic)

I just solved my problem. It was the StoredProc code. Of course!