preparedStatement.getString() method not working for nested sql query

Question

I am trying to query my HBase using the below method, the SQL query has a nested query, the issue is when using setString() method to add inline parameters, it is not replacing the ? in the query.

I am getting an exception as

Parameter value unbound. Parameter at index 1 is unbound

I am using phoenix queryserver and avatica JDBC driver to connect to my HBase

Below is my function.

public ResultSet getTopRiskEntitiesCount(String tenantId, String entityType){
    ResultSet resultSet = null;
            try{String query = "SELECT TID,ENTITYTYPE, ENTITYID AS USER,SCORE*100 AS RISK,TIMESTAMP FROM ANALYTICS_SCORES WHERE TID = ? AND ENTITYTYPE = ? AND TIMESTAMP >= (SELECT MAX(TIMESTAMP) FROM ANALYTICS_SCORES) ORDER BY RISK DESC LIMIT 100";
                PreparedStatement preparedStatement = getConnection().prepareStatement(query);
                preparedStatement.setString(1, tenantId);
                preparedStatement.setString(2, entityType);
                resultSet = preparedStatement.executeQuery();
            } catch (Exception e) {
                e.printStackTrace();
            }
            closeConnection();
            return resultSet;
        }

Below is the exception.

org.apache.calcite.avatica.AvaticaSqlException: Error -1 (00000) : while preparing SQL: SELECT TID,ENTITYTYPE, ENTITYID AS USER,SCORE*100 AS RISK,TIMESTAMP FROM ANALYTICS_SCORES WHERE TID = ? AND ENTITYTYPE = ? AND TIMESTAMP >= (SELECT MAX(TIMESTAMP) FROM ANALYTICS_SCORES) ORDER BY RISK DESC LIMIT 100
    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:327)
    at org.apache.calcite.avatica.AvaticaConnection.prepareStatement(AvaticaConnection.java:163)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.testng.internal.MethodInvocationHelper.invokeMethod(MethodInvocationHelper.java:124)
    at org.testng.internal.Invoker.invokeMethod(Invoker.java:580)
    at org.testng.internal.Invoker.invokeTestMethod(Invoker.java:716)
    at org.testng.internal.Invoker.invokeTestMethods(Invoker.java:988)
    at org.testng.internal.TestMethodWorker.invokeTestMethods(TestMethodWorker.java:125)
    at org.testng.internal.TestMethodWorker.run(TestMethodWorker.java:109)
    at org.testng.TestRunner.privateRun(TestRunner.java:648)
    at org.testng.TestRunner.run(TestRunner.java:505)
    at org.testng.SuiteRunner.runTest(SuiteRunner.java:455)
    at org.testng.SuiteRunner.runSequentially(SuiteRunner.java:450)
    at org.testng.SuiteRunner.privateRun(SuiteRunner.java:415)
    at org.testng.SuiteRunner.run(SuiteRunner.java:364)
    at org.testng.SuiteRunnerWorker.runSuite(SuiteRunnerWorker.java:52)
    at org.testng.SuiteRunnerWorker.run(SuiteRunnerWorker.java:84)
    at org.testng.TestNG.runSuitesSequentially(TestNG.java:1208)
    at org.testng.TestNG.runSuitesLocally(TestNG.java:1137)
    at org.testng.TestNG.runSuites(TestNG.java:1049)
    at org.testng.TestNG.run(TestNG.java:1017)
    at org.testng.remote.AbstractRemoteTestNG.run(AbstractRemoteTestNG.java:114)
    at org.testng.remote.RemoteTestNG.initAndRun(RemoteTestNG.java:251)
    at org.testng.remote.RemoteTestNG.main(RemoteTestNG.java:77)
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:683)
    at org.apache.calcite.avatica.jdbc.JdbcMeta.prepare(JdbcMeta.java:709)
    at org.apache.calcite.avatica.remote.LocalService.apply(LocalService.java:209)
    at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1199)
    at org.apache.calcite.avatica.remote.Service$PrepareRequest.accept(Service.java:1170)
    at org.apache.calcite.avatica.remote.AbstractHandler.apply(AbstractHandler.java:94)
    at org.apache.calcite.avatica.remote.ProtobufHandler.apply(ProtobufHandler.java:46)
    at org.apache.calcite.avatica.server.AvaticaProtobufHandler.handle(AvaticaProtobufHandler.java:127)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.Server.handle(Server.java:499)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:311)
    at org.apache.phoenix.shaded.org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:257)
    at org.apache.phoenix.shaded.org.eclipse.jetty.io.AbstractConnection$2.run(AbstractConnection.java:544)
    at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:635)
    at org.apache.phoenix.shaded.org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:555)
    at java.lang.Thread.run(Thread.java:748)

perhaps this link may help: https://community.hortonworks.com/questions/45896/problem-executing-joins-on-phoenix-query-server.html — Mustafa Çil, Jul 12 '19 at 07:44

score 0 · Answer 1 · answered Jul 12 '19 at 09:36

0

Try use this block in your code.

for (int i = 1; i <= pmetadata.getParameterCount(); i++) { System.out.println("Type: " + pmetadata.getParameterType(i)); }

answered Jul 12 '19 at 09:36

Answer

1
2

preparedStatement.getString() method not working for nested sql query

1 Answers1