SQL Server Requires Chilkat v11.5.0+
SQL Server
Box: Poll User Events and Save a Checkpoint
See more Box Examples
Poll the authenticated user event stream in bounded batches. The local JSON checkpoint keeps processed event IDs and the next stream position. This single-process demonstration logs events; a production worker should commit its business action, deduplication record, and cursor transactionally and prune old IDs.
See the Box API guide for setup and the complete workflow.
Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Requires Chilkat 11.5.0 or later and a previously unlocked Chilkat bundle.
-- See Global Unlock Sample.
-- Guide: Box API Guide.
SELECT @success = 0
-- Supply an unexpired token obtained with one of the Box authentication examples.
-- A token represents a particular user or service account. IDs must be accessible to it.
DECLARE @curl int
EXEC @hr = sp_OACreate 'Chilkat.HttpCurl', @curl OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'access_token', 'BOX_ACCESS_TOKEN'
DECLARE @state int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @state OUT
DECLARE @files int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @files OUT
DECLARE @statePath nvarchar(4000)
SELECT @statePath = 'qa_data/box_events_state.json'
EXEC sp_OAMethod @files, 'FileExists', @iTmp0 OUT, @statePath
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @state, 'LoadFile', @success OUT, @statePath
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
RETURN
END
EXEC sp_OAMethod @state, 'HasMember', @iTmp0 OUT, 'position'
IF @iTmp0 <> 1
BEGIN
PRINT 'Invalid checkpoint: position missing.'
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
RETURN
END
END
ELSE
BEGIN
-- "now" starts a new subscription. It deliberately skips historical events.
EXEC sp_OAMethod @state, 'UpdateString', @success OUT, 'position', 'now'
END
EXEC sp_OAMethod @state, 'StringOf', @sTmp0 OUT, 'position'
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'position', @sTmp0
DECLARE @page int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @page OUT
DECLARE @seenPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @seenPath OUT
DECLARE @eventId nvarchar(4000)
DECLARE @nextPosition nvarchar(4000)
DECLARE @batch int
SELECT @batch = 0
DECLARE @i int
DECLARE @count int
DECLARE @more int
SELECT @more = 1
WHILE (@more = 1) and (@batch < 20)
BEGIN
EXEC sp_OAMethod @curl, 'ClearTargetOutput', NULL, '*'
DECLARE @authorizedCommand1 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @authorizedCommand1 OUT
EXEC sp_OAMethod @authorizedCommand1, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/events?stream_type=all&stream_position={{position}}&limit=100" -H "Authorization: Bearer {{access_token}}"'
DECLARE @headerReplacement1 int
EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
EXEC sp_OAMethod @authorizedCommand1, 'Replace', @headerReplacement1 OUT, '{{access_token}}', @sTmp0
EXEC sp_OAMethod @authorizedCommand1, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @curl, 'DoYourThing', @success OUT, @sTmp0
EXEC sp_OAGetProperty @curl, 'StatusCode', @iTmp0 OUT
IF (@success <> 1) or (@iTmp0 <> 200)
BEGIN
EXEC sp_OAGetProperty @curl, 'StatusCode', @iTmp0 OUT
PRINT 'Box request failed. HTTP status: ' + @iTmp0
EXEC sp_OAGetProperty @curl, 'FailReason', @iTmp0 OUT
PRINT 'Failure reason: ' + @iTmp0
EXEC sp_OAGetProperty @curl, 'ResponseBodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @curl, 'GetResponseJson', @success OUT, @page
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @page, 'SizeOfArray', @count OUT, 'entries'
SELECT @i = 0
WHILE @i < @count
BEGIN
EXEC sp_OASetProperty @page, 'I', @i
EXEC sp_OAMethod @page, 'StringOf', @eventId OUT, 'entries[i].event_id'
IF @eventId = ''
BEGIN
PRINT 'Event without an event_id; checkpoint not advanced.'
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @seenPath, 'Clear', NULL
EXEC sp_OAMethod @seenPath, 'Append', @success OUT, 'seen.'
EXEC sp_OAMethod @seenPath, 'Append', @success OUT, @eventId
EXEC sp_OAMethod @seenPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @state, 'HasMember', @iTmp0 OUT, @sTmp0
IF @iTmp0 = 0
BEGIN
-- Replace this logging with your idempotent business operation.
-- Record success only after that operation succeeds.
EXEC sp_OAMethod @page, 'StringOf', @sTmp0 OUT, 'entries[i].event_type'
EXEC sp_OAMethod @page, 'StringOf', @sTmp1 OUT, 'entries[i].source.id'
PRINT @eventId + ' ' + @sTmp0 + ' ' + @sTmp1
EXEC sp_OAMethod @seenPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @state, 'UpdateBool', @success OUT, @sTmp0, 1
END
SELECT @i = @i + 1
END
EXEC sp_OAMethod @page, 'StringOf', @nextPosition OUT, 'next_stream_position'
IF @nextPosition = ''
BEGIN
PRINT 'Missing next_stream_position; checkpoint not advanced.'
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'position'
SELECT @more = (@count > 0) and (@nextPosition <> @sTmp0)
EXEC sp_OAMethod @state, 'UpdateString', @success OUT, 'position', @nextPosition
EXEC sp_OAMethod @state, 'WriteFile', @success OUT, @statePath
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @curl, 'SetVar', NULL, 'position', @nextPosition
SELECT @batch = @batch + 1
END
PRINT 'Checkpoint saved. Run again later to continue polling.'
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @page
EXEC @hr = sp_OADestroy @seenPath
EXEC @hr = sp_OADestroy @authorizedCommand1
END
GO