Change Data Capture
Logins & ACL
Enzo Server allows you to execute commands without waiting for the results to be returned. This allows your code to continue execution, check for the status of the operation periodically, and access the data once it is available. Unliked a scheduled operation, an async call executes as soon as possible.
See the Scheduling section for information on how to run background operations more than once
Run Async Operation
To run an async operation with Enzo Server, you need to add the __async extension to a valid SQL command.
The general format for the __async extension is as follows, where the parameters list
are those supported by the handler:
Execute Async Call
EXEC [adapter].[handler]__async 'correlationId', [,parameter1...]
The correlationId a logical identifier allowing you to group background operations; for example you can delete all async operations that have a given correlationId value.
You can execute background operations asynchronously using the _async extension on SELECT statements as well; however this option is not available through a Linked Server. To execute an async request against Enzo Server directly using a SELECT command, use this format:
SELECT * FROM [adapter].[handler]_async
Read Operation Output
Once the call of a background operation has completed, you can access the output generated by using the _asyncResult handler. This handler requires the _reqUid, which is a unique identifier assigned when the operation is created:
SELECT * FROM adapter._asyncResult WHERE _reqUid='918fb98f-cbe9-4260-88ff-357d1592e13e'
List available result sets
Call the _asyncResultsList handler to obtain the list of available results.
SELECT * FROM adapter._asyncResultsList
This handler takes a few optional parameters to help you filter the information. For example you can list all available outputs given the correlationId:
SELECT * FROM adapter._asyncResultsList WHERE _correlationId='mygroup'
Rerun an async operation
You can execute an async operation manually as many times as you want.
To do so, call the _asyncRefresh handler with the operation _reqUid or the correlationId (using a correlationId reruns all the
operations that have the same correlation value):
--Rerun a single operation by _reqUid EXEC adapter._asyncRefresh '918fb98f-cbe9-4260-88ff-357d1592e13e'
--Rerun all operations that belong to the specified collectionId EXEC adapter._asyncRefresh null, 'mygroup'
Cancel/delete async operation
To delete the cached data set, call the _asyncCancel handler:
--Cancel/delete a single operation by _reqUid EXEC adapter._asyncCancel '918fb98f-cbe9-4260-88ff-357d1592e13e'
--Cancel/delete all operations that belong to the specified collectionId EXEC adapter._asyncCancel null, 'mygroup'
--Other way to cancel/delete all operations that belong to the specified collectionId DELETE FROM adapter._asyncRequest WHERE _reqCorrelationId='mygroup'
Run a background operation
The following code gets data asynchronously, retrieves the cached data, and deletes it.
The code sample provided below runs directly against an Enzo Server. However you will need to replace the _reqUid with the one assigned to you by the __asyncAt handler.
-- This is the SQL command that returns data from SharePoint in real-time that we want to schedule EXEC sharepoint.getlistitemsex 'US States', 'ID,Title,State Code,Created' -- Let's run this call asynchronously by adding __async (we specify a null correlationId) EXEC sharepoint.getlistitemsex__async null, 'US States', 'ID,Title,State Code,Created' -- The previous method returned a _reqUid: efd55d0f-17b9-4e06-86cf-eca6df5e3c7e -- An async call is considered a scheduled operation that is executed just once: SELECT * FROM sharepoint._asyncScheduled -- wait a few seconds, and access the cached data set: SELECT * FROM SharePoint._asyncResult WHERE _reqUid = 'efd55d0f-17b9-4e06-86cf-eca6df5e3c7e' -- delete the cached data set: exec SharePoint._asyncCancel 'efd55d0f-17b9-4e06-86cf-eca6df5e3c7e'