jdbc errors when parameter is a boolean

[JVesely]

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

[Kaniz_Fatma]

Hi @JVesely., Could you check the data type of the sendReminder column in your Databricks table? Could you please ensure that the data type is one that is supported by the JDBC driver? Common data types that work well with JDBC include INTEGER, VARCHAR, TIMESTAMP, etc. If the data type is not supported, you may need to modify the table schema in Databricks to use a compatible data type.

[JVesely]

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 · Accepted solution]

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.