 | | | Stored Procedures & odbc | Stored Procedures & odbc 2006-03-14 - By Oscar Claros
Back
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("@(protected)",
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 (See http://qld-5.ora-code.com).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("@(protected)",
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 (See http://qld-5.ora-code.com).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
--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=mysql@(protected)
|
|
|