finder that checks for zero length parameter does not work?

davilch Nov 7, 2003 11:32 AM

Looking for a strategy to handle searches where some of the parameters may be null or empty. I have seen this question before but no answers. I tried the following simple case where I check for a zero length input string...

<query-method>
<method-name>findByStatus</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql>
<![CDATA[
SELECT OBJECT(ab)
FROM ApplicationBean ab
WHERE ((LENGTH(?1) = 0) OR (ab.status = ?1))]]>
</ejb-ql>

If I call this with a status that is in the db it works fine. If I call it with a zero length status I get no rows back. Anyone have thoughts on why this won't work?

[I am using jboss 3.2.2 and oracle 9i]

1. Re: finder that checks for zero length parameter does not wo

raja05 Nov 9, 2003 6:49 PM (in response to davilch)

Im not sure if you can use functions in a WHERE clause. Shouldnt it be easier to do the check in the Caller?

-Raj
Actions
2. Re: finder that checks for zero length parameter does not wo

davilch Nov 10, 2003 10:34 AM (in response to davilch)

You could check the parameter prior to calling the sql but that won't scale well. The scenario I have in mind is where there would be multiple parameters to the sql, any or all of which might be null/empty. I would like to have only one sql statement that could handle any combination. This is an easy thing to do in oracle pl/sql. Not so much in ejb-ql.

Functions are legal in a WHERE clause and I am sure that if I was checking the length of a column that it would work fine. Just trying to get some insight as to whether what I am trying to do should or should not work.
Actions

Go to original post