⬅ Forum: Building VoltDB Applications

Post: Integer Array as procedure param?

Integer Array as procedure param?

FloM

Mar 20, 2014

Hi,

(I'm kind of new to voltDB and actually don't know if this is the right place to post.)

I've been trying to create a stored procedure as
"SELECT data FROM table WHERE column IN ?"
(I can't define a fixed size for the "IN" list)

But using the java client, I couldn't find a way to use the .callProcedure method...

The initial .java of the procedure :


public class Z extends VoltProcedure {

    public final SQLStmt findCurrent = new SQLStmt(
            " SELECT * FROM table WHERE column IN ?;");


    public VoltTable[] run(XXX in) throws VoltAbortException {
        voltQueueSQL(findCurrent, in);
        return voltExecuteSQL();
    }
}

The way I'm trying to call it:

VoltTable[] res = this.voltDB.callProcedure("Z", myData).getResults();

My question is what should be the type of XXX? And what should be the type of myData?
I tried with int[], String[], even a formatted String (such as "(value, value, value...)") without success.

Anyone already lived that? Any ideas?

pmartel

Mar 20, 2014

Assuming that column is of type INTEGER, int[] is the way to go. What happened when you tried that?

bballard

Mar 20, 2014

There are some notes about using "WHERE column IN ?;" syntax in Using VoltDB, App. B - SELECT (https://voltdb.com/docs/UsingVoltDB/sqlref_select.php) about halfway down the page.

For example, if you wanted to test with literals declared in the stored procedure, it might look like this:

int[] inputArray = { 1, 2, 3 };

voltQueueSQL(findCurrent, (Object)inputArray);

The (Object) casting makes the voltQueueSQL method consider the array as a single object, rather than as separate input parameters. This is to get around the array of input parameters that voltQueueSQL accepts:

voltQueueSQL(SQLStmt stmt, Object... args)

The same issue is present for the input parameters to the run() method that you pass in via Client.callProcedure:

callProcedure(String procName, Object... parameters)

This should work:

public VoltTable[] run(int[] in) throws VoltAbortException {
voltQueueSQL(findCurrent, (Object)in);
return voltExecuteSQL();
}

----------client: ----------

int[] myData = {1,2,3};
VoltTable[] res = this.voltDB.callProcedure("Z", (Object)myData).getResults();

FloM

Mar 21, 2014

Yeah it works great now, thank you!

FloM

Mar 24, 2014

I was wondering, is there any way to do the same with the Python client?

bballard

Mar 24, 2014

Hi FloM,

Yes, this can also work with the Python client. If you refer to the Python Client README file, it mentions that parameters can be arrays (or lists). You declare the VoltProcedure wrapper the same way that you would if you were passing a single value, but when you call the procedure you can pass in a list of values. For example:

proc = VoltProcedure( client, "SelectIn", [FastSerializer.VOLTTYPE_STRING])
arrayparam = ["Hello","Bonjour"]
response = proc.call( [arrayparam] )

In this case, "SelectIn" is a java stored procedure that uses the techniques discussed above, with a String[] in input parameter, and using (Object)in when passing the parameter to the voltQueueSQL() method:

import org.voltdb.*;

public class SelectIn extends VoltProcedure {

public final SQLStmt sql = new SQLStmt(
"SELECT HELLO, WORLD FROM HELLOWORLD " +
" WHERE hello IN ?;"
);

public VoltTable[] run( String[] language)
throws VoltAbortException {
voltQueueSQL( sql, (Object)language );
return voltExecuteSQL();
}
}

This example uses the schema from the HelloWorld tutorial example under voltdb/doc/tutorials/helloworld.

Ben