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