Establishing a Database Connection

When a client application requests a connection to a database, the database server uses GLS locales to perform the following steps:

1. Examine the client locale information that the client passes.
2. Verify that it can establish a connection between the client application and the database that it requested.
3. Determine the server-processing locale, which the database server uses to handle locale-specific information for the connection.

Sending the Client Locale

When the client application requests a connection, it sends the following environment variables from the client locale to the database server:

• Locale information
  • CLIENT_LOCALE
    If CLIENT_LOCALE is not set, the client sets it to the default locale.
  • DB_LOCALE
    If DB_LOCALE is not set, the client does not send a DB_LOCALE value to the database server.
• User-customized end-user formats
  • Date and time end-user formats: GL_DATE and GL_DATETIME
  • Monetary end-user formats: DBMONEY

  If you do not set any of these environment variables, the client application does not send them to the database server, and the database server uses the end-user formats that the CLIENT_LOCALE defines.

The database server uses these settings to extract the following information:

• How are numeric and monetary values formatted?
• How are dates and times formatted?
• What database locale does the client expect?

The database server uses this information to verify the database locale and to establish the server-processing locale.

Verifying the Database Locale

To open an existing database, the client application must correctly identify the database locale for that database. To verify the database locale, the database server compares the following two locales:

• The locale specified by DB_LOCALE that the client application sends
• The database locale that is stored in the system catalog of the database that the client application requests

  For more information, see The Database Locale.

Two database locales match if their language, territory, code set, and any locale modifiers are the same. If these database locales do not match, the database server performs the following actions:

• It sets the eighth character field of the SQLWARN array in the SQL Communications Area (SQLCA structure) to W as a warning flag. Values for W are ASCII 32 (blank) and ASCII 87 (W).
• It uses the database locale that is stored in the system catalog of the requested database as the database locale.

Checking for Connection Warnings

To check for the eighth character field of the SQLWARN array, an ESQL/C client application can check the sqlca.sqlwarn.sqlwarn7 field.

If the sqlwarn7 field has a value of W, the database server has ignored the database locale that the client specified and has instead used the locale in the database as the database locale.

For more information on how to handle exceptions within an ESQL program, see the IBM Informix: ESQL/C Programmer's Manual.

Determining the Server-Processing Locale

The database server uses the server-processing locale to obtain locale information for its own internal sessions and for any connections. When the database server begins execution, it initializes the server-processing locale to the default locale. When a client application requests a connection, the database server must redetermine the server-processing locale to include the client and database locales. The database server uses the server-processing locale to obtain locale information that it needs when it transfers data between the client system and the database.

Once the Informix database server verifies the database locale, it uses a precedence of environment variables from the client and database locales to set the server-processing locale.

The database server obtains the following information from the server-processing locale:

• Locale information for the database

  This database information includes the localized order and code set for data in columns with the locale-sensitive data types (NCHAR and NVARCHAR). The database server obtains this information from the name of the database locale that it has just verified.

• Locale information for client-application data

  This client-application information provides the end-user formats for date, time, numeric, and monetary data. The database server obtains this information from the client application when the client requests a connection.

Figure 3 shows the relationship between the client locale, database locale, server locale, and server-processing locale.

Figure 3. The Server-Processing Locale

Locale Information for the Database

The database server must know how to interpret the data in any columns with the locale-specific data types, NCHAR and NVARCHAR. To handle this locale-specific data correctly, the database server must know the localized order for the collation of the data and the code set of the data. In addition, the database server uses the code set of the database locale as the code set of the server-processing locale.

The database server might have to perform code-set conversion between the code sets of the server-processing locale and the server locale. For more information, see Performing Code-Set Conversion.

The database server uses the following precedence to determine this database information:

1. The locale that the database server uses to determine the database information for the server-processing locale depends on the state of the database to which the client application requests a connection, as follows:
   1. For a connection to an existing database, the database server uses the database information from the database locale that it obtains when it verifies the database locale. If the client application does not send DB_LOCALE, the database server uses the DB_LOCALE that is set on the server computer.
   2. For a new database, the database server uses the DB_LOCALE, which the client application has sent.
2. The locale that the DB_LOCALE environment variable on the server computer indicates
3. The default locale (U.S. English)

For more information on how the database server obtains these environment variables, see Sending the Client Locale.

If the client application makes another request to open a database, the database server must reestablish the database information for the server-processing locale, as follows:

1. Reverify the database locale by comparing the database locale in the database to be opened with the value of the DB_LOCALE environment variable from the client application.
2. Reestablish the server-processing locale with the newly verified database locale (from the preceding step).

For example, suppose that your client application has DB_LOCALE set to en_us.8859-1 (U.S. English with the ISO8859-1 code set). The client application then opens a database with the U.S. English locale (en_us.8859-1), and the database server establishes a server-processing locale with en_us.8859-1 as the locale that defines the database information.

If the client application now closes the U.S. English database and opens another database, one with the French locale (fr_fr.8859-1), the database server must reestablish the server-processing locale. The database server sets the eighth character field of the SQLWARN array to W indicate that the two locales are different. The client application, however, might choose to use this connection because both these locales support the ISO8859-1 code set. If the client application opens a database with the Japanese SJIS locale (ja_jp.sjis) instead of one with a French locale, your client application would probably not continue with this connection because the locales are too different.

Locale Information For the Client Application

The database server must know how to interpret the end-user formats when they appear in monetary, date, or time data that the client application sends. It must also convert data from the database to any appropriate end-user format before it sends this data to the client application. For more information about end-user formats, see End-User Formats.

The database server uses the following precedence to determine this client-application information:

1. DBDATE and DBTIME environment variables for the date and time end-user formats and DBMONEY for the monetary end-user formats (if one of these is set on the client)

   Support for DBDATE and DBTIME provides backward compatibility for client applications that are based on earlier versions of IBM Informix products. It is recommended that you use GL_DATE and GL_DATETIME for new applications.

2. GL_DATE and GL_DATETIME environment variables (if one of these is set on the client) for the date and time end-user formats
3. The locale that the CLIENT_LOCALE environment variable from the client application indicates

The client application passes the DBDATE, DBMONEY, DBTIME, GL_DATE, and GL_DATETIME environment variables (if they are set) to the database server. It also passes the CLIENT_LOCALE and DB_LOCALE environment variables. For more information, see Sending the Client Locale.