Can't get not() to work

[Stephen Gower]

I want to exclude rows from my query. I'm trying:

where.and();

where.not().like(Test.FUNDS_FIELD_NAME, "%allocations%");

I get the following error:

java.lang.IllegalStateException: com.j256.ormlite.stmt.query.ManyClause@16a7209b is already waiting for a future clause, can't add: NOT without comparison

Can someone help point out my mistake  I've tried to make this work a couple times in the past and never was able to and could not find any examples online.

Thanks!

[Stephen Gower]

Can anyone help?  This seems like it should be a simple thing to do.  ??

[Nathan Jones]

Hi Stephen,

That looks like a bug to me; I don't see why you shouldn't be able to
use the fluid API to build a "not" clause inside an "and" clause such as
this:

final PreparedQuery query =
sqliteDao.queryBuilder().where()
.like("col1", "%foo%")
.and().not().like("col2", "%bar%").prepare();

As you pointed out, the above syntax fails with the "already waiting for
a future clause" error. As far as I can tell this is because the Sqlite
fluid syntax is only keeping track of a single current "needs future"
clause but the "and" and the "not" clauses are both "needs future" clauses.

I did manage to build the desired query, however, by using the following
alternative, slightly clunky, syntax:

final Where where = sqliteDao.queryBuilder().where();
final Where clause1 = where.like("col1", "%foo%");
final Where clause2 = where.not().like("col2", "%bar%");
final PreparedQuery query =
where.and(clause1, clause2).prepare();

I hope this helps.

Maybe Gray can comment on whether this is a bug or a by-design
limitation of Sqlite. I guess adding support for the and-not fluid
syntax will require maintaining a stack of nested current "needs future"
clauses.

- Nathan

[Nathan Jones]

Sorry, read "sqlite" as "ormlite" in my previous message - I just had a
brain fade :P

- Nathan

[Nathan Jones]

It just occurred to me that, if you only have one "not" clause, you can
work around this limitation using the fluid syntax by making sure your
"not" clause is on the left hand side of the "and" clause. For example:

final PreparedQuery query =
sqliteDao.queryBuilder().where()

.not().like("col2", "%bar%")
.and().like("col1", "%foo%")
.prepare();

- Nathan

[Gray Watson]

On Oct 26, 2013, at 12:39 AM, Stephen Gower <stephe...@gmail.com> wrote:

> where.and();
> where.not().like(Test.FUNDS_FIELD_NAME, "%allocations%");
>
> I get the following error:
> java.lang.IllegalStateException: com.j256.ormlite.stmt.query.ManyClause@16a7209b is already waiting for a future clause, can't add: NOT without comparison

Sorry for the delay on this. This seems to be a limitation to ORMLite. The inline way of building queries allows only _one_ "needs-future" clause. In this case the and() uses up the "needs-future" and then not() tries to set another one.

As Stephen points out, you can solve it

where.not().like(Test.FUNDS_FIELD_NAME, "%allocations%");
where.and();
...

I'd recommend switching to the argument way of building queries: So you could would look like

where.and(..., where.not(where.like(...)))

You can also build the queries other ways. See the docs here:

http://ormlite.com/docs/building-queries

I've submitted the following bug:

https://sourceforge.net/p/ormlite/bugs/159/

gray

[Stephen Gower]

Thanks for checking this.  For now I worked around it by filtering out the "allocations" query from the results.  Not ideal, but it works.  Now maybe you can look into my new issue relating to "createTable".  Will post a new message now :)

[Stephen Gower]

Actually disregard my reference to a "createTable" problem.

In H2 I had the following jdbc string:

Constants.jdbcString="jdbc:h2:file:"+databaseFolder+"asdff"+";LOG=2;CACHE_SIZE=32768;DB_CLOSE_ON_EXIT=FALSE;";

I needed to change this to DB_CLOSE_ON_EXIT=TRUE because with the previous setting the "createTable" change wasn't being committed properly. Some of the time the table failed to create.  Other times the table would be created but not the indexes.  Thanks!