CODEWITHSUNDEEP
voltdb
Naresh Joshi
Naresh Joshi

Reputation: 4567

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

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

Upvotes: 0

Views: 293

Answers (2)

Naresh Joshi
Naresh Joshi

Reputation: 4567

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);
}

Upvotes: 0

Andrew
Andrew

Reputation: 311

The @Adhoc system procedure is recognizing the array as one parameter. This kind of thing happens with @Adhoc because there is no planning of the procedure going on where one can explicitly state what each parameter is.

You have the right idea about parsing the sqlArgs array into the actual parameters to pass in separately. You could also concatenate these separate parameters into the SQL statement itself. That way, your adhoc statement will simply be:

voltdbClient.callProcedure("@AdHoc", sql)

Full disclosure: I work at VoltDB.

Upvotes: 1

Related Questions