Database Transport

Help

The Database Transport provider enables you to receive data (in the form of a message) from a database query, and send data (as a message) to a database query. Currently, only SQLServer and Access databases are supported though support for Oracle and MySQL are being implemented.

The Receive Transport requires you to configure a query. If the query returns XML data (in SQLServer it contains the FOR XML directive), you should check the This query returns XML checkbox. You also need to specify root and record names (the latter only if the query doesn’t return XML) and an XML namespace in order to form an XML Message. The Test link allows you to preview the execution of the query, and Generate Schemas creates a compatible schema in your Schema library.

You can also specify a Commit Query, enabling for example the database to delete a record on successful processing. This query will be executed when the message has been successfully submitted. You can use the Insert Token link to insert fields from the message into the query as tokens (used to assist in correlation).

The Send Transport requires you to configure an update query, which similarly supports tokens to extract data from the Message or Message Context. This update query can return a Response Message, which is configured in a similar way as for the Receive Transport, and can be obtained using a Receive Send Port Message Response Workflow Activity.

For example, imagine you have a Database table called CustomerDetails, and you have configured CustomerName and CustomerEmail properties on the General tab of the Send Port Configuration Form to be initialized from the Message Xml. To insert a record into the database, you could enter the following Update Query :

INSERT INTO CustomerDetails (CustRef, CustName, CustEmail) VALUES (’%MessageID%’, ’%CustomerName%’, ’%CustomerEmail%’)

Alternatively it would be better practice to use a stored procedure. Note the ‘ quotes around the tokens, these are required in order for your query to be valid SQL once the tokens are replaced.

Note the ExecutionScope drop-down, which allows you to specify that the query is repeated for multiple Xml elements in the Message. If a repeating scope is chosen, the Insert Token link allows you to insert XPath expressions directly.

Database Transport

Help menu