ODBC Parameterized-Query with Hex Value



IBM's flagship relational database management system

ODBC Parameterized-Query with Hex Value

Postby compbrat75 » Wed Jul 20, 2016 4:53 am

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.
compbrat75
 
Posts: 2
Joined: Wed Jul 20, 2016 4:02 am
Has thanked: 0 time
Been thanked: 0 time

Re: ODBC Parameterized-Query with Hex Value

Postby prino » Wed Jul 20, 2016 6:21 am

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
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 635
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: ODBC Parameterized-Query with Hex Value

Postby Robert Sample » Wed Jul 20, 2016 6:34 am

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.
Robert Sample
Global moderator
 
Posts: 3719
Joined: Sat Dec 19, 2009 8:32 pm
Location: Dubuque, Iowa, USA
Has thanked: 1 time
Been thanked: 279 times

Re: ODBC Parameterized-Query with Hex Value

Postby compbrat75 » Wed Jul 20, 2016 9:04 am

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.
compbrat75
 
Posts: 2
Joined: Wed Jul 20, 2016 4:02 am
Has thanked: 0 time
Been thanked: 0 time


Return to DB2