exec StoredProcedure passing Array of Integers?

Question

I have a stored procedure which has .. WHERE something IN ? .....

I can't find any documentation on how to call this procedure using "exec" I tried all combinations

exec bestThumbs ([[324622 ,321235]]); Invalid parameter count for procedure: bestThumbs (expected: 1, received: 2)

exec bestThumbs [324622,321235,3454345]; Invalid parameter count for procedure: bestThumbs (expected: 1, received: 3)

exec bestThumbs [[324622 ,321235, 3454345]]; Invalid parameter count for procedure: bestThumbs (expected: 1, received: 3)

Furthermore , trying to do the same in PHP via JSON interface:

$a = array([163195,163199,163196]);
$params = json_encode($a);
$params = urlencode($params);
$querystring = "Procedure=$proc&Parameters=$params";

returns: VOLTDB ERROR: PROCEDURE bestThumbs TYPE ERROR FOR PARAMETER 0: org.voltdb.VoltTypeException: tryScalarMakeCompatible: Unable to match parameter array:int to provided long

What is the proper way of doing this ?

Thanks !

Answer 1

VoltDB's sqlcmd interface and PHP client library do not support array parameters. Some of the other client libraries do.

If you are using a java procedure, you can format an array of numbers as a string and split the string and parse the values within the procedure, then build an int[] or long[] to pass into voltQueueSQL() when calling the SQLStmt.

However, if your procedure's only input was intended to be an array of integers, keep in mind that a concatenated String parameter as I suggested would not allow the procedure to be partitioned. Even if you were using a client library such as python or java that supports array parameters, the procedure cannot be partitioned on a parameter which is an array. This would mean it must be a multi-partition procedure which runs in all of the partitions. It would be more scalable to have a procedure that takes a single parameter value, if you then partitioned the procedure so it runs in only one partition based on that input value. If the client has an array of values to evaluate, you could iterate through the array and make separate calls to the procedure, each one being executed on only one partition.

I work at VoltDB.