The following table lists the message population configuration properties for a database component:

substituteColumn

Defines a mapping between a database table column and a message field or a message property where message content population is required with a returned value after the SQL query has been executed. The name attribute must point to one of the lookup columns in the SQL statement. The simple element data is the message field by which return value of the lookup column will be replaced. Optionally you can have a default attribute whose value is used when the column value is not found or null; ‘@error’ can be used as its value for which an error will be produced if the return value is null.

The append option enables all retrieved results to be inserted into all repeating fields.

resultSet

Contains a one line instruction about the action with the result set of the SQL query. The SQL statement is expected to return one row result set. Currently provides two valid instructions that can be used together. If you set ReadFirstLine, the first row is regarded as valid return when more than one line result set is returned. If you set AllowNullResultSet, the substitute fields are either filled with default values or left empty, if default value is not set, when no data is found from the SQL query.

You can also set ReadAllLines to read all rows returned by an SQL query (this instruction can only be added by manually editing the database component configuration XML file).

Examples

The following code excerpt populates the message field MSH.MessageControlID with the GroupName column value. Only the first result is read and the rest are ignored.

<statement name="group">
    <sql>
        SELECT GroupName FROM GroupTable WHERE Type=@EVN.EventTypeCode
    </sql>
    <resultset>ReadFirstLine</resultset>
    <resultset>AllowNullResultSet</resultset>
    <substituteColumn name="GroupName" default=”Default Group”>
        @MSH.MessageControlID
    </substituteColumn>
</statement>

The following code excerpt populates the message property ACC_BroughtInBy with the Name column values and message field /Patient/Address with the Address column values. The entire result set is read.

<statement name="detail">
    <resultSet>ReadAllLines</resultSet>
    <sql>
        SELECT Name,Address FROM PatientTable
        LEFT OUTER JOIN MappingTable
        ON PatientTable.ID=MappingTable.TableID
        WHERE MsgPID=@PID.SetID AND PLACE='auckland'
    </sql>
    <statement name="populate">
        <substituteColumn name="Name" default="Unknown">
            $ACC_BroughtInBy[]
        </substituteColumn>
        <substituteColumn name="Address" append='true' >`/Patients[1]/Patient[*]/Address`</substituteColumn>
    </statement>
</statement>

The following SELECT query returns three rows and three PatientAddress elements will be populated using all the result rows (if the element does not exist, it is created):

<statement> 
   <sql>SELECT name, StreetAddress, City FROM PersonDetails WHERE id = 1</sql> 
   <substituteColumn append='true' name='StreetAddress'>PID.PatientAddress[*].StreetAddress</substituteColumn>         
   <substituteColumn append='true' name='City'>PID.PatientAddress[*].City</substituteColumn>
</statement>

The following code excerpt populates list message properties with all the results from the query:

<statement name="parent">
	<sql>SELECT person_id, address FROM person</sql>
	<resultSet>ReadAllLines</resultSet>
	<substituteColumn name="person_id" default="unknown">
		$PersonID[]
	</substituteColumn>
	<substituteColumn name="address" default="unknown">
		$Address[]
	</substituteColumn>
</statement>