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

Box: Search Files with Offset Pagination

See more Box Examples

Run the reusable setup once per HttpCurl object, then select names and execute the operation. The setup is repeated here only to make this published example independently runnable. Search within a folder hierarchy. Box search is indexed and may lag recent changes; use returned upload IDs for immediate follow-up requests. Search uses offset pagination, not folder-list markers.

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.7.0 or later and a previously unlocked Chilkat bundle.
    --  See Global Unlock Sample.
    --  Guide: Box API Guide.

    SELECT @success = 0

    --  BEGIN ONE-TIME SETUP
    --  This block is repeated so each published example can run on its own.
    --  In an application, run the setup example once and pass the SAME curl object
    --  to operation code below. Do not recreate it for every operation.
    --  See Reusable Box HttpCurl Setup.
    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'
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'parent_name', '/'
    --  Box's fixed root ID is an implementation detail; callers supply names.
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'parent_id', '0'
    DECLARE @setupCommand int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @setupCommand OUT

    DECLARE @setupReplacements int

    --  Register findParent; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/folders/0/items?limit=1000&fields=id,type,name" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'findParent', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register findFolder; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/folders/{{parent_id}}/items?limit=1000&fields=id,type,name" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'findFolder', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register findFile; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/folders/{{folder_id}}/items?limit=1000&fields=id,type,name" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'findFile', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register findDestination; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/folders/0/items?limit=1000&fields=id,type,name" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'findDestination', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register getFileDetails; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/files/{{file_id}}?fields=id,sha1,file_version" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'getFileDetails', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register getEnterprise; registration sends no request.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/users/me?fields=enterprise" -H "Authorization: Bearer ACCESS_TOKEN"'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @setupCommand, 'Replace', @setupReplacements OUT, 'ACCESS_TOKEN', @sTmp0
    EXEC sp_OAMethod @setupCommand, 'GetAsString', @sTmp0 OUT
    EXEC sp_OAMethod @curl, 'AddFunction', @success OUT, 'getEnterprise', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END

    --  Register output rules once. Chilkat 11.7.0 evaluates {{name}} variables
    --  when extracting each output, using the current value at that time.
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'findParent', 'entries', 'name', '{{parent_name}}', 1, 'id', 'parent_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'findFolder', 'entries', 'name', '{{folder_name}}', 1, 'id', 'folder_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'findFile', 'entries', 'name', '{{file_name}}', 1, 'id', 'file_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'findDestination', 'entries', 'name', '{{destination_name}}', 1, 'id', 'destination_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput', @success OUT, 'getFileDetails', 'file_version.id', 'file_version_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput', @success OUT, 'getFileDetails', 'sha1', 'file_sha1'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    EXEC sp_OAMethod @curl, 'AddOutput', @success OUT, 'getEnterprise', 'enterprise.id', 'enterprise_id'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @curl, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        RETURN
      END
    --  END ONE-TIME SETUP
    --  The name lookups below assume each listing fits in one page (1000 items).
    --  Parent and destination names select root children; folder_name selects a child
    --  of parent_name. "/" selects the root parent. These are not arbitrary paths.

    --  BEGIN OPERATION -- keep the configured object when reusing it.
    --  Inputs are names. Retain cached IDs when the selected names have not changed.
    DECLARE @parentName nvarchar(4000)
    SELECT @parentName = '/'
    DECLARE @folderName nvarchar(4000)
    SELECT @folderName = 'images'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'parent_name'
    EXEC sp_OAMethod @curl, 'VarDefined', @iTmp0 OUT, 'parent_id'
    IF (@sTmp0 <> @parentName) or ((@parentName = '/') and (@iTmp0 = 0))
      BEGIN
        EXEC sp_OAMethod @curl, 'SetVar', NULL, 'parent_name', @parentName
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_version_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_sha1'
        IF @parentName = '/'
          BEGIN
            EXEC sp_OAMethod @curl, 'SetVar', NULL, 'parent_id', '0'
          END
      END
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'folder_name'
    IF @sTmp0 <> @folderName
      BEGIN
        EXEC sp_OAMethod @curl, 'SetVar', NULL, 'folder_name', @folderName
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_version_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_sha1'
      END
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'query', 'penguins'
    DECLARE @page int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @page OUT

    DECLARE @number int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @number OUT

    DECLARE @offset int
    SELECT @offset = 0
    DECLARE @count int

    DECLARE @pageLimit int

    DECLARE @total int

    DECLARE @i int

    DECLARE @more int
    SELECT @more = 1
    WHILE @more = 1
      BEGIN
        EXEC sp_OAMethod @number, 'Clear', NULL
        EXEC sp_OAMethod @number, 'AppendInt', @success OUT, @offset
        EXEC sp_OAMethod @number, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @curl, 'SetVar', NULL, 'offset', @sTmp0
        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/search?query={{query}}&type=file&ancestor_folder_ids={{folder_id}}&content_types=name&limit=100&offset={{offset}}" -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 @setupCommand
            EXEC @hr = sp_OADestroy @page
            EXEC @hr = sp_OADestroy @number
            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 @setupCommand
            EXEC @hr = sp_OADestroy @page
            EXEC @hr = sp_OADestroy @number
            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', @sTmp0 OUT, 'entries[i].id'

            EXEC sp_OAMethod @page, 'StringOf', @sTmp1 OUT, 'entries[i].name'
            PRINT @sTmp0 + ' ' + @sTmp1
            SELECT @i = @i + 1
          END
        EXEC sp_OAMethod @page, 'IntOf', @pageLimit OUT, 'limit'
        EXEC sp_OAMethod @page, 'IntOf', @total OUT, 'total_count'
        IF @pageLimit <= 0
          BEGIN

            PRINT 'Missing or invalid search page limit.'
            EXEC @hr = sp_OADestroy @curl
            EXEC @hr = sp_OADestroy @setupCommand
            EXEC @hr = sp_OADestroy @page
            EXEC @hr = sp_OADestroy @number
            EXEC @hr = sp_OADestroy @authorizedCommand1
            RETURN
          END
        SELECT @offset = @offset + @pageLimit
        SELECT @more = (@offset < @total) and (@count > 0)
        IF (@more = 1) and (@offset > 9999)
          BEGIN

            PRINT 'Search offset limit reached; narrow the query or folder scope.'
            EXEC @hr = sp_OADestroy @curl
            EXEC @hr = sp_OADestroy @setupCommand
            EXEC @hr = sp_OADestroy @page
            EXEC @hr = sp_OADestroy @number
            EXEC @hr = sp_OADestroy @authorizedCommand1
            RETURN
          END
      END

    EXEC @hr = sp_OADestroy @curl
    EXEC @hr = sp_OADestroy @setupCommand
    EXEC @hr = sp_OADestroy @page
    EXEC @hr = sp_OADestroy @number
    EXEC @hr = sp_OADestroy @authorizedCommand1


END
GO