cancel
Showing results for 
Search instead for 
Did you mean: 

Do we need to Upgrade to UCA Support for International Character Storage?

glenn_barber
Participant
1,904

We have an app that's been around since the very early days of SQL Anywhere. It is currently under the default collation 1252Latin1. Although the primary language for users is English - there are some cases where they need to store names and descriptions in languages like Chinese, Korean, and eastern European. Users are wanting to cut this and paste some of these names from excel into a form and have it preserve the presentation. I found that even in Sybase Central this will not work with our current collation. I have tried defining these columns as nvarchar's but pasting in the characters still sees it being converted to something unreadable on update.

It does seem to work if I create a SQL Anywhere database with UCA.

FWIW - The nvarchar solution does work for MS SQL Server ( we have a different app that uses this db).

SAP support thinks I might have to convert my entire database to UCA to support a few columns that need to support these characters. However the catch is there is no easy way to do it. The database wizard will not support designation of a different collation and unloading the database and loading into a new UCA database results in a variety of truncation errors (perhaps because of the required extra length needed because UCA char/varchar length definitions are in bytes).

We would really like to avoid changing our entire schema and collation to be able to store a few local language names.

Are there any more efficient solutions than redesigning tables and rebuilding everything under a UCA database by hand?

Accepted Solutions (0)

Answers (1)

Answers (1)

johnsmirnios
Participant

If you want to insert data into an NCHAR column, ensure that your application is using host variables to insert the data rather than inline string literals. SQLA SQL statements are processed in CHAR charset (1252 in your case). If you are using ODBC, bind the host variables using SQLWCHAR and bind as SQLWCHAR when fetching the data too. If you are using a different client interface, you may need other instructions and I can provide those if needed.

For example, if you do

insert into customer( name ) values( '(chinese characters)' )

it will not work since the whole SQL statement with be converted to 1252 before it is executed.

Instead, do

insert into customer( name ) values( ? )

Then bind the first parameter as SQLWCHAR & provide a value in UTF16.

glenn_barber
Participant
0 Kudos

Thanks John - a very helpful suggestion - although using PowerBuilder, our control of the update is limited to the datawindow behavior - which is likely controlled by the ODBC Connection and whether we designate to disable Bind characters.

I'll have to dive into the connection parameters we are using. There was some issue a while back related to the SQLAnywhere interface eating special characters in Rich Text where we had to change the interface. It is possible that the app might work with Bind Enabled, but our testing using Sybase Central fails because the ODBC connection parameters might be different.

If you have any insite on the PB interface using ODBC, that would be helpful.

BTW - I looked back at our applications connection configuration - we are using DisableBind=1 (even though that causes a problem with rich text). The reason is that many of our queries pass too many parameters and were failing until we turned off Bind Variables. This probably warrants a reinvestigation to see if this limitation has changed in updated SQLA interfaces.

johnsmirnios
Participant

Unfortunately, I have no knowledge of PB. Sorry.

Avoid setting charset parameters in your ODBC connection string. It will always default to the right thing on Windows: SQLCHAR will be OS ANSI charset and, if necessary, I believe the driver converts ANSI to/from database CHAR charset to distribute conversion load to clients rather than making the server do it all. SQLWCHAR will be UTF16 and the driver converts SQLWCHAR UTF16 to/from database NCHAR UTF8. Changing your charset on the connection string messes with that system.

I cannot speak for what Sybase Central does. I'd like to think it is a Unicode app and does inserts using host variables but I cannot confirm.

VolkerBarth
Contributor
0 Kudos

According to that FAQ, you might also use the UNISTR builtin function to create NChar literals. Don't know whethet that will work with PB.