[IPT] GBIF Case 1773: UTF8

[Burke Chih-Jen Ko (GBIF)]

Hi MIchaël,

Yes this time I have correct data to test. I changed from your script is to re-save the file in latin1, and add drop-table script from your previous dump. It was saved in UTF-8 despite the sql settings in the script are all latin1. The refined file is attached.

Then I reproduce the environment as the steps here:

1) Adjust MySQL server and client encoding settings to match yours:
The server and client connection show:
Server characterset:	latin1
Db     characterset:	latin1
Client characterset:	latin1
Conn.  characterset:	latin1

2) Create a database from the attached script, the database encoding is latin1.

3) Follow the normal procedure to create a SQL source in IPT. See "settings" screen shot.

4) Since JDBC driver detects source encoding automatically, the encoding setting in the bottom-left doesn't matter for SQL source. However, we're thinking about forcing the encoding as instructed. Please refer to mysql jdbc connector page[1].

5) The preview result on my side is attached as the result.png image. Närke is rendered correctly, whether your browser encoding is latin1 or UTF-8.

Since we assume everything on your side is latin1, if it still doesn't work, you can change a line in the jdbc.properties file of a *deployed* IPT, to force jdbc encoding:

6) In [Tomcat root]/webapps/ipt/WEB-INF/classes, you have jdbc.properties, at line 7, you have

mysql.url=jdbc:mysql://{host}/{database}

7) add the encoding setting to the connection, so it reads as
mysql.url=jdbc:mysql://{host}/{database}?characterEncoding=Cp1252

The encoding name used by JDBC driver is slightly different from MySQL[1, again].

Let me know if you can work out a refreshed result. Otherwise I suspect there was once UTF-8 encoding involved in certain steps while you establishing the database, therefore you might want to consider a clean start, using a small set of data, or the script you gave me, by;

1. Export all your database as SQL script, make sure the client you use(phpmyadmin?) also honours latin1 in every step.
2. Check the file encoding and the contents are exported correctly.
3. Import the SQL and try from IPT again.

Hope this helps. Do let us know if your problem is resolved.

Thanks,

Burke

[1] http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-charsets.html




-------------- next part --------------
A non-text attachment was scrubbed...
Name: settings.png
Type: image/png
Size: 50496 bytes
Desc: not available
Url : http://lists.gbif.org/pipermail/ipt/attachments/20110826/6d990c02/attachment-0002.png 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rc_test_as_is_default_latin1.sql
Type: application/octet-stream
Size: 30078 bytes
Desc: not available
Url : http://lists.gbif.org/pipermail/ipt/attachments/20110826/6d990c02/attachment-0001.obj 
-------------- next part --------------
A non-text attachment was scrubbed...
Name: rc_test.png
Type: image/png
Size: 106454 bytes
Desc: not available
Url : http://lists.gbif.org/pipermail/ipt/attachments/20110826/6d990c02/attachment-0003.png