Search code examples
databasestored-proceduresvoltdb

Voltdb stored procedures with array parameters

I have a VoltDB stored procedure that takes a string array String[] and it's loaded successfully but I cannot seem to execute the stored procedure using the VoltDB SQL query interface with exec.

Cannot also execute it in sqlcmd interface.

I get this error:

Error: PrepareStatement error: Invalid parameter count for procedure "StoredProcedure" (received: 3, expected: 2, )

How can I construct the exec statement to retrieve the result of the stored procedure from VoltDB?

Solution

  • Unfortunately, there is no good way to pass an array as a parameter when using the sqlcmd interface. If you need to test this procedure, the best way is to write a simple java / python script to call the procedure with an array. In python, it would look something like this:

    #!/usr/bin/env python
from voltdbclient import *
client = FastSerializer("localhost", 21212)

# declare array parameters the same as primitives
fooProc = VoltProcedure( client, "Foo", [FastSerializer.VOLTTYPE_BIGINT])

# the set of all parameters is the outer array, the inner array is the parameter value
fooResponse = fooProc.call( [ [1, 2, 3, 4, 5] ] )
for x in fooResponse.tables:
   print x

    Full disclosure: I work at VoltDB.