In our project, we used voltdb stored procedure for any operations. In such cases we want to implement IN operator where we can pass any number of parameters in a String array and IN operator accept that array and based on that query will be fire and we'll get appropriate records.
So we tried to search for such implementation where we can use IN operator in stored procedure by which we can get our expected result and we don't have to query again and again for multiple records. After a certain research we came to know about this type of implementation and found a reference in voltdb documentation .
SELECT * from EMPLOYEE where STATUS IN (?, ?,?);
SELECT * from EMPLOYEE where STATUS IN ?;
In the first statement, there are three parameters that replace individual values in the IN list, allowing you to specify exactly three selection values. In the second statement the placeholder replaces the entire list, including the parentheses. In this case the parameter to the procedure call must be an array and allows you to change not only the values of the alternatives but the number of criteria considered.
The following Java code fragment demonstrates how these two queries can be used in a stored procedure, resulting in equivalent SQL statements being executed:
String arg1 = "Salary";
String arg2 = "Hourly";
String arg3 = "Parttime";
voltQueueSQL( query1, arg1, arg2, arg3);
String listargs[] = new String[3];
listargs[0] = arg1;
listargs[1] = arg2;
listargs[2] = arg3;
voltQueueSQL( query2, (Object) listargs);
Note: when passing arrays as parameters in Java, it is a good practice to explicitly cast them as an object to avoid the array being implicitly expanded into individual call parameters.
We tried this implementation. We know that it's callProcedure method accepts the Object type of varargs so we did explicit type casting also.
Method signature
public ClientResponse callProcedure(String procName, Object... parameters)
throws IOException, NoConnectionsException, ProcCallException;
While we debugging we got error message in explicit type casting which is cannot cast java.lang.String[] to java.lang.Object. So we debug more and we reached at it's library class namely as ParameterSet.java.
I'm sharing one of the use of this class as per my understanding i.e This class is responsible to convert passed parameter in respective VoltType.
We checked a log and we came to know that we didn't receive any error related to type casting but while ParameterSet.java was checking passed parameter type it recognised our array as [Ljava.lang.String and it throws Error Code: -2. Reason: VOLTDB ERROR: PROCEDURE ABC TYPE ERROR FOR PARAMETER 0: org.voltdb.VoltTypeException: Array / Scalar parameter mismatch ([Ljava.lang.String; to java.lang.String) {}
If you've any solution to implement this then please share.
Prompt assistance will be highly appreciated.
