question Re: ODBC driver for Hive - OdbcCommand not working if parameter is Null in Archives of Support Questions (Read Only)

ODBC driver for Hive - OdbcCommand not working if parameter is Null

jazzman71 — Fri, 16 Sep 2022 11:54:58 GMT

Hello all,

I wrote the simple C# sample below that uses OdbcCommand and OdbcParameter :

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

string connectionString = "DRIVER={Cloudera ODBC Driver for Apache Hive};HOST=myserver;PORT=10000;DB=default;UID=me;PWD=mypwd";

string queryString = "select * from my_table where foo=? AND bar=?";

using (OdbcConnection connection =

new OdbcConnection(connectionString))

{

OdbcCommand command = new OdbcCommand(queryString, connection);

// Open the connection and execute the select command.

try

{

connection.Open();

OdbcParameter param1 = new OdbcParameter("@p1", "hadoop");

OdbcParameter param2 = new OdbcParameter("@p2", DBNull.Value);

command.Parameters.Add(param1);

command.Parameters.Add(param2);

OdbcDataReader reader = command.ExecuteReader();

if (reader.HasRows) {

Console.WriteLine("Hurray");

}

catch (Exception ex)

{

Console.WriteLine(ex.Message);

}

// The connection is automatically closed when the

// code exits the using block.

}

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Although I am expecting to find some results, the query does not return any rows. Obviously, if I change the bar=? parameter by "bar is null", I get the results that I am expecting.

It looks like using an OdbcParameter with a DBNull.Value value is not working with Cloudera ODBC...

Re: ODBC driver for Hive - OdbcCommand not working if parameter is Null

saranvisa — Wed, 12 Jul 2017 19:10:07 GMT

@jazzman71

I think there could be two possibilities for this issue

Posibility 1. As you said, this might be an issue with odbc command with parameter

Posibility 2. Hive is not recognizing the argument as '?' from your code. May be you can try with different argument options as mentioned in this link

https://stackoverflow.com/questions/12464636/how-to-set-variables-in-hive-scripts

Re: ODBC driver for Hive - OdbcCommand not working if parameter is Null

jazzman71 — Mon, 17 Jul 2017 08:03:55 GMT

Thank you for your answer, but I do not see the relationship between ODBC and Hive scripts...

It would be interesting to have the feedback of someone who did use the Cloudera ODBC Driver with OdbcParameter and DBNull.

Re: ODBC driver for Hive - OdbcCommand not working if parameter is Null

jazzman71 — Thu, 03 Aug 2017 16:58:56 GMT

I realized today, after browsing many google results, that it is illicit to use a "null" value as a parameter of a where clause.

Because null is considered to be unknown, two null values compared to each other are not considered to be equal. In expressions using arithmetic operators, if any of the operands is null, the result is null as well.

Details can be found here : https://stackoverflow.com/questions/36992184/sqlcommand-createparameter-value-dbnull-value-not-returning-results

So it was obvious that my simple C# sample would not work and it had actually nothing to do with the Cloudera ODBC driver.