Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

START VIOLATIONS TABLE

The START VIOLATIONS TABLE statement creates a violations table and a diagnostics table for a specified target table. The database server associates the violations and diagnostics tables with the target table by recording the relationship among the three tables in the sysviolations system catalog table.

Syntax

(1 of 2)

Element Purpose Restrictions Syntax
diagnostics
The name of the diagnostics table to be associated with the target table. The default name is the name of the target table followed by the characters _dia. For further information on the diagnostics table, see "Structure of the Diagnostics Table".
Whether you specify the name of the diagnostics table explicitly, or the database server generates the name implicitly, the name cannot match the name of any existing table in the database.
Identifier, p. 1-966

numrows
The maximum number of rows that can be inserted into the diagnostics table when a single statement (for example, `INSERT` or `SET`) is executed on the target table. There is no default value for numrows. If you do not specify a value for numrows, there is no upper limit on the number of rows that can be inserted into the diagnostics table when a single statement is executed on the target table.
You must specify an integer value in the range 1 to the maximum value of the `INTEGER` data type.
Literal Number, p. 1-1001

table name
The name of the target table for which a violations table and diagnostics table are to be created. There is no default value.
If you do not include the `USING` clause in the statement, the name of the target table must be less than 15 characters. The target table cannot have a violations and diagnostics table associated with it before you execute the statement. The target table cannot be a system catalog table. The target table must be a local table.
Identifier, p. 1-966

violations
The name of the violations table to be associated with the target table. The default name is the name of the target table followed by the characters _vio. For further information on the violations table, see "Structure of the Violations Table".
Whether you specify the name of the violations table explicitly, or the database server generates the name implicitly, the name cannot match the name of any existing table in the database.
Identifier, p. 1-966

START VIOLATIONS TABLE FOR cust_subset

Starting Violations and Diagnostics Tables and Specifying Their Names

The following statement starts a violations and diagnostics table for the target table named items. The USING clause assigns explicit names to the violations and diagnostics tables. The violations table is to be named exceptions, and the diagnostics table is to be named reasons.

START VIOLATIONS TABLE FOR items

USING exceptions, reasons

Specifying the Maximum Number of Rows in the Diagnostics Table

The following statement starts violations and diagnostics tables for the target table named orders. The MAX ROWS clause specifies the maximum number of rows that can be inserted into the diagnostics table when a single statement, such as an INSERT or SET statement, is executed on the target table.

START VIOLATIONS TABLE FOR orders MAX ROWS 50000

Privileges Required for Starting Violations Tables

To start a violations and diagnostics table for a target table, you must meet one of the following requirements:

You must have the DBA privilege on the database.
You must be the owner of the target table and have the Resource privilege on the database.
You must have the Alter privilege on the target table and the Resource privilege on the database.

Structure of the Violations Table

When you issue a START VIOLATIONS TABLE statement for a target table, the violations table that the statement creates has a predefined structure. This structure consists of the columns of the target table and three additional columns.

The following table shows the structure of the violations table.

Column Name Type Explanation
All columns of the target table, in the same order that they appear in the target table
These columns of the violations table match the data type of the corresponding columns in the target table, except that SERIAL columns in the target table are converted to INTEGER data types in the violations table.
The table definition of the target table is reproduced in the violations table so that rows that violate constraints or unique-index requirements during insert, update, and delete operations can be filtered to the violations table. Users can examine these bad rows in the violations table, analyze the related rows that contain diagnostics information in the diagnostics table, and take corrective actions.

informix_tupleid
SERIAL
This column contains the unique serial identifier that is assigned to the nonconforming row.

informix_optype
CHAR(1)
This column indicates the type of operation that caused this bad row. This column can have the following values:

I = Insert

D = Delete

O = Update (with this row containing the original values)

N = Update (with this row containing the new values)

S = SET statement

informix_recowner
CHAR(8)
This column identifies the user who issued the statement that created this bad row.

Relationship Between the Violations and Diagnostics Tables

Users can take advantage of the relationships among the target table, violations table, and diagnostics table to obtain complete diagnostic information about rows that have caused data-integrity violations during INSERT, DELETE, and UPDATE statements.

Each row of the violations table has at least one corresponding row in the diagnostics table. The row in the violations table contains a copy of the row in the target table for which a data-integrity violation was detected. The row in the diagnostics table contains information about the nature of the data-integrity violation caused by the bad row in the violations table. The row in the violations table has a unique serial identifier in the informix_tupleid column. The row in the diagnostics table has the same serial identifier in its informix_tupleid column.

A given row in the violations table can have more than one corresponding row in the diagnostics table. The multiple rows in the diagnostics table all have the same serial identifier in their informix_tupleid column so that they are all linked to the same row in the violations table. Multiple rows can exist in the diagnostics table for the same row in the violations table because a bad row in the violations table can cause more than one data-integrity violation.

For example, a bad row can violate a unique-index requirement for one column, a not null constraint for another column, and a check constraint for yet another column. In this case, the diagnostics table contains three rows for the single bad row in the violations table. Each of these diagnostic rows identifies a different data-integrity violation that the nonconforming row in the violations table caused.

By joining the violations and diagnostics tables, the DBA or target table owner can obtain complete diagnostic information about any or all bad rows in the violations table. You can use SELECT statements to perform these joins interactively, or you can write a program to perform them within transactions.

Initial Privileges on the Violations Table

When you issue the START VIOLATIONS TABLE statement to create the violations table, the database server uses the set of privileges granted on the target table as a basis for granting privileges on the violations table. However, the database server follows different rules when it grants each type of privilege.

The following table shows the initial set of privileges on the violations table. The Privilege column lists the privilege. The Condition column explains the conditions under which the database server grants the privilege to a user.

(1 of 2)

Privilege Condition
Insert
The user has the Insert privilege on the violations table if the user has any of the following privileges on the target table: the Insert privilege, the Delete privilege, or the Update privilege on any column.

Delete
The user has the Delete privilege on the violations table if the user has any of the following privileges on the target table: the Insert privilege, the Delete privilege, or the Update privilege on any column.

Select
The user has the Select privilege on the informix_tupleid, informix_optype, and informix_recowner columns of the violations table if the user has the Select privilege on any column of the target table.

The user has the Select privilege on any other column of the violations table if the user has the Select privilege on the same column in the target table.

Update
The user has the Update privilege on the informix_tupleid, informix_optype, and informix_recowner columns of the violations table if the user has the Update privilege on any column of the target table.

The user has the Update privilege on any other column of the violations table if the user has the Update privilege on the same column in the target table.

Index
The user has the Index privilege on the violations table if the user has the Index privilege on the target table.

Alter
The Alter privilege is not granted on the violations table. (Users cannot alter violations tables.)

References
The References privilege is not granted on the violations table. (Users cannot add referential constraints to violations tables.)

The following rules apply to ownership of the violations table and privileges on the violations table:

When the violations table is created, the owner of the target table becomes the owner of the violations table.
The owner of the violations table automatically receives all table-level privileges on the violations table, including the Alter and References privileges. However, the database server prevents the owner of the violations table from altering the violations table or adding a referential constraint to the violations table.
You can use the GRANT and REVOKE statements to modify the initial set of privileges on the violations table.
When you issue an INSERT, DELETE, or UPDATE statement on a target table that has a filtering-mode unique index or constraint defined on it, you must have the Insert privilege on the violations and diagnostics tables.

If you do not have the Insert privilege on the violations and diagnostics tables, the database server executes the SET statement provided that you have the necessary privileges on the target table. The database server does not return an error concerning the lack of insert permission on the violations and diagnostics tables unless an integrity violation is detected during the execution of the SET statement.

The grantor of the initial set of privileges on the violations table is the same as the grantor of the privileges on the target table. For example, if the user henry has been granted the Insert privilege on the target table by both the user jill and the user albert, the Insert privilege on the violations table is granted to user henry both by user jill and by user albert.
Once a violations table has been started for a target table, revoking a privilege on the target table from a user does not automatically revoke the same privilege on the violations table from that user. Instead you must explicitly revoke the privilege on the violations table from the user.
If you have fragment-level privileges on the target table, you have the corresponding fragment-level privileges on the violations table.

Example of Privileges on the Violations Table

The following example illustrates how the initial set of privileges on a violations table is derived from the current set of privileges on the target table.

For example, assume that we have created a table named cust_subset and that this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

User alvin is the owner of the table.
User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset

	USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_viols violations table:

User alvin is the owner of the violations table, so he has all table-level privileges on the table.
User barbara has the Insert, Delete, and Index privileges on the violations table. She also has the Select privilege on the following columns of the violations table: the ssn column, the lname column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.
User carrie has the Insert and Delete privileges on the violations table. She has the Update privilege on the following columns of the violations table: the city column, the informix_tupleid column, the informix_optype column, and the informix_recowner column. She has the Select privilege on the following columns of the violations table: the ssn column, the informix_tupleid column, the informix_optype column, and the informix_recowner column.
User danny has no privileges on the violations table.

Using the Violations Table

The following rules concern the structure and use of the violations table:

Every pair of update rows in the violations table has the same value in the informix_tupleid column to indicate that both rows refer to the same row in the target table.
If the target table has columns named informix_tupleid, informix_optype, or informix_recowner, the database server attempts to generate alternative names for these columns in the violations table by appending a digit to the end of the column name (for example, informix_tupleid1). If this attempt fails, the database server returns an error, and the violations table is not started for the target table.
When a table functions as a violations table, it cannot have triggers or constraints defined on it.
When a table functions as a violations table, users can create indexes on the table, even though the existence of an index affects performance. Unique indexes on the violations table cannot be set to the filtering object mode.
If a target table has a violations and diagnostics table associated with it, dropping the target table in cascade mode (the default mode) causes the violations and diagnostics tables to be dropped also. If the target table is dropped in the restricted mode, the existence of the violations and diagnostics tables causes the DROP TABLE statement to fail.
Once a violations table is started for a target table, you cannot use the ALTER TABLE statement to add, modify, or drop columns in the target table, violations table, or diagnostics table. Before you can alter any of these tables, you must issue a STOP TABLE VIOLATIONS statement for the target table.
The database server does not clear out the contents of the violations table before or after it uses the violations table during an Insert, Update, Delete, or Set operation.
If a target table has a filtering-mode constraint or unique index defined on it and a violations table associated with it, users cannot insert into the target table by selecting from the violations table. Before you insert rows into the target table by selecting from the violations table, you must take one of the following steps:
- You can set the object mode of the constraint or unique index to the enabled or disabled object mode.
- You can issue a STOP VIOLATIONS TABLE statement for the target table.

If it is inconvenient to take either of these steps, but you still want to copy records from the violations table into the target table, a third option is to select from the violations table into a temporary table and then insert the contents of the temporary table into the target table.

If the target table that is specified in the START VIOLATIONS TABLE statement is fragmented, the violations table has the same fragmentation strategy as the target table. Each fragment of the violations table is stored in the same dbspace as the corresponding fragment of the target table.
If the target table specified in the START VIOLATIONS TABLE statement is not fragmented, the database server places the violations table in the same dbspace as the target table.
If the target table has blob columns, blobs in the violations table are created in the same blob space as the blobs in the target table.

Example of a Violations Table

To start a violations and diagnostics table for the target table named customer in the stores7 demonstration database, enter the following statement:

START VIOLATIONS TABLE FOR customer

Because your START VIOLATIONS statement does not include a USING clause, the violations table is named customer_vio by default. The customer_vio table includes the following columns:

customer_num
fname
lname
company
address1
address2
city
state
zipcode
phone
informix_tupleid
informix_optype
informix_recowner

The customer_vio table has the same table definition as the customer table except that the customer_vio table has three additional columns that contain information about the operation that caused the bad row.

Structure of the Diagnostics Table

When you issue a START VIOLATIONS TABLE statement for a target table, the diagnostics table that the statement creates has a predefined structure. This structure is independent of the structure of the target table.

The following table shows the structure of the diagnostics table.

(1 of 2)

Column Name Type Explanation
informix_tupleid
INTEGER
This column in the diagnostics table implicitly refers to the values in the informix_tupleid column in the violations table. However, this relationship is not declared as a foreign-key to primary-key relationship.

objtype
CHAR(1)
This column identifies the type of the violation. This column can have the following values.

C = Constraint violation

I = Unique-index violation

objowner
CHAR(8)
This column identifies the owner of the constraint or index for which an integrity violation was detected.

objname
CHAR(18)
This column contains the name of the constraint or index for which an integrity violation was detected.

Initial Privileges on the Diagnostics Table

When the START VIOLATIONS TABLE statement creates the diagnostics table, the database server uses the set of privileges granted on the target table as a basis for granting privileges on the diagnostics table. However, the database server follows different rules when it grants each type of privilege.

The following rules concern privileges on the diagnostics table:

When the diagnostics table is created, the owner of the target table becomes the owner of the diagnostics table.
The owner of the diagnostics table automatically receives all table-level privileges on the diagnostics table, including the Alter and References privileges. However, the database server prevents the owner of the diagnostics table from altering the diagnostics table or adding a referential constraint to the diagnostics table.
You can use the GRANT and REVOKE statements to modify the initial set of privileges on the diagnostics table.
When you issue an INSERT, DELETE, or UPDATE statement on a target table that has a filtering-mode unique index or constraint defined on it, you must have the Insert privilege on the violations and diagnostics tables.

The grantor of the initial set of privileges on the diagnostics table is the same as the grantor of the privileges on the target table. For example, if the user jenny has been granted the Insert privilege on the target table by both the user wayne and the user laurie, both user wayne and user laurie grant the Insert privilege on the diagnostics table to user jenny.
Once a diagnostics table has been started for a target table, revoking a privilege on the target table from a user does not automatically revoke the same privilege on the diagnostics table from that user. Instead you must explicitly revoke the privilege on the diagnostics table from the user.
If you have fragment-level privileges on the target table, you have the corresponding table-level privileges on the diagnostics table.

Example of Privileges on the Diagnostics Table

The following example illustrates how the initial set of privileges on a diagnostics table is derived from the current set of privileges on the target table.

For example, assume that there is a table called cust_subset and that this table consists of the following columns: ssn (customer's social security number), fname (customer's first name), lname (customer's last name), and city (city in which the customer lives).

The following set of privileges exists on the cust_subset table:

User alvin is the owner of the table.
User barbara has the Insert and Index privileges on the table. She also has the Select privilege on the ssn and lname columns.
User carrie has the Update privilege on the city column. She also has the Select privilege on the ssn column.
User danny has the Alter privilege on the table.

Now user alvin starts a violations table named cust_subset_viols and a diagnostics table named cust_subset_diags for the cust_subset table, as follows:

START VIOLATIONS TABLE FOR cust_subset

	USING cust_subset_viols, cust_subset_diags

The database server grants the following set of initial privileges on the cust_subset_diags diagnostics table:

User alvin is the owner of the diagnostics table, so he has all table-level privileges on the table.
User barbara has the Insert, Delete, Select, and Index privileges on the diagnostics table.
User carrie has the Insert, Delete, Select, and Update privileges on the diagnostics table.
User danny has no privileges on the diagnostics table.

Using the Diagnostics Table

For information on the relationship between the diagnostics table and the violations table, see "Relationship Between the Violations and Diagnostics Tables".

The following issues concern the structure and use of the diagnostics table:

The MAX ROWS clause of the START VIOLATIONS TABLE statement sets a limit on the number of rows that can be inserted into the diagnostics table when you execute a single statement, such as an INSERT or SET statement, on the target table.
The MAX ROWS clause limits the number of rows only for operations in which the table functions as a diagnostics table.
When a table functions as a diagnostics table, it cannot have triggers or constraints defined on it.
When a table functions as a diagnostics table, users can create indexes on the table, even though the existence of an index affects performance. You cannot set unique indexes on the diagnostics table to the filtering object mode.
If a target table has a violations and diagnostics table associated with it, dropping the target table in the cascade mode (the default mode) causes the violations and diagnostics tables to be dropped also. If the target table is dropped in the restricted mode, the existence of the violations and diagnostics tables causes the DROP TABLE statement to fail.
Once a violations table is started for a target table, you cannot use the ALTER TABLE statement to add, modify, or drop columns in the target table, violations table, or diagnostics table. Before you can alter any of these tables, you must issue a STOP TABLE VIOLATIONS statement for the target table.
The database server does not clear out the contents of the diagnostics table before or after it uses the diagnostics table during an Insert, Update, Delete, or Set operation.
If the target table that is specified in the START VIOLATIONS TABLE statement is fragmented, the diagnostics table is fragmented with a round-robin strategy over the same dbspaces in which the target table is fragmented.

Example of a Diagnostics Table

To start a violations and diagnostics table for the target table named stock in the stores7 demonstration database, enter the following statement:

START VIOLATIONS TABLE FOR stock

Because your START VIOLATIONS TABLE statement does not include a USING clause, the diagnostics table is named stock_dia by default. The stock_dia table includes the following columns:

informix_tupleid
objtype
objowner
objname

This list of columns shows an important difference between the diagnostics table and violations table for a target table. Whereas the violations table has a matching column for every column in the target table, the columns of the diagnostics table do not match any columns in the target table. The diagnostics table created by any START VIOLATIONS TABLE statement always has the same columns with the same column names and data types.

References

See the STOP VIOLATIONS TABLE and SET statements in this manual.

For information on the system catalog tables that are associated with the START VIOLATIONS TABLE statement, see the sysobjstate and sysviolations tables in the Informix Guide to SQL: Syntax.

STOP VIOLATIONS TABLE

The STOP VIOLATIONS TABLE statement drops the association between a target table and the special violations and diagnostics tables.

Syntax

Element Purpose Restrictions Syntax
table name
The name of the target table whose association with the violations and diagnostics table is to be dropped. There is no default value.
The target table must have a violations and diagnostics table associated with it before you can execute the statement. The target table must be a local table.
Identifier, p. 1-966

Usage

The STOP VIOLATIONS TABLE statement drops the association between the target table and the violations and diagnostics tables. After you issue this statement, the former violations and diagnostics tables continue to exist, but they no longer function as violations and diagnostics tables for the target table. They now have the status of regular database tables instead of violations and diagnostics tables for the target table. You must issue the DROP TABLE statement to drop these two tables explicitly.

When Insert, Delete, and Update operations cause data-integrity violations for rows of the target table, the nonconforming rows are no longer filtered to the former violations table, and diagnostics information about the data-integrity violations is not placed in the former diagnostics table.

Example of Stopping a Violations and Diagnostics Table

Assume that a target table named cust_subset has an associated violations table named cust_subset_vio and an associated diagnostics table named cust_subset_dia. To drop the association between the target table and the violations and diagnostics tables, enter the following statement:

STOP VIOLATIONS TABLE FOR cust_subset

Example of Dropping a Violations and Diagnostics Table

After you execute the STOP VIOLATIONS TABLE statement in the preceding example, the cust_subset_vio and cust_subset_dia tables continue to exist, but they are no longer associated with the cust_subset table. Instead they now have the status of regular database tables. To drop these two tables, enter the following statements:

DROP TABLE cust_subset_vio;
DROP TABLE cust_subset_dia;

Privileges Required for Stopping a Violations Table

To stop a violations and diagnostics table for a target table, you must meet one of the following requirements:

You must have the DBA privilege on the database.
You must be the owner of the target table and have the Resource privilege on the database.
You must have the Alter privilege on the target table and the Resource privilege on the database.

References

See the SET and START VIOLATIONS TABLE statements in this manual.

For information on the system catalog tables associated with the STOP VIOLATIONS TABLE statement, see the sysobjstate and sysviolations tables in the Informix Guide to SQL: Syntax.

UNLOAD

Use the UNLOAD statement to write the rows retrieved in a SELECT statement to an operating-system file.

Syntax

Element Purpose Restrictions Syntax
delimiter
A quoted string that identifies the character to use as the delimiter in the output file. The delimiter is a character that separates the data values in each line of the output file. If you do not specify a delimiter character, the database server uses the setting in the DBDELIMITER environment variable. If DBDELIMITER has not been set, the default delimiter is the vertical bar (|).
You cannot use the following items as the delimiter character: backslash (\), new-line character (=`CTRL`-`J`), hexadecimal numbers (0 to 9, a to f, `A` to `F`).
Quoted String, p. 1-1014

filename
A quoted string that specifies the pathname and filename of an `ASCII` operating-system file. This output file receives the selected rows from the table during the unload operation. The default pathname for the output file is the current directory.
You can unload table data containing `VARCHAR` or `BLOB` data types to the output file, but you should be aware of the consequences. See "The UNLOAD TO File" for further information.
Quoted String, p. 1-1014. The pathname and filename specified in the quoted string must conform to the conventions of your operating system.

Usage

To use the UNLOAD statement, you must have the Select privilege on all columns selected in the SELECT statement. For information on database-level and table-level privileges, see the GRANT statement on page 1-461.

The SELECT statement can consist of a literal SELECT statement or the name of a character variable that contains a SELECT statement. (See the SELECT statement on page 1-596.)

The UNLOAD TO File

The UNLOAD TO file contains the selected rows retrieved from the table. You can use the UNLOAD TO file as the LOAD FROM file in a LOAD statement.

The following table shows types of data and their output format for an UNLOAD statement in DB-Access (when DB-Access uses the default locale, U.S. English).

(1 of 3)

Data Type Output Format
boolean
BOOLEAN data is represented as a't' for a TRUE value and an 'f' for a FALSE value.

character
If a character field contains the delimiter character, Informix products automatically escape it with a backslash (\) to prevent interpretation as a special character. (If you use a LOAD statement to insert the rows into a table, backslashes are automatically stripped.) Trailing blanks are automatically clipped.

collections
A collection is unloaded with its values surrounded by braces ({}) and a field delimiter separating each element. For more information, see "Unloading Complex Types".

date
DATE values are represented as mm/dd/yyyy, where mm is the month (January = 1, and so on), dd is the day, and yyyy is the year. If you have set the GL_DATE or DBDATE environment variable, the UNLOAD statement uses the specified date format for DATE values. See the Guide to GLS Functionality for more information about these environment variables.

MONEY
MONEY values are unloaded with no leading currency symbol. They use the comma (,) as the thousands separator and the period as the decimal separator. If you have set the DBMONEY environment variable, the UNLOAD statement uses the specified currency format for MONEY values. See the Guide to GLS Functionality for more information about this environment variable.

NULL
NULL columns are unloaded by placing no characters between the delimiters.

number
Number data types are displayed with no leading blanks. INT8, INTEGER or SMALLINT zero are represented as 0, and FLOAT, SMALLFLOAT, DECIMAL, or MONEY zero are represented as 0.00.

row types
(named and unnamed)
A row type is unloaded with its values surrounded by parentheses and a field delimiter separating each element. For more information, see "Unloading Complex Types".

simple large objects
(TEXT, BYTE)
TEXT and BYTE columns are unloaded directly into the UNLOAD TO file. For more information, see "Unloading Simple Large Objects".

smart large objects (CLOB, BLOB)
CLOB and BLOB columns are unloaded into a separate operating-system file on the client computer. The field for the CLOB or BLOB column in the UNLOAD TO file contains the name of this separate file. For more information, see "Unloading Smart Large Objects".

time
DATETIME and INTERVAL values are represented in character form, showing only their field digits and delimiters. No type specification or qualifiers are included in the output. The following pattern is used: yyyy-mm-dd hh:mi:ss.fff, omitting fields that are not part of the data. If you have set the GL_DATETIME or DBTIME environment variable, the UNLOAD statement uses the specified format for DATETIME values. See the Guide to GLS Functionality for more information on these environment variables.

user-defined data formats (opaque types)
The associated opaque type must have an export support function defined if special processing is required to copy the data in the internal format of the opaque type to the format in the UNLOAD TO file. An exportbinary support function might also be required if the data is in binary format. The data in the UNLOAD TO file would correspond to the format that the export or exportbinary support function returns.

GLS

If you are using a nondefault locale, the formats of DATE, DATETIME, MONEY, and numeric column values in the UNLOAD TO file are determined by the formats that the locale supports for these data types. For more information, see the Guide to GLS Functionality.

The following statement unloads rows from the customer table where the value of customer_num is greater than or equal to 138, and puts them in a file named cust_file:

UNLOAD TO 'cust_file' DELIMITER '!' 

	SELECT * FROM customer WHERE customer_num> = 138

The output file, cust_file, appears as shown in the following example:

138!Jeffery!Padgett!Wheel Thrills!3450 El Camino!Suite 
10!Palo Alto!CA!94306!!

139!Linda!Lane!Palo Alto Bicycles!2344 University!!Palo 
Alto!CA!94301!(415)323-5400

If you are unloading columns of the VARCHAR data type, the database server does not retain trailing blanks.

Unloading Simple Large Objects

The database server unloads simple large objects (BYTE and TEXT columns) directly into the UNLOAD TO file. BYTE data are written in hexadecimal dump format with no added spaces or new lines. Consequently, the logical length of an unloaded file that contains BYTE items can be very long and very difficult to print or edit.

GLS

For TEXT columns, the database server handles any required code-set conversions for the data. For more information, see the Guide to GLS Functionality.

If you are unloading files that contain simple large object data types, objects smaller than 10 kilobytes are stored temporarily in memory. You can adjust the 10-kilobyte setting to a larger setting with the DBBLOBBUF environment variable. Simple large objects that are larger than the default or the setting of the DBBLOBBUF environment variable are stored in a temporary file. For additional information about the DBBLOBBUF environment variable, see the Informix Guide to SQL: Syntax.

Unloading Smart Large Objects

The database server unloads smart large objects (BLOB and CLOB columns) into a separate operating-system file on the client computer. It creates this file in the same directory as the UNLOAD TO file. The filename of this file has one of the following formats:

For a BLOB value:

blob########

For a CLOB value:

clob########

In the preceding formats, the # symbols represent the digits of the unique hexadecimal smart large-object identifier. The database server uses the hexadecimal id for the first smart large object in the file. The maximum number of digits for a smart large-object identifier is 17; however must smart large objects would have an identifier with significantly fewer digits.

When the database server unloads the first smart large object, it creates the appropriate BLOB or CLOB file with the hexadecimal identifier of the smart large object. It appends any additional BLOB or CLOB values to the appropriate file until the file size reaches a limit of 2 gigabytes. If there are still additional smart large-object values, the database server creates another BLOB or CLOB file whose filename contains the hexadecimal identifier of the next smart large object to unload.

Each BLOB or CLOB value is appended to the appropriate file. The database server might create several files if the values are extremely large or there any many values.

In an UNLOAD TO file, a BLOB or CLOB column value appears as follows:

start_off, end_off, client_path

In this format, start_off is the starting offset of the smart large-object value within the file, end_off is the length of the BLOB or CLOB value, and client_path is the pathname for the client file.

For example, suppose the database server unloads the following CLOB values.

CLOB Value Size
1
2048

2
4096

3
1024

4
1024

5
2048

If the first CLOB value has a hexadecimal identifier of 203b2, the database server creates the clob203b2 file to hold this first value. It then unloads the next four CLOB values to the clob203b2 file. The corresponding fields in the UNLOAD TO file appears as follows:

|0, 2048, /usr/apps/clob203b2|

|2049, 6145, /usr/apps/clob203b2|

|6146, 7170, /usr/apps/clob203b2|

|7171, 8195, /usr/apps/clob203b2|

|8196, 10244, /usr/apps/clob203b2|

If the database server unloaded additional CLOB values, it would store these values in the clob203b2 file until this file reached a size of 2 gigabytes. It would then create a new file of the form clob########, with the # symbols replaced by the hexadecimal identifier of the first CLOB value in the file.

Unloading Complex Types

In an UNLOAD TO file, complex types appear as follows:

Collections are introduced with the appropriate constructor (SET, MULTISET, LIST), and have their elements enclosed in braces ({}) and separated with a comma, as follows:

constructor{val1 , val2 , ... }

SET

SET (INTEGER NOT NULL)

UNLOAD TO

|SET{1 , 3 , 4}|

Row types (named and unnamed) have their fields enclosed with parentheses and separated with the field separator, as follows:

(val1 | val2 | ... )

ROW

UNLOAD TO

|(1 | abc)|

DELIMITER Clause

Use the DELIMITER clause to identify the delimiter that separates the data contained in each column in a row in the output file. If you omit this clause, DB-Access checks the DBDELIMITER environment variable.

If DBDELIMITER has not been set, the default delimiter is the vertical bar (|). See Chapter 3 of the Informix Guide to SQL: Syntax for information about setting the DBDELIMITER environment variable.

You can specify the TAB (= CTRL-I) or <blank> (= ASCII 32) as the delimiter symbol. You cannot use the following as the delimiter symbol:

Backslash (\)
New-line character (= CTRL-J)
Hexadecimal numbers (0 to 9, a to f, A to F)

Do not use the backslash (\) as a field separator or UNLOAD delimiter. It serves as an escape character to inform the UNLOAD statement that the next character is to be interpreted as part of the data.

The following statement specifies the semicolon (;) as the delimiter character:

UNLOAD TO 'cust.out' DELIMITER ';'

	SELECT fname, lname, company, city

		FROM customer

References

See the LOAD and SELECT statements in this manual.

In the Guide to GLS Functionality, see the discussion of the GLS aspects of the UNLOAD statement.

In the Informix Migration Guide, see the task-oriented discussion of the UNLOAD statement and other utilities for moving data.

UNLOCK TABLE

Use the UNLOCK TABLE statement in a database without transactions to unlock a table that you previously locked with the LOCK TABLE statement.

Usage

You can lock a table if you own the table or if you have the Select privileges on the table, either from a direct grant or from a grant to public. You can only unlock a table that you locked. You cannot unlock a table that another process locked. Only one lock can apply to a table at a time.

The table name either is the name of the table you are unlocking or a synonym for the table. Do not specify a view or a synonym of a view.

To change the lock mode of a table in a database without transactions, use the UNLOCK TABLE statement to unlock the table, then issue a new LOCK TABLE statement.

The UNLOCK TABLE statement fails if it is issued within a transaction. Table locks set within a transaction are released automatically when the transaction completes.

ANSI

You should not issue an UNLOCK TABLE statement within an ANSI-compliant database. The UNLOCK TABLE statement fails if it is issued within a transaction, and a transaction is always in effect in an ANSI-compliant database.

References

See the COMMIT WORK, ROLLBACK WORK, and LOCK TABLE statements in this manual.

UPDATE

Use the UPDATE statement to change the values in one or more columns or one or more elements in an SPL or INFORMIX-ESQL/C collection variable.

Syntax

Element Purpose Restrictions Syntax
derived column
An alias for the column name used in a SET clause to update a collection.
You can only specify a derived column if the collection being updated is a collection of opaque, distinct, built-in, or collection data types. You cannot specify a derived column for collections of named or unnamed row types.
Identifier, p. 1-966

cursor name
The name of the cursor to be used by the UPDATE statement. The current row of the active set for this cursor is updated when the UPDATE statement is executed. See "WHERE CURRENT OF Clause" for more information on this parameter.
You cannot update a row with a cursor if that row includes aggregates. The specified cursor (as defined in the SELECT...FOR UPDATE portion of a DECLARE statement) can contain only column names. If the cursor was created without specifying particular columns for updating, you can update any column in a subsequent UPDATE...WHERE CURRENT OF statement. But if the DECLARE statement that created the cursor specified one or more columns in the FOR UPDATE clause, you can update only those columns in a subsequent UPDATE...WHERE CURRENT OF statement.
Identifier, p. 1-966

Usage

Use the UPDATE statement to update any of the following types of objects:

A row in a table: a single row, a group of rows, or all rows in a table

E/C
An element in a collection variable
A ESQL/C row variable: a field or all fields

For information on how to update elements of a collection variable, see "Updating a Collection Variable". The other sections of this UPDATE statement describe how to update a row in a table.

To update data in a table, you must either own the table or have the Update privilege for the table (see the GRANT statement on page 1-461). To update data in a view, you must have the Update privilege, and the view must meet the requirements that are explained in "Updating Rows Through a View".

If you omit the WHERE clause, all rows of the target table are updated.

If you are using effective checking, and the checking mode is set to IMMEDIATE, all specified constraints are checked at the end of each UPDATE statement. If the checking mode is set to DEFERRED, all specified constraints are not checked until the transaction is committed.

DB

If you omit the WHERE clause and are in interactive mode, DB-Access does not run the UPDATE statement until you confirm that you want to change all rows. However, if the statement is in a command file, and you are running from the command line, the statement executes immediately.

Updating Rows Through a View

You can update data through a single-table view if you have the Update privilege on the view (see the GRANT statement on page 1-461). To do this, the defining SELECT statement can select from only one table, and it cannot contain any of the following elements:

DISTINCT keyword
GROUP BY clause
Derived value (also called a virtual column)
Aggregate value

You can use data-integrity constraints to prevent users from updating values in the underlying table when the update values do not fit the SELECT statement that defined the view. For further information, refer to the WITH CHECK OPTION discussion in the CREATE VIEW statement on page 1-289.

Because duplicate rows can occur in a view even though the underlying table has unique rows, be careful when you update a table through a view. For example, if a view is defined on the items table and contains only the order_num and total_price columns, and if two items from the same order have the same total price, the view contains duplicate rows. In this case, if you update one of the two duplicate total price values, you have no way to know which item price is updated.

Important: You cannot update rows to a remote table through views with check options.

Updating Rows in a Database Without Transactions

If you are updating rows in a database without transactions, you must take explicit action to restore updated rows. For example, if the UPDATE statement fails after updating some rows, the successfully updated rows remain in the table. You cannot automatically recover from a failed update.

Updating Rows in a Database with Transactions

If you are updating rows in a database with transactions, and you are using transactions, you can undo the update using the ROLLBACK WORK statement. If you do not execute a BEGIN WORK statement before the update, and the update fails, the database server automatically rolls back any database modifications made since the beginning of the update.

ANSI

If you are updating rows in an ANSI-compliant database, transactions are implicit, and all database modifications take place within a transaction. In this case, if an UPDATE statement fails, you can use the ROLLBACK WORK statement to undo the update.

When you use INFORMIX-Universal Server, you are within an explicit transaction, and the update fails, the database server automatically undoes the effects of the update.

Locking Considerations

When Universal Server selects a row with the intent to update, the update process acquires an update lock. Update locks permit other processes to read, or share, a row that is about to be updated but do not let those processes update or delete it. Just before the update occurs, the update process promotes the shared lock to an exclusive lock. An exclusive lock prevents other processes from reading or modifying the contents of the row until the lock is released.

Universal Server allows only one update lock at a time on a row or a page (the type of lock depends on the lock mode that is selected in the CREATE TABLE or ALTER TABLE statements). An update process can acquire an update lock on a row or a page that has a shared lock from another process, but you cannot promote the update lock from shared to exclusive (and the update cannot occur) until the other process releases its lock.

If the number of rows affected by a single update is very large, you can exceed the limits placed on the maximum number of simultaneous locks. If this occurs, you can reduce the number of transactions per UPDATE statement, or you can lock the page or the entire table before you execute the statement.

SET Clause

The SET clause identifies the columns to be updated and assigns values to each column. The clause supports the following formats:

A single-column SET clause, which pairs a single column to a single expression
A multiple-column SET clause, which lists multiple columns and sets them equal to corresponding expressions.

Single-Column SET Clause

Element Purpose Restrictions Syntax
column name
The name of the column that you want to update
You cannot update `SERIAL` or `SERIAL8` columns. You can use this syntax to update a row column.
An expression list can include an `SQL` subquery that returns a single row of multiple values as long as the number of columns named in the column list equals the number of values that the expressions in the expression list produce.
Identifier, p. 1-966

You can use a SET clause to set a single column to a single expression. A single column in a SET clause can be a named row type column or an unnamed row type column. When you use UPDATE to update a database column, you can include any number of single-column to single-expressions in the UPDATE statement. The following examples illustrate the single-column to single-expression form of the SET clause:

UPDATE  customer

	SET  address1  =  '1111  Alder  Court',

		city  =  'Palo  Alto',

		zipcode  =  '94301'

	WHERE  customer_num  =  103

 

UPDATE  orders

	SET  ship_charge  =

		(SELECT  SUM(total_price)  *  .07  

			FROM  items

			WHERE  orders.order_num  =  items.order_num)

		WHERE  orders.order_num  =  1001

 

UPDATE  stock

	SET  unit_price  =  unit_price  *  1.07 



UPDATE   empinfo
	SET  name  =  ROW('dennis', 'banks')
		WHERE emp_id  =  322

For more information on the column values that are valid in a SET clause, see "SET-Clause Values". For more information on how to specify values of a row column in a SET clause, see "Updating Row-Type Columns".

E/C

If you use UPDATE to update a collection variable (an UPDATE with a Collection Derived Table segment), you can include only one pair of column name and column value. Furthermore, you cannot include complex expressions as the column values. Column values are restricted to literal values or collection variables. For more information, see "Updating a Collection Variable".

Multiple-Column SET Clause

Element Purpose Restrictions Syntax
*
Indicator that all columns in the specified table or view are to be updated
The restrictions that are discussed under column name also apply to the asterisk ().
The asterisk () is a literal value with a special meaning in this statement.

column name
The name of the column that you want to update
You cannot update `SERIAL` or `SERIAL8` columns. You cannot use this syntax to update a row column. For more information, see page 1-789.
An expression list can include an `SQL` subquery that returns a single row of multiple values as long as the number of columns named in the column list equals the number of values that the expressions in the expression list produce.
Identifier, p. 1-966

You can use a second format of the SET clause to set multiple columns to multiple expression. The SET clause offers the following options for listing a series of columns you intend to update:

Explicitly list each column, placing commas between columns and enclosing the set of columns in parentheses.
Implicitly list all columns in table name using the asterisk notation (*).

To complete the multiple-column SET clause, you must list each expression explicitly, placing commas between expressions and enclosing the set of expressions in parentheses. The number of columns in the column list must be equal to the number of expressions in the expression list, unless the expression list includes an SQL subquery. The following examples illustrate this form of the multiple-column SET clause:

UPDATE  customer

	SET  (fname,  lname)  =  ('John',  'Doe')

	WHERE  customer_num  =  101

 

 

UPDATE  manufact

	SET  *  =  ('HNT',  'Hunter')

	WHERE  manu_code  =  'ANZ'

An expression list can include an SQL subquery that returns a single row of multiple values as long as the number of columns named, explicitly or implicitly, equals the number of values produced by the expression or expressions that follow the equal sign. The following examples show the use of SQL subqueries in a multiple-column SET clause:

UPDATE  items

	SET  (stock_num,  manu_code,  quantity)  =  

		(  (SELECT  stock_num,  manu_code  FROM  stock  

			WHERE  description  =  'baseball'),  2)

	WHERE  item_num  =  1  AND  order_num  =  1001

 

 

UPDATE  table1

	SET  (col1,  col2,  col3)  =

		((SELECT  MIN  (ship_charge),

			MAX  (ship_charge)  FROM  orders),

			'07/01/1993')

	WHERE  col4  =  1001

For more information on the column values that are valid in a SET clause, see "SET-Clause Values".

SET-Clause Values

You can express a value in a single-column or multiple-column SET clause in any of following ways:

As an expression
As a SELECT statement
As a NULL value

Subset of Expressions Allowed in the SET Clause

When you update a table or view, you cannot use an expression comprised of aggregate functions in the SET clause. For a complete description of syntax and usage, see the Expression segment on page 1-880.

Subset of SELECT Statements Allowed in the SET Clause

A SELECT statement used in a SET clause can return more than one column of information in a row. However, the SELECT statement cannot return more than one row of information in a table. For a complete description of syntax and usage, refer to the SELECT statement on page 1-596.

Updating a Column to NULL

You can use the NULL keyword to modify a column value when you use the UPDATE statement. For a customer whose previous address required two address lines but now requires only one, you would use the following entry:

UPDATE customer SET

	address1 = '123 New Street',

	address2 = null,

	city = 'Palo Alto',

	zipcode = '94303'

	WHERE customer_num = 134

Updating Row-Type Columns

You use the SET clause to update a named row type or unnamed row type column. For example, suppose you define the following named row type and a table that contains columns of both named and unnamed row types:

CREATE ROW TYPE address_t 
(
	street CHAR(20), 
	city CHAR(15),

	state CHAR(2)

);


CREATE TABLE empinfo 

(

	emp_id INT
	name ROW ( fname CHAR(20), lname CHAR(20)),

	address address_t

);

To update an unnamed row type, specify the ROW constructor before the parenthesized list of field values. The following statement updates the name column (an unnamed row type) of the empinfo table:

UPDATE empinfo
SET name = ROW('John','Williams')
WHERE emp_id =455

To update a named row type, specify the ROW constructor before the parenthesized list of field values and use the cast operator (::) to cast the row value as a named row type. The following statement updates the address column (a named row type) of the empinfo table:

UPDATE empinfo
SET address = ROW('103 Baker St','Tracy','CA')::address_t
WHERE emp_id = 3568

For more information on the syntax for ROW constructors, see "Constructor Expressions" in the Expression segment. See also the Literal Row segment on page 1-1003.

ESQL

The row-column SET clause can only support literal values for fields. To use a variable to specify a field value, you must select the row into a row variable, use host variables for the individual field values, then update the row column with the row variable. For more information, see "Updating a Row Variable".

E/C

You can use ESQL/C host variables to insert non-literal values as:

an entire row type into a column.

row

SET

individual fields of a row type.

row

collection

SET

UPDATE

When you use a row variable in the SET clause, the row variable must contain values for each field value. For information on how to insert values into a row variable, see "Updating a Row Variable".

To update only some of the fields in a row, you can perform one of the following operations:

Specify the field names with field projection for all fields whose values remain unchanged.

UPDATE

street

city

address

empinfo

UPDATE  empinfo

SET  address  =  ROW('23 Elm St', 'Sacramento',

					address.state)

      WHERE  emp_id  =  433

address.state

ESQL

Select the row into a row variable and update the desired fields.

For more information, see

"Updating a Row Variable"

Updating Collection Columns

You can use the SET clause to insert literal values into a collection column, which can be a LIST, MULTISET, or SET. For example, suppose you define the tab1 table as follows:

CREATE TABLE tab1 

(

	int1 INTEGER, 

	list1 LIST(ROW(a INTEGER, b CHAR(5)) NOT NULL),

	dec1 DECIMAL(5,2)

)

The following UPDATE statement updates a row in the tab1 table with literal values:

EXEC SQL update tab1 

	set list1 = "LIST{ROW(4, 'opqrs'),

				ROW(5, 'tuvwx'),

				ROW(6, 'yxabc')}"

	where int1 = 5

The collection column, list1, in the tab1 row has three elements, and each element is an unnamed row type with an INTEGER field and a CHAR(5) field. For more information on the syntax for literal collection values, see "Literal DATETIME".

E/C

You can use an ESQL/C collection variable or an SPL collection variable to update:

an entire collection into a column.

variable name

SET

For example, the following ESQL/C code fragment updates with the elements of the a_set host variable the set_col column of the row in the tab_a table whose int_col value is 6:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(smallint not null) a_set;

EXEC SQL END DECLARE SECTION;
...
EXEC SQL update tab_a set set_col = :a_set

	where int_col = 6;

individual elements in a collection.

To update a collection column with non-literal values, you must first update the elements in a collection variable and then specify the collection variable in the SET clause of an UPDATE statement. For information on how to update elements of a collection variable, see

"Updating a Collection Variable"

Updating Values in Opaque-Type Columns

Some opaque data types require special processing when they are updated. For example, if an opaque data type contains spatial or multirepresentational data, it might provide a choice of how to store the data: inside the internal structure or, for very large objects, in a smart large object.

This processing is accomplished by calling a user-defined support function called assign(). When you execute the UPDATE statement on a table whose rows contains one of these opaque types, the database server automatically invokes the assign() function for the type. The assign() function can make the decision of how to store the data. For more information about the assign() support function, see the Extending INFORMIX-Universal Server: Data Types manual.

WHERE Clause

The WHERE clause lets you limit the rows that you want to update. If you omit the WHERE clause, every row in the table is updated.

The WHERE clause consists of a standard search condition. (For more information, see the SELECT statement on page 1-596). The following example illustrates a WHERE condition within an UPDATE statement. In this example, the statement updates three columns (state, zipcode, and phone) in each row of the customer table that has a corresponding entry in a table of new addresses called new_address.

UPDATE  customer

	SET  (state,  zipcode,  phone)  =

		((SELECT  state,  zipcode,  phone  FROM  new_address N

			WHERE  N.cust_num  =

				 customer.customer_num))

		WHERE  customer_num  IN

			(SELECT  cust_num  FROM  new_address)

Updating and the WHERE Clause

ANSI

When you update a table in an ANSI-compliant database using an UPDATE statement with the WHERE clause, and the database server finds no matching rows, the database server issues a warning. You can detect this warning condition in either of the following ways:

The GET DIAGNOSTICS statement sets the RETURNED_SQLSTATE field to the value `02000.' In an SQL API application, the SQLSTATE variable contains this same value.
In an SQL API application, the sqlca.sqlcode and SQLCODE variables contain the value 100.

The database server also sets SQLSTATE and SQLCODE to these values if the UPDATE ... WHERE ... is a part of a multistatement prepare and the database server returns no rows.

In a database that is not ANSI compliant, the database server does not return a warning when it finds no matching rows for the WHERE clause of an UPDATE statement. The SQLSTATE code is `00000' and the SQLCODE code is zero (0). However, if the UPDATE ... WHERE ... is a part of a multistatement prepare, and no rows are returned, the database server does issue a warning. It sets SQLSTATE to `02000' and SQLCODE value to 100.

For additional information about the SQLSTATE code, see the GET DIAGNOSTICS statement in this manual. For information about the SQLCODE code, see the description of the sqlca structure in the Informix Guide to SQL: Tutorial.

WHERE CURRENT OF Clause

ESQL

You can use the WHERE CURRENT OF clause to update either of the following objects:

The current row of the active set of a cursor
The current element of a collection cursor (INFORMIX-ESQL/C only)

You access both of these objects with an update cursor. An update cursor is a sequential cursor that is associated with a SELECT statement but can modify and delete the contents of the cursor. For more information on the update cursor, see page 1-310.

ESQL

To use the WHERE CURRENT OF clause, you must have previously used the DECLARE statement with the FOR UPDATE clause to define the cursor name for the update cursor. (See the DECLARE statement on page 1-303.)

SPL

Before you can use the WHERE CURRENT OF clause, you must declare a cursor with the FOREACH statement. (See the FOREACH statement on page 2-27.)

ANSI

All select cursors are potentially update cursors in ANSI-compliant databases. You can use the WHERE CURRENT OF clause with any select cursor.

Tip: You can use an update cursor to perform updates that are not possible with the UPDATE statement.

Updating the Current Row

ESQL

When you specify a table or view name in the FROM clause of the SELECT, the DECLARE statement defines a cursor that populates an active set with the rows of the specified tables or views. The UPDATE...WHERE CURRENT OF statement updates values in the current row of the active set. However, you cannot update a row with a cursor if that row includes aggregates. The cursor named in the WHERE CURRENT OF clause can only contain column names. The UPDATE statement does not advance the cursor to the next row, so the current row position remains unchanged.

To use the WHERE CURRENT OF clause, you must have previously used the DECLARE statement with the FOR UPDATE clause to define the cursor name for the update cursor. (See the DECLARE statement on page 1-303.) If you created the cursor without specifying any columns for updating, you can update any column in a subsequent UPDATE...WHERE CURRENT OF statement.

You can restrict the effect of the WHERE CURRENT OF clause if the DECLARE statement that created the cursor specified one or more columns in the FOR UPDATE clause. In this case, you are restricted to updating only those columns in a subsequent UPDATE...WHERE CURRENT OF statement. The advantage to specifying columns in the FOR UPDATE clause of a DECLARE statement is speed. Universal Server can usually perform updates more quickly if columns are specified in the DECLARE statement.

The following INFORMIX-ESQL/C example illustrates the WHERE CURRENT OF clause of the UPDATE statement. In this example, updates are performed on a range of customers who receive 10-percent discounts (assume that a new column, discount, is added to the customer table). The UPDATE statement is prepared outside the WHILE loop to ensure that parsing is done only once. (For more information, see the PREPARE statement on page 1-541.)

char answer [1] = 'y';

EXEC SQL BEGIN DECLARE SECTION;

	char fname[32],lname[32];

	int low,high;

EXEC SQL END DECLARE SECTION;



main()

{

	EXEC SQL connect to 'stores7';

	EXEC SQL prepare sel_stmt from

		'select  fname, lname  from  customer  \

		 where  cust_num  between  ?  and  ?  for  update';

	EXEC SQL declare  x  cursor  for  sel_stmt;

	printf("\nEnter lower limit customer number: ");

	scanf("%d", &low);

	printf("\nEnter upper limit customer number: ");

	scanf("%d", &high);

	EXEC SQL open x using :low, :high;

	EXEC SQL prepare u from

		'update customer set discount = 0.1  \

		where current of x';

		

	while  (1)

		{

		EXEC SQL fetch  x  into  :fname, :lname;

 		if  ( SQLCODE ==  SQLNOTFOUND)

			break;

	 	}

	printf("\nUpdate %.10s %.10s (y/n)?",  fname, lname);

	if  (answer = getch() == 'y')

		EXEC SQL execute  u;

	EXEC SQL close x;

}

Updating A Collection Element

ESQL

You declare a collection cursor when you associate a cursor with SELECT statement that includes a Collection Derived Table clause. You use one of the following statements to declare a collection cursor:

In an ESQL/C program, use the DECLARE statement.

For more information, see

"Associating a Cursor With a Collection Variable"

DECLARE

In an SPL routine, use the FOREACH statement.

For more information, see the FOREACH statement on

page 2-27

A collection cursor is an update cursor by default. However, you can optionally specify the FOR UPDATE clause with the SELECT statement. With an update cursor, you can use the UPDATE...WHERE CURRENT OF statement to update the current element of a collection cursor. For more information, see "Updating a Collection Variable".

Important: You can only declare a select cursor on a collection variable. Neither INFORMIX-ESQL/C nor SPL supports cursors on row variables. For more information, see "Updating a Row Variable".

Updating a Collection Variable

The UPDATE statement with the Collection Derived Table segment allows you to update elements in a collection variable. The Collection Derived Table segment identifies the collection variable in which to update the elements. For more information on the Collection Derived Table segment, see page 1-831.

E/C

In an INFORMIX-ESQL/C program, declare a host variable of type collection for a collection variable. This collection variable can be typed or untyped.

SPL

In an SPL routine, declare a variable of type COLLECTION, LIST, MULTISET, or SET for a collection variable. This collection variable can be typed or untyped.

To update elements, follow these steps:

1. Create a collection variable in your SPL routine or ESQL/C program.

2. Optionally, select a collection column into the collection variable with the SELECT statement (without the Collection Derived Table segment).

3. Update elements of the collection variable with the UPDATE statement and the Collection Derived Table segment.

4. Once the collection variable contains the correct elements, you then use the UPDATE or INSERT statement on a table or view name to save the collection variable in the collection column (SET, MULTISET, or LIST).

The UPDATE statement and the Collection Derived Table segment allow you to perform the following operations on a collection variable:

Update a particular element in the collection

You must declare an update cursor for the collection variable and use UPDATE with the WHERE CURRENT OF clause. For more information on how to use an update cursor with ESQL/C, see the DECLARE statement. For more information on how to use an update cursor with SPL, see

"FOREACH"

The application or SPL routine must position the update cursor on the element to be updated and then use UPDATE...WHERE CURRENT OF to update this value. For more information on the WHERE CURRENT OF clause of UPDATE, see

page 1-793

Update all elements in the collection to the same value

For example, the following UPDATE changes all elements in the a_list ESQL/C collection variable to a value of 16:

EXEC SQL update table(:a_list) (list_elmt)

		set list_elmt = 16;

SPL

The following UPDATE changes all elements in a_list to the value 16 in an SPL routine:

UPDATE TABLE (a_list) (list_elmt)

		SET list_elmt = 16;

In these examples, the derived column list_elmt provides an alias to identify an element of the collection. No update cursor is required to update all elements of a collection.

An UPDATE of an element or elements in a collection variable cannot include a WHERE clause. When you use UPDATE to update a collection variable, you can only include one pair of column-name and column-value. Furthermore, you cannot include complex expressions as the column values. Column values are restricted to literal values or collection variables.

The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the collection column with one of the following SQL statements:

To update the collection column in the table with the collection variable, use an UPDATE statement on a table or view name and specify the collection variable in the SET clause.

For more information, see

"Updating Collection Columns"

To insert a collection in a column, use the INSERT statement on a table or view name and specify the collection variable in the VALUES clause.

For more information, see

"Inserting Into a Collection Variable"

INSERT

E/C

Suppose that the set_col column of a table called table1 is defined as a SET and that it contains the values {1,8,4,5,2}. The following ESQL/C program changes the element whose value is 4 to a value of 10.

main

{
	EXEC SQL BEGIN DECLARE SECTION;

		int a;

		collection b;

	EXEC SQL END DECLARE SECTION;

	EXEC SQL allocate collection :b;
	EXEC SQL select set_col into :b from table1

		where int_col = 6;



	EXEC SQL declare set_curs cursor for

		select * from table(:b)

		for update;

	EXEC SQL open set_curs;

	while (SQLCODE != SQLNOTFOUND)

	{

		EXEC SQL fetch set_curs into :a;

		if (a = 4)

		{

			EXEC SQL update table(:b)(x)

				set x = 10

				where current of set_curs;

			break;

		}

	}

	EXEC SQL update table1 set set_col = :b

		where int_col = 6;

	EXEC SQL deallocate collection :b;

	EXEC SQL close set_curs;

	EXEC SQL free set_curs;

}

After you execute this ESQL/C program, the SET column in table1 contains the values {1,8,10,5,2}.

This ESQL/C program defines two collection variables, a and b, and selects a SET from table1 into b. The WHERE clause ensures that only one row is returned. Then, the program defines a collection cursor, which selects elements one at a time from b into a. When the program locates the element with the value 4, the first UPDATE statement changes that element value to 10 and exits the loop.

In the first UPDATE statement, x is a derived column name used to update the current element in the collection derived table. The second UPDATE statement updates the base table table1 with the new collection. For information on how to use collection host variables in an ESQL/C program, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

SPL

The following SPL routine performs the same task as preceding ESQL/C program.

CREATE PROCEDURE test5()

	DEFINE a SMALLINT;
	DEFINE b SET(SMALLINT NOT NULL);

	SELECT set_col INTO b FROM table1

		WHERE id = 6;
		-- select the collection from the database 
		-- into a collection variable

	FOREACH cursor1 FOR
		SELECT * INTO a FROM TABLE(b);
			-- select one element at a time
			-- from the collection derived table b
		IF a = 4 THEN
			UPDATE TABLE(b)(x)
				SET x = 10
				WHERE CURRENT OF cursor1;
			EXIT FOREACH;
			-- if the element has the value 4
			-- update it to have the value 10 and exit
		END IF;
	END FOREACH;

	UPDATE table1 SET set_col = b;

		WHERE id = 6;
		-- update the base table with the new collection

END PROCEDURE;

After you execute this SPL routine, the SET stored in table1 will contain the values {1,8,10,5,2}. This SPL routine defines two collection variables, a and b, and selects a SET from table1 into b. For more information on how to use SPL collection variables, see Chapter 14 in the Informix Guide to SQL: Tutorial.

You can also use a collection variable as a variable name in the SET clause to update all elements of a collection. For more information, see "Updating Collection Columns".

Updating a Row Variable

E/C

The UPDATE statement with the Collection Derived Table segment allows you to update fields in a row variable. The Collection Derived Table segment identifies the row variable in which to update the fields. For more information on the Collection Derived Table segment, see page 1-831.

To update fields, follow these steps:

1. Create a row variable in your ESQL/C program.

2. Optionally, select a row-type column into the row variable with the SELECT statement (without the Collection Derived Table segment).

3. Update fields of the row variable with the UPDATE statement and the Collection Derived Table segment.

4. Once the row variable contains the correct fields, you then use the UPDATE or INSERT statement on a table or view name to save the row variable in the row column (named or unnamed).

The UPDATE statement and the Collection Derived Table segment allow you to update a particular field or group of fields in the row variable. You specify the new field value(s) in the SET clause. For example, the following UPDATE changes the x and y fields in myrect ESQL/C row variable:

EXEC SQL BEGIN DECLARE SECTION;

	row (x int, y int, length float, width float) myrect;

EXEC SQL END DECLARE SECTION;

.
.
.

EXEC SQL select into :myrect from rectangles

	where area = 64;

EXEC SQL update table(:myrect) 

	set x=3, y=4;

Suppose that after the SELECT statement, the myrect2 variable has the values x=0, y=0, length=8, and width=8. After the UPDATE statement, myrect2 variable has field values of x=3, y=4, length=8, and width=8.

You cannot use a row variable in the Collection Derived Table segment of an INSERT statement. However, you can use the UPDATE statement and the Collection Derived Table segment to insert new field values into a row host variable, as long as you specify a value for every field in the row. For example, the following code fragment inserts new field values into the myrect row variable and then inserts this row variable into the database:

EXEC SQL update table(:myrect)

	set x=3, y=4, length=12, width=6;

EXEC SQL insert into rectangles

	values (72, :myrect);

If the row variable is an untyped variable, you must use a SELECT statement before the UPDATE so that ESQL/C can determine the data types of the fields. An UPDATE of a field or fields in a row variable cannot include a WHERE clause.

The row variable stores the fields of the row. However, it has no intrinsic connection with a database column. Once the row variable contains the correct field values, you must then save the variable into the row column with one of the following SQL statements:

To update the row column in the table with contents of the row variable, use an UPDATE statement on a table or view name and specify the row variable in the SET clause.

For more information, see

"Updating Row-Type Columns"

UPDATE

To insert a row in a column, use the INSERT statement on a table or view name and specify the row variable in the VALUES clause.

For more information, see

"Inserting Values into Row-Type Columns"

For more information on how to use SPL row variables, see Chapter 14 in the Informix Guide to SQL: Tutorial. For more information on how to use ESQL/C row variables, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

References

See the DECLARE, INSERT, OPEN, and SELECT statements in Chapter 1 of this manual. See also the FOREACH statement in Chapter 2 of this manual.

In the Informix Guide to SQL: Tutorial, see the discussion of the UPDATE statement in Chapter 6 and Chapter 12. In the Guide to GLS Functionality, see the discussion of the GLS aspects of the UPDATE statement.

For information on how to access row and collections with ESQL/C host variables, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

START VIOLATIONS TABLE

Usage

Starting Violations and Diagnostics Tables and Specifying Their Names

Specifying the Maximum Number of Rows in the Diagnostics Table

Privileges Required for Starting Violations Tables

Structure of the Violations Table

Example of Privileges on the Violations Table

Using the Violations Table

Example of a Violations Table

Example of Privileges on the Diagnostics Table

Using the Diagnostics Table

Example of a Diagnostics Table

Usage

Example of Dropping a Violations and Diagnostics Table

Privileges Required for Stopping a Violations Table

References

DELIMITER Clause

References

Usage

Subset of Expressions Allowed in the SET Clause

Updating Row-Type Columns

Updating and the WHERE Clause

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book