ASA User's Guide

Home

ASA User's Guide

Part 1 Working with Databases

Chapter 11: International Languages and Character Sets

International language and character set tasks

This section groups together the tasks associated with international language and character set issues.

Finding the default collation

If you do not explicitly specify a collation when creating a database, a default collation is used. The default collation depends on the operating system you are working on.

To find the default collation for your machine:

Start Interactive SQL. Connect to the sample database.

Enter the following command:

SELECT PROPERTY( 'DefaultCollation' )

The default collation is returned. You can find information about this collation in Supplied collations .

Configuring your character set environment

This section describes how to set up your computing environment so that character set issues are handled properly. If you set your locale environments properly, then you do not need to explicitly choose collations for your databases, and you do not need to turn on character set translation between client and server.

To configure your character set environment:

Determine the default locale of each computing platform in your environment. The default locale is the character set and language of each computer. On Windows operating systems, the character set is the ANSI code page.

For how to find locale information, see Determining locale information .

Decide whether the locale settings are appropriate for your environment.

For more information, see Understanding collations .

If the default settings are inappropriate, decide on a character set, language, and database collation that matches your data and avoids character set translation.

For more information, see Avoiding character-set translation .

Set locales on each of the machines in the environment to these values.

For more information, see Setting locales .

Create your database using the default collation. If the default collation does not match your needs, create a database using a named collation.

For more information, see Creating a database with a named collation , and Changing a database from one collation to another .

When choosing the collation for your database,

Choose a collation that uses a character set and sort order appropriate for the data in the database. It is often the case that there are several alternative collations that meet this requirement, including some that are OEM collations and some that are ANSI collations.
There is a performance cost, as well as extra complexity in system configuration, when you use character set translation. Choose a collation that avoids the need for character set translation.
You can avoid character set translation by using a collation sequence in the database that matches the character set in use on your client machine operating system. In the case of Windows operating systems on the client machine, choose the ANSI character set.
For information, see Avoiding character-set translation .

Determining locale information

You can determine locale information using system functions.

For a complete list, see System functions .

To determine the locale of a database server:

Start Interactive SQL, and connect to a database server.

Execute the following statement to determine the database server character set:

SELECT PROPERTY( 'CharSet' )

The query returns one of the supported character sets listed in Character set labels .

Execute the following statement to determine the database server language:

SELECT PROPERTY( 'Language' )

The query returns one of the supported languages listed in Language label values .

Execute the following statement to determine the database server default collation:

SELECT PROPERTY( 'DefaultCollation' )

The query returns one of the collations listed in Supplied collations .

Notes

To obtain client locale information, connect to a database server running on your current machine.

To obtain the character set for an individual database, execute the following statement:

SELECT DB_PROPERTY ( 'CharSet' )

Setting locales

You can use the default locale on your operating system, or explicitly set a locale for use by the Adaptive Server Anywhere components on your machine.

To set the Adaptive Server Anywhere locale on a computer:

If the default locale is appropriate for your needs, you do not need to take any action.

To find out the default locale of your operating system, see Determining locale information .

If you need to change the locale, create a SQLLOCALE environment variable with the following value:

Charset=cslabel;Language=langlabel;CollationLabel=colabel

where cslabel is a character set label from the list in Character set labels , langlabel is a language label from the list in Language label values , and CollationLabel is taken from the list in Understanding collations , or is a custom collation label.

For information on how to set environment variables on different operating systems, see Setting environment variables .

Creating a database with a named collation

You may specify the collation for each database when you create the database. The default collation is inferred from the code page and sort order of the database server's computer's operating system.

To specify a database collation when creating a database (Sybase Central):

You can use the Create Database wizard in Sybase Central to create a database. The wizard has a Collation Sequence page where you choose a collation from a list.

To specify a database collation when creating a database (Command line):

List the supplied collation sequences:

dbinit /l

The first column of the list is the collation label, which you supply when creating the database.

437LATIN1  Code Page 437, Latin 1, Western
437ESP     Code Page 437, Spanish
437SVE     Code Page 437, Swedish/Finnish
819CYR     Code Page 819, Cyrillic
819DAN     Code Page 819, Danish
819ELL     Code Page 819, Greek
...

Create a database using the dbinit utility, specifying a collation sequence using the

-z

option. The following command creates a database with a Greek collation.

dbinit -z 869ELL mydb.db

To specify a database collation when creating a database (SQL)

You can use the CREATE DATABASE statement to create a database. The following statement creates a database with a Greek collation:

CREATE DATABASE 'mydb.db'
COLLATION '819ELL'

Starting a database server using character set translation

Character set translation takes place if the client and server locales are different, but only if you specifically turn on character set conversion on the database server command line.

To enable character-set translation on a database server:

Start the database server using the

-ct

command-line option. For example:

dbsrv6 -ct asademo.db

Using ODBC code page translation

Adaptive Server Anywhere provides an ODBC translation driver. This driver converts characters between OEM and ANSI code pages. It allows Windows applications using ANSI code pages to be compatible with databases that use OEM code pages in their collations.

Not needed if you use ANSI character sets

If you use an ANSI character set in your database, and are using ANSI character set applications, you do not need to use this translation driver.

The translation driver carries out a mapping between the OEM code page in use in the "DOS box" and the ANSI code page used in the Windows operating system. If your database uses the same code page as the OEM code page, the characters are translated properly. If your database does not use the same code page as your machine's OEM code page, you will still have compatibility problems.

Embedded SQL does not provide any such code page translation mechanism.

To use the ODBC translation driver:

In the ODBC Administrator, choose Add to create a new Adaptive Server Anywhere data source or Configure to edit an existing Adaptive Server Anywhere data source.

On the ODBC tab of the ODBC Configuration for Adaptive Server Anywhere window, click Select and choose Adaptive Server Anywhere 6.0 Translator from the list of translation drivers.

Using character set translation for Sybase Central and Interactive SQL

Interactive SQL and Sybase Central both employ internal OEM to ANSI code page translation if the database uses an OEM character set. As with the ODBC translation driver, there is an assumption that the OEM code page on the local machine matches the data in the database.

To turn off character set translation in Interactive SQL:

Set the Interactive SQL option CHAR_OEM_Translation to a value of OFF.

SET OPTION CHAR_OEM_TRANSLATION = 'OFF'

For more information on OEM to ANSI character set translation in Interactive SQL, see CHAR_OEM_TRANSLATION option .

Creating a custom collation

If none of the supplied collations meet your needs, you can modify a supplied collation to create a custom collation. You can then use this custom collation when creating a database.

For a list of supplied collations, see Supplied collations .

To create a custom collation:

Decide on a starting collation

You should choose a collation as close as possible to the one you want to create as a starting point for your custom collation.

For a listing of supplied collations, see Understanding collations . Alternatively, run dbinit with the

-l

(lower case L) option:

dbinit -l

Create a custom collation file

You do this using the Collation utility. The output is a collation file.

For example, the following statement extracts the 1252LATIN1 collation into a file named mycol.col:

dbcollat -z 1252LATIN1 mycol.col

Edit the custom collation file

Open the collation file (in this case mycol.col) in a text editor.

Change the name of the collation

The name of the collation is specified on a line near the top of the file, starting with

Collation

. You should edit this line to provide a new name, The name you need to change is the second word on the line: in this case 1252LATIN1.

The other entries on this line are descriptive only, and do not need to be changed.

Change the collation definition

Make the changes you wish in the custom collation file to define your new collation.

For information on the collation file contents and format, see Collation internals .

Convert the file to SQL scripts

You do this using the dbcollat command-line utility using the

-d

switch.

For example, the following command line creates the mycustmap.sql file and mycustom.sql files from the mycol.col collation file:

dbcollat -d mycol.col mycustmap.sql mycustom.sql

Add the SQL scripts to the scripts in your installation

The scripts used when creating databases are held in the scripts subdirectory of your Adaptive Server Anywhere installation directory. Append the contents of mycustmap.sql to custmap.sql, and the contents of mycustom.sql to end of custom.sql.

The new collation is now in place, and can be used when creating database.

Creating a database with a custom collation

If none of the supplied collations meet your needs, you can create a database using a custom collation. The custom collation is used in indexes and any string comparisons.

To create a database with a custom collation:

Create a custom collation

You must have a custom collation in place to use when creating a database.

For instructions on how to create custom collations, see Creating a custom collation .

Create the new database

Using the Initialization utility, specifying the name of your custom collation.

For example, the following command line creates a database named newcol.db using the custom collation sequence newcol.

dbinit -z newcol temp.db

You can also use the Initialization utility from Sybase Central.

Changing a database from one collation to another

If you wish to change your database from one collation to another, perhaps to avoid the need for character set translation across your setup, you can do so by unloading and reloading your data. It is not possible to change the collation in an existing database,because al the indexes would become invalid.

Another reason that you may need to change collations is if you find that the character set you are using does not match the collation in your database.

To convert database data from one collation to another:

Unload the data from the source database using the Unload utility or from specific tables using the Interactive SQL OUTPUT statement.

This produces a reload.sql file and a set of data files in the character set of the source database.

For information on the Unload utility, see The Unload utility .

Create a target database with the appropriate collation using the Initialization utility.

For information on specifying collations when creating databases, see Creating a database with a named collation .

Start the target database using a database server running with the

-ct

switch. This is to enable the database server to carry out character set translation into the character set of the target database.

For more information, see Starting a database server using character set translation .

Create the SQLLOCALE environment variable with the CHARSET parameter set to the character set of the source database. If the character set of the data does not match the collation of the source database, use

-ct

and a SQLLOCALE based on the character set of the data, not on the old collation.

For more information, see Setting the SQLLOCALE environment variable .

Start Interactive SQL and connect to the server started in step 3.

From Interactive SQL, use the READ statement to run the reload.sql script, or use the INPUT statement to load the data files created in step 1. The database server translates the character set of the data transferred to the target database collation defined when the database was created in step 2.


	Expand Search