INSERT statement

The INSERT statement inserts a row into a database table.

Syntax

WHERE

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

A table reference is a special case of the field references that are used to refer to message trees. It always starts with the word Database and 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

In each case, the name can be specified directly or by an expression enclosed in braces ({...}). A directly-specified data source, schema, or table name is subject to name substitution. That is, if the name used has been declared to be a known name, the value of the declared name is used rather than the name itself (see DECLARE statement).

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

Errors can occur during insert operations. For example, the database might not be operational, or the table might have constraints defined that the new row would violate. In these cases, an exception is thrown, unless you have cleared the node property Throw Exception on Database Error. These exceptions set appropriate values for the following items, and can be dealt with by error handlers (see the DECLARE HANDLER statement):

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.

Given a message with the following generic XML body:

<A>
 <B>1</B>
 <C>2</C>
 <D>3</D>
</A>

The following INSERT statement inserts a new row into the table with the values 1, 2, and 3 for the columns A, B, and C:

INSERT INTO Database.TABLE1(A, B, C) VALUES (Body.A.B, Body.A.C, Body.A.D);

The next example shows the use of calculated data source, schema, and table names:

-- 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);

Alternatively, if the input message is in an XML domain such as 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);

If the input messages received by your message flow come from different code pages, the 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.

The following set of numeric fields are contained in the 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)

The following example uses 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);

If you want to insert an XML message into a database column that has a CHAR or VARCHAR data type, the ESQL must be modified to convert the input message to the CHAR data type before the 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.