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.