Use the DatabaseRetrieve node to ensure that information in a message is up to date.
This topic contains the following sections:
Use the DatabaseRetrieve node to modify a message using information from a database. For example, you can add information to a message using a key that is contained in a message; the key could be an account number.
The DatabaseRetrieve node is contained in the Database drawer of the message flow node palette, and is represented in the workbench by the following icon:
JDBC Type | Java™ Type | ESQL Type |
---|---|---|
CHAR | String | CHARACTER |
VARCHAR | String | CHARACTER |
LONGVARCHAR | String | CHARACTER |
NUMERIC | java.math.BigDecimal | DECIMAL |
DECIMAL | java.math.BigDecimal | DECIMAL |
BIT | Boolean | BOOLEAN |
BOOLEAN | Boolean | BOOLEAN |
TINYINT | byte | INTEGER |
SMALLINT | short | INTEGER |
INTEGER | int | INTEGER |
BIGINT | long | INTEGER |
REAL | float | FLOAT |
FLOAT | double | FLOAT |
DOUBLE | double | FLOAT |
BINARY | byte[] | BLOB |
VARBINARY | byte[] | BLOB |
LONGVARBINARY | byte[] | BLOB |
DATE | java.sql.Date | DATE |
TIME | java.sql.Time | TIME |
TIMESTAMP | java.sql.Timestamp | TIMESTAMP |
If an error is found in the XPath expression of a pattern, it is reported during validation in the Message Broker Toolkit. The reported error message might include the incorrect expression string and its associated unique dynamic or static terminal name, or the string might be marked as broker within the table.
The DatabaseRetrieve node looks up values from a database and stores them as elements in the message assembly trees. The type of information that is obtained from the database is checked to ensure that it is of the correct type to be stored in an existing location in the outgoing message. If it is not, an exception occurs. If a message element already exists in the message, the new value overwrites the old value. If the target element does not exist, it is created and the value is stored as a type that matches that of the database column from which the value was retrieved.
The node needs database information that is used to form a multi-table SQL select query, which can have multiple test conditions. Information that is collected to form a query includes a list of table qualified column values to retrieve and a list of test conditions, which form the WHERE clause of the SELECT statement. Table qualified column values can form the left hand operand in a test condition. Choose a comparison operator to apply to this operand and, optionally, specify a right hand operand to complete the test condition. The operator could be a NULL comparison test, in which no right hand operand is needed. The value of the right hand operand can be a database type (such as Integer, Boolean, or Long), another table qualified column (specified as an integer index into the table where it is defined), or a value that is acquired from an element in the incoming message, as expressed through either an XPath path location expression, or an ESQL field reference expression. If the path does not exist in the input message, an exception occurs. If the query returns multiple rows, the first row is chosen and the rest are ignored.
It can be useful to combine a DatabaseRetrieve node with other message flow nodes. For example, you can use an XSLTransform node to manipulate data before or after the DatabaseRetrieve node is invoked.
The DatabaseRetrieve node has one input terminal (In) and three output terminals (Out, keyNotFound, and Failure). If the message is modified successfully, it is routed to the Out terminal. If the message is not modified successfully, it is routed, unchanged, to the keyNotFound terminal. If a failure is detected during processing, the message is routed to the Failure terminal.
For example, if connectionURLFormat = jdbc:oracle:thin:@serverName:portNumber:connectionUrlFormatAttr1, and connectionUrlFormatAttr1 = Oracle SID, the server identifier property is not included as part of the standard set of JDBC data source properties within the registry, so for the Oracle-specific connection URL pattern, the general purpose connection URL attribute connectionUrlFormatAttr1 is used to represent this non-standard property in the URL definition. This attribute is substituted with its assigned value, Oracle SID, which is a user-defined SID.
The DSN entry provides a user ID and password value pair, which are used to access the specified data source for a given JDBC provider entry. This property is ignored if the connection URL does not contain both a user ID and password pair, which require property values to be substituted for such inserts. If the securityIdentify is blank or left as the default (jdbc::myuserid@myserver), but it is required for the connection URL pattern, or if a valid unique security identity key is entered and it cannot be found under the DSN key, the DataSourceUserId and DataSourcePassword properties under which the broker was created are used.
Use the -c parameter on the mqsichangeproperties command to change the property settings for an object name that already exists in the broker persistent store. For a JDBC provider, this object name is a JDBC provider name.
Use the mqsideleteconfigurableservice command to delete a JDBC provider resource that was created by the mqsicreateconfigurableservice command.
Use the -c parameter on the mqsireportproperties command to report on the properties of any JDBC provider resource (both default and user-defined).
When you have put an instance of the DatabaseRetrieve node into a message flow, you can configure it. For more information, see Configuring a message flow node. Depending on the preferences that you have set, you can view the node's properties in the Properties view below the Message Flow editor or in the Properties dialog box. Either click the Properties view tab, or right-click the node and click Properties. All mandatory properties for which you must enter a value (those that do not have a default value defined) are marked with an asterisk.
EmployeeNumber | FamilyName | FirstName | Salary |
---|---|---|---|
00001 | Smith | John | 20000 |
00002 | Jones | Harry | 26000 |
00003 | Doe | Jane | 31000 |
<EmployeeRecord> <EmployeeNumber>00001</EmployeeNumber> </EmployeeRecord>
Table Name | Column Name | Operator | ValueType | Value |
---|---|---|---|---|
Employee | FamilyName | |||
Employee | Salary | |||
Employee | EmployeeNumber | = | Element | /EmpRecord/EmpNumber |
Column Name | Message element |
Employee.FamilyName | /EmployeeRecord/Surname |
Employee.Salary | /EmployeeRecord/Wage |
select FamilyName, Salary from Employee where EmployeeNumber=xwhere x is the value that is retrieved from the incoming message, which is located through the Value property.
<EmployeeRecord> <EmployeeNumber>00001</EmployeeNumber> <Surname>Smith</Surname> <Wage>20000</Wage> </EmployeeRecord>
<EmployeeRecord> <EmployeeNumber>00002</EmployeeNumber> <Surname>Jones</Surname> <Wage>26000</Wage> </EmployeeRecord>
The DatabaseRetrieve node terminals are described in the following table.
Terminal | Description |
---|---|
In | The input terminal that accepts a message for processing by the node. |
Out | The output terminal to which the outgoing message is routed when it has been modified successfully. |
keyNotFound | The output terminal to which the original message is routed, unchanged, when modification is unsuccessful. |
Failure | The output terminal to which the message is routed if a failure is detected during processing. |
The following tables describe the node properties. The column headed M indicates whether the property is mandatory (marked with an asterisk if you must enter a value when no default is defined); the column headed C indicates whether the property is configurable (you can change the value when you add the message flow to the bar file to deploy it).
Property | M | C | Default | Description |
---|---|---|---|---|
Node name | No | No | The node type, DatabaseRetrieve | The name of the node. |
Short Description | No | No | A brief description of the node. | |
Long Description | No | No | Text that describes the purpose of the node in the message flow. |
Property | M | C | Default | Description |
---|---|---|---|---|
Data Source Name | Yes | Yes | DB2 | The alias that is used to locate JDBC provider details
that are stored in the broker registry. The alias is used to locate and build
the JDBC connection URL that is used to connect to a DBMS. The connection
URL is driver specific, but it includes the database name to which to connect. If connection to the database is by a login account and password, the node also uses this property as a lookup key, through which these values can be acquired from an expected matching broker registry DSN entry. If the DBMS is password protected, define the -n parameter on the mqsisetdbparms command for the JDBC unique security key before you deploy the message flow that contains this DatabaseRetrieve node. |
Copy Message | No | Yes | Cleared | This property indicates if a copy of the original incoming message is required because the message tree is to be updated, possibly in addition to logical trees within the message assembly. |
Query Elements | Yes | No | A table of query elements that are used to compose a single SQL select statement. The table consists of five columns and one or more rows. The columns are Table Name, Column Name, Operator, Value Type, and Value. These five properties describe a query element, indicating the database tables and columns that are retrieved. | |
Table Name | Yes | No | The name of a database table that forms part of the SQL select statement, including the schema name; for example, myschema.mytable. | |
Column Name | Yes | No | The name of the column in the database table to be retrieved in the results set, as qualified by the value of the Table Name property. This SELECT clause can refer to this name as a column value to return from a query or to be referenced in a test condition within the WHERE clause. | |
Operator | No | No | An optional comparison operator to apply to a left hand operand (the table column specified in the row's first two columns) and a right hand operand value. If you do not specify a value for this property, this row signifies the declaration of a table and column that can be referenced in future rows as a right hand operand value. | |
Value Type | No | No | An optional value type that indicates the type of value that is expressed in the last column of this row. A row that describes a test condition within the WHERE clause of the SQL select statement. | |
Value | No | No | This property specifies one of a given set of property types as expressed by the Value Type property. For example, if the Value Type property is set to Element, the Value property collects tan XPath 1.0 path location or ESQL field reference expression. The value that is returned from the expression when applied to the node's incoming message is used as the right hand operand value to be compared via this predicate. The compared value of the right hand operand must match the type that is retrieved for the table column that is compared against as the left hand operand. |
Property | M | C | Default | Description |
---|---|---|---|---|
Data Elements | No | No | A list of data elements. A data element is described by the Value Column Name and Message Element properties. | |
Value Column Name | Yes | Yes | The name of the database column from which to obtain the element value. The list of names is updated dynamically based on the column entries that are entered in the query elements table. | |
Message Element | Yes | Yes | An XPath 1.0 expression that describes the path location of a message element. The message element is where the database value is stored. The XPath expression must evaluate to a single element in the message. |
Property | M | C | Default | Description |
---|---|---|---|---|
Validate | No | Yes | None | This property indicates whether validation is required.
Valid values are:
|
Property | M | C | Default | Description |
---|---|---|---|---|
Failure Action | No | Yes | Exception | The action to take when a validation failure occurs.
Valid values are:
|