Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-563

JDBC adapter fails to execute a prepared statement with a bind variable

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.0.0-incubating
    • Fix Version/s: 1.18.0
    • Component/s: jdbc-adapter
    • Labels:
    • Environment:

      Any

      Description

      Description:
      Calcite fail to execute PreparedStatement bind variable to external JDBC datasource.

      Problem:
      RexCall of kind DYNAMIC_PARAM is not supported in JdbcAdaptor.

      Error StackTrace:

      java.sql.SQLException: Error while preparing statement [SELECT ID, VALS FROM T1 where id = ?]
      	at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:161)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:1)
      	at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:121)
      	at org.apache.calcite.jdbc.TestPrepareStatementBindVar.main(TestPrepareStatementBindVar.java:48)
      Caused by: java.lang.ClassCastException: org.apache.calcite.rex.RexDynamicParam incompatible with org.apache.calcite.rex.RexCall
      	at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:210)
      	at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:268)
      	at org.apache.calcite.adapter.jdbc.JdbcImplementor$Context.toSql(JdbcImplementor.java:212)
      	at org.apache.calcite.adapter.jdbc.JdbcRules$JdbcFilter.implement(JdbcRules.java:538)
      	at org.apache.calcite.adapter.jdbc.JdbcImplementor.visitChild(JdbcImplementor.java:118)
      	at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:286)
      	at org.apache.calcite.adapter.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:89)
      	at org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:99)
      	at org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:867)
      	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:298)
      	at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:192)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:486)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:383)
      	at org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:352)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:174)
      	at org.apache.calcite.jdbc.CalciteConnectionImpl.prepareStatement(CalciteConnectionImpl.java:157)
      

      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 TestPrepareStatementBindVar {
      
      	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.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);
      
      			    PreparedStatement calcitePS = calciteConnection.prepareStatement("SELECT ID, VALS FROM T1 where id = ?");
      			    calcitePS.setInt(1, 1);
      			    ResultSet rs = calcitePS.executeQuery();
      			    rs.close();
      			    calciteConnection.close();
      
      		}catch (Exception e){
      			e.printStackTrace();
      		}
      	}
      
      }
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                julianhyde Julian Hyde
                Reporter:
                jiunnjye Ng Jiunn Jye
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: