COALESCE ArrayIndexOutOfBoundException

[Johann NG]

Hi,

A great thanks to the Apache Derby Team!

I’m using Apache Derby 10.1.3.1 in an embedded mode with Apache Tomcat 5.0.28 and a 1.4.2_03 Java platform. I took the Apache Derby enterprise integration type described in Lance Bader’s article : http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0508bader/

 

I’ve got a problem to pass an SQL NULL argument from a JSP to a SELECT query  with COALESCE function .

 

My initial objective was to execute this query with  the JSTL sql tag library :

 

SELECT ID_ITM, ITM_NAME, ITM_CODE

    FROM ITEMS

    WHERE ITM_CODE = COALESCE(?,ITM_CODE )

 

ITM_CODE is a NUMERIC column, here’s the DDL script of the ITEMS table :

CREATE TABLE ITEMS (

        ID_ITM                BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,

        ITM_CODE              NUMERIC (22) NOT NULL,

        ITM_NAME              VARCHAR (50),

        ITM_LAST_MODIFIED     DATE         

    )

;

 

I got this message error :

: The exception 'java.lang.ArrayIndexOutOfBoundsException: -1' was thrown while evaluating an _expression_. SQLSTATE: XJ001: Java exception: '-1: java.lang.ArrayIndexOutOfBoundsException'. (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)

                 

 

When I searched in Derby’s documentation, I’ve found that COALESCE is not documented nor completely supported. 

Is it a resolved BUG for future release or is it necessary to open a new BUG issue?

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.