Inserting Rows into a Database with Transactions

If you are inserting rows into a database and you are using explicit transactions, use the ROLLBACK WORK statement to undo the INSERT. If you do not execute BEGIN WORK before the INSERT, and the INSERT fails, the database server automatically rolls back any data modifications made since the beginning of the INSERT. If you are using an explicit transaction, and the INSERT fails, the database server automatically undoes the effects of the INSERT.

In an ANSI-compliant database, transactions are implicit, and all database modifications take place within a transaction. In this case, if an INSERT statement fails, use the ROLLBACK WORK statement to undo the insertions.

Tables that you create with the RAW logging type are never logged. Thus, raw tables are not recoverable, even if the database uses logging. For information about raw tables, refer to the IBM Informix Guide to SQL: Reference.

Rows that you insert with a transaction remain locked until the end of the transaction. The end of a transaction is either a COMMIT WORK statement, where all modifications are made to the database, or a ROLLBACK WORK statement, where none of the modifications are made to the database. If many rows are affected by a single INSERT statement, you can exceed the maximum number of simultaneous locks permitted. To prevent this situation, either insert fewer rows per transaction, or lock the page (or the entire table) before you execute the INSERT statement.

VALUES Clause

The VALUES clause can specify values to insert into one or more columns. When you use the VALUES clause, you can insert only one row at a time.

Each value that follows the VALUES keyword is assigned to the corresponding column listed in the INSERT INTO clause (or in column order, if a list of columns is not specified). If you are inserting a quoted string into a column, the maximum length that can be inserted without error is 256 bytes.

VALUES Clause:
 
|--VALUES------------------------------------------------------->
 
      .-,-------------------------------------------.
      V                                             |
>--(----+-input_var--+-------------------------+--+-+--)--------|
        |            |  (1)                    |  |
        |            +--------:--indicator_var-+  |
        |            |  (2)                    |  |
        |            '--------$--indicator_var-'  |
        +-+-NULL--------------------------------+-+
        | +-USER--------------------------------+ |
        | |                   (3)               | |
        | +-| Quoted String |-------------------+ |
        | |                    (4)              | |
        | +-| Literal Number |------------------+ |
        | |  (2)                           (5)  | |
        | +--------| Constant Expression |------+ |
        | |                       (6)           | |
        | '-| Column Expression |---------------' |
        |  (7)                            (8)     |
        '--------+-| Literal Collection |------+--'
                 |                 (9)         |
                 +-| Literal Row |-------------+
                 |                (10)         |
                 +-| Expression |--------------+
                 +-'literal_Boolean'-----------+
                 '-literal_opaque--------------'

Notes:

ESQL/C only
Informix extension
See Quoted String
See Literal Number
See Constant Expressions
See Column Expressions
Dynamic Server only
See Literal Collection
See Literal Row
See Expression

Element	Description	Restrictions	Syntax
indicator_var	Variable to show if SQL statement returns NULL to input_var	See the IBM Informix ESQL/C Programmer's Manual.	Language specific
input_var	Variable that holds value to insert. This can be a collection variable.	Can contain any value option of VALUES clause	Language specific
literal_opaque	Literal representation for an opaque data type	Must be recognized by the input support function of the opaque data type	See documentation of the opaque type.
literal_Boolean	Literal representation of a BOOLEAN value as a single character	Either `'t'` (TRUE) or `'f'` (FALSE)	Quoted String

In ESQL/C, if you use an input_var variable to specify the value, you can insert character strings longer than 256 bytes into a table.

For the keywords and the types of literal values that are valid in the VALUES clause, refer to Constant Expressions.