Informix Guide to GLS Functionality

Informix Guide to GLS Functionality
Chapter 3: SQL Features

Home Contents Index Master Index New Book

Using Data Manipulation Statements

The choice of a locale can affect the following SQL data manipulation statements:

DELETE
INSERT
LOAD
UNLOAD
UPDATE

The following sections describe the GLS aspects of these SQL statements. For a complete description of the use and syntax of these statements, see Chapter 1 of the Informix Guide to SQL: Syntax.

Specifying Conditions in the WHERE Clause

The following SQL statements might include a WHERE clause to tell the database server on which rows to operate:

For the DELETE statement, the WHERE clause specifies which rows to delete.
For the INSERT statement if the statement includes an embedded SELECT, the WHERE clause specifies which rows to insert from another table.
For the UPDATE statement, the WHERE clause specifies which rows to update. In addition, the SET clause can include an embedded SELECT statement whose WHERE clause identifies a row whose values are to be assigned to another row.
For the UNLOAD statement, the WHERE clause of the embedded SELECT specifies which rows to unload.

The choice of a locale affects these uses of a WHERE clause in the same way that it affects the WHERE clause of a SELECT. For more information, see "Logical Predicates in a WHERE Clause" and "Comparisons with MATCHES and LIKE Conditions".

Specifying Era-Based Dates

The following SQL statements might specify DATE and DATETIME column values:

The WHERE clause of the DELETE statement
The VALUES clause of the INSERT statement
The SET clause of the UPDATE statement

When you specify a DATE column value in one of the preceding SQL statements, the database server uses the GL_DATE (or DBDATE) environment variable to interpret the date expression, as follows:

If you have set GL_DATE (or DBDATE) to an era-based (Asian) date format, you can use era-based date formats for date expressions.
If you have not set the GL_DATE (or DBDATE) environment variable to an era-based date format, you can use era-based date formats for date expressions only if the server processing locale supports era-based dates. For more information on the server processing locale, see "Determining the Server-Processing Locale".
If your locale does not support era-dated dates, you cannot use era-based date formats for date expressions. If you attempt to specify an era-based date format in this case, the SQL statement fails.

When you specify a DATETIME column value, the database server uses the GL_DATETIME (or DBTIME) environment variable instead of the GL_DATE (or DBDATE) environment variable to interpret the expression.

For more information, see "Era-Based Date and Time Formats". For information on how to set the GL_DATE, GL_DATETIME, DBDATE, and DBTIME environment variables to an era-based format, see their entries in Chapter 2, "GLS Environment Variables."

Loading and Unloading Data

The LOAD and UNLOAD statements allow you transfer data to and from your database with operating-system text files. The following sections describe the GLS aspects of the LOAD and UNLOAD statements. For a complete description of the use and syntax of these statements, see Chapter 1 of the Informix Guide to SQL: Syntax.

Loading Data into a Database

The LOAD statement inserts data from an operating-system text file into a table or view. This operating-system text file is called a LOAD FROM file. The data in this file can contain any character that the client code set defines. If the client locale supports a multibyte code set, this data can contain multibyte characters. If the database locale supports a code set that is different from but convertible to the client code set, the client performs code-set conversion on the data before it sends this data to the database server. For more information, see "Performing Code-Set Conversion".

The locale also defines the formats for date, time, numeric, and monetary data. You can use any format that the client locale supports as a column value in the LOAD FROM file. For example, a French locale might define monetary values that have a space as the thousands separator and a comma as the decimal separator. When you use this locale, the following MONEY column value is valid in a LOAD FROM file:

3 411,99

You can set environment variables to specify alternative end-user formats for date and monetary data. If you set these environment variables, the LOAD FROM files can use the alternative end-user formats for DATE, DATETIME, and MONEY column values. For more information, see "Customizing Date and Time End-User Formats" and "Customizing Monetary Values".

Unloading Data from a Database

The UNLOAD statement writes the rows that a SELECT statement retrieves to an operating-system text file. This operating-system text file is called an UNLOAD TO file. The data in this file contains characters that the client code set defines. If the client locale supports a multibyte code set, this data can contain the multibyte characters. If the database locale supports a code set that is different from but convertible to the client code set, the client performs code-set conversion on the data before it writes this data to the UNLOAD TO file. (For more information, see "Performing Code-Set Conversion".)

The client locale and certain environment variables determine the output format of certain data types in the UNLOAD TO file. These data types include DATE values, MONEY values, values of numeric data types, and DATETIME values. For further information, see "End-User Formats" and "How Do You Customize Client End-User Formats?".

Important: You can use an UNLOAD TO file, which the UNLOAD statement generates, as the input file (the LOAD FROM file) to a LOAD statement that loads another table or database. When you use an UNLOAD TO file in this manner, make sure that all environment variables and the client locale have the same values when you perform the LOAD as they did when you performed the UNLOAD.

Informix Guide to GLS Functionality, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.