Microsoft SQL Watcher
Provides a Flowgear Trigger that enables monitoring of a change in an MS SQL database.
This Node should not be used at a DropPoint. In the event of a connectivity issue, the platform may not recognize that the Node has been triggered, resulting in a deadlock where the platform is waiting for a response from the DropPoint, and the DropPoint is waiting for a request from the platform.
If the target endpoint is not accessible over the Internet (i.e. a DropPoint is required), consider using a Node like Timer or Day Scheduler followed by a Microsoft SQL Query Node, to check for changes.
Revision History
1.0.0.0 Initial Release
3.0.0.6 Minor Updates and bugfixes.
3.0.0.14 Added support for custom connection string fragments.
3.1.0.0 Patch required for updates to Query, Query Paged, and Table Update.
3.1.0.1 Patch required for updates to Query, Query Paged, and Table Update.
Properties
Connection
Type: Connection Input
Server
Type: String Input
The name or IP address of your SQL Server. Specify a DropPoint if your SQL instance is not Internet-exposed. For example, if the DropPoint is installed on the SQL server, this property may be simply (local) or 127.0.0.1.
Database
Type: String Input
The name of the database you want to watch.
UserId
Type: String Input
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 Input
Provides a password for the specified user account.
UseWinAuth
Type: Boolean Input
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.
FireOn
Type: List Input
Indicates whether the Node should return when rows are returned in the result set or only when the result-set changes.
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.
PollInterval
Type: Int32 Input
Provides the interval, in seconds, at which the database should be polled.
ConnectionStringFragments
Type: String Input
Provide a list of extra connection string fragments that are not part of the standard connection properties. The fragments must be in the format key=value and different fragments should be listed underneath each other.
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 by polling it at the interval specified in the Connection.
When FireOn is set to RowsChanged, the Node will fire its output each time a row with a changed value is returned by the query specified in WatchQuery. When FireOn is set to RowsReturned, the Node will fire its output when at least 1 row is returned by the query specified in WatchQuery.