Retrieving latin1 encoded results with JDBC

Question

I am trying to retrieve result sets from a MySQL database sing JDBC which is then used to generate reports in BiRT. The connection string is set up in BiRT.

The database is latin1:

SHOW VARIABLES LIKE 'c%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     | 
| character_set_connection | latin1                     | 
| character_set_database   | latin1                     | 
| character_set_filesystem | binary                     | 
| character_set_results    | latin1                     | 
| character_set_server     | latin1                     | 
| character_set_system     | utf8                       | 
| character_sets_dir       | /usr/share/mysql/charsets/ | 
| collation_connection     | latin1_swedish_ci          | 
| collation_database       | latin1_swedish_ci          | 
| collation_server         | latin1_swedish_ci          | 
| completion_type          | 0                          | 
| concurrent_insert        | 1                          | 
| connect_timeout          | 5                          | 
+--------------------------+----------------------------+

So I have been trying to correct the strange looking encoding results that are returned (German characters). I thought it would make sense to us the "characterSetResults" property to retrieve the result set as "latin1" like this:

jdbc:mysql://localhost:3306/statistics?useUnicode=true&characterEncoding=latin1&characterSetResults=latin1

This connection string fails and by deduction I have discovered that it is the property:

characterSetResults=latin1

is causing the connection to fail. The error is a long java error which means little to me. It starts with:

org.eclipse.birt.report.data.oda.jdbc.JDBCException: There is an error in get connection, Communications link failure

Last packet sent to the server was 38 ms ago..
    at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.doConnect(JDBCDriverManager.java:262)
    at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.getConnection(JDBCDriverManager.java:186)
    at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.tryCreateConnection(JDBCDriverManager.java:706)
    at org.eclipse.birt.report.data.oda.jdbc.JDBCDriverManager.testConnection(JDBCDriverManager.java:634)
    at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:120)
    at org.eclipse.birt.report.data.oda.jdbc.ui.util.DriverLoader.testConnection(DriverLoader.java:133)
    at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.testConnection(JDBCSelectionPageHelper.java:687)
    at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper.access$7(JDBCSelectionPageHelper.java:655)
    at org.eclipse.birt.report.data.oda.jdbc.ui.profile.JDBCSelectionPageHelper$7.widgetSelected(JDBCSelectionPageHelper.java:578)
    at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:234)

If I change this to:

 characterSetResults=utf8

the connection string connects without errors, but the encoding issue remains.

Does anyone know the correct way to retrieve latin1? And yes, I know UTF8 is the thing to use, but this is not my database....

Thank you for reading this, Stephen

I have the same probleam. Do you resolve it ? – Geln Yang Dec 23 '11 at 06:22 — Geln Yang, Dec 23 '11 at 06:22

score 0 · Answer 1 · answered Oct 28 '15 at 06:46

When specifying character encodings on the client side, use Java-style names(Mysql connector-j-reference-charsets).So it is supposed to work by using jdbc:mysql://localhost:3306/statistics?useUnicode=true&characterEncoding=utf-8&characterSetResults=Cp1252

score 0 · Answer 2 · answered Apr 05 '11 at 14:38

0

After some digging, have you tried characterSetResults=ISO8859_1? This is equivalent to latin1 and there is evidence MySQL handles this much better.

I do not have a DB to test this on, but it looks form what I read to be spot-on for what you need.

answered Apr 05 '11 at 14:38

MystikSpiral

5,018
27
22

I am afraid that did not work. I think the only thing for it is to convert the database to use UTF8 by dumping it and then importing it again again into a database whose UTF8 encoding is defined in the `CREATE DATABASE` clause. – LostInTheWoods Apr 07 '11 at 10:17

Retrieving latin1 encoded results with JDBC

2 Answers2