topic Re: jdbc errors when parameter is a boolean in Get Started Discussions

jdbc errors when parameter is a boolean

JVesely — Fri, 12 Jul 2024 14:28:43 GMT

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

Re: jdbc errors when parameter is a boolean

JVesely — Tue, 16 Jul 2024 09:32:56 GMT

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)

Re: jdbc errors when parameter is a boolean

JVesely — Tue, 16 Jul 2024 13:28:14 GMT

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.