Astyanax parepared statement withvalues() not working properly

Question

today I migrated to Astyanax 1.56.42 and discovered, that withValues() on prepared statements doesn't seem to work properly with SQL SELECT...WHERE...IN ().

ArrayList<ByteBuffer> uids = new ArrayList<ByteBuffer>(fileUids.size());
for (int i = 0; i < fileUids.size(); i++) {
    uids.add(ByteBuffer.wrap(UUIDtoByteArray(fileUids.get(i)), 0, 16));
}

result = KEYSPACE.prepareQuery(CF_FILESYSTEM)
    .withCql("SELECT * from files where file_uid in (?);")
    .asPreparedStatement()
    .withValues(uids)
    .execute();

If my ArrayList contains more than one entry, this results in error

SEVERE: com.netflix.astyanax.connectionpool.exceptions.BadRequestException: BadRequestException: [host=hostname(hostname):9160, latency=5(5), attempts=1]InvalidRequestException(why:there were 1 markers(?) in CQL but 2 bound variables)

What am I doing wrong? Is there any other way to handle a SQL SELECT...WHERE...IN () - statement or did I find a bug?

Best regards Chris

Answer 1

As you mentioned because you are supplying a collection (ArrayList) to a single ? Astyanax throws an exception. I think you need to add a ? for each element you want to have inside the IN clause.

Say you want to have 2 ints stored in an ArrayList called arrayListObj the where clause, your statement looks like this:

SELECT & FROM users WHERE somevalue IN (arrayListObj);

Because you are suppling a collection, this cant work, so you will need multiple ?'s. I.e. you want :

SELECT name, occupation FROM users WHERE userid IN (arrayListObj.get(0), arrayListObj.get(1));

I couldn't find anything on the Astyanax wiki about using the IN clause with prepared statements.