Problem with EJB-QL and primary-keys

[Oliver Rensen]

Hi all,

I'm using JBoss 4.0.2, and I try to access to a HSQLDB-table with a finder-method.

The name of the HSQLDB-table is RecipePos and it has the following fields:

RECIPEID (CMP-Integer-field and part of the primary-key)
RECIPEPS (CMP-Integer-field and part of the primary-key)
RECIPEPOSPK (Primary-Key-compound-class)
SIDE (CMP-String-field)
WORKDESC (CMP-String-field)
MACHINE (CMP-String-field)
COUNT (CMP-String-field)
COLOR (CMP-String-field)
RECIPEDESC (CMP-String-field)
COMMENT (CMP-String-field)

This is my primary-key-compound-class:

public class RecipePosPK implements Serializable {
public java.lang.Integer recipeId;
public java.lang.Integer recipePs;

public RecipePosPK() { }

public boolean equals(Object obj) {
if (this.getClass().equals(obj.getClass())) {
RecipePosPK other = (RecipePosPK) obj;
return (this.recipeId.intValue() == other.recipeId.intValue()
&& this.recipePs.intValue() == other.recipePs.intValue());
}

return false;
}

public int hashCode() {
return(("" + recipeId.intValue()
+ recipePs.intValue()).hashCode());
}
}

I want to search for all recipe-positions with a specific ID-number. Thus, I have created the following finder-method:

@ejb.finder signature="java.util.Collection
findRecipe(java.lang.Integer recipe)"
query="SELECT OBJECT(p) FROM RecipePos AS p
WHERE p.recipeId = ?1"

I call this finder-method in a session-bean:

Collection col = home.findRecipe(recipeID);

But it fails with an error-message:

Log failed: CausedByException is:
Unexpected token in statement
[SELECT recipeId, recipePs, recipePosPK, side,
workDesc, machine, count, color, recipeDesc, comment)
FROM RECIPEPOS WHERE (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
OR (recipeId = ? AND recipePs = ?)
...
OR (recipeId = ? AND recipePs = ?)

I have tried many EJB-QLs to read data from the table RecipePos, but the "Unexpected token"-error occurs in all of my EJB-QLs. Even "SELECT OBJECT(p) FROM RecipePos AS p" does not work. JBoss supplements every where-clause with the primary-key-fields "(recipeId = ? AND recipePs = ?)", and I don't know why.

EJB-QLs on other tables without a primary-key-compound-class works fine. For example, there is a table named RecipeHead with a simple Integer-ID as primary-key, and I can access this table through EJB-QL without problems. But RecipePos is my first table with a primary-key-compound-class, and there is no EJB-QL that will work with RecipePos.

Could anybody please help me, whether I have missed something to handle the access on tables with primary-key-compound-classes?

Regards,
Oliver

[Oliver Rensen]

I suppose, there is no error in the EJB-QL, because I have tried the query "SELECT OBJECT(p) FROM RecipePos AS p WHERE p.color = '999'", and if there is no record with the color 999, the finder-method doesn't throw an exception. But when I create one record with color 999, the finder-method breaks:

TransactionRolledbackLocalException in method:
public abstract java.lang.Integer
entitybeans.interfaces.RecipePosLocal.getRecipePs(),
causedBy: java.sql.SQLException: Unexpected token in statement
[SELECT recipePosPK, side, workDesc, machine, count, color, recipeDesc, comment
FROM RECIPEPOS WHERE (recipeId=? AND recipePs=?)]
at org.hsqldb.jdbc.Util.throwError(Unknown Source)
at org.hsqldb.jdbc.jdbcPreparedStatement

There is only one "(recipeId=? AND recipePs=?)" in the WHERE-clause, and this indicates, that JBoss knows that there is only one record with the color 999. Hence the query should work correct, but JBoss seems to have problems to replace the placeholders ? in the PreparedStatement with the real values. But how can I influence this behavior?

recipeId and recipePs are Integers, and I have replaced it through Strings, but this doesn't work, too.

I assume, there is only a little bagatelle in my program which is not correct, but I cannot find the error.

[Oliver Rensen]

I have solved the problem. One column in my table is named with count. Count is not a reserved word in EJB-QL, but it is reserved in SQL, and I have used it many many times before. After 14 years experience with SQL I fall into this simple trap.