File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes EJB and other Java EE Technologies and the fly likes EJB-QL input parameter problem. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "EJB-QL input parameter problem." Watch "EJB-QL input parameter problem." New topic

EJB-QL input parameter problem.

Benson Fung
Ranch Hand

Joined: Apr 10, 2003
Posts: 214
Dear all,
I got very simple EJB-QL statement and using CMP.

![CDATA[SELECT OBJECT(o) FROM CompanyVendorBean o WHERE o.CO_NUM = ?1 and o.VEND_NUM = ?2]]
I found that the input parameter must be upper case.
Do anyone know what the problem is? Can I change to case insensitive?
Sergiu Truta
Ranch Hand

Joined: Dec 16, 2003
Posts: 121
Unless you have the attributes declared as CO_NUM and VEND_NUM, you shouldn't have to call them using upper case.
Make sure you have them declared like co_num and vend_num...make sure they are not declared with upper case....also..if any of these fields represents a CMR field you should check the deployment descriptor and make sure that the CMR field <cmr-field-name> is not declared with upper case. I'm walking the path...
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Originally posted by Benson Fung:
I found that the input parameter must be upper case.
Do anyone know what the problem is? Can I change to case insensitive?

If you mean that the *data* is in uppercase and thus you need to uppercase the input parameters themselves (not the CMP field names in the EJB-QL, though those should be standard JavaBeans identifiers like coNum and vendNum to match the getCoNum() and getVendNum() accessors), you'll need to do one of the following.
  • Wait for EJB 2.1 compliant servers to implement the UPPER EJB-QL function.
  • Use your vendor's EJB-QL extensions if they implement UPPER.
  • Uppercase the parameter in a facade method before passing it to the finder.

  • If you need to perform a case-insensitive search against a mixed-case attribute, you may have to do a funky work-around to avoid using vendor extensions.
    If you're using Oracle and WebLogic, for example, you could create a function-based index on the column and use WebLogic's syntax for passing a select hint to Oracle. This way you won't have to uppercase the parameter. However, you're tied to both Oracle and WebLogic at that point.
    I got around this by adding another attribute that is the uppercase version of the attribute I want to search (email in my case). When email is set, I also set the uppercased version. The finder then searches on this new attribute, and I uppercase the parameter before feeding it to the finder. This way I'm not using any WL- or Oracle-specific features from my beans.
    I would have been tempted to use the select hint, but that would require embedding "'/* use <index> */'" in the EJB-QL. Since I'm using XDoclet to generate the finders, the EJB-QL is in the class's JavaDoc, and the */ would terminate the comment.
    I agree. Here's the link:
    subject: EJB-QL input parameter problem.
    It's not a secret anymore!