Jdbi and cassandra, arguments not working

[Matias]

Hi, I've  been using jdbi with sqlite and it worked perfectly. Now I'm testing it with cassandra, and I'm getting the following error on one of my tests:

Tests in error:

  findAll(com.example.db.tests.HostTest): java.sql.SQLSyntaxErrorException: InvalidRequestException(why:line 1:48 mismatched input '?' expecting INTEGER) [statement:"SELECT * FROM hosts LIMIT :limit", located:"SELECT * FROM hosts LIMIT :limit", rewritten:"/* HostDAO.findAll */ SELECT * FROM hosts LIMIT ?", arguments:{ positional:{}, named:{limit:20,order:hostname ASC}, finder:[]}]

The cassandra jdbc driver is:

https://code.google.com/a/apache-extras.org/p/cassandra-jdbc/

And my HostDAO class is:

@RegisterMapper(Host.Mapper.class)

public interface HostDAO extends DAO {

  @SqlQuery("SELECT * FROM hosts LIMIT :limit")

  ImmutableList<Host> findAll(@Bind("limit") int limit, @Bind("order") String order)

      throws DBIException;

  void close();

}

Any idea what is going on?

[Steven Nelson]

It might have something to do with

 

https://code.google.com/a/apache-extras.org/p/cassandra-jdbc/issues/detail?id=72

 

since the LIMIT is an integer…

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

---

No virus found in this message.
Checked by AVG - www.avg.com
Version: 2012.0.2241 / Virus Database: 3162/5785 - Release Date: 04/30/13

[Brian McCallister]

It looks like the sql parser for cassandra-jdbc does not allow parameterizing limit clause. Try making the limit a literal instead of a bind param, see what it does.

[Brian McCallister]

Looking at the CQL grammar (1) it look slike it does require an integer literal:

See the line

          ( K_LIMIT rows=INTEGER { numRecords = Integer.parseInt($rows.text); } )?

in:

/**
 * SELECT
 *  (REVERSED)? <expression>
 * FROM
 *     <CF>
 * USING
 *     CONSISTENCY <LEVEL>
 * WHERE
 *     KEY = "key1" AND KEY = "key2" AND
 *     COL > 1 AND COL < 100
 * LIMIT <NUMBER>;
 */
selectStatement returns [SelectStatement expr]
    : { 
          int numRecords = 10000;
          SelectExpression expression = null;
          boolean isCountOp = false;
          ConsistencyLevel cLevel = ConsistencyLevel.ONE;
      }
      K_SELECT
          ( s1=selectExpression                 { expression = s1; }
          | K_COUNT '(' s2=selectExpression ')' { expression = s2; isCountOp = true; }
          )
          K_FROM (keyspace=(IDENT | STRING_LITERAL | INTEGER) '.')? columnFamily=( IDENT | STRING_LITERAL | INTEGER )
          ( K_USING K_CONSISTENCY K_LEVEL { cLevel = ConsistencyLevel.valueOf($K_LEVEL.text.toUpperCase()); } )?
          ( K_WHERE whereClause )?
          ( K_LIMIT rows=INTEGER { numRecords = Integer.parseInt($rows.text); } )?
          endStmnt
      {
          return new SelectStatement(expression,
                                     isCountOp,
                                     $keyspace.text,
                                     $columnFamily.text,
                                     cLevel,
                                     $whereClause.clause,
                                     numRecords);
      }
    ;

-Brian
1) http://svn.apache.org/repos/asf/cassandra/tags/cassandra-1.0.6/src/java/org/apache/cassandra/cql/Cql.g

[Matias Surdi]

Hi,

Brian, it looks like with a literal in the sql statement it works. Do
you think I should open an issue with the cassandra-jdbc developers?

Thanks a lot for your help.


2013/5/1 Brian McCallister <bri...@skife.org>:

> You received this message because you are subscribed to a topic in the
> Google Groups "jDBI" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/jdbi/NgXy-2dzUOk/unsubscribe?hl=en-US.
> To unsubscribe from this group and all its topics, send an email to

> jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>

--
Matias Emanuel Surdi.

[Stephen Connolly]

I would think opening an issue would be a good plan. Be sure to open the issue with the cassandra-jdbc project and not the cassandra project as, while the two overlap, they are separate projects

[Matias Surdi]

Here we go: https://code.google.com/a/apache-extras.org/p/cassandra-jdbc/issues/detail?id=81&can=1&q=cassandra&sort=-id

Thanks for your help!


2013/5/1 Stephen Connolly <stephen.al...@gmail.com>:

[Brian McCallister]

This is actually a Cassandra issue, not cassandra-jdbc. The issue is in CQL itself, not the JDBC driver.

-Brian