DB2 cast issue in UPDATE with parameter marker

I'm having trouble with a piece of SQL code in an UPDATE statement on DB2 9.1 using JDBC. The idea is to concatenate a java String and a column value in the same row to set another column. So it's setting a varchar(800) to the concatenation of a short String and a varchar(80) column value in the same row. The varchar(80) value is not set in the update. The code to set the varchar(800) column is
This sometimes works, but on certain rows I always get a DB2 error -302 with SQLSTATE 22001. However, if I take out the parameter marker and hardcode the String in the SQL, it works without error.

Any idea what's going on here?

The error code description suggests that the concatenated String is too long, but the inputs which provoke the error are quite short (e.g. if I pass in 'foo/' as the parameter and the TR_LIB value is 'bar').

re: DB2 cast issue in UPDATE with parameter marker

Hi,

See if you can find something over here.

http://issues.apache.org/jira/browse/DERBY-1132

Raise this with IBM and update to latest Fixpack

Cheers, Vijay

re: DB2 cast issue in UPDATE with parameter marker

Thanks for the response. I'm not sure what Derby has to do with it - it's a DB2 problem. But I think the SQL casts the strings to a length of 1, which is not what I expected, and if the parameterized argument is longer than 1, it throws an Exception.

I think it will run okay if I take out the cast. The problematic SQL code was auto-generated, and a cast ("CHAR(?)") was added to fix a bug with other inputs. I was trying to fix the cast by trying a different syntax, but I think I should just avoid casting in this case and see if it works.

re: DB2 cast issue in UPDATE with parameter marker

Am still not able to replicate this in my environment. But couple of things that you can look out for which can help to know more :
1) What is the client version of DB2 UDB from where you are trying this code?
2) Examples of some input that works and for which you get a error?
3) Is there any difference in code page for your client and server ?

Preferably do a client trace for the sql statement and raise it with IBM for some fix/workaround.

Cheers, Vijay

re: DB2 cast issue in UPDATE with parameter marker

FYI, the code I'm using that is now working is

If you're trying to reproduce the original problem, all I can say is that the String values were fairly short (& to 20 characters, usually) and ended in "/".