aspose file tools*
The moose likes JDBC and the fly likes Special Character in DB2 taking 2 bytes to store Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Special Character in DB2 taking 2 bytes to store" Watch "Special Character in DB2 taking 2 bytes to store" New topic
Author

Special Character in DB2 taking 2 bytes to store

Haroon Chaudhary
Greenhorn

Joined: Oct 28, 2010
Posts: 6
In our application we send data like product, prices etc from a central system to a local store system.

The central System is using MySQL (Swedish/Finnish charaterset) database and the local store system is using DB2 (UTF8). when we send data we take data from central database and create a java object and send it to local store using serialization. On the local store the data is deserialized and
inserted into local Db2 database. the trouble is when we have special character like 'Ä' in the product description then it takes 2 bytes, the column in MySQL and DB2 is VARCHAR(30) the MySQL handles the 30 character data (which has special character 'Ä') but when it tries to insert this data in the Db2 then it throws an exception

ERROR::2012-08-08 01:24:23,786::uk.co.datafit.system.error.ServerException: ServerError COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT] SQL0302N The value of a host variable in the EXECUTE or OPEN statement is out of range for its corresponding use. SQLSTATE=22001


This error is thrown becuase a 30 character data with a special character takes 31 bytes to store but Db2 VARCHAR(30) will handle only 30 bytes.
Appraently changing the characterset for the databases is not an option. If anyone has faced similar issue before suggest a solution please.

Haroon Chaudhary
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Did you try to google DB2 varchar multibyte characters?

I've found this page. It looks like you should declare the column with MIXED DATA. I don't do DB2, so I don't know the exact syntax here (and I don't know whether that would cause the limit to be enforced in characters, not bytes - the documentation I've found is a bit obscure about this).
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Special Character in DB2 taking 2 bytes to store