Purpose
The purpose of this page is to explain how to change the collation and codepage when rebuilding a SQL Anywhere database.
Introduction
In the example used in this document, the original database uses an OEM collation (codepage 850) and the rebuilt database uses an ANSI collation.
In some cases, accented characters, such as German umlauts, are not displayed correctly when an ANSI client (such as a Windows ODBC client without a translator) retrieves data from a database that uses an OEM codepage.
Given is a database originally created with an OEM collation based on codepage 850. (c:\oem.db).
Instructions
- Start the database with character set translation turned off.
dbeng16 -ct- c:\oem.db - Unload the database using an internal unload and an external reload. This means the generated reload.sql file has INPUT INTO statements instead of LOAD TABLE statements. LOAD TABLE does not make the server convert/translate data, 'INPUT INTO' does, if required.
dbunload -c "UID=dba;PWD=SQL;ENG=OEM" -ix -r c:\unload\reload.sql c:\unload\data - Create a new database using an ANSI collation. For example, if German umlauts are used and you want to have them sorted, the 1252DEU collation, based on codepage 1252 (the Windows default), is recommended.
dbinit -z 1252DEU c:\newdatabase.db - Stop the old database server and start the new database with character set conversion turned on.
dbeng16 -ct+ -n new-server c:\newdatabase.db - Load the data into the new database. You must specify the codepage that was used in the old database to force proper character set translation. In this example, the codepage used in the original database is codepage 850. You must also connect using the ODBC driver rather than jConnect.
dbisql -c "UID=dba;PWD=sql" -codepage 850 c:\unload\reload.sql - Stop the database and shut down the database server once the data is loaded into the new database.