SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-151
Considerations for INSERT
avoid dynamic recompilation because of a change in the transaction mode, consider
specifying access options for individual DML statements instead of using SET
TRANSACTION.
Use of a VALUES Clause for the Source Query Expression
If the query expression consists of the VALUES keyword followed by rows of values,
each row consists of a list of value expressions or a row subquery (a subquery that
returns a single row of column values). A value in a row can also be a scalar subquery
(a subquery that returns a single row consisting of a single column value).
Within a VALUES clause, the operands of a value expression can be numeric, string,
datetime, or interval values; however, an operand cannot reference a column (except
in the case of a scalar or row subquery returning a value or values in its result table).
Inserting From Host Variables
To insert a row from host variables, an application program moves the new values to a
sequence of host variables, and then executes an INSERT statement to transfer the
row of values from the host variables to the table or view.
In this situation, the query expression that defines the insert source is specified as:
VALUES (variable-spec [,variable-spec]...)
Each variable specification has the form:
:variable-name [[INDICATOR] :indicator-name]
The variable specification is a declared host variable with an optional indicator variable.
To insert null into a database, set the indicator variable to a value less than zero.
For more information, see the SQL/MX Programming Manual for C and COBOL.
Requirements for Inserted Rows
Each row to be inserted must satisfy the constraints of the table or underlying base
table of the view. A table constraint is satisfied if the check condition is not false—it is
either true or has an unknown value.
Using Compatible Data Types
To insert a row, you must provide a value for each column in the table that has no
default value. The data types of the values in each row to be inserted must be
compatible with the data types of the corresponding target columns.
Inserting Character Values
Any character string data type is compatible with all other character string data types
that have the same character set. For fixed length, an inserted value shorter than the
column length is padded on the right with single-byte ASCII blanks (HEX 20). If the
Embed
C/COBOL