I've asked the question also here on stack overflow
When using nested data structures in Databricks (e.g. `ARRAY` or `ROW`) using JDBC, it appears that the results can be fetched as JSON `String` values, e.g.:
try (Statement s = connection.createStatement();
ResultSet rs = s.executeQuery(
"""
select array(1, 2), array('a', 'b'), array(true, false)
"""
)) {
while (rs.next()) {
System.out.println(rs.getString(1));
System.out.println(rs.getObject(1).getClass());
System.out.println(rs.getString(2));
System.out.println(rs.getObject(2).getClass());
System.out.println(rs.getString(3));
System.out.println(rs.getObject(3).getClass());
}
}
Produces valid JSON strings (at least, that's what they look like):
[1,2]
class java.lang.String
["a","b"]
class java.lang.String
[true,false]
class java.lang.String
Calling `ResultSet::getArray` isn't implemented:
java.sql.SQLDataException: [Databricks][JDBC](10400) Invalid type for data - column: 3, type: Array.
at com.databricks.client.exceptions.ExceptionConverter.toSQLException(Unknown Source)
at com.databricks.client.jdbc.common.SForwardResultSet.getArray(Unknown Source)
at org.jooq.testscripts.JDBC.main(JDBC.java:44)
This seems fine until the array contains `"` characters, e.g. this query `select array('"')` produces this invalid value:
["""]
Instead of:
["\""]
Likewise, when reading `select array(date '2000-01-01')`, I'm getting:
[2000-01-01]
Instead of e.g.:
["2000-01-01"]
Is this a known bug / limitation in the 2.7.1 JDBC driver version that I'm using? Is there a known workaround?
