SQL Statement Parameters

In the configuration XML file, the statements can contain parameters in them. There are three types of parameters:

An incoming message property specified by $<message property name>. Message properties can be referenced in the XML configuration file by preceding their names with a $ character.
An incoming message field value specified by @<full path of a message field> for an EDI message, or `<full path of a message field>` for an XML message . It is the full path of the message field. Not available if the database communication point is in input mode.
A database table column value specified by @<column name>. It is the name of one of the columns returned by the parent statement.

To assign or insert a full segment using the @ notation in a SQL statements, ensure a dot (.) is appended to the segment name (for example, @MSH.).

An XPath expression can be used to specify a field path when incoming message type is XML. The value should be started with ` followed by the XPath expression (for example, `/message/patient[1]/name, `/message/patient[1]/@id). Note that the XPath expression should be point to only one field. If the XPath expression refers multiple fields, only first field of the corresponding expression will be populated.

For example, to insert the value for the property PatientIDProperty into a database table, the following XML can be used:

<sql>insert into table1 values ( ..., $PatientIDProperty, ...) </sql>

Escaping '@'

If you need to use the @ character as part of your SQL query syntax, you must escape it by preceding it with a back-slash character (\).

For example, if you have the following configuration:

<statement> 
    <sql> 
        select id from parentTable where ... 
    </sql> 
    <statement> 
        <sql> 
            insert into table1 values ( ..., @id, ... ) 
         </sql> 
    </statement> 
    <statement> 
        <sql> 
            update table2 set \@id = @id where ... 
        </sql> 
	</statement> 
</statement>

In the first child statement, the @id would be replaced by a column value from the parent statement and executed; if the parent statement returns 123 for the id column of parentTable then insert into table1 values (..., '123', ...) would be executed.

In the second child statement, @id (on the left hand side) would be replaced by @id (on the right hand side). For example, @id is replaced by 123 when the following SQL query is executed: update table2 set @id='123' where ..., where @id must be a column name of table2.

Accessing Message Body String

The variable $messageContent (case insensitive) can be used to refer to the entire message body as a string.

<sql>insert into table values ( ..., $messageContent, ... ) </sql>

The above SQL statement inserts the message body into the database column.

Convert NULL Fields

When a message field does not exist in the message to be processed, null is used for the value.

<sql>insert into table values ( ..., @PID.PatientID, ... ) </sql>

In the above SQL statement the @PID.PatientID would be replaced by a PID.PatientID value from the incoming message; if the PID.PatientID does not exist in the message, then insert into table values (..., null, ...) is executed by default. However, if you wish to insert an empty string instead of null value, then set the convertNullFields property in the configuration file.

SYNTAX:

<convertNullFields/>

If convertNullFields exists in the configuration file, then if the PID.PatientID does not exist in the message, then insert into table values (..., '', ...) is executed.