Pages

7/26/2010

java.sql.SQLException: Incorrect string value

just wrote a program to crawl webpages and store the whole HTML page in MySQL database.
and when it ran, it popped out something similar to this

java.sql.SQLException: Incorrect string value: '\xF2\x9F\xB0\x98}\x0E...' for column 'text' at row 1

i did check what the html string that it imports but i can't find that weird code anywhere in the string.
because the error actually occurred in low-level in transfer data to MySQL. 

It's because it sending UTF-8 from Java to MySQL

then, i decided to change the "connection" from Java to MySQL to be something like this

conn = DriverManager.getConnection(
"jdbc:mysql://localhost/codesnippet?useUnicode=yes&characterEncoding=utf8&user=root&password=xxxxxx");

it still cause me the same error.
then i think it's because the string that i put in sql prepare statement that is not UTF-8
so i tried new String(htmlStr.getBytes(),"UTF-8") but it doesn't work, so i removed it out

finally, i changed the whole database into UTF-8 by alter schema, table, and even columns.
A little annoyance is, the model in MySQLWorkbench 5.2 doesn't change the column collation when you change the collation of schema and table. so i need to alter it manually. 

to show column's collation using this command
mysql> show full columns from table_name;

So, The solution for this is 
Change the connection to be UTF-8
and Change database and everything to be UTF-8

No comments:

Post a Comment