I'm new and I hope I post this clearly and in the correct location.
I am in the process of converting numerous SQL calls through ODBC from concatenated queries to parameterized queries.
The majority of these programs are written in C#. We connect using ODBC Driver CA-DATACOM/DB.
After a day's worth of searching the internet, I cannot determine how to correctly pass a value to be compared as a Hex value in a parameterized query.
Working concatenated example:
try
{
string myCustomerHexId = "77";
string myCustomerName = null;
using (OdbcConnection odbcConnect = new OdbcConnection(myConnectionString))
{
odbcConnect.Open();
using (OdbcCommand odbcCommand = new OdbcCommand())
{
odbcCommand.Connection = odbcConnect;
odbcCommand.CommandText = "SELECT customerName FROM customers WHERE customerHexId = X'" + myCustomerHexId + "';";
using (OdbcDataReader odbcReader = odbcCommand.ExecuteReader())
{
if ((odbcReader.HasRows) && (odbcReader != null))
{
while (odbcReader.Read())
{
myCustomerName = odbcReader["customerName"].ToString();
}
}
}
}
}
}
catch (Exception oe)
{
Global.logging.writeToLog(oe.Message);
}
When I change to the parameterized query, I am no longer finding the customer record.
try
{
string myCustomerHexId = "77";
string myCustomerName = null;
using (OdbcConnection odbcConnect = new OdbcConnection(myConnectionString))
{
odbcConnect.Open();
using (OdbcCommand odbcCommand = new OdbcCommand())
{
odbcCommand.Connection = odbcConnect;
odbcCommand.CommandText = "SELECT customerName FROM customers WHERE customerHexId = ?;";
odbcCommand.Parameters.AddWithValue("hexParam", myCustomerHexId);
using (OdbcDataReader odbcReader = odbcCommand.ExecuteReader())
{
if ((odbcReader.HasRows) && (odbcReader != null))
{
while (odbcReader.Read())
{
myCustomerName = odbcReader["customerName"].ToString();
}
}
}
}
}
}
catch (Exception oe)
{
Global.logging.writeToLog(oe.Message);
}
I have also tried:
odbcCommand.CommandText = "SELECT customerName FROM customers WHERE customerHexId = X?;";
odbcCommand.Parameters.AddWithValue("hexParam", myCustomerHexId);
as well as:
odbcCommand.CommandText = "SELECT customerName FROM customers WHERE customerHexId = hex(?);";
odbcCommand.Parameters.AddWithValue("hexParam", myCustomerHexId);
and:
odbcCommand.CommandText = "SELECT customerName FROM customers WHERE customerHexId = ?;";
odbcCommand.Parameters.AddWithValue("hexParam", Convert.ToInt32(customerHexId).ToString("X"));
I should note that non-hex parameterized queries work fine. I can even use parameters for other values in the predicate and concatenate my hex value, but surely there is a way to pass this value as a parameter and have this specific brand of SQL interpret it correctly.
I appreciate any assistance. Please let me know if I need to supply more information. Thank you.
