Save Asian language problem

[liudi]

Hi, happy new year everyone!

I m using SmartGwt2.1 + MySQL5 + Java6, Ubuntu 10.04

our system requires chinese and other asian languages to be saved and displayed according to the locale of the user. The system has no problem to display and retrieve Chinese from the database and display, but cannot save Chinese characters.

From the server logs and Dev Console RPC tab, the Chinese characters are sent to the sever properly, but the SQL log shows question marks.

I m using SQL DataSource, do i need to configure the XML file to accept UTF8 encoding to do this or something else?

P.S. The JVM character set is UTF8

Thanks!

[Isomorphic]

Based on your description, SmartGWT is passing a Java String to the JDBC driver, so the problem is happening somewhere downstream of there - possibly the wrong encoding being used as the JDBC driver creates a network request. Look for MySQL JDBC driver or MySQL settings to find the issue.

However, it would also help to see the complete server log of the request. Note, this is always something you should post.

[liudi]

Thanks Iso.

I m sure the MySQL settings are correctly set to UTF8, and it can store Chinese characters.

Here's the logs:

RPC Tab DSRequest log, the pickup_address shows correct Chinese characters

{
    "actionURL":"http://127.0.0.1:8888/transact/sc/IDACall", 
    "showPrompt":true, 
    "prompt":"Saving form...", 
    "transport":"xmlHttpRequest", 
    "promptStyle":"dialog", 
    "bypassCache":true, 
    "data":{
        "values":{
            "parent_carrier":"2", 
            "dispatch_date":"2011-01-05", 
            "required_date":"2011-01-05", 
            "pickup_state":"ACT", 
            "pickup_time":"0:00", 
            "delivery_state":"ACT", 
            "delivery_time":"0:00", 
            "account_code":"23456", 
            "connote_number":"CMA00000130", 
            "service_level":5, 
            "service_type":4, 
            "pickup_address_code":"LD2", 
            "sender_name":"LIUDI2", 
            "pickup_address":"北京大街20号", 
            ......
        }, 
        "operationConfig":{
            "dataSource":"transact_consignment", 
            "repo":null, 
            "operationType":"add"
        }, 
        "componentId":"isc_ValuesManager_1", 
        "appID":"builtinApplication", 
        "operation":"transact_consignment_add", 
        "oldValues":{
            "parent_carrier":"2", 
            "dispatch_date":"2011-01-05", 
            "required_date":"2011-01-05", 
            "pickup_state":"ACT", 
            "pickup_time":"0:00", 
            "delivery_state":"ACT", 
            "delivery_time":"0:00"
        }
    }
}

MYSQL log shows question marks

INSERT INTO transact_consignment 
(delivery_time, pickup_address_code, pickup_address, delivery_postcode, delivery_address, pickup_suburb, required_date, parent_carrier, service_type, account_code, dispatch_date, sender_name, service_level, delivery_state, receiver_name, connote_number, delivery_suburb, pickup_state, delivery_address_code, pickup_postcode, pickup_time, parent_user) 
VALUES ('0:00', 'LD2', '????20?' ......

Now i manually change the pickup_address to Chinese, and do a retrieve

RPC Tab DSResponse log shows correct characters

[
    {
        endRow:1, 
        totalRows:1, 
        isDSResponse:true, 
        invalidateCache:false, 
        status:0, 
        startRow:0, 
        data:[
            {
                delivery_time:"0:00", 
                pickup_address_code:"LD2", 
                required_date:new Date(1294146000000), 
                service_type:4, 
                parent_carrier:2, 
                account_code:"23456", 
                dispatch_date:new Date(1294146000000), 
                sender_name:"LIUDI2", 
                delivery_state:"ACT", 
                parent_consignor:0, 
                pickup_time:"0:00", 
                parent_user:5, 
                pickup_address:"北京大街20号", 
                ......
            }
        ]
    }
]

And the correct Chinese characters are displayed on the web page with no problem.

[Isomorphic]

Still need the complete server log for the request. This will be required every time you have you ask any question that may be related to the server.

[liudi]

Just recalled one thing. When i setup JDBC connection for other projects, the URL is like (in Spring)


<property name="jdbcUrl" value="jdbc:mysql://localhost:3306/db_name?autoReconnect=true&amp;jdbcCompliantTruncation=false&amp;useUnicode=yes&amp;characterEncoding=UTF-8"/>


The same thing happens, if i remove the parameter bit of the URL, it can retrieve Chinese characters from the DB, but when save it gets all question marks.

is there equivalent settings in the server.properties file to enable UTF8 character encoding?


P.S. just saw this in the server.properties file:
sql.Transact.database.ansiMode: false
sql.Transact.driver.useUnicode: true

[Isomorphic]

Yes, use the Admin Console - there is an option for directly entering the JDBC URL instead of separate properties.

[liudi]

thanks, but could u be more specific? i can't find where that option on the Admin Console. Is it only available for HSQLDB?

[aquajax]

I am no SQL expert but when I have saved unicode to MS SQL Databases ( particularly for saving asian characters) I have had to use a N before the string literal in the insert statement so I would think the generated SQL should be something like
INSERT INTO transact_consignment
(delivery_time, pickup_address_code, pickup_address, delivery_postcode, delivery_address, pickup_suburb, required_date, parent_carrier, service_type, account_code, dispatch_date, sender_name, service_level, delivery_state, receiver_name, connote_number, delivery_suburb, pickup_state, delivery_address_code, pickup_postcode, pickup_time, parent_user)
VALUES ('0:00', 'LD2', N'北京大街20号' ......

Not sure if this is the same with mysql but it appears to be part of SQL-92
http://support.microsoft.com/kb/239530/en-us

Can you see the generated SQL in the eclipse console to confirm the SQL being generated by SmartGWT. I am a little confused as I thought you had to supply your own RPC code when working with mysql

[liudi]

aquajax, thanks for your help.

I m using SQL DataSource, the SQL is generated by SmartGwt server automatically when a 'save' operation is called. As in your case, you put a 'N' in front of the unicode string (outside the quotes), i m not sure if such special format can be generated or not.

i also have Spring+iBatis in my project, in the Spring Data Source configuration, the database URL has a couple of parameters to indicate the acceptance of UTF-8 encoding, i don't know if the sql.Transact.driver.useUnicode: true setting in the server.properties file of SmartGwt is equivalent to the database URL unicode parameter.

i still think it's my configuration problem, thanks for your reply anyway :)

[Isomorphic]

Again, in the Admin Console, you can directly enter the JDBC URL (in the field "jdbc URL") or you can set driver.url in server.properties (see the SQL Settings overview doc).

As far as setting useUnicode in server.properties, basically, anything subproperty you set on the "driver" in server.properties is applied to the JDBC driver object via reflection. So for example, if your sql.Transact.driver is "com.mysql.jdbc.jdbc2.optional.MysqlDataSource", setting driver.useUnicode to true means we'll attempt to call setUseUnicode(true) on this class.