Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » SQL Anywhere Server - Database Administration » Database configuration » International languages and character sets » International language and character set tasks


Changing a database from one collation to another

 Change collations

Changing a database to another collation requires a rebuild of the database. Collations are chosen at database creation time and cannot be changed.

  1. Start the database.

    START databasefile 
  2. Determine the character set for the existing database as follows:

    SELECT DB_PROPERTY( 'CharSet' );

    For early versions of SQL Anywhere, this property may not exist. The character set is also implied by the collation name. For example, collation 1252LATIN1 uses code page 1252.

    The character set in the existing database should be the same as, or compatible with, the database character set. If it is not, it is an excellent reason to rebuild the database, but requires great care in the rebuild process.

    In particular, if you have been using a database with collation 850LATIN1 with earlier versions of SQL Anywhere that either did not support character set conversion (versions 5 and earlier) or disabled it by default (versions 6 and 7), and if your client applications were normal Windows applications, you may have code page 1252 character data in your database that is expecting data to be in code page 850.

  3. Determine the character set for the data in the existing database as follows:

    UNLOAD TABLE mytable TO 'mytable_data_in_utf8.dat' ENCODING 'UTF-8';

    View the results in Notepad.

    If accented data is correct, then the character data in the database matches the Windows ANSI code page, which for English and other Western European languages is code page 1252. If the data appears correct in a DOS-based editor, then the character data matches the Windows OEM code page, which is likely 437 or 850.

  4. Unload the database as follows:

    If the data character set is incompatible with the database character set, it is critical that the data be unloaded without character set conversion. Depending on the version of SQL Anywhere being used, you can use the internal unload feature of dbunload, or manually unload the data using the UNLOAD TABLE statement.

  5. Create the new database, specifying the collations and character sets you want to use as follows:

    dbinit -z 1252LATIN1 c:\newdatabase.db 
  6. Stop the old database server and start the new database server as follows:

    dbeng12 -n new-server c:\newdatabase.db 
  7. Load the data into the new database.

    If the unloaded data and schema (reload.sql) match the character set of the computer used to do the reload, you can use the external reload option of dbunload. The data is automatically converted to the correct character set for the database.

    dbunload -ix c:\databasefile

    If the data's encoding does not match the character set of the database, and you are loading data using LOAD TABLE statements (internal reload), you must use the ENCODING clause; the database server does not, by default, perform character set conversion for data loaded using LOAD TABLE statements.

    If the data's encoding does not match the code page of the computer on which you are working, and you are loading using INPUT statements (external reload), you must use the ENCODING clause; otherwise, the database server assumes that the data is in the computer's native character set.

 See also