[
https://issues.apache.org/jira/browse/CALCITE-563?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15401074#comment-15401074
]
YeongWei commented on CALCITE-563:
----------------------------------
Hi [~julianhyde],
This JIRA is reported for executing Prepared Statements with Bind Variables
over Jdbc Adapters. I have proceeded to add more test cases to find out more
about the problem. However I am surprised by the fact that it is also happening
for the ReflectiveSchema(s).
The additional test cases are committed to my GitHub at
https://github.com/apache/calcite/compare/master...yeongwei:CALCITE-565-JDBC-adapter-fails-to-execute-a-prepared-statement-with-a-bind-variable?diff=split&name=CALCITE-565-JDBC-adapter-fails-to-execute-a-prepared-statement-with-a-bind-variable#diff-f985d2e1a0804e0c7250da49b53b9d85
with split view and hyperlink ready. By clicking the link it should bring you
to the test file with relevant test into view point.
The 2 test requires you attention are
*RelToSqlConverterTest#testParsePrepareStatementWithJdbcAdapter* and
*RelToSqlConverterTest#testParsePrepareStatementWithoutJdbcAdapter*.
Summary about the new test(s) added,
1. Created 2 planners. One based on the SchemaSpec of *JDBC_FOODMART* and the
other based on *HR*
2. RelToSqlConverterTest#testParsePrepareStatementWithJdbcAdapter is fired
based on JDBC_FOODMART
3. RelToSqlConverterTest#testParsePrepareStatementWithoutJdbcAdapter is fired
based on HR
4. Both attempts to parse query with predicate that has Bind Variable of int
type. E.g. _where \"department_id\" = ?_ and _where \"deptno\" = ?_
Both test cases threw the exception as follows,
{quote}
Caused by: java.lang.ClassCastException: org.apache.calcite.rex.RexDynamicParam
cannot be cast to org.apache.calcite.rex.RexCall
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:537)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:606)
at
org.apache.calcite.rel.rel2sql.SqlImplementor$Context.toSql(SqlImplementor.java:539)
at
org.apache.calcite.rel.rel2sql.RelToSqlConverter.visit(RelToSqlConverter.java:120)
... 40 more
{quote}
Looking at the Type Hierarchy of RexDynamicParam, it does not have any super
class related to RexCall, which is causing the exception above.
Nevertheless I can see that the predicated is being modelled into the notation
of *=($0, ?0)* which the RexDynamicParam object instance is used to represent
the *?0* component.
I suspect that we might need to enhance the SqlKind support during the
execution at *SqlImplementor#toSql*
Look forward to hear from you.
Thanks!
> 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
> Affects Versions: 1.0.0-incubating
> Environment: Any
> Reporter: Ng Jiunn Jye
> Assignee: Julian Hyde
> 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:
> 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)
