Execute - Stored procedure with return value

Question

solomon_13000 14 Junior Poster in Training

12 Years Ago

The code below produced the error: OUT or INOUT argument 3 for routine mysql.getAuthentication is not a variable or NEW pseudo-variable in BEFORE trigger. Did I miss something?.

using System;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
        string MyConString = "SERVER=localhost;" +
        "DATABASE=mysql;" +
        "UID=root;" +
        "PASSWORD=12345;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = new MySqlCommand("getAuthentication", connection);
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.Add(new MySqlParameter("uname", "admbsa1"));
        command.Parameters.Add(new MySqlParameter("pws", "$$!@!#*&"));
        command.Parameters.Add(new MySqlParameter("@total",SqlDbType.Int));
        command.Parameters.Add(new MySqlParameter("@pwsval",SqlDbType.VarChar));
        command.Parameters.Add(new MySqlParameter("@logId",SqlDbType.VarChar));
        command.Parameters.Add(new MySqlParameter("@grpname", SqlDbType.VarChar));
        command.Connection.Open();
        command.ExecuteNonQuery();
        command.Connection.Close();

        string t_return = command.Parameters["@total"].Value.ToString();
        Console.Write(t_return);
    }

    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
}

2 Contributors
13 Replies
2K Views
21 Hours Discussion Span
Latest Post 12 Years Ago Latest Post by thines01

thines01 401 Postaholic

12 Years Ago

Do you need to use "{call getAuthentication}"?
...checking...

as in:

cmd.CommandText = "CALL MyProcedure(@MyParam1, @MyParam2);";

reference:http://markcordell.blogspot.com/2008/12/calling-mysql-stored-procedures-from-c.html

Edited 12 Years Ago by thines01 because: n/a

thines01 401 Postaholic

12 Years Ago

Correction: you don't need the braces.
Here is an example I just created:

using System;
using System.Collections.Generic;
//
using MySql.Data.MySqlClient;

/* // STORED PROCEDURE //
 create procedure GetWordsByLength(IN len INT)
BEGIN
	SELECT asdf.x
	FROM XXX.asdf
	WHERE (length(x)=len);
END 
*/
namespace DW_398532
{
   using DB_MyLister;
   public class DW_398532
   {
      private static int m_intLen = 3;

      public static bool Load(List<string> master, ref string strError)
      {
         bool blnRetVal = true;
         try
         {
            string strSQL = "call GetWordsByLength(@paramTargetLen)";
            using (MySqlConnection conn = new MySqlConnection(CDB_MyLister.csb.ToString()))
            {
               conn.Open();

               MySqlCommand cmd = new MySqlCommand(strSQL, conn);
               cmd.Parameters.Add("@paramTargetLen", MySqlDbType.Int16).Value = m_intLen;

               using (MySqlDataReader rdr = cmd.ExecuteReader())
               {
                  while (rdr.Read())
                  {
                     master.Add(rdr["x"].ToString().Trim());
                  }
                  //
                  rdr.Close();
               }

               conn.Close();
            }
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }

      static void Main(string[] args)
      {
         string strError = "";
         List<string> lst_str = new List<string>();
         if (!Load(lst_str, ref strError))
         {
            Console.WriteLine("Could not load: " + strError);
            return;
         }

         Console.WriteLine("Finished");
      }
   }
}

The table contains:
one
two
three
four
five
six
seven
eight
nine
ten

and the result of the query is:
one
two
six
ten

thines01 401 Postaholic

12 Years Ago

Is there any reason you're mixing styles of parameter adds?
And shouldn't that SqlDbType be a MySqlDbType?

thines01 401 Postaholic

12 Years Ago

You could also try something like this (untested code):

protected void Page_Load(object sender, EventArgs e)
      {
         // Get this from elsewhere (like another DLL)
         MySqlConnectionStringBuilder csb = new MySqlConnectionStringBuilder()
         {
            Database = "mysql",
            Server = "localhost",
            UserID = "root",
            Password = "abcde",
         };

         string t_return = ""; // must return no matter what

         try
         {
            using (MySqlConnection connection = new MySqlConnection(csb.ToString()))
            {
               connection.Open(); // can open any time

               string strSQL = "call getAuthentication(@param1,@param2,@total,@pwsval);";
               MySqlCommand command = new MySqlCommand(strSQL, connection);
               //
               command.Parameters.Add("@param1", MySqlDbType.VarChar).Value = "admbsa1";
               command.Parameters.Add("@param2", MySqlDbType.VarChar).Value = DBNull.Value;
               command.Parameters.Add("@total", MySqlDbType.Int16);
               command.Parameters.Add("@pwsval", MySqlDbType.VarChar);
               command.ExecuteNonQuery();
               //
               t_return = command.Parameters["@total"].Value.ToString();

               connection.Close();
            }
         }
         catch (Exception exc)
         {
            t_return = exc.Message;
         }

         Console.Write(t_return);
      }

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

solomon_13000 14 Junior Poster in Training · Answer 1 · 2011-12-06T02:39:45+00:00

I made the changes but received the error: OUT or INOUT argument 3 for routine mysql.getAuthentication is not a variable or NEW pseudo-variable in BEFORE trigger.

using System;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
        /*string MyConString = "SERVER=localhost;" +
        "DATABASE=mysql;" +
        "UID=root;" +
        "PASSWORD=sumbat31;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "select * from corporate";
        connection.Open();
        MySqlDataAdapter myadapter = new MySqlDataAdapter(command);
        DataSet dts = new DataSet();
        myadapter.Fill(dts);
        dts.Tables[0].Columns[0].ColumnName = "ID";
        GridView1.DataSource = dts.Tables[0];
        GridView1.DataBind();
        connection.Close();*/

        string MyConString = "SERVER=localhost;" +
        "DATABASE=mysql;" +
        "UID=root;" +
        "PASSWORD=abcde;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "call getAuthentication(?param1,?param2,@total,@pwsval);";
        command.Parameters.AddWithValue("?param1", "admbsa1");
        command.Parameters.AddWithValue("?param2",null);
        command.Parameters.Add(new MySqlParameter("@total",SqlDbType.Int));
        command.Parameters.Add(new MySqlParameter("@pwsval",SqlDbType.VarChar));
        command.Connection.Open();
        command.ExecuteNonQuery();
        command.Connection.Close();

        string t_return = command.Parameters["@total"].Value.ToString();
        Console.Write(t_return);
    }

    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
}

solomon_13000 14 Junior Poster in Training · Answer 2 · 2011-12-06T04:42:16+00:00

I made the changes and received the error OUT or INOUT argument 3 for routine mysql.getAuthentication is not a variable or NEW pseudo-variable in BEFORE trigger.

using System;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{
    
    protected void Page_Load(object sender, EventArgs e)
    {
        string MyConString = "SERVER=localhost;" +
        "DATABASE=mysql;" +
        "UID=root;" +
        "PASSWORD=abcde;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "call getAuthentication('admbsa1',null,@total,@pwsval);";
        command.Parameters.Add(new MySqlParameter("@total",MySqlDbType.Int64));
        command.Parameters.Add(new MySqlParameter("@pwsval",MySqlDbType.VarChar,20));
        command.Connection.Open();
        command.ExecuteNonQuery();
        command.Connection.Close();

        string t_return = command.Parameters["@total"].Value.ToString();
        Console.Write(t_return);
    }

    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
}

thines01 401 Postaholic Team Colleague Featured Poster · Answer 3 · 2011-12-06T10:08:09+00:00

thines01 401 Postaholic

12 Years Ago

testing...

solomon_13000 14 Junior Poster in Training · Answer 4 · 2011-12-06T04:53:24+00:00

DELIMITER$$
CREATE PROCEDURE getAuthentication(IN uname varchar(12), IN pws VARCHAR(12), OUT total INT, OUT pwsval VARCHAR(12))
BEGIN
   IF(uname IS NOT NULL AND pws IS NOT NULL) THEN
     SELECT COUNT(id) INTO total FROM authentication WHERE loginId=uname and password=pws;
   END IF;
   IF(uname IS NOT NULL AND pws IS NULL) THEN
     SELECT COUNT(id) INTO total FROM authentication WHERE loginId=uname;
     SELECT password INTO pwsval FROM authentication WHERE loginId=uname;
   END IF;
END$$
DELIMITER;


call getAuthentication('admbsa1','$$!@!#*&',@total,@pwsval);
call getAuthentication('admbsa1',null,@total,@pwsval);
select @total as 'total';
select @pwsval as 'pws';

solomon_13000 14 Junior Poster in Training · Answer 5 · 2011-12-06T04:55:19+00:00

DELIMITER$$
CREATE PROCEDURE getAuthentication(IN uname varchar(12), IN pws VARCHAR(12), OUT total INT, OUT pwsval VARCHAR(12))
BEGIN
   IF(uname IS NOT NULL AND pws IS NOT NULL) THEN
     SELECT COUNT(id) INTO total FROM authentication WHERE loginId=uname and password=pws;
   END IF;
   IF(uname IS NOT NULL AND pws IS NULL) THEN
     SELECT COUNT(id) INTO total FROM authentication WHERE loginId=uname;
     SELECT password INTO pwsval FROM authentication WHERE loginId=uname;
   END IF;
END$$
DELIMITER;


call getAuthentication('admbsa1','$$!@!#*&',@total,@pwsval);
call getAuthentication('admbsa1',null,@total,@pwsval);
select @total as 'total';
select @pwsval as 'pws';

thines01 401 Postaholic Team Colleague Featured Poster · Answer 6 · 2011-12-06T09:55:12+00:00

Some of the comments seem to be in a weird order.
Please ignore my last comment.
...testing...

Here is an odd solution I've seen:
http://forums.mysql.com/read.php?98,162564,162756#msg-162756

thines01 401 Postaholic Team Colleague Featured Poster · Answer 7 · 2011-12-06T11:50:58+00:00

On this page, codegecko says

And it's fixed! Removed the IN and OUT specifications and for variables going OUT, simply passed a null value into the procedure.

solomon_13000 14 Junior Poster in Training · Answer 8 · 2011-12-06T12:14:33+00:00

I found the solution:

using System;
using System.Data.Odbc;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using MySql.Data.MySqlClient;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string MyConString = "SERVER=localhost;" +
        "DATABASE=mysql;" +
        "UID=root;" +
        "PASSWORD=abcde;";
        MySqlConnection connection = new MySqlConnection(MyConString);
        MySqlCommand command = connection.CreateCommand();
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "getAuthentication";
        command.Parameters.AddWithValue("uname", "admbsa1");
        command.Parameters.AddWithValue("pws", null);
        command.Parameters.Add(new MySqlParameter("@total", MySqlDbType.Int32));
        command.Parameters["@total"].Direction = ParameterDirection.Output;
        command.Parameters.Add(new MySqlParameter("@pwsval", MySqlDbType.VarChar, 12));
        command.Parameters["@pwsval"].Direction = ParameterDirection.Output;
        command.Connection.Open();
        command.ExecuteNonQuery();
        command.Connection.Close();

        string t_return = command.Parameters["@pwsval"].Value.ToString();
        TextBox1.Text = t_return;
    }

    protected void GridView1_SelectedIndexChanged1(object sender, EventArgs e)
    {

    }
}

thines01 401 Postaholic Team Colleague Featured Poster · Answer 9 · 2011-12-06T12:48:54+00:00

You're correct. FANTASTIC!
I had gotten the call to not throw an exception, but had forgotten to pull the value FROM the parameter.

This works, too:

public static bool Load(string master, ref string strError)
      {
         bool blnRetVal = true;
         try
         {
            string strSQL = "xxx.GetFirstWordByLength";
            using (MySqlConnection conn = new MySqlConnection(CDB_MyLister.csb.ToString()))
            {
               conn.Open();
               MySqlCommand cmd = new MySqlCommand(strSQL, conn);
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.Parameters.Add("@len", MySqlDbType.Int16).Value = m_intLen;
               cmd.Parameters["@len"].Direction = ParameterDirection.Input;
               cmd.Parameters.Add("@lastword", MySqlDbType.VarChar).Direction = ParameterDirection.Output;
               cmd.ExecuteNonQuery();

               //Console.WriteLine(cmd.Parameters["@lastword"].Value);
               master = cmd.Parameters["@lastword"].Value;
               conn.Close();
            }
         }
         catch (Exception exc)
         {
            blnRetVal = false;
            strError = exc.Message;
         }

         return blnRetVal;
      }

The magic being removing the parameters and any other decorators from the name of the stored procedure.
Only use the procedure name, but the addition of the CommandType.StoredProcedure; is essential.