Provides a Flowgear Trigger that enables monitoring of a change in an MS SQL database.


Properties

Connection

Type: Connection Input

Server
Type: String
The name or IP address of the MS SQL instance (including the instance name). When using a DropPoint, the Server is relative to the DropPoint. For example, if the DropPoint is installed on the SQL server, the Server property may be simply (local) or 127.0.0.1.

Database
Type: String
Provides the name of the MS SQL Database.

UserId
Type: String
Provides the name of the user account against which the SQL query will be executed. When using a DropPoint, consider used Windows Authentication (UseWinAuth property) and running the DropPoint under an appropriate Windows account.

Password
Type: Password
Provides a password for the specified user account.

UseWinAuth
Type: Boolean
When the Node executes at a DropPoint, allows the user context to be derived from the Windows service account under which the DropPoint is running.

WatchMethod
Type: List
Notify - Specifies that SQL Query Notifications should be consumed to determine when a database change has taken place. This option requires MS SQL 2008 R2 or later.
Poll - Indicates that the database should be polled at a WatchPollInterval for changes.
Determines whether the database should be polled for changes or use events.

FireOn
Type: List
RowsChanged - Specifies that the Node should fire whenever a change occurs in the result set.
RowsReturned - Specifies that the Node should fire whenever execution of WatchQuery results in rows being returned.
Indicates whether the Node should return when rows are returned in the result set or only when the result-set changes.

WatchPollInterval
Type: Int32
Provides the interval, in seconds, at which the database should be polled when WatchMethod is set to Poll.

Provides a connection to the MS SQL database and associated configuration.

WatchQuery

Type: Multiline Text Input
Provides the query that will be evaluated based on the configuration specified in Connection.

Remarks

SQL Watcher provides a lightweight mechanism for responding to changes in a database. In Poll  mode, the Node eliminates the steps needed to manually poll the database while in Notify  mode, SQL Query Notifications provide asynchronous change notifications that do not have the synchronicity problems of SQL Triggers.

Note that Notify mode is not recommended when the Node is being executed via a DropPoint. This is because if there is a disruption in connectivity in the DropPoint and the DropPoint goes offline, it is possible for the Workflow engine to miss a notification (due to retries being exceeded). Once in this state, the DropPoint will have returned control to the Workflow engine but the Workflow engine will still be waiting for the DropPoint and no further execution will occur on the DropPoint.

Use of this Node is recommended for low-volume transactions where near-realtime action is required when the state of a database changes (for example, the addition or modification of a customer record).

Refer to https://msdn.microsoft.com/en-us/library/t9x04ed2(v=vs.110).aspx for details of SQL Query Notifications.

Limitations

This Node may not function correctly via a DropPoint where the Internet connection is unstable. In such cases it is possible that a notification event may be missed.

Did this answer your question?