jdbc errors when parameter is a boolean

JVesely
New Contributor III

‎07-12-2024 07:28 AM

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
 
 

 

0 Kudos

JVesely
New Contributor III
In response to Retired_mod

‎07-16-2024 02:32 AM

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)

 

 

 

 

 

Preview file
115 KB

JVesely
New Contributor III

‎07-16-2024 06:28 AM

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