[jira] [Created] (CALCITE-563) Calcite JDBCAdaptor Fail to execute PreparedStatement with bind variable.

["Ng Jiunn Jye (JIRA)" <ji...@apache.org>]

Ng Jiunn Jye created CALCITE-563:
------------------------------------

             Summary: Calcite JDBCAdaptor Fail to execute PreparedStatement with bind variable. 
                 Key: CALCITE-563
                 URL: https://issues.apache.org/jira/browse/CALCITE-563
             Project: Calcite
          Issue Type: Bug
    Affects Versions: 1.0.0-incubating
         Environment: Any
            Reporter: Ng Jiunn Jye
            Assignee: Julian Hyde


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();
		}
	}

}
-----------------------------------------------






--
This message was sent by Atlassian JIRA
(v6.3.4#6332)