The INSERT statement inserts a row into a database table.
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.
If a schema name is not specified, the default schema for the broker'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.
For further information about handling database errors, see Capturing database state.
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 bitstream 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);
DECLARE propRef REFERENCE TO InputRoot.Properties; DECLARE msgBitStream BLOB ASBITSTREAM(InputRoot.XMLNS, propRef.Encoding, propRef.CodedCharSetId); INSERT INTO Database.TABLE1(MSGDATA) VALUES (msgBitStream);
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 above 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 codepage
and encoding, the database table can be extended to hold all the numeric
fields of MQMD header.
For example, a message flow running
on AIX® inserts a message bitstream 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)
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);
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 bitstream from a database, based on the two previous examples, see Selecting bitstream data from a database.