Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
New Contributor

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

 

1 ACCEPTED SOLUTION

avatar
New Contributor

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.

View solution in original post

3 REPLIES 3

avatar
Champion

@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

 

 

avatar
New Contributor

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.

 

 

avatar
New Contributor

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.