Forum: Building VoltDB Applications

Post: Passing byte[] as parameter to stored procedure

Passing byte[] as parameter to stored procedure
andiklein
Dec 5, 2016
We cannot seem to figure out how to pass an array of TINYINT values as parameter to a stored procedure.

A table we wish to query uses a TINYINT column, to store an enum property that can only have the values 0 to 5.
Seeing how TINYINT's closest equivalent in Java is a primitive byte, one would assume that to pass a number of values to our stored procedure's condition "... WHERE tinyintcolumn IN ?" could be achieved by invoking callProcedure("procName", otherParameters, byteArray), with byteArray obviously being a btye[]. (We are aware that the callProcedure(String, Object...) method's variable argument signature might cause other problems, if byteArray were the only argument after the procedure name)

In any case, unfortunately the above does not work, because the Java client believes the byte[] argument to represent a Volt VARBINARY type, instead of an array of TINYINTs.
Nothing we have tried so far works, passing a Byte[], int[], short[], List<Byte/Short/Integer>. Everything comes back with an error.

The only thing that surprisingly doesn't throw an exception, is passing a String, e.g. "1,2,3" and declaring the procedure as "WHERE ourTinyIntColumn in (?)". But even that does not work as it should. While not throwing an error, we do not get the same results when passing "1,2,3" as parameter vs. "hardcoding" the procedure in the form "WHERE ourTinyIntColumn in (1,2,3)"

So how do we pass a byte[] to be used as an array/list/collection of TINYINTs?
bballard
Dec 5, 2016
How is the procedure defined? Can you provide the CREATE PROCEDURE statement, and if it's a java stored procedure also the signature of the run() method, the SQLStmt syntax and the voltQueueSQL() call?
rmorgenstein
Dec 5, 2016
You are right, TINYINT arrays are being treated like VARCHARs in the wire protocol. We opened a ticket at https://issues.voltdb.com/browse/ENG-10107. Thank you for bringing this to our attention.

Ruth
andiklein
Dec 5, 2016
Table, unrelated columns omitted:


CREATE TABLE CACHEDATA (
OPERATORCID bigint NOT NULL,
PRODUCTCID bigint NOT NULL,
CALDATE timestamp NOT NULL,
...
STATUSA tinyint NOT NULL,
STATUSR tinyint NOT NULL,
...
CONSTRAINT PKEY PRIMARY KEY (OPERATORCID, PRODUCTCID, CALDATE)
);


Procedures would for instance be:

CREATE PROCEDURE GetUsableCache AS SELECT * FROM cachedata WHERE StatusA IN ? AND productcid=?;

We would then try to invoke the procedure by calling:


...
List<CacheData> cds = new ArrayList<>(400);
byte[] statusAValues = new byte[]{1,2,3,4};
long productCid = 42L

VoltTable vt = client.callProcedure("GetUsableCache", statusAValues, productCid).getResults()[0];
while (vt.advanceRow()) {
CacheData cd = CacheDataFactory.getInstance().getCacheDataByVoltTable(vt);
cds.add(cd);
}

return cds;


We have now changed the column type of STATUSA and STATUSR to a SMALLINT and pass the parameter statusAValues as a short[], and there is no problem with that.
The enum values of STATUSA and STATUSR currently all convert to a numeric value between 0 and 5, and we don't expect any significant increase in enum values, so tinyint would be the best suited column type.
But sacrificing an extra byte of RAM per CACHEDATA row has so far been the only thing we could find to sort out this issue with not being able to invoke any procedures that require a collection of TINYINT values as parameter.
vtkstef
Dec 5, 2016
as a work around you may change the data type to smallint and pass in an array of shorts

client.callProcedure("inClauseProc", new Object[] { new short[] {1,2,3} })

also keep in mind that when you parameterize the IN clause you need to omit the parenthesis

"WHERE ourSmallIntColumn in ?"

Ciao
Stefano
andiklein
Dec 5, 2016
Interestingly the procedure's signature, as it is displayed in the admin backend VoltDB Management Center > SQL Query > Stored Procedures > User Defined Stored Procedures, is also quite different when using TINYINT and SMALLINT.
When we intially declared the columns as TINYINT and created our procedure, the procedure would be listed as

GetUsableCache
Param0 (byte[])
Param1 (bigint)
Return Table[]

After changing the columns to SMALLINT and dropping/recreating the procedure, the signature would be

GetUsableCache
Param0 (smallint_array)
Param1 (bigint)
Return Table[]
andiklein
Dec 6, 2016
Thanks everyone for the replies and the affirmation that our SMALLINT workaround is probably a good and acceptable approach until the issue is fixed.
And thanks for pointing us to the JIRA ticket. I had not been able to find anything on any previously known issue through Google & Co, well I wasn't aware of the Volt issue tracker altogether.