Specifying Defaults

Go Up to Character Sets and Collation Orders

This section describes the mechanics of specifying character sets for databases, table columns, and client connections. In addition, it describes how to specify collation orders for columns, comparisons, ORDER BY clauses, and GROUP BY clauses.

Specifying a Default Character Set for a Database

A database’s default character set designation specifies the character set the server uses to tag CHAR, VARCHAR, and text BLOB columns in the database when no other character set information is provided. When data is stored in such columns without additional character set information, the server uses the tag to determine how to store and transliterate that data. A default character set should always be specified for a database when it is created with CREATE DATABASE.

To specify a default character set, use the DEFAULT CHARACTER SET clause of CREATE DATABASE. For example, the following statement creates a database that uses the ISO8859_1 character set:

CREATE DATABASE 'europe.ib' DEFAULT CHARACTER SET ISO8859_1;

Important:
If you do not specify a character set, the character set defaults to NONE. Using character set NONE means that there is no character set assumption for columns; data is stored and retrieved just as you originally entered it. You can load any character set into a column defined with NONE, but you cannot later move that data into another column that has been defined with a different character set. In this case, no transliteration is performed between the source and destination character sets, and errors may occur during assignment.

For the complete syntax of CREATE DATABASE, see Language Reference Guide.

Specifying a Character Set for a Column in a Table

Character sets for individual columns in a table can be specified as part of the column’s CHAR or VARCHAR data type definition. When a character set is defined at the column level, it overrides the default character set declared for the database. For example, the following isql statements create a database with a default character set of ISO8859_1, then create a table where two column definitions include a different character set specification:

CREATE DATABASE 'europe.ib' DEFAULT CHARACTER SET ISO8859_1;
CREATE TABLE RUS_NAME(
LNAME VARCHAR(30) NOT NULL CHARACTER SET CYRL,
FNAME VARCHAR(20) NOT NULL CHARACTER SET CYRL,);

For the complete syntax of CREATE TABLE, see Language Reference Guide.

Specifying a Character Set for a Client Connection

When a client application, such as isql, connects to a database, it may have its own character set requirements. The server providing database access to the client does not know about these requirements unless the client specifies them. The client application specifies its character set requirement using the SET NAMES statement before it connects to the database.

SET NAMES specifies the character set the server should use when translating data from the database to the client application. Similarly, when the client sends data to the database, the server translates the data from the client’s character set to the database’s default character set (or the character set for an individual column if it differs from the database’s default character set).

For example, the following isql command specifies that isql is using the DOS437 character set. The next command connects to the europe database created in Specifying a Character Set for a Column in a Table:

SET NAMES DOS437;
CONNECT 'europe.ib' USER 'JAMES' PASSWORD 'U4EEAH';

For the complete syntax of SET NAMES and CONNECT, see Language Reference Guide.

Advance To:

• Specifying Collation Orders