CODEWITHSUNDEEP
javacassandracql3
DoNuT
DoNuT

Reputation: 487

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

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?

Upvotes: 0

Views: 3850

Answers (2)

RussS
RussS

Reputation: 16576

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.

Upvotes: 2

catpaws
catpaws

Reputation: 2283

In answer to your first question, does it make sense from a modeling perspective to store "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," yes absolutely. This is a similar scenario to the one the Cassandra team uses in its data modeling example of a Music Service: http://www.datastax.com/documentation/cql/3.1/cql/ddl/ddl_music_service_c.html.

Look for a minor glitch in your code because I can do your example on the command line:

CREATE TABLE MyTable (
    myId timeuuid,
    myTypeId int,
    myVal varchar,
    PRIMARY_KEY( myId, myTypeId )
);
CREATE INDEX MyTable_myTypeID 
    ON MyTable( myTypeId );
insert into mytable( myid, mytypeid, myval) VALUES (d2177dd0-eaa2-11de-a572-001b779c76e3, 100, 'somechar')
insert into mytable( myid, mytypeid, myval) VALUES (d2177dd0-eaa2-11de-a572-001b779c76e3, 200, 'anotherchar')
SELECT * FROM MyTable where myId = d2177dd0-eaa2-11de-a572-001b779c76e3 AND myTypeId IN (100,200) ALLOW FILTERING;

Output is:

myid                                 | mytypeid | myval
--------------------------------------+----------+-------------
 d2177dd0-eaa2-11de-a572-001b779c76e3 |      100 |    somechar
 d2177dd0-eaa2-11de-a572-001b779c76e3 |      200 | anotherchar

Upvotes: 1

Related Questions