Null values in namedQuery

General Discussion
1

I have a named UPDATE Query to do an SQL INSERT.
It works great when all parameters have values but, I get errors when passing NULL values to columns that allow Nulls.

Columns in the table include:
…
DATA_TYPE, CHAR, NULLABLE=No
NUMERIC_VALUE, NUMBER, NULLABLE=Yes
DATE_VALUE, DATE, NULLABLE=Yes
…

Depending on the value of DATA_TYPE (either ‘D’ or ‘N’), one of those parameters will be NULL.
Even though the database allows the columns to have NULLs, the NamedQuery throws exceptions when either parameter is passed a NULL value:
 java.sql.SQLSyntaxErrorException: ORA-01722: invalid number
 java.sql.SQLSyntaxErrorException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
For NULL Number or NULL Date respectively.

I was able to work around the invalid number by changing the parameter Data Type to string and changing the SQL query to recast the string as a number with:
 to_number(:numberValue)

I’m still working on the string TO_DATE conversions to handle a NULL date.

Are there better ways of handling NULL parameters with Named Queries?
Is that a possible future enhancement?

2

Here’s what finally worked for the date:

Change the parameter type from DateTime to String, then change the query for the date field to be:
 (SELECT CASE WHEN :dateValue IS NULL THEN TO_DATE(NULL) ELSE TO_DATE(:dateValue,‘YYYY-MM-DD HH24:MI:SS’) END FROM DUAL),

This works well as long as the datestring is formatted correctly before calling the NamedQuery.
It moves responsibility for date formatting to the caller instead of letting the NamedQuery manage the date as a date but, functional consistency is king and this fits the bill.
Note: This is Oracle syntax; syntax for other databases will be different.

2 Likes
3

Thanks, that was helpful for me.

I was dealing with a situation where I wanted a named query to default to return unfiltered results if a date range was not entered and I was having similar results.