Comment on page
Version 5.4 of the Cinchy platform introduced data polling, which uses the Cinchy Event Listener to continuously monitor and sync data entries from your SQLServer or DB2 server into your Cinchy table. This capability makes data polling a much easier, effective, and streamlined process and avoids implementing the complex orchestration logic that was previous necessary.
This page outlines the necessary Listener Config values that need to be used prior to setting up your data sync.
To set up an Stream Source, you must navigate to the Listener Config table and insert a new row for your data sync (Image 1). Most of the columns within the Listener Config table persist across all Stream Sources, however exceptions will be noted. You can find all of these parameters and their relevant descriptions in the tables below.
Image 1: The Listener Config table
The following column parameters can be found in the Listener Config table:
The below table can be used to help create your Topic JSON needed to set up a real-time sync.
FROM [SourceTable] WHERE Id IN (SELECT TOP (100) Id FROM [SourceTable] WHERE Id > 0 AND Name IS NOT NULL ORDER BY Id) AND Id > 0 AND Name IS NOT NULL | | FromClause | Mandatory. This must contain at least the table name but can also contain Joined tables as written in SQL language. | Example: [Source Table] | | CursorColumn | Mandatory. Column name that's used in any 'WHERE' condition(s) and for ordering the result of a query | Example: [Id] | | BatchSize | Mandatory. Minimum size of a batch of data per query. This can be larger to prevent infinite loops if the CursorColumn isn't unique. | Example: 100 | | FilterCondition | All filtering options used in any 'WHERE' condition(s) of the query | Example:
Name IS NOT NULL| | Columns | Mandatory. A list of columns that we want to show in a result. | Example:
Name| | ReturnDataConfiguration |
The parameters here are used in more complex queries. In our example, there are 2 related tables, but want to show the contents of one of them based on the CursorColumn from a second table. Since Timestamp values aren't unique, we need to find all combinations of Id, Timestamp that match the filter condition in a subquery, and then join this result with the outer-query to get the final result. Note that in ReturnDataConfiguration, our parameters area of concern is everything outside of first open parenthesis
(and last closing parenthesis
( ... ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0 ORDER
Example complex query:
ts.[Id],ts.[Name] FROM ( SELECT Id,Timestamp FROM [Table2] WHERE Timestamp IN
(SELECT TOP (2) Timestamp FROM [Table2] WHERE Timestamp > '2022-11-18 11:34:09
AM' AND Timestamp <= '2022- 11-19 11:34:09 AM' AND 1=1 ORDER BY Timestamp)
AND 1=1 ) AS t INNER JOIN [Table1] ts ON ts.[Id] = t.[Id] WHERE ts.[Id] > 0
ORDER BY Id
| | CursorAlias | Mandatory. This is the alias for a subquery result table. It's used in 'JoinClause', and can be used in 'Columns' if we want to return values from a subquery table. | Example: "t" | | JoinClause | Mandatory. Our result table to which we join the subquery result, plus the condition of the join. | Example: [Table1] ts ON ts.[Id] = t.[Id] | | FilterCondition | All filtering options used in any 'WHERE' conditions. | Example: "ts.[Id] > 0" | | OrderByClause | Mandatory. This is the column(s) that we want to order our final result by. | Example: "Id" | | Columns | Mandatory. A list of columns that we want to show in the final result. | Example: "ts.[Id]" "ts.[name]" | | Delay | Mandatory. This represents the delay, in second, between data sync cycles once it no longer finds any new data. | Example: 10 | | messageKeyExpresssion | Optional, but recommended to mitigate data loss. See Appendix A for more information on this parameter. | id |
Example Topic JSON
"FromClause": "[Source Table]",
"FilterCondition": "Name IS NOT NULL",
"JoinClause": "[Table1] ts ON ts.[id] = t.[id]",
"FilterCondition": "ts.[id] > 0",
The below table can be used to help create your Connection Attributes JSON needed to set up a real-time sync.
"connectionString": "Server=;Database=;User ID=cinchy;password=example;Trusted_Connection=False;Connection Timeout=30;Min Pool Size=10;",
The messageKeyExpression parameter is an optional, but recommended, parameter that can be used to ensure that you aren't faced with a unique constraint violation during your data sync. This violation could occur if both an insert and an update statement happened at nearly the same time. If you choose not to use the messageKeyExpression parameter, you could face data loss in your sync.
This parameter was added to the Data Polling event stream in Cinchy v5.6.
Each of your Event Listener message keys a message key. By default, this key is unique for every message in the queue.
When the worker processes your Event Listener messages it does so in batches and, for efficiency and to guarantee order, messages that contain the same key won't be processed in the same batch.
The messageKeyExpression property allows you to change the default message key to something else.