List:MySQL ODBC« Previous MessageNext Message »
From:Oscar Claros Date:March 14 2006 9:44pm
Subject:Stored Procedures & odbc
View as plain text  
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;
            }
        }

_________________________________________________________________
Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

Thread
Stored Procedures & odbcOscar Claros14 Mar
  • Re: Stored Procedures & odbcMartin J. Evans14 Mar