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

[#1]

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...

[#2]

@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

[#3]

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.

[#4 · Accepted solution]

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-retur...

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