DB2 Select Character Issues

Question

I am trying to select data from a DB2 database managed by another person so I can insert it into my SQL database managed by me for data manipulation. I am using a java program to do this and the program has tested successfully on test data. However, I am getting errors because of strange symbols in the db2 data. Here is my error log.

Incorrect string value: '\xC2\x97D #5...' for column 'Name' at row 1
java.sql.SQLException: Incorrect string value: '\xC2\x97D #5...' for column 'Name' at row 1
INSERT INTO `Temp_Equipment_Inventory`.`PC_Table10i` SET `Account_No`='1019TJ148001',`Inventory_No`='569931',`Building_No`='0060',`Location`='CLASSRM',`FYYR_No`='2004',`Cost`='635.00',`Name`='MICROPHONE LAVALIER WIRELESS  (ISCÂD #5290)                 SHURE MODEL ULXP14/85                                       ',`CDCATY`='   ',`CDSRCE`='M',`FLDCAL`=' ',`CDACQN`='G',`FLOWNR`='Y',`FLSHAR`=' ',`CDDELT`='00',`CNYTDT`='00',`NOPURO`='6870607-01 ',`NOPIMO`='01',`CDPREI`='E',`Original_Amount`='155.00',`Serial_Code`='0309040351          ',`CDCOMP`=' ',`NOCHECK`='680146 ',`CDCOMM`='3651400',`Last_Update`='2008-07-18',`CDDEPT`='148',`Room_No`='0300 ',`Date_Scanned`=NULL,`Date_Acquired`='2004-03-09',`Manufacturer_Name`='SHURE       ',`Expiry_Date`=NULL

As you can see, the Name column has the data (ISCÂD #5290) with the funny  throwing the error. However, when I browse the data in the db2 table, this  does not appear.

I have set the mysql tables to UTF-8 unicode ci.

I cannot edit the db2 database since it is not managed by me.

What else can I do to try and get around this  symbol in my data?

Answer 1

\xC2\x97 is the UTF-8 byte sequence for encoding unicode character — (the em dash)

It seems like the java code has converted the UTF-8 data to UTF-16 (the native java encoding) without decoding it. Then the insert is failing because the UTF-16 code points for the characters  and em dash are actually invalid UTF-8 code points.

So perhaps the solution is to decode what is read in DB2 with the UTF-8 codec to obtain the proper UTF-16 representation before doing the insert, or if the target database requires UTF-8 encoding, then perhaps the best solution is not to go through java.String at all but just read and write binary data.

Answer 2

What else can I do to try and get around this  symbol in my data?

1. What is your test data? Is it tested for the  -cases?

2. Make a dummy copy of a similar database or take a sample of the database at the location of the problem. Replace the character with \^A.

I still believe that the problem is in your java -code that you are not parsing things right. Try python or simpler code with Java to do the same thing.