Error searching for tasks by actor id on MS SQL Server

[tobysaville]

Hello,

I am trying to execute the method
findTaskInstances(String actorId)
On MS SQL Server

However, when I do, I get the following exception:

Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
 at org.hibernate.loader.Loader.doList(Loader.java:1596)
 at org.hibernate.loader.Loader.list(Loader.java:1577)
 at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:395)
 at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:271)
 at org.hibernate.impl.SessionImpl.list(SessionImpl.java:844)
 at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
 at org.jbpm.db.TaskMgmtSession.findTaskInstances(TaskMgmtSession.java:34)
 ... 22 more
Caused by: java.sql.SQLException: Invalid column name 'false'.
 at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
 at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2754)
 at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2195)
 at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:620)
 at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:372)
 at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:672)
 at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
 at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
 at org.hibernate.loader.Loader.doQuery(Loader.java:391)
 at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:218)
 at org.hibernate.loader.Loader.doList(Loader.java:1593)
 ... 28 more

I copied part of the SQL generated by hibernate, and ran this in the MS SQL Enterprise Manager. It generated the same error:
Invalid column name 'false'.
Here is the offending SQL:

SELECT ID_ AS ID1_, NAME_ AS NAME2_26_, DESCRIPTION_ AS DESCRIPT3_26_, ACTORID_ AS ACTORID4_26_, CREATE_ AS CREATE5_26_, START_ AS START6_26_, END_ AS END7_26_, DUEDATE_ AS DUEDATE8_26_, PRIORITY_ AS PRIORITY9_26_, ISCANCELLED_ AS ISCANCE10_26_, ISSIGNALLING_ AS ISSIGNA11_26_, ISBLOCKING_ AS ISBLOCKING12_26_, TASK_ AS TASK13_26_, TOKEN_ AS TOKEN14_26_, SWIMLANINSTANCE_ AS SWIMLAN15_26_, TASKMGMTINSTANCE_ AS TASKMGM16_26_
FROM JBPM_TASKINSTANCE taskinstan0_
WHERE (ACTORID_ = ?) AND (END_ IS NULL) AND (ISCANCELLED_ = false)

So I changed the line

AND (ISCANCELLED = false)

To

AND (ISCANCELLED = 0)

Because the MS SQL Server Enterprise manager says this column is of type tinyint.

The query then works. Has anyone else come across this problem? is it a bug?

thanks,

toby

[kukeltje]

From the hibernate docs:

3.4.5. Query Language Substitution

You may define new Hibernate query tokens using hibernate.query.substitutions. For example:

hibernate.query.substitutions true=1, false=0

would cause the tokens true and false to be translated to integer literals in the generated SQL.

[kukeltje]

oops, also pressed post instead of preview.... while adding this to the jbpm hibernate config file, you do not need to change the code.

hth

[tobysaville]

Thanks a lot for your help, thats exactly what i needed.

I apolagise for asking "is this a bug?". as expected, it is my lack of knowledge causing the problem :)

cheers, toby