[
https://issues.apache.org/jira/browse/CALCITE-596?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Julian Hyde updated CALCITE-596:
--------------------------------
Fix Version/s: next
> JDBCAdaptor: Resultset.get does not handle Null value
> -----------------------------------------------------
>
> Key: CALCITE-596
> URL: https://issues.apache.org/jira/browse/CALCITE-596
> Project: Calcite
> Issue Type: Bug
> Affects Versions: 1.0.0-incubating
> Environment: Tested on remote database hsql and h2
> Reporter: Ng Jiunn Jye
> Assignee: Julian Hyde
> Fix For: next
>
> Attachments: [CALCITE-596]-Description.patch
>
>
> Description:
> When reading null data(ie integer field) using getObject from remote database
> using JDBC Adaptor, Calcite return value as 0.
> Cause:
> JDBCAdaptor use getInt (and other getPrimitive method) to retrieve data from
> remote DB (see generated code line 11,12). According to JDBC Spec, getInt
> will return 0 for null data. Thus the original null value got lost in the
> transition.
> On Client side it fail to get null value using getObject.
> Sample Test Code:
> package org.apache.calcite.jdbc;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> import java.util.Properties;
> import org.hsqldb.jdbcDriver;
> public class TestJdbcAdaptorNullValue {
> public static void main(String[] args) {
> try {
> String hsqldbMemUrl = "jdbc:hsqldb:mem:.";
> Connection baseConnection =
> DriverManager.getConnection(hsqldbMemUrl);
> Statement baseStmt =
> baseConnection.createStatement();
> baseStmt.execute("CREATE TABLE T1 (\n"
> + "ID INTEGER,\n"
> + "VALS INTEGER)");
> baseStmt.execute("INSERT INTO T1 VALUES (1, 1)");
> baseStmt.execute("INSERT INTO T1 VALUES (2, null)");
> baseStmt.close();
> baseConnection.commit();
> Properties info = new Properties();
> info.put("model",
> "inline:"
> + "{\n"
> + " version: '1.0',\n"
> + " defaultSchema: 'BASEJDBC',\n"
> + " schemas: [\n"
> + " {\n"
> + " type: 'jdbc',\n"
> + " name: 'BASEJDBC',\n"
> + " jdbcDriver: '" +
> jdbcDriver.class.getName() + "',\n"
> + " jdbcUrl: '" + hsqldbMemUrl +
> "',\n"
> + " jdbcCatalog: null,\n"
> + " jdbcSchema: null\n"
> + " }\n"
> + " ]\n"
> + "}");
> Connection calciteConnection =
> DriverManager.getConnection(
> "jdbc:calcite:", info);
> ResultSet baseRs =
> baseConnection.prepareStatement("select * from t1").executeQuery();
> while (baseRs.next()){
> System.out.println ("ID:" + (Integer)
> baseRs.getObject("ID"));
> System.out.println ("VALS:" + (Integer)
> baseRs.getObject("VALS"));
> }
> baseRs.close();
> ResultSet rs =
> calciteConnection.prepareStatement("select * from t1").executeQuery();
> while (rs.next()){
> System.out.println ("ID:" + (Integer)
> rs.getObject("ID"));
> System.out.println ("VALS:" + (Integer)
> rs.getObject("VALS"));
> }
> rs.close();
> calciteConnection.close();
> }catch (Exception e){
> e.printStackTrace();
> }
> }
> }
> Generated Code:
> /* 1 */ org.apache.calcite.DataContext root;
> /* 2 */
> /* 3 */ public org.apache.calcite.linq4j.Enumerable bind(final
> org.apache.calcite.DataContext root0) {
> /* 4 */ root = root0;
> /* 5 */ final org.apache.calcite.linq4j.function.Function1
> rowBuilderFactory = new org.apache.calcite.linq4j.function.Function1() {
> /* 6 */ public org.apache.calcite.linq4j.function.Function0 apply(final
> java.sql.ResultSet resultSet) {
> /* 7 */ return new org.apache.calcite.linq4j.function.Function0() {
> /* 8 */ public Object apply() {
> /* 9 */ try {
> /* 10 */ final Object[] values = new Object[2];
> /* 11 */ values[0] = resultSet.getInt(1);
> /* 12 */ values[1] = resultSet.getInt(2);
> /* 13 */ return values;
> /* 14 */ } catch (java.sql.SQLException e) {
> /* 15 */ throw new RuntimeException(
> /* 16 */ e);
> /* 17 */ }
> /* 18 */ }
> /* 19 */ }
> /* 20 */ ;
> /* 21 */ }
> /* 22 */ public Object apply(final Object resultSet) {
> /* 23 */ return apply(
> /* 24 */ (java.sql.ResultSet) resultSet);
> /* 25 */ }
> /* 26 */ }
> /* 27 */ ;
> /* 28 */ final org.apache.calcite.linq4j.Enumerable enumerable =
> org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema)
>
> root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(),
> "SELECT *\nFROM \"T1\"", rowBuilderFactory);
> /* 29 */ return enumerable;
> /* 30 */ }
> /* 31 */
> /* 32 */
> /* 33 */ public java.lang.reflect.Type getElementType() {
> /* 34 */ return java.lang.Object[].class;
> /* 35 */ }
> /* 36 */
> /* 37 */
>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
