You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Johann NG <ns...@yahoo.fr> on 2006/09/25 15:12:17 UTC
CASE Invalid data conversion
Hi,
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 its 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 Im 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 Ive 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 its a JDBC driver bug. The JSTL SQL NULL value isnt correctly treated. I tested the same query and JSP with Oracle Database and it works. I didnt 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/%3c4405E9E2.9030201@apache.org%3e
Is it the default behaviour for Apache Derby or is it a BUG that have already been reported and that I missed?
Regards,
J.Bert NG SING KWONG
---------------------------------
Découvrez un nouveau moyen de poser toutes vos questions quelque soit le sujet ! Yahoo! Questions/Réponses pour partager vos connaissances, vos opinions et vos expériences. Cliquez ici.
Re: CASE Invalid data conversion
Posted by "Dag H. Wanvik" <Da...@Sun.COM>.
Please see my comment inlined.
Johann NG <ns...@yahoo.fr> 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/%3c4405E9E2.9030201@apache.org%3e
>
> 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