Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.5.0+

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

SQL Server
-- 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