Jan 20, 2017
Can you post your code that didn't work? I don't totally understand what you're trying to do.
Jan 23, 2017
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:
SET existing_key = 1
WHERE key_name IN ? ;
So how could I create a INLIST_OF_STRING type in Java?
Jan 23, 2017
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.
Jan 24, 2017
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.
Jan 24, 2017
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.