DB2 and Parameter Substitution Problem

[indiefan23]

Hey again,

I have a question regarding some odd behavior I can't seem to find on the forums.

I'm using DB2 via a native JDBC connection and run a sql string like

select * from myTable where ${myParam} is not null

or

select * from myTable where myTable.myDateField > Date(${myParam})

Anyway, I can pretty easily get around the second one by just using java.sql.date parameters, but I'm trying to test for null params. Both scenarios won't let me generate the report and return SQL errors. Is there a special way I should be testing for null on DB2? Is it a bug? Can you not use parameters in DB2 functions?

[TomS]

Hi indiefan,

the first query won't work unless you have a column in your table which has the same name as the parameter value (this is, what your first query is saying)

The second query looks good, given, you always provide a parameter-value which can
be converted by the DB2-Date() Function into a real date.

What kind of type did you assign to the parameter in the PRD?
What value did you provide?

Best regards,

Tom

[indiefan23]

Hi indiefan,

the first query won't work unless you have a column in your table which has the same name as the parameter value (this is, what your first query is saying)

Sorry to take so long... it is? I thought that using the ${} syntax it would substitute my parameter value in for that, not look for a column in the table.

The second query looks good, given, you always provide a parameter-value which can
be converted by the DB2-Date() Function into a real date.

What kind of type did you assign to the parameter in the PRD?
What value did you provide?

Best regards,

Tom

The second one does not work either. I gave the value a date type but I can't get it to work with any type and have virtually tried them all save for like, int or what have you.

Really, I just want to know how to test for a null parameter in my SQL, or, use different SQL based on if a parameter is there or not. Thanks!

[Taqua]

Sorry to take so long... it is? I thought that using the ${} syntax it would substitute my parameter value in for that, not look for a column in the table.

Assume you feed the value "hello" into that parameter. Then your query would be:

WHERE "hello" is not null
(which would make sense to a human)

However, computer are not humans, and the SQL standard says that the IS NOT NULL condition works only on column names. Thus the JDBC driver rejects any attempt to use value-parameter in there.

To work with null-values, use one of the following constructs:

http://www.w3schools.com/SQL/sql_isnull.asp