How to handle SQL parameters with Apache Calcite Planner (Facade Interface)？

Question

I need some help with processing queries using Apache Calcite, I am currently using Planner to process my queries.

My code looks like this:

// custom config
FrameworkConfig frameworkConfig = Frameworks.newConfigBuilder()
        .parserConfig(parserConfig)
        .defaultSchema(rootSchema)
        .sqlToRelConverterConfig(convertConfig)
        .operatorTable(operatorTable)
        .build();

Planner planner = Frameworks.getPlanner(frameworkConfig);

String sql = "Select id, name from t where id in (?, ?)";
SqlNode parsedSqlNode = planner.parse(sql);
SqlNode validatedSqlNode = planner.validate(parsedSqlNode);
RelRoot relRoot = planner.rel(validatedSqlNode);
PreparedStatement preparedStatement = RelRunners.run(relRoot.rel);
preparedStatement.setLong(1, 11111);
preparedStatement.setLong(2, 22222);

ResultSet resultSet = preparedStatement.executeQuery();
// ....

It works fine without SQL parameters, but there are some queries with SQL parameters.

While using SQL parameter and setting the parameter(preparedStatement.setLong), I got the following error message:

Caused by: java.sql.SQLException: parameter ordinal 1 out of range
    at org.apache.calcite.avatica.Helper.createException(Helper.java:60)
    at org.apache.calcite.avatica.AvaticaPreparedStatement.getParameter(AvaticaPreparedStatement.java:427)
    at org.apache.calcite.avatica.AvaticaPreparedStatement.getSite(AvaticaPreparedStatement.java:434)
    at org.apache.calcite.avatica.AvaticaPreparedStatement.setLong(AvaticaPreparedStatement.java:178)

SQL parameters are obviously not being handled correctly. What is the proper way to define and set SQL parameters when using Planner in a case like this?

How to handle SQL parameters with Apache Calcite Planner (Facade Interface)？

0 Answers0