[ http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439557 ] A B commented on DERBY-1904: ----------------------------
Thanks for continuing to investigate this problem, Cédric. > I can confirm that in your JDBC Client driver source, the case where arg is > null in the > method setObject(idx, arg) is not treated. Your should call internally in > this case the > method setNull(idx, type); The problem with this approach is that we don't know what "type" should be. If the object in setObject(idx, arg) is a Java null, how does one figure out the corresponding SQL type? I know very (very) little about what JDBC expects here, but I did look at the JDBC API for 1.4.2 and the description of setObject(parameterIndex, x) says the following: "The JDBC specification specifies a standard mapping from Java Object types to SQL types. The given argument will be converted to the corresponding SQL type before being sent to the database. [...] This method throws an exception if there is an ambiguity, for example, if the object is of a class implementing more than one of the interfaces named above." Does JDBC specify a mapping for a typeless null Java object? If not, that seems to suggest "ambiguity" to me and hence Derby would be doing the right thing by throwing an exception. But again, I don't know enough about JDBC to say for sure. Note that there is another JDBC call, setObject(parameterIndex, x, targetSqlType), which allows the caller to explicitly give a target SQL type, thus removing ambiguity. In that case Derby correctly binds the parameter and executes without error (I wrote up a quick test to verify)... > 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
