Use Case: Trying to get changes from our Microsoft SQL Server to automatically trigger Web Service request to an external endpoint using CA Live API Creator.
We have already configured LAC with a working connection to our MSSQL database. We can make changes to the database and it will appear on LAC as well as vise versa from LAC to our database.
Any advice on the workflow and configurations we should make to be able to detect/listen for these changes (new entries, removed, updated, etc) and automatically trigger the web service request to be sent out?
What are some things to consider? I am trying to avoid using the API Gateway because the client does not have this product implemented in their environment.
I can think of two possibilities: either a notification from SQL Server, or a polling mechanism.
If you can define some sort of trigger or trigger-like mechanism in SQL Server to notify LAC that something has changed, that would definitely be the most efficient and elegant solution. SQL Server does have a query notification service that can invoke .NET code. That code could then make a call to LAC, ideally passing it some description of what has just changed so LAC doesn't have to figure that out on its own. See Query Notifications in SQL Server | Microsoft Docs for some pointers.
The other option is to poll, which is conceptually simpler, but much less efficient. You'd have to use a timer and poll the database at regular intervals. Obviously this is much cruder -- you have to hit the database all the time, but it is simple.
Do you possibly have any references or example/sample structures or workflows as to how to create a simple poll in LAC? The only source of information that I have found on polling was in the Integrate Systems and Data section of the LAC documentation about what polling IS, NOT necessarily how to do or setup polling.
I don't know that we have a specific example. The general idea is simple: you create a timer in LAC, and every time the timer runs, it looks for changes in the database. This is usually done using timestamps in the database, so the timer can query for all changes since last time it looked. The upside is that this is very simple, the downside is that you'll have the timer executing these queries every time it runs, which is not too bad if it runs every hour, but much more expensive if it runs every 5 seconds.