Details
Description
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.
Attachments
Attachments
Issue Links
- is duplicated by
-
DERBY-2445 Function VARCHAR truncates the results at 15 characters
-
- Closed
-