NextGen Knowledge Center

Database Reader

This Database Reader connects to an external database, performs a query, and reads selected rows into messages that get dispatched to the channel. This can be done using a SQL statement, or by using JavaScript mode to perform the query manually. The database connection will automatically be kept open across multiple polling windows, unless otherwise specified. This connector also supports a Post-Process section where an update statement can be performed after each row is read in, for example to set a processed flag in the source table. The values selected from the query will be automatically converted into an XML document where each column will be a separate node. That XML document is what actually gets dispatched to the channel as a message.

Supported property groups:

ItemNameDefault ValueDescription
ADriver Specifies the type of database driver to use to connect to the database. The following values are supported by default:
  • Sun JDBC-ODBC Bridge
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server / Sybase
  • SQLite

Additional drivers can be added by editing dbdrivers.xml File.

BURL The JDBC URL to connect to the database with. This is not used when Yes for Use JavaScript is checked. However, it is used when the Generate Connection / Select feature is used to generate code. Use the Insert URL Template button above to populate the URL field with a starting template.
CUsername The username to connect to the database with. This is not used when Yes for Use JavaScript is checked. However, it is used when the Generate Connection / Select feature is used to generate code.
DPassword The password to connect to the database with. This is not used when Yes for Use JavaScript is checked. However, it is used when the Generate Connection / Select feature is used to generate code.
EUse JavaScriptNoIf enabled, the below JavaScript scripts will be used to select messages and run a post-process update. If disabled, SQL code (either standard or database-specific) may be used, and the connection will be handled automatically.
FKeep Connection OpenYesRe-use the same database connection each time the select query is executed. If disabled, the connection will be closed after all selected messages have finished processing.
GAggregate ResultsNoIf enabled, all rows returned in the query will be aggregated into a single XML message. Note that all rows will be read into memory at once, so use this with caution.
HCache ResultsYesCache the entire result set in memory prior to processing messages.
IFetch Size1000The JDBC ResultSet fetch size to be used when fetching results from the current cursor position.
J# of Retries on Error3The number of times to retry executing the statement or script if an error occurs.
KRetry Interval10000The amount of time that should elapse between retry attempts.
LEncodingDefaultSelect the character set encoding used to convert binary data into message strings, or select Default to use the default character set encoding for the JVM Mirth® Connect is running on.
MGenerate 
  • Connection: This button is enabled when Use JavaScript is enabled. When selected, it inserts boilerplate Connection construction code into the JavaScript pane at the current caret position.
  • Select: Opens a window to assist in building a select query to select records from the database specified in the URL above.
NSQL / JavaScript The actual SQL or JavaScript code to execute for each polling window. When JavaScript mode is used, the return value of the script is expected to be a ResultSet or a List<Map<String, Object>> (a list of maps, where each entry in each map has a String key and any object value).
ORun Post-Process SQL / JavaScriptNeverDetermines whether the post-process update script is active, and if so whether to execute it after each message or only once after all messages in the ResultSet have completed.

If Aggregate Results is disabled:

  • Never: Do not run the post-process statement/script.
  • After each message: Run the post-process statement/script after each message finishes processing.
  • Once after all messages: Run the post-process statement/script only after all messages have finished processing.

If Aggregate Results is enabled:

  • Never: Do not run the post-process statement/script.
  • For each row: Run the post-process statement/script for each row in the result set.
  • Once for all rows: Run the post-process statement/script only once. If JavaScript mode is used, a List of Maps representing all rows in the result set will be available as the variable "results".
PGenerate (post-process) 
  • Connection: This button is enabled when Use JavaScript is enabled and a post-process script is being used. When selected, it inserts boilerplate Connection construction code into the JavaScript pane at the current caret position.
  • Update: Opens a window to assist in building an update statement to update records in the database specified in the URL above. Only enabled if a post-process statement/script is being used.
QSQL / JavaScript (post-process) The actual SQL or JavaScript code to execute after each row/message or after all rows/messages have completed.
RResult Map When using the After each message / For each row post-process option, values originally selected using the query above will be available in the SQL or JavaScript context. Drag the entries from this section into the post-process script to use them in your update statement. For example if you selected a unique ID column in your initial query, you may want to use that same value to update the table and set a processed flag.