Re: [eclipselink-users] Querying on null parameter values

[bht@xxxxxxxxxxxxx]

Phillip,

You are one of probably thousands of developers who encounter this
type of issue. Your chances to get the someColumnName = null test to
work are fairly slim, because SQL dictates that that "IS NULL" must be
coded instead for null values.

There are varying outcomes with "=" even with the same database but
different drivers such as in Sybase native versus Sypacce over ODBC.
Alternatively, you can code in your named query:

select object(p) from Purchase p where :detail1 is null or p.detail1 =
:detail1

This is efficient, elegant, compact and correct if one accepts that
the database is smart enough to avoid the second test if the first
(null is null) is true.

Unfortunately, even this fails with EclipseLink 2.3.0 on postgresql or
derby.

This issue has just been discussed under the subject "Cannot pass null
parameter"

Please refer to

http://en.wikipedia.org/wiki/Java_Persistence_Query_Language#Examples

and

https://bugs.eclipse.org/bugs/show_bug.cgi?id=362414

I would suggest to:

1) study the testcase and file a bug with the providers of these
stupid SQL database engines

2) vote for the EclipseLink issue

Kind Regards,

Bernard


On Thu, 3 Nov 2011 16:33:31 -0400, you wrote:

>Hi all... I'm looking for a little assistance on using named queries
>using parameters that may have null values.  I'm positive this worked
>before but it's not working for me now and I'm wondering if I'm
>running into incorrect usage scenario or a bug.
>
>Basically I have an entity with some fields that are nullable and a
>named query that uses them in the query.  Example code:
>
>query annotation:
>@NamedQuery(name="Purchase.findByDetails", query="select p from
>Purchase p where p.detail1 = :detail1 and p.detail2 = :detail2 and
>p.detail3 = :detail3")
>
>The property declarations in the Purchase entity:
>private String detail1;
>private String detail2;
>private String detail3;
>
>Example query code:
>query = entityManager.createNamedQuery("Purchase.findByDetails");
>query.setParameter("detail1", detail1);
>query.setParameter("detail2", detail2);
>query.setParameter("detail3", detail3);
>purchases = query.getResultList();
>
>
>So when the content of detail1, 2, 3 parameters are null, the query
>fails to return records where the values of detail1/2/3 are null.
>This is surprising as I was positive that the provider would
>automatically handle the translation to database platform specific
>clauses that would use "is null" predicates if necessary.  This is NOT
>what happens with eclipselink 2.3.0 on postgresql or derby.  It simply
>doesnt return the results.
>
>Am I just doing this wrong and really have to dynamically build the
>JPQL query to use JPQL "is null" with null checks on the parameter
>values and all of that?  Or am I running into a bug?
>
>Thanks
>- Phillip
>_______________________________________________
>eclipselink-users mailing list
>eclipselink-users@xxxxxxxxxxx
>https://dev.eclipse.org/mailman/listinfo/eclipselink-users