[
http://issues.apache.org/jira/browse/DERBY-1904?page=comments#action_12439441 ]
Cédric Gérard commented on DERBY-1904:
--------------------------------------
I have some new stuff for you.
When I use the Derby 10.1.2.6 Client Driver for all my clients (so it was not
the case previously). Its works with ij but a cast is needed ;-(
But my problem is now when using JSTL with the same query. It is impossible to
pass null argument to this.
I activate the stack trace from the JDBC driver using tracefile parameter and I
obtain the following error log when my parameter is null
[EMAIL PROTECTED] BEGIN TRACE_PARAMETER_META_DATA
[EMAIL PROTECTED] Parameter meta data for statement [EMAIL PROTECTED]
[EMAIL PROTECTED] Number of parameter columns: 1
[EMAIL PROTECTED] Column 1: { label=1, name=1, type name=BIGINT, type=-5,
nullable=1, precision=19, scale=0, schema name=, table name=, writable=false,
sqlPrecision=19, sqlScale=0, sqlLength=8, sqlType=493, sqlCcsid=0, sqlName=1,
sqlLabel=null, sqlUnnamed=0, sqlComment=null, sqlxKeymem=0, sqlxGenerated=0,
sqlxParmmode=1, sqlxCorname=null, sqlxName=null, sqlxBasename=null,
sqlxUpdatable=0, sqlxSchema=null, sqlxRdbnam=, internal type=-5, }
[EMAIL PROTECTED] { sqldHold=1, sqldReturn=0, sqldScroll=0, sqldSensitive=0,
sqldFcode=0, sqldKeytype=0, sqldRdbnam=, sqldSchema=null }
[EMAIL PROTECTED] END TRACE_PARAMETER_META_DATA
[derby][Time:1159872072810][Thread:[EMAIL PROTECTED] prepareStatement ()
returned [EMAIL PROTECTED]
[derby][Time:1159872072810][Thread:[EMAIL PROTECTED] setObject (1, null) called
[derby][Time:1159872072810][Thread:[EMAIL PROTECTED] isClosed () returned false
[derby][Time:1159872072810][Thread:[EMAIL PROTECTED] isClosed () returned false
[derby] BEGIN TRACE_DIAGNOSTICS
[EMAIL PROTECTED] java.sql.SQLException
[EMAIL PROTECTED] SQL state = XJ021
[EMAIL PROTECTED] Error code = 20000
[EMAIL PROTECTED] Message = Type is not supported.
[EMAIL PROTECTED] Stack trace follows
org.apache.derby.client.am.SqlException: Type is not supported.
at org.apache.derby.client.am.PreparedStatement.setObject(Unknown
Source)
at
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.setObject(DelegatingPreparedStatement.java:165)
at
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.setParameters(QueryTagSupport.java:295)
at
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:214)
...
[derby] END TRACE_DIAGNOSTICS
So with 10.2.1.6, it works SQL side but still not in the JDBC layer ...
Can you help me to solve this issue ?
Regards,
Cédric
> 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
