INSERT statement
The INSERT statement inserts a row into a database table.
Syntax
A single row is inserted into the table identified by TableReference. The ColumnName list identifies those columns in the target table that are to be given specific values. These values are determined by the expressions within the VALUES clause (the first expression gives the value of the first named column, and so on). The number of expressions in the VALUES clause must be the same as the number of named columns. Any columns present in the table but not mentioned in the list are given their default values.
Table reference
Databaseand can contain any of the following elements:
- A table name only
- A schema name and a table name
- A data source name (that is, the name of a database instance), a schema name, and a table name
If a schema name is not specified, the default schema for the integration node's database user is used.
If a data source name is not
specified, the database pointed to by the node's data source
attribute
is used.
Handling errors
- SQL code
- State
- Native error
- Error text
For further information about handling database errors, see Capturing database state.
Examples
The following example assumes that the Data Source property of the Database node has been configured, and that the database it identifies has a table called TABLE1 with columns A, B, and C.
<A>
<B>1</B>
<C>2</C>
<D>3</D>
</A>
INSERT INTO Database.TABLE1(A, B, C) VALUES (Body.A.B, Body.A.C, Body.A.D);
-- Declare variables to hold the data source, schema, and table names
-- and set their default values
DECLARE Source CHARACTER 'Production';
DECLARE Schema CHARACTER 'db2admin';
DECLARE Table CHARACTER 'DynamicTable1';
-- Code which calculates their actual values comes here
-- Insert the data into the table
INSERT INTO Database.{Source}.{Schema}.{Table} (Name, Value) values ('Joe', 12.34);
Inserting a bit stream into a database
If the database column into which you want to insert data is set to a binary data type such as BLOB, the input message must be represented in bitstream form. If the input message is in the BLOB domain, use the following ESQL code:DECLARE msgBitStream BLOB InputRoot.BLOB.BLOB;
INSERT INTO Database.TABLE1(MSGDATA) VALUES (msgBitStream);
XMLNS
,
then the message tree must be serialized before the INSERT
statement.
To serialize the message tree and insert the contents into the database,
use the following ESQL code:DECLARE propRef REFERENCE TO InputRoot.Properties;
DECLARE msgBitStream BLOB ASBITSTREAM(InputRoot.XMLNS, propRef.Encoding, propRef.CodedCharSetId);
INSERT INTO Database.TABLE1(MSGDATA) VALUES (msgBitStream);
CodedCharSetID
and Encoding
information
is lost if you use the previous example. To capture CodedCharSetID
and Encoding
information,
you can extend the table with two numeric columns to store the CodedCharSetID
and Encoding
data.
To extend the table, modify the ESQL from the previous example to
insert the CodedCharSetID
and Encoding
data
into separate database columns:DECLARE propRef REFERENCE TO InputRoot.Properties;
DECLARE inCCSID INT propRef.CodedCharSetId;
DECLARE inEncoding INT propRef.Encoding;
DECLARE msgBitStream BLOB ASBITSTREAM(InputRoot.XMLNS, inEncoding, inCCSID);
INSERT INTO Database.TABLE1(MSGDATA, MSGENCODING, MSGCCSID) VALUES
(msgBitStream, inEncoding, inCCSID);
As an extension to the previous example, if you require the entire message to be stored along with its MQMD header, and use it later for reconstructing the entire message in another message flow on a different platform using a different code page and encoding, the database table can be extended to hold all the numeric fields of the MQMD header.
For example, a message flow running on AIX® inserts a message bit stream into the database table and another message flow running on Windows retrieves it and attempts to reconstruct the message along with the stored MQMD header.
BackoutCount (MQLONG)
CodedCharSetId (MQLONG)
Encoding (MQLONG)
Expiry (MQLONG)
Feedback (MQLONG)
MsgFlags (MQLONG)
MsgSeqNumber (MQLONG)
MsgType (MQLONG)
Offset (MQLONG)
OriginalLength (MQLONG)
Persistence (MQLONG)
Priority (MQLONG)
PutApplType (MQLONG)
Report (MQLONG)
Version (MQLONG)
CodedCharSetID
, Encoding
, Priority
,
and MsgSeqNumber
: DECLARE propRef REFERENCE TO InputRoot.Properties;
DECLARE mqmdRef REFERENCE TO InputRoot.MQMD;
DECLARE inCCSID INT propRef.CodedCharSetId;
DECLARE inEncoding INT propRef.Encoding;
DECLARE inPriority INT mqmdRef.Priority;
DECLARE inMsgSeqNumber INT mqmdRef.MsgSeqNumber;
DECLARE msgBitStream BLOB ASBITSTREAM(InputRoot, inEncoding, inCCSID);
INSERT INTO Database.TABLE1(MSGDATA, MSGENCODING, MSGCCSID, MSGPRIORITY,MSGSEQNUMBER)
VALUES (msgBitStream, inEncoding, inCCSID, inPriority, inMsgSeqNumber);
INSERT
statement.
In the following example, a CAST
is used to transform
the serialized message to the CHAR data type. The CodedCharSetID
and Encoding
data
are inserted into separate database columns.DECLARE propRef REFERENCE TO InputRoot.Properties;
DECLARE inCCSID INT propRef.CodedCharSetId;
DECLARE inEncoding INT propRef.Encoding;
DECLARE msgBitStream BLOB ASBITSTREAM(InputRoot.XMLNS, inEncoding, inCCSID);
DECLARE msgChar CHAR CAST(msgBitStream AS CHAR CCSID inCCSID);
INSERT INTO Database.TABLE1(MSGDATA, MSGENCODING, MSGCCSID) VALUES (msgChar, inEncoding, inCCSID);
For examples of how to extract a message bit stream from a database, based on the two previous examples, see Selecting bitstream data from a database.