[ https://issues.apache.org/jira/browse/CALCITE-563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16678384#comment-16678384 ]
Piotr Bojko commented on CALCITE-563: ------------------------------------- I've taken the branch from [~vladimirsitnikov]. As for now I've managed to implement something like this (result of conversion): {code:java} org.apache.calcite.DataContext root; public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) { root = root0; final org.apache.calcite.linq4j.function.Function1 rowBuilderFactory = new org.apache.calcite.linq4j.function.Function1() { public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) { return new org.apache.calcite.linq4j.function.Function0() { public Object apply() { try { final Object[] values = new Object[2]; values[0] = resultSet.getInt(1); if (resultSet.wasNull()) { values[0] = null; } values[1] = resultSet.getDouble(2); if (resultSet.wasNull()) { values[1] = null; } return values; } catch (java.sql.SQLException e) { throw new RuntimeException( e); } } } ; } public Object apply(final Object resultSet) { return apply( (java.sql.ResultSet) resultSet); } } ; final org.apache.calcite.linq4j.function.Function1 preparedStatementConsumer = new org.apache.calcite.linq4j.function.Function1() { public java.sql.PreparedStatement apply(final java.sql.PreparedStatement preparedStatement) { org.apache.calcite.adapter.jdbc.JdbcPreparedStatementUtils.fillDynamicParameters(new Integer[] { 0}, root); return preparedStatement; } public Object apply(final Object preparedStatement) { return apply( (java.sql.PreparedStatement) preparedStatement); } } ; 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 \"T3\"\nWHERE \"VALS\" =?", rowBuilderFactory); return enumerable; } public Class getElementType() { return java.lang.Object[].class; } {code} > JDBC adapter fails to execute a prepared statement with a bind variable > ----------------------------------------------------------------------- > > Key: CALCITE-563 > URL: https://issues.apache.org/jira/browse/CALCITE-563 > Project: Calcite > Issue Type: Bug > Components: jdbc-adapter > Affects Versions: 1.0.0-incubating > Environment: Any > Reporter: Ng Jiunn Jye > Assignee: Julian Hyde > Priority: Major > Labels: newbie > > 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: > {noformat} > 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) > {noformat} > Test Code: > {code:java} > 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(); > } > } > } > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005)