Page 1 of 1

ODBC Parameterized-Query with Hex Value

PostPosted: Wed Jul 20, 2016 4:53 am
by compbrat75
Hello,
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.

Re: ODBC Parameterized-Query with Hex Value

PostPosted: Wed Jul 20, 2016 6:21 am
by prino
Stop wasting our time here, ODBC and C# don't have anything to do with IBM z/OS systems.

Did you see where you posted?

A Help & Support Forum for Mainframe Beginners

Re: ODBC Parameterized-Query with Hex Value

PostPosted: Wed Jul 20, 2016 6:34 am
by Robert Sample
Contact the vendor -- they are the experts in the product. There is not a lot of use of C# on mainframes, so you won't find very many answers here and they'll probably take a while to show up.

Re: ODBC Parameterized-Query with Hex Value

PostPosted: Wed Jul 20, 2016 9:04 am
by compbrat75
Thank you for the suggestion to contact the vendor. The forum post was a step before contacting CA through our support contract.

The C# is being used to communicate with the Mainframe. There are not a lot of places on the internet where programmers that work with IDEAL, JCL, Cobal, ASM, and non-mainframe languages like C#, VB, and PHP can go to find help about getting them all to play nice and talk to each other. I posted here because I searched the forum first and found other ODBC and SQL questions. It's unfortunate that a forum advertised for beginners and students appears to be unwelcoming to them.