Error while passing multiple parameters as array in voltdb Adhoc stored procedure

Question

I have a scenario where I am getting a SQL query and SQL arguments (to avoid SQL injection) as input.

And I am running that SQL using VoltDB's AdHoc stored procedure using below code.

private static final String voltdbServer = "localhost";
private static final int voltdbPort = 21212;

public ClientResponse runAdHoc(String sql, Object... sqlArgs) throws IOException, ProcCallException
{
    ClientConfig clientConfig = new ClientConfig();
    Client voltdbClient = ClientFactory.createClient(clientConfig);
    voltdbClient.createConnection(voltdbServer, voltdbPort);

    return  voltdbClient.callProcedure("@AdHoc", sql, sqlArgs);
}

But I get an error org.voltdb.client.ProcCallException: SQL error while compiling query: Incorrect number of parameters passed: expected 2, passed 1

For runAdHoc("select * from table where column1 = ? and column2 = ?", "column1", "column2"), when there are two or more parameters.

And I get error org.voltdb.client.ProcCallException: Unable to execute adhoc sql statement(s): Array / Scalar parameter mismatch ([Ljava.lang.String; to java.lang.String)

For runAdHoc("select * from table where column1 = ?", "column1");, when there is only one parameter.

But I do not face this problem when I directly call voltdbClient.callProcedure("@AdHoc", "select * from table where column1 = ? and column2 = ?", "column1", "column2")

I think VoltDb is not able to treat sqlArgs as separate parameters instead, it is treating them as one array.

One way to solve this problem is parsing the SQL string myself and then passing it but I am posting this to know the efficient way to solve this problem.

Note:- Used SQL is just a test SQL

Answer 1

I posted the same question on VoltDB public slack channel and got one response which solved the problem which is as follows:

The short explanation is that your parameters to @Adhoc are being turned into [sql, sqlArgs] when they need to be [sql, sqlArg1, sqlArg2, …]. You’ll need to create a new array that is sqlArgs.length + 1, put sql at position 0, and copy sqlArgs into the new array starting at position 1. then pass that newly constructed array in the call to client.callProcedure("@AdHoc", newArray)

So I modified my runAdHoc method as below and it solved this problem

public ClientResponse runAdHoc(String sql, Object... sqlArgs) throws IOException, ProcCallException
{
    ClientConfig clientConfig = new ClientConfig();
    Client voltdbClient = ClientFactory.createClient(clientConfig);
    voltdbClient.createConnection(voltdbServer, voltdbPort);

    Object[] procArgs;
    if (sqlArgs == null || sqlArgs.length == 0)
    {
        procArgs = new Object[1];
    } else
    {
        procArgs = new Object[sqlArgs.length + 1];
        System.arraycopy(sqlArgs, 0, procArgs, 1, sqlArgs.length);
    }

    procArgs[0] = sql;

    return  voltdbClient.callProcedure("@AdHoc", procArgs);
}