Hello, I have a query like this on my program
SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID = '1' AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID = '1' AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = '1' AND STATUS = 'N' ) AS TEMP And it runs smoothly when I tried it using SquirrelSQL with Phoenix JDBC Thin Client. However, I try the query in Java with a prepared statement, the query changed like below: SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID = ? AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID = ? AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = ? AND STATUS = 'N' ) AS TEMP And as the ID is a string, I set the parameter with code like this PreparedStatement secondStatement = super.getConnection().prepareStatement( sqlQuery); secondStatement.setString(1, ID); secondStatement.setString(2, ID); secondStatement.setString(3, ID); ResultSet secondResultset = secondStatement.executeQuery(); I expect it should be working, but I got this error rg.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT COUNT(*) AS COUNTER from ( SELECT FULLNAME FROM TABLE_A WHERE ID = ? AND STATUS = 'N' UNION ALL SELECT FULLNAME FROM TABLE_B WHERE ID = ? AND STATUS = 'Y' UNION ALL SELECT FULLNAME FROM TABLE_C WHERE ID = ? AND STATUS = 'N' ) AS TEMP at org.apache.calcite.avatica.Helper.createException(Helper.java:53) at org.apache.calcite.avatica.Helper.createException(Helper.java:41) at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:314) at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:153) at com.btpn.jdbc.interfacing.interfaces.impl.VerifyCIF.query(VerifyCIF.java:49) at com.btpn.jdbc.interfacing.Fetcher.call(Fetcher.java:39) at MainPhoenix.main(MainPhoenix.java:69) java.lang.RuntimeException: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound at org.apache.calcite.avatica.jdbc.JdbcMeta.propagate(JdbcMeta.java:651) at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:677) at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:177) at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1113) at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1091) at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:102) at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:38) at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:68) at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52) at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97) at org.eclipse.jetty.server.Server.handle(Server.java:497) at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:310) at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:245) at org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:540) at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635) at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: ERROR 2004 (INT05): Parameter value unbound. Parameter at index 1 is unbound at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:422) at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:145) at org.apache.phoenix.jdbc.PhoenixParameterMetaData.getParam(PhoenixParameterMetaData.java:88) at org.apache.phoenix.jdbc.PhoenixParameterMetaData.isSigned(PhoenixParameterMetaData.java:138) at org.apache.calcite.avatica.jdbc.JdbcMeta.parameters(JdbcMeta.java:231) at org.apache.calcite.avatica.jdbc.JdbcMeta.signature(JdbcMeta.java:244) at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:669) ... 15 more How do I fix this? Is there anything wrong? I am using Apache Phoenix 4.7.0 and Hbase 1.1 Thank you and regards, Ganda -- Ganda Manurung
