db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Johann NG <nskjoh...@yahoo.fr>
Subject CASE Invalid data conversion
Date Mon, 25 Sep 2006 13:12:17 GMT
 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 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?
  
  
 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. 
Mime
View raw message