EJB 2.1 Query language issue

[sankhla123]

Hi,

I am currently using Jboss 5.1 GA version and trying to migrate Entity beans

from Weblogic 8.1 to Jboss.

As i under stand EJB QL which is part of ejb-jar.xml

is a standard and should work across the application servers, however when i try to use same EJb QL in Jboss 5.1 it throws exception

I noticed that it throws exception only when i try to add day in a date. Please ses the highlighted underlined part in EJB QL

Following i the EJB QL which i am using

SELECT OBJECT(o) FROM CampEJB as o WHERE o.clientID= ?1 AND o.locationID = ?2 AND (o.brand = ?3 OR o.brand = 'All') and o.active = ?4 and o.startDate <= ?5 and ((o.endDate is not null and o.endDate + 1 >= ?5) OR (o.endDate is null)) ORDERBY o.priority

When i use above EJB QL it throws following exception, Any help would be greatly appreciated.

06:57:06,206 ERROR [CampEJB#findByBrandLocationDate] Encountered "o.endDate is not null and ( o.endDate +" at line 1, column 168.

Was expecting one of:

    "ABS" ...

    "LENGTH" ...

    "LOCATE" ...

    "SQRT" ...

    "MOD" ...

    "(" ...

    "+" ...

    "-" ...

    <INTEGER_LITERAL> ...

    <FLOATING_POINT_LITERAL> ...

    <NUMERIC_VALUED_PARAMETER> ...

    <NUMERIC_VALUED_PATH> ...

    "NOT" ...

    <COLLECTION_VALUED_PATH> ...

    <STRING_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "NOT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "NOT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "(" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <COLLECTION_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <STRING_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "CONCAT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "SUBSTRING" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <BOOLEAN_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> "=" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> ">" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> "<" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> "<>" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> ">=" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <DATETIME_VALUED_PATH> "<=" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <ENTITY_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <IDENTIFICATION_VARIABLE> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <NUMERIC_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "LENGTH" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "LOCATE" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "ABS" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "SQRT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "MOD" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "+" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" "-" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <INTEGER_LITERAL> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <FLOATING_POINT_LITERAL> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "(" <NUMERIC_VALUED_PARAMETER> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <COLLECTION_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <STRING_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "CONCAT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "SUBSTRING" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <BOOLEAN_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <DATETIME_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <ENTITY_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <IDENTIFICATION_VARIABLE> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <NUMERIC_VALUED_PATH> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "LENGTH" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "LOCATE" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "ABS" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "SQRT" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "MOD" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "+" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" "-" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <INTEGER_LITERAL> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <FLOATING_POINT_LITERAL> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "AND" <NUMERIC_VALUED_PARAMETER> ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" "OR" ...

    <DATETIME_VALUED_PATH> "IS" "NOT" "NULL" ")" ...

[wdfink]

What type is endDate?

Maybe you have to change to 'endDate = ?5 -1'.

But I suppose that the handling of weblogic and JBoss will be different.

You might try the add the query into the jboss deployment descriptor and check whether this works.

[sankhla123]

Type of endDate id java.sql.Timestamp and Moving to jboss deployment descriptor is not helping either. Also i tried to convert endDate to java.util.Date or java.sql.Date but none working

I guess date + 1 thing is only supported by Weblogic 8.1 not by jboss.

[wdfink]

Yep, might that it will work for the database but the parser of JBoss will not allow it (reason might that this will not work for all databases).

But endDate +1 >= X might be the same as endDate > X.

Unfortunately only if you use Date and not timestamp

But for me it is also not very sharp what will be the effect of +1 on a timestamp? ms/sec/minute/.../day??

So if you check a period the expression above migh fit your case.

[sankhla123]

yep, that's correct even i converted it to endDate > X which is logically same as endDate +1 >= X expression.

I have one more question if you can help on that. is LIKE operator is in EJB-QL case-sensitive ? I know I know JBossQL supports UCASE/LCASE and i can use that but i don't want my EJB QL to be server specific, I guess if it is not then i can have fully portable EJB.

Thanks for your help.

[wdfink]

No there is nothing to ignore case like you want .

You might use '_' for specific if you know whether a char can be upper or lowercase.

Also I see solutions like special views where the field is shadowed to simulate a to_lower() by database.