asked on

symbol being replaced by ? during data transfer

Hi
I have an MySQL 5.1 database which is set up to use Unicode (utf8) charactersets and collections.

I have uploaded an approximately equal symbol (Shmax ≈ Shmin) and when I view it in my first table it looks fine.

We then use an insert command ( Java and uses Hibernate API for persistence) to transfer the data to a second table which has also been set up with utf8 collections but when I view this table after the data transfer the data has now become Shmax ? Shmin

Can anyone tell me what is causing this behaviour and how I can get around it.

CEHJ

but when I view this table after the data transfer

Are you viewing it with the same application as the one you're viewing the source table with and is it set up in the same way?

jimyX

You have set your Database to handle Unicode, you need to do the same for your Java application.
I am not a Java expert but can suggest, try UTF8 Encode/Decode.

Nemetona

ASKER

When I view the contents of the table in MySQL workbench, the original table displays Shmax ≈ Shmin but the second table, after the data has been inserted, displays Shmax ? Shmin. If I manually update the second table then it will hold the value of Shmax ≈ Shmin.

I have also check the original table on a web page (.jsp) and it does show Shmax ≈ Shmin.

The second table displays Shmax ? Shmin

As far as I can tell the problem arises with the data insert command.

CEHJ

Can you please execute the following (change placeholder as appropriate) at the MySql client prompt and post the last meaningful line?

show create table target_table;

Open in new window

Nemetona

ASKER

This is the create table statement for the two tables I am using:

CREATE TABLE `Table1` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ANSWER_REF` varchar(11) DEFAULT NULL,
`QUESTION_ID` int(11) NOT NULL,
`ANSWER_TEXT` mediumtext NOT NULL,
`IS_CORRECT` int(11) NOT NULL,
`POINTS` int(10) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1822 DEFAULT CHARSET=utf8

CREATE TABLE `Table2` (
`ID` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID of the Assessment Answer',
`ANSWER_REF` varchar(10) DEFAULT NULL COMMENT 'Maps to the ID of the original Answer it was derived from',
`ASSESSMENT_QUESTION_ID` int(11) NOT NULL COMMENT 'Maps to the Assessment Question Id the answer belongs to',
`ANSWER_TEXT` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT 'A snapshot of the exact answer text with unicode',
`IS_CORRECT` int(11) NOT NULL COMMENT 'True if this assessment answer is correct',
`POINTS` int(11) NOT NULL COMMENT 'The points awareded if this assessment answer is selected',
`IS_SELECTED` int(1) DEFAULT NULL COMMENT 'Set to true if the delegate has selected this answer in the assessment',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=9187 DEFAULT CHARSET=utf8

ASKER CERTIFIED SOLUTION

CEHJ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CEHJ