db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mitesh Meswani (JIRA)" <derby-...@db.apache.org>
Subject [jira] Created: (DERBY-1132) Truncation Error with Concat
Date Mon, 20 Mar 2006 18:28:58 GMT
Truncation Error with Concat
----------------------------

         Key: DERBY-1132
         URL: http://issues.apache.org/jira/browse/DERBY-1132
     Project: Derby
        Type: Bug
  Components: JDBC  
    Versions: 10.1.1.0    
 Environment: Solaris, Windwos, JDK 1.5
    Reporter: Mitesh Meswani


Consider the table
CREATE TABLE CUSTOMER_TABLE ( ID VARCHAR(255)   PRIMARY KEY NOT NULL, NAMEZ VARCHAR(255) ,
COUNTRY VARCHAR(255) )

Sql:
PreparedStatement ps = conn.prepateStatement ("SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  =
VARCHAR ( CAST (? AS VARCHAR(32672) ) || CAST (? AS VARCHAR(32672) ) )" );
ps.setString(1, "Alan E. ");
ps.setString(2, "Frechette");
ps.executeQuery()

Error:
"A truncation error was encountered trying to shrink VARCHAR 'Alan E. Frechette' to length
15."
getErrorCode()-1
getSQLState()22001 

Please note that
-The query executes ok against DB2 database
-The query executes ok if the total length of both the parameters bound is less than 15. That
is as follows
   //Total length of parameters bound = len("Alan E. Fre") = 11
   ps.setString(1, "Alan E. ");
   ps.setString(2, "Fre");
-The error occurs both with embedded and network mode of derby
-Omitting the casts as follows also gives the same error
   query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = VARCHAR( 'Frechette' || ? )
   error: SQLState: 22001 "A truncation error was encountered trying to shrink VARCHAR 'FrechetteAlan
E. ' to length 15"
-Using parameter markers for both the variables without cast like as follows results in error
   query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = ? || ?
   error: SQLState: 42X35 "It is not allowed for both operands of '||' to be ? parameters."
-Using parameter markers for only one variables without cast like as follows results in error
   query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ  = 'Frechette' || ?
   error:SQLState: 42818 "Comparisons between 'VARCHAR' and 'LONG VARCHAR' are not supported."

-It works to cast to VARCHAR(2000), but not VARCHAR(2001) or larger.  Regardless of length,
if it fails, the magic number is always 15. 

-- 
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


Mime
View raw message