using character literals in WHERE clause

[akehurst]

We are porting ejb-ql that works under Resin CMP over to JBoss 3.2.x, and ran into this snag.

compiling JBossQL statement 'select object(o) from v_content o,
IN(o.contract.phoneContractList) as phoneContract, IN(o.categoryList)
as category where now() > phoneContract.contract.startDate and now() <
phoneContract.contract.endDate and o.approvalCode = 'a' and
phoneContract.phone.phoneId = ?1 and (
lcase(o.imageContent.description) like ?2 or
lcase(o.imageContent.notes) like ?2 or
lcase(o.imageContent.file.userFriendlyFilename) like ?2 or
lcase(category.name) like ?2 or lcase(o.keywords) like ?2) order by
o.contentId DESC'; - nested throwable:
(org.jboss.ejb.plugins.cmp.ejbql.ParseException: Encountered "\'a\'" at
line 1, column 229.
Was expecting one of:
"ABS" ...
"LENGTH" ...
"LOCATE" ...
"SQRT" ...
"(" ...
"+" ...
"-" ...
<INTEGER_LITERAL> ...
<FLOATING_POINT_LITERAL> ...
<NUMERIC_VALUED_PARAMETER> ...
<NUMERIC_VALUED_PATH> ...
)]

Is there a way to specify a char for a comparison against java.lang.Character in ejb-ql?

[kv_moj]

Hi,

Looks like for the 'a', you need double quotes "a".

So your query generator code would need to change to:

.... phoneContract.contract.endDate and o.approvalCode = \"a\" and .....

Looks like you'd use the single qoutes for numeric constants.

About using java.lang.Character - don't know.

kv.

[kv_moj]

BTW - What is the sql/java data type of o.approvalCode ?

I've assumed VARCHAR(n)/java.lang.String for the above.

[kv_moj]

Regard to:

Is there a way to specify a char for a comparison against java.lang.Character in ejb-ql?

Here's an extract from standardjbosscmp-jdbc.xml:


<java-type>java.lang.Character</java-type>
<jdbc-type>CHAR</jdbc-type>
<sql-type>CHAR</sql-type>


I'd imagine if you want to make a comparison on a filed in your entity which you've defined as java.lang.Character then you'd use the int value of the char for a literal value as you would the long value of a java.util.Date when using date literals.

If this is the case then you could try:

.... phoneContract.contract.endDate and o.approvalCode = '" + Character.getNumericValue('a')+ "' and .....

[akehurst]

>
> Regard to:
>
> Is there a way to specify a char for a comparison
> against java.lang.Character in ejb-ql?
>
> Here's an extract from standardjbosscmp-jdbc.xml:
>
>
> <java-type>java.lang.Character</java-type>
> <jdbc-type>CHAR</jdbc-type>
> <sql-type>CHAR</sql-type>
>
>
> I'd imagine if you want to make a comparison on a
> filed in your entity which you've defined as
> java.lang.Character then you'd use the int value of
> the char for a literal value as you would the long
> value of a java.util.Date when using date literals.
>
> If this is the case then you could try:
>
> .... phoneContract.contract.endDate and
> o.approvalCode = '" + Character.getNumericValue('a')+
> "' and .....
>

We tried using integer value 97 for 'a' and it seems to work. Seems sorta hacky though. Our Ejb-ql is in xdoclet form inside a comment so doing the programmatic way of getting 97 as shown above wouldn't work. Will anyone add char comparisons to jbossql?

[akehurst]

approvalCode is java.lang.Character
In the database it is type CHAR(1)

[kv_moj]

Have you tried all the different permutations of the literal. Such as 'a' , a , "a".
I'd give it a try but I don't have an entity with any Character fields.