cassandra-driver-core: pass list values to parameterized SimpleStatement

Question

I'm currently writing a very basic CQL access layer using the official Datastax Driver (V2.0) and struggling a bit on passing parameter values to a statement.

Here's an example

Column family (simplified)

USE myKeyspace;

CREATE TABLE MyTable (
    myId timeuuid,
    myTypeId int,
    myVal varchar,
    PRIMARY_KEY( myId, myTypeId )
);

CREATE INDEX MyTable_myTypeID 
    ON MyTable( myTypeId );

The basic idea is storing some event data with multiple values (per "event"), that's why I'm using the combined PK. Every event has its time-based UUID, there might be multiple entries per typeId. Does that even make sense from a modelling perspective?

What I'm trying to do now is fetching only entries for an event with a selection of 'typeIds'.

public void myQueryCode() {

    Cluster.Builder builder = Cluster.builder();
    builder.withPort( 9142 );
    builder.addContactPoints( "127.0.0.1" );
    cluster = builder.build();
    Session session = cluster.connect( "myKeyspace" );

    List<Integer> typeFilter = new ArrayList<> ();
    typeFilter.add( 1 );
    typeFilter.add( 2 );

    Statement stmt = new SimpleStatement( 
        "SELECT * FROM MyTable where myId = ?" +
        " AND myTypeId IN (?,?)" +
        " ALLOW FILTERING",
        UUID.randomUUID(),
        typeFilter );

    ResultSet result = session.execute( stmt );

    // do something with results
}

However, I'm just getting an exception deep down in serialization of the Statement's values.

com.datastax.driver.core.exceptions.InvalidQueryException: Expected 4 or 0 byte int (8)
at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:35)
at com.datastax.driver.core.DefaultResultSetFuture.extractCauseFromExecutionException(DefaultResultSetFuture.java:256)
at com.datastax.driver.core.DefaultResultSetFuture.getUninterruptibly(DefaultResultSetFuture.java:172)
at com.datastax.driver.core.AbstractSession.execute(AbstractSession.java:52)

I'm not sure on passing the list as second parameter, the driver is taking the parameter, but maybe that is only suitable for inserts on collection-typed columns?

Answer 1

You are correct that the List you are passing is being forced into the second parameter and this causes the InvalidQueryException.

Statement stmt = new SimpleStatement( 
        "SELECT * FROM MyTable where myId = ?#1" +
        " AND myTypeId IN (?#2,?#3)" +
        " ALLOW FILTERING",
        #1 UUID.randomUUID(),
        #2 typeFilter );

Since typeFilter is a list the driver then attempts to put a List Collection object into parameter 2 and things go wonky. This is because when you run statements like this (without preparing) the driver is unable to check the types, Comment in code. If instead you passed in

Statement stmt = new SimpleStatement( 
            "SELECT * FROM MyTable where myId = ?#1" +
            " AND myTypeId IN (?#2,?#3)" +
            " ALLOW FILTERING",
            #1 UUID.randomUUID(),
            #2 typeFilter.get(0)
            #3 typeFilter.get(1));

You would be fine. Or if you had prepared the statement first you would have been warned with a compile time error.