Forum: Building VoltDB Applications

Post: How to use INLIST_OF_STRING with IN (SQL)

How to use INLIST_OF_STRING with IN (SQL)
fred
Jan 20, 2017
Hi,

I'm Fred and I'm testing voltdb.
I've got a DML with a 'IN' in a stored procedure statement. My first try was a SELECT into this IN, but voltdb didn't accept it.
I found in 'Java Stored Procedure API' a VoltType INLIST_OF_STRING born to be used with 'IN', but how does it work?
I need to replace the question mark of a statement with a value of INLIST_OF_STRING from an other SELECT.

Thanks,

Fred.
rmorgenstein
Jan 20, 2017
Can you post your code that didn't work? I don't totally understand what you're trying to do.

Thanks,
Ruth
fred
Jan 23, 2017
Hi Ruth,

thanks for your interest.
I have a SQL query to update a table in a stored procedure. I put this query in a statement. In this update, in a "where" clause, I would like to use "IN" comparator and a parameter with a question mark. First I tried with a sub-query "select" but voltdb don't accept it. So in the documentation, in "Java Stored Procedure API" section, I found a voltType "INLIST_OF_STRING" with this descrition: Special purpose internal type to describe expectations for parameters to statements that contain syntax like " varchar_expr IN ? ".
It seems this type could be use in my case, but I can't manage to create a variable with INLIST_OF_STRING type in Java and to put into this variable the result of an other "select" query.
My query is like:
UPDATE load_resource_name
SET existing_key = 1
WHERE key_name IN ? ;

So how could I create a INLIST_OF_STRING type in Java?

thanks,

Fred.
bballard
Jan 23, 2017
Hi Fred,

In a Java stored procedure, you would first run a select query and collect the values into a String[], then pass that into the update statement.

However, when you "WHERE key_name IN ?", even if you have an index on the key_name column, the query engine will not use that index. You would be better using "WHERE key_name = ?" and then calling voltQueueSQL(...) within a loop to queue an update statement for each value, then the update would use the index on key_name if it exists.

Based on your SQL, this looks like the procedure is not partitioned, so it would run as a Multi-Partition procedure. You might find it would perform better running as separate transactions. One to select the names, and then a sequence of calls to update the record for each name.

Best regards,
Ben
fred
Jan 24, 2017
Hi Ben,

To collect the values into a String[], I have to loop the result of my select query. And to pass a String[] into an update statement is not possible on a single question mark : "Number of arguments provided was 160 where 1 was expected for statement UPDATE load_EP1MASS_EPDG SET existing_key = 1 WHERE key_name IN ? ;"

And using "WHERE key_name = ?" is the solution I had chosen, but the loop is on the result of my select query. With "IN", the update query loops on the records of the table load_resource_name.

I found INLIST_OF_STRING and the description seems to be perfect to use with "IN". But with your reply I anderstand this is not possible, so I move on and I keep the previous solution.

Thanks,
Fred
bballard
Jan 24, 2017
Hi Fred,

I think you need to cast your String[] as an Object before passing it into voltQueueSQL, because that method uses varargs:

voltQueueSQL(SQLStmt stmt, Object... args)

What I wrote previously about IN not using an index is not quite right. It materializes the IN list as a table and then does a join on these values, and for the join it CAN use an index. Still, it may or may not be faster than separate statements. It would be worth trying both ways to see which provides better performance.

Best regards,
Ben