Please see my comment inlined.
Johann NG <[EMAIL PROTECTED]> writes:
> I wanted to use COALESCE function with JSTL but it didn't worked (I reported
> my problem in this post "COALESCE ArrayIndexOutOfBoundException"). So I
> changed my query and used CASE function and to prevent conversion type
> problems I made a CAST:
>
> select ID_ITM, ITM_NAME, ITM_CODE from ITEMS
> where ITM_CODE = (CASE WHEN CAST(? as BIGINT) IS NULL
> THEN ITM_CODE ELSE CAST(? as BIGINT) END)
>
> In static mode with ij console, this query works perfectly when ?=NULL.
> But it.s not the case when ? is dynamically replaced with the JSTL SQL NULL
> value.
>
> At first I thought it was a SQL type problem so I tried the same query on
> different column type :
> select ID_ITM, ITM_NAME, ITM_CODE from ITEMS where ITM_NAME = (CASE WHEN
> CAST(? as VARCHAR(50)) IS NULL THEN ITM_NAME ELSE CAST(? as VARCHAR(50))
> END)
>
> select ID_ITM, ITM_NAME, ITM_CODE from ITEMS where ITM_LAST_MODIFIED =
> (CASE WHEN CAST(? as DATE) IS NULL THEN ITM_LAST_MODIFIED ELSE CAST(? as
> DATE) END)
>
> I tried the latest snapshot jdbc driver derbyclient.jar with no success.
> Error message:
> : Type is not supported. (Apache Derby snapshot-10.2.0.4 client driver)
> : Invalid data conversion: Parameter object type is invalid for requested
> conversion.( Apache Derby release 10.1.3.1 client driver)
>
> In the JSP file I.m passing SQL parameters with the JSTL SQL tag library :
>
>
>
> <c:set var="code" value="${param.code}"/> <c:if test="${not empty code}">
> <c:catch var="excpt">
> <fmt:parseNumber type="number" var="parsecode" value="${code}"
> integerOnly="false"/>
> </c:catch>
> . . .
> <sql:query var="ItemList">
> <%@ include file="./ItemList.sql"%>
> <sql:param value="${parsecode}"/>
> <sql:param value="${parsecode}"/>
> </sql:query>
>
>
> I made a log on the JSP entries to check parameters JSTL type. When the
> parsecode parameter is defined, the result is displayed correctly .
>
>
> I checked the JSTL specification, when parsecode parameter is not
> defined, the SQL NULL value is returned to the application, with Oracle
> database, this value is correctly treated. But with Apache Derby I.ve got
> this error :
>
>
>
> 2006-09-25 12:33:47,920 [http-8080-Processor22] ERROR util.error -
> Stack=javax.servlet.jsp.JspException: SELECT ID_ITM, ITM_NAME, ITM_CODE
> FROM ITEMS
> WHERE ITM_CODE =(CASE WHEN CAST(? as BIGINT) IS NULL
> THEN ITM_CODE ELSE CAST(? as BIGINT) END)
>
>
> : Invalid data conversion: Parameter object type is invalid for requested
> conversion. (Apache Derby release 10.1.3.1 client driver)
> : Type is not supported. (Apache Derby snapshot-10.2.0.4 client driver)
> at
> org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:220)
>
> So, from my investigations I think that it.s a JDBC driver bug. The JSTL
> SQL NULL value isn.t correctly treated. I tested the same query and JSP with
> Oracle Database and it works. I didn.t find any specific BUG issue but I
> found a post about a problem that might be the cause of this specific JSTL
> SQL NULL value BUG :
> http://mail-archives.apache.org/mod_mbox/db-derby-user/200603.mbox/[EMAIL
> PROTECTED]
>
> Is it the default behaviour for Apache Derby or is it a BUG that
> have already been reported and that I missed?
Can this be the same as DERBY-1904/DERBY-1938? Its looks like you have
hit the same issue..
Dag
