From: Martin J. Evans
Date: March 14 2006 10:06pm
Subject: Re: Stored Procedures & odbc
List-Archive: http://lists.mysql.com/myodbc/10650
Message-Id: <44173E64.6000202@easysoft.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Oscar,

Your procedure does not return anything. Instead it generates
a result-set. You probably want to look into "select into", declare
a variable and do select into variable with a return variable.

Martin

Oscar Claros wrote:
> I am new to using mysql.  I like it so far, but I have been having 
> issues when trying to use C#, odbc.net and mysql.
> 
> Here is the stored procedure that I wrote in sqlyog:
> 
> DELIMITER $$;
> 
> DROP PROCEDURE IF EXISTS 
> `webcustomers`.`P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME`$$
> 
> CREATE PROCEDURE `P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME`(IN cLastName 
> varchar(50))
> BEGIN
> SELECT * FROM customer WHERE lastName = cLastName;
> END$$
> 
> DELIMITER ;$$
> 
> Here the first C# code snippet that does not work:
> public DataTable ExecuteDataTableTest()
>        {
>            string strLastName = "Simpson";
>            string strSP = "{ ? = CALL 
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME (?)}";
>            string strConn = 
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
>            DataTable dt = new DataTable();
>            OdbcConnection myOdbcConn = new OdbcConnection(strConn);
>            OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
> 
>            try
>            {
>                myOdbcDa = new OdbcDataAdapter(strSP, myOdbcConn);
>                myOdbcDa.SelectCommand.Parameters.Add("@cLastName", 
> OdbcType.VarChar, 50);
>                myOdbcDa.SelectCommand.Parameters[0].Value = strLastName;
>                myOdbcDa.SelectCommand.CommandType = 
> CommandType.StoredProcedure;
> 
>                myOdbcDa.Fill(dt);
>            }
>            catch (Exception ex)
>            {
>                CreateLog(ex.Message);
>            }
>            finally
>            {
>                myOdbcDa.Dispose();
>                myOdbcConn.Close();
>                myOdbcConn.Dispose();
>            }
> 
>            return dt;
> 
>        }
> 
> Here is the error code that I get:
> ERROR [07002] [MySQL][ODBC 3.51 
> Driver][mysqld-5.0.18-nt]SQLBindParameter not used for all parameters
> 
> So I tried again, but I could not figure out what the type of 
> "RETURN_VALUE" was.  I did some searching on the web and it looks like 
> the type is an int.
> 
> public DataTable ExecuteDataTableTest()
>        {
>            string strLastName = "Simpson";
>            string strSP = "{ ? = CALL 
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME (?)}";
>            string strConn = 
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
> 
>            DataTable dt = new DataTable();
>            OdbcConnection myOdbcConn = new OdbcConnection(strConn);
>            OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
> 
>            try
>            {
>                myOdbcDa = new OdbcDataAdapter(strSP, myOdbcConn);
>                myOdbcDa.SelectCommand.Parameters.Add("RETURN_TYPE", 
> OdbcType.Int, 2);
>                myOdbcDa.SelectCommand.Parameters.Add("@cLastName", 
> OdbcType.VarChar, 50);
>                myOdbcDa.SelectCommand.Parameters[1].Value = strLastName;
>                myOdbcDa.SelectCommand.CommandType = 
> CommandType.StoredProcedure;
> 
>                myOdbcDa.Fill(dt);
>            }
>            catch (Exception ex)
>            {
>                CreateLog(ex.Message);
>            }
>            finally
>            {
>                myOdbcDa.Dispose();
>                myOdbcConn.Close();
>                myOdbcConn.Dispose();
>            }
> 
>            return dt;
> 
>        }
> 
> Here is the error that was return:
> 
> ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-5.0.18-nt]You have an 
> error in your SQL syntax; check the manual that corresponds to your 
> MySQL server version for the right syntax to use near '{ 0 = CALL 
> P_CUSTOMER_GET_CUSTOMER_REC_BY_NAME ('Simpson')}' at line 1
> 
> 
> Here is the work around that I did:
> 
> public DataTable ExecuteDataTable()
>        {
>            string strLastName = "Simpson";
>            string strSQL = "SELECT * FROM customer WHERE lastName =" + 
> strLastName;
>            string strConn = 
> ConfigurationManager.ConnectionStrings["MySQL"].ToString();
>            DataTable dt = new DataTable();
>            OdbcConnection myOdbcConn = new OdbcConnection(strConn);
>            OdbcDataAdapter myOdbcDa = new OdbcDataAdapter();
> 
>            try
>            {
>                myOdbcConn.Open();
>                try
>                {
> 
>                    myOdbcDa = new OdbcDataAdapter(strSQL, myOdbcConn);
>                    myOdbcDa.Fill(dt);
>                }
>                catch (Exception ex)
>                {
>                    CreateLog(ex.Message);
>                }
>                finally
>                {
>                    myOdbcDa.Dispose();
>                }
>            }
> 
>            catch (OdbcException odbcEx)
>            {
>                CreateLog(odbcEx.Message);
> 
>            }
>            finally
>            {
>                myOdbcConn.Close();
>                myOdbcConn.Dispose();
>            }
>            return dt;
>        }
> public Customer[] GetACustomer()
>        {
>                        DALHelper myDALHelper = new DALHelper();
>            DataTable dtCur = new DataTable();
>            dtCur = myDALHelper.ExecuteDataTable(strSQL);
> 
>            if (dtCur != null)
>            {
>                if (dtCur.Rows.Count != 0)
>                {
>                    Customer[] cust = new Customer[dtCur.Rows.Count];
>                    for (int i = 0; i <= dtCur.Rows.Count - 1; i++)
>                    {
>                        {
>                            cust[i] = new Customer();
>                            cust[i] = 
> ConvertDbDataToCustomerObject(dtCur.Rows[i]);
>                        }
>                    }
>                    return cust;
>                }
>                else
>                {
>                    return null;
>                }
>            }
>            else
>            {
>                return null;
>            }
>        }
> 
> _________________________________________________________________
> Is your PC infected? Get a FREE online computer virus scan from McAfeeŽ 
> Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
> 
>