cancel
Showing results for 
Search instead for 
Did you mean: 
Get Started Discussions
Start your journey with Databricks by joining discussions on getting started guides, tutorials, and introductory topics. Connect with beginners and experts alike to kickstart your Databricks experience.
cancel
Showing results for 
Search instead for 
Did you mean: 

jdbc errors when parameter is a boolean

JVesely
New Contributor III

I'm trying to query a table from Java code. The query works when I use a databricks notebook / query editor directly in Databricks. 

However, when using Jdbc with Spring, I get following stacktrace. 

 
org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT rowkey, sendReminder FROM ANIMAL WHERE sendReminder = ?]; SQL state [HY000]; error code [500352]; [Databricks][JDBCDriver](500352) Error getting the parameter data type: HIVE_PARAMETER_QUERY_DATA_TYPE_ERR_NON_SUPPORT_DATA_TYPE; nested exception is java.sql.SQLException: [Databricks][JDBCDriver](500352) Error getting the parameter data type: HIVE_PARAMETER_QUERY_DATA_TYPE_ERR_NON_SUPPORT_DATA_TYPE
 
at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1542)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:667)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:713)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:744)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:757)
at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:810)
 
I tried to search for HY000, 500352 and HIVE_PARAMETER_QUERY_DATA_TYPE_ERR_NON_SUPPORT_DATA_TYPE but didn't get any results that seemed helpful.
 
Using other datatypes as parameter than boolean works.
 
Did you face this issue, is it a known thing and most importantly, is there a workaround?
 
Thank you
 
 

 

1 ACCEPTED SOLUTION

Accepted Solutions

JVesely
New Contributor III

As I see it, there's two things:

  • jdbcTemplate converts boolean to bit. This is according to JDBC specs (this is a "spring-jdbc" thing and according to documentation; the jdbcTemplate.queryForList makes the best possible guess of the desired type).
  • Databricks can't handle a boolean marked as bit

Either using PreparedStatement or queryForList(sql, args, argTypes) works.

View solution in original post

2 REPLIES 2

JVesely
New Contributor III

Thank you very much for your answer and suggestions. 

The data type is Boolean - unless I'm mistaken, it is supported because I did the 2 following experiments:

1. Used queryForList with an extra argument, manually specifying type of arguments, where argTypes uses java.sql.Types.BOOLEAN (num value == 16)

jdbcTemplate.queryForList(sql, args.toArray(), argTypes); 

2. Using a prepared statement

ResultSet resultSet = null;
try (PreparedStatement preparedStatement = dataSource.getConnection().prepareStatement(sql)) {
preparedStatement.setBoolean(1, (Boolean) args.get(0));

Both work fine. I did a bit of deep debugging into JdbcTemplate source code and it seems like when using only queryForList(sql, args), the argument types in case of boolean don't get converted correctly. 

While we should have java.sql.Types.BOOLEAN (int value 16), we get java.sql.Types.BIT (int value -7)

 

 

 

 

 

JVesely
New Contributor III

As I see it, there's two things:

  • jdbcTemplate converts boolean to bit. This is according to JDBC specs (this is a "spring-jdbc" thing and according to documentation; the jdbcTemplate.queryForList makes the best possible guess of the desired type).
  • Databricks can't handle a boolean marked as bit

Either using PreparedStatement or queryForList(sql, args, argTypes) works.

Join Us as a Local Community Builder!

Passionate about hosting events and connecting people? Help us grow a vibrant local community—sign up today to get started!

Sign Up Now