[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439894 ] Cédric Gérard commented on DERBY-1904: --------------------------------------
OK, for me too. Thanks for your explaination. It is clear. Thanks to open the improvement as you suggest. I will ask and open the setObject with 3 args improvement to JSTL team on my side. I was thinking that Derby database will be the only one Java database because of its pakaging with Java 6. For the moment, i will used MySql to replace my Oracle database in place of Derby that are not already compatible with JSTL (J2EE 1.5). Thanks a lot. Cédric Gérard > COALESCE with NULL parameter problem > ------------------------------------ > > Key: DERBY-1904 > URL: http://issues.apache.org/jira/browse/DERBY-1904 > Project: Derby > Issue Type: Bug > Components: JDBC, SQL > Affects Versions: 10.1.3.1 > Environment: Windows 2000 SP4 > JSTL 1.0.6 on J2EE 1.3 > Tomcat 5.0.28 using a SUN JVM 1.4.2_03 > Reporter: Cédric Gérard > Priority: Blocker > Attachments: dblook.log, stackfromderby.log > > > Hi, > 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 > ); > When ITM_CODE is not set in my application, JSTL bind the parameter as null. > When ITM_CODE is set, it works. > > I got this message error : > : Invalid data conversion: Parameter object type is invalid for requested > conversion. (Apache > Derby release 10.1.3.1 client driver) > => We try to use CAST(? AS NUMERIC) in place of ?. We obtain the same error > when NULL is passed. > : 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) > With ij, we have the same problem (but not the same message) > ij> SELECT ID_ITM, ITM_NAME, ITM_CODE > FROM ITEMS > WHERE ITM_CODE = COALESCE(NULL,ITM_CODE ); > ERROR 42X01: Syntax error: Encountered "NULL" at line 3, column 31. > ij> SELECT ID_ITM, ITM_NAME, ITM_CODE > FROM ITEMS > WHERE ITM_CODE = COALESCE(NULLIF(1,1),ITM_CODE ); > ID_ITM |ITM_NAME > |ITM_CODE > ------------------------------------------------------------------------------------------------ > ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while > evaluating an expression. SQLSTATE: XJ001: Java exception: ': > java.lang.NullPointerException'. > ij> SELECT ID_ITM, ITM_NAME, ITM_CODE > FROM ITEMS > WHERE ITM_CODE = COALESCE(CAST(NULLIF(1,1) AS BIGINT), ITM_CODE); > ID_ITM |ITM_NAME > |ITM_CODE > ------------------------------------------------------------------------------------------------ > ERROR 38000: The exception 'java.lang.NullPointerException' was thrown while > evaluating an expression. SQLSTATE: XJ001: Java exception: ': > java.lang.NullPointerException'. > We have the same errors using 10.2.0.4 snapshot. > The coalesce function should accept NULL parameter. > Of course, My sample is very simple and I have n search criteria; so I don't > want to create 2^n SQL queries to deal with null or not null criterion. > I try to replace the coalesce function with a CASE statement but I > encountered the same problems... > So I don't actually have any workaround. > Regards, > Cédric -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
