Clues for "Incorrect string value: '\xCE\x94F508...' for column"

[Troy Murray]

I was wondering if anyone has any clues on this one.

I have an Excel spreadsheet that has some special characters, such as é.

I export the spreadsheet to the CSV format and specify to use UTF-8 text encoding.

I've created a database in MySQL using the UTF-8 character set and collation with the following command:

CREATE DATABASE `importdatabase` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */

Last I've set: 

Railo Web Administrator > Datasource > mydsn > "Use Unicode" to true

Railo Web Administrator > Datasource > mydsn > "Charset" = UTF-8

Now using file form field I upload the CSV file to the server, convert it to an array, and then loop over the array to insert it into this database.  The first 1024 records are created without any problems, but the 1025 has a special character in it and returns the following Railo error:

Incorrect string value: '\xCE\x94F508...' for column 'value' at row 1

Value in this case is one of the database fields and the actual text it's failing to insert is: 

'Diagnostic, ΔF508 and R117H with 5T/9T',

I think I've covered all of my character set bases here, but somethings still not correct.  I'm looking for any clues.

--
Troy Murray

[Troy Murray]

It's interesting to note that I'm able to perform this INSERT statement with the characters if using the MySQL command-line utility.  I'm wondering if this is a JDBC problem.

--
Troy Murray

[Troy Murray]

I tried upgrading the MySQL JDBC driver included with Railo 3.3.1 to the latest available, mysql-connector-java-5.1.18 ( Revision: tonci...@oracle.com-20110930151701-jfj14ddfq48ifkfq ), but the problem persists.

So the INSERT works from the command-line, which seems to point to the database not being the issue.

The INSERT doesn't work with either the included MySQL 5.0.x JDBC driver or the upgraded MySQL 5.1.18 JDBC driver. 

--
Troy Murray

[Troy Murray]

I've found the problem.

It would seem that while the database is set to UTF-8 the table was being created specifically with latin1 as the character set.  Once I changed this to UTF-8 then I was able to insert the record correctly from Railo via JDBC.  Yet it seems strange to me that this worked from the MySQL command-line.

-t

--
Troy Murray

[Cathy Shapiro]

Hi Troy,

I'm glad you followed up on your question, even though there were no answers. I've had similar problems with MySQL and found this helpful:

http://dev.mysql.com/doc/refman/5.0/en/charset-database.html

 

--
You received this message because you are subscribed to the Google Groups "ColdFusion on Wheels" group.
To post to this group, send email to cfwh...@googlegroups.com.
To unsubscribe from this group, send email to cfwheels+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/cfwheels?hl=en.

--
Flash Pro Design
4646 Poplar, Suite 517
Memphis, TN 38117

Phone: (901) 767-8767
Fax: (901) 685-9054

http://www.flashprodesign.com