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

Box: Resolve Folder and File IDs with the HttpCurl Dependency Engine

See more Box Examples

Download images/penguins.jpg using search-based HttpCurl dependencies and one DoYourThing call. Register search requests/output rules once, then supply names. Search results must each contain exactly one item; exact names and immediate parent IDs are checked before saving the downloaded bytes. The candidate content is downloaded into memory before validation, so this example is for small files. Multiple results are rejected instead of paginated or silently choosing a candidate. Search indexing can delay visibility of newly created or renamed items.

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 @sTmp2 nvarchar(4000)
    DECLARE @sTmp3 nvarchar(4000)
    DECLARE @sTmp4 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
    --  Requires Chilkat 11.7.0. Registration sends no requests.
    --  Run this once and keep the SAME HttpCurl object for later downloads.
    --  This focused setup searches for a root folder and a file directly inside 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 @setupCommand int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @setupCommand OUT

    DECLARE @setupReplacements int

    --  searchFolder: Box searches names; quotes request a phrase match.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/search?query={{folder_query}}&type=folder&content_types=name&fields=id,type,name,parent&limit=2" -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, 'searchFolder', @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
    --  Deferred selection: folder_name need not be set during setup.
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'searchFolder', '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, 'searchFolder', 'entries', 'name', '{{folder_name}}', 1, 'name', 'folder_result_name'
    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, 'searchFolder', 'entries', 'name', '{{folder_name}}', 1, 'parent.id', 'folder_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, 'AddOutput', @success OUT, 'searchFolder', 'total_count', 'folder_search_count'
    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

    --  searchFile: Box searches names; quotes request a phrase match.
    EXEC sp_OAMethod @setupCommand, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/search?query={{file_query}}&type=file&content_types=name&fields=id,type,name,parent&limit=2&ancestor_folder_ids={{folder_id}}" -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, 'searchFile', @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
    --  Deferred selection: file_name need not be set during setup.
    EXEC sp_OAMethod @curl, 'AddOutput2', @success OUT, 'searchFile', '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, 'searchFile', 'entries', 'name', '{{file_name}}', 1, 'name', 'file_result_name'
    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, 'searchFile', 'entries', 'name', '{{file_name}}', 1, 'parent.id', 'file_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, 'AddOutput', @success OUT, 'searchFile', 'total_count', 'file_search_count'
    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
    --  Search is indexed: newly uploaded/renamed items may not appear immediately.
    --  Search matches phrases, not exact strings, and ancestor filtering includes
    --  subfolders. The operation validates counts, exact names, and immediate parents.
    --  limit=2 is enough to demonstrate an ambiguous search; total_count covers all
    --  results, not just this page. This introductory example requires exactly one
    --  search result at each step and does not select among multiple candidates.

    --  BEGIN OPERATION -- use the previously configured object.
    --  Human-readable inputs. No resource IDs are supplied by the caller.
    DECLARE @folderName nvarchar(4000)
    SELECT @folderName = 'images'
    DECLARE @fileName nvarchar(4000)
    SELECT @fileName = 'penguins.jpg'
    --  Encode the quoted search queries separately from the literal output filters.
    --  In this build, URL variable substitution does not encode '&' in a name.
    DECLARE @queryText int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @queryText OUT

    EXEC sp_OAMethod @queryText, 'SetString', @success OUT, '"'
    EXEC sp_OAMethod @queryText, 'Append', @success OUT, @folderName
    EXEC sp_OAMethod @queryText, 'Append', @success OUT, '"'
    EXEC sp_OAMethod @queryText, 'GetEncoded', @sTmp0 OUT, 'url', 'utf-8'
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'folder_query', @sTmp0
    EXEC sp_OAMethod @queryText, 'SetString', @success OUT, '"'
    EXEC sp_OAMethod @queryText, 'Append', @success OUT, @fileName
    EXEC sp_OAMethod @queryText, 'Append', @success OUT, '"'
    EXEC sp_OAMethod @queryText, 'GetEncoded', @sTmp0 OUT, 'url', 'utf-8'
    EXEC sp_OAMethod @curl, 'SetVar', NULL, 'file_query', @sTmp0
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'folder_name'
    IF @sTmp0 <> @folderName
      BEGIN
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_search_count'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_search_count'
        EXEC sp_OAMethod @curl, 'SetVar', NULL, 'folder_name', @folderName
      END
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'file_name'
    IF @sTmp0 <> @fileName
      BEGIN
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_search_count'
        EXEC sp_OAMethod @curl, 'SetVar', NULL, 'file_name', @fileName
      END
    --  With unchanged names, cached IDs and validation fields can be reused.
    --  Clear the corresponding cached fields to refresh after a remote rename/move.
    DECLARE @localPath nvarchar(4000)
    SELECT @localPath = 'qa_output/penguins.jpg'
    --  A cold plan is searchFolder -> searchFile -> targetCurl.
    --  No listing endpoint or manual search call is used.
    DECLARE @plan int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @plan OUT

    EXEC sp_OASetProperty @plan, 'EmitCompact', 0
    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/files/{{file_id}}/content" -H "Authorization: Bearer {{access_token}}" -L'
    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, 'ExaminePlan', @success OUT, @sTmp0, @plan
    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 @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        RETURN
      END
    EXEC sp_OAMethod @plan, 'Emit', @sTmp0 OUT
    PRINT @sTmp0
    --  Keep the candidate download in memory; do not write an unvalidated result.
    EXEC sp_OASetProperty @curl, 'ResponseFilePath', ''
    EXEC sp_OAMethod @curl, 'ClearTargetOutput', NULL, '*'
    DECLARE @authorizedCommand2 int
    EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @authorizedCommand2 OUT

    EXEC sp_OAMethod @authorizedCommand2, 'SetString', @success OUT, 'curl -X GET "https://api.box.com/2.0/files/{{file_id}}/content" -H "Authorization: Bearer {{access_token}}" -L'
    DECLARE @headerReplacement2 int
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'access_token'
    EXEC sp_OAMethod @authorizedCommand2, 'Replace', @headerReplacement2 OUT, '{{access_token}}', @sTmp0
    EXEC sp_OAMethod @authorizedCommand2, '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 @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        EXEC @hr = sp_OADestroy @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        EXEC @hr = sp_OADestroy @authorizedCommand2
        RETURN
      END
    --  AddOutput2 supplies candidate IDs. Box's phrase search and recursive
    --  ancestor filter are not an exact path lookup, so validate before saving.
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'folder_search_count'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp1 OUT, 'file_search_count'
    IF (@sTmp0 <> '1') or (@sTmp1 <> '1')
      BEGIN

        PRINT 'Search was ambiguous or incomplete. Expected exactly one result per search; no file was saved.'

        EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'folder_search_count'

        EXEC sp_OAMethod @curl, 'GetVar', @sTmp1 OUT, 'file_search_count'
        PRINT 'Folder results: ' + @sTmp0 + ' File results: ' + @sTmp1
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_search_count'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_search_count'
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        EXEC @hr = sp_OADestroy @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        EXEC @hr = sp_OADestroy @authorizedCommand2
        RETURN
      END
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp0 OUT, 'folder_result_name'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp1 OUT, 'folder_parent_id'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp2 OUT, 'file_result_name'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp3 OUT, 'file_parent_id'
    EXEC sp_OAMethod @curl, 'GetVar', @sTmp4 OUT, 'folder_id'
    IF (@sTmp0 <> @folderName) or (@sTmp1 <> '0') or (@sTmp2 <> @fileName) or (@sTmp3 <> @sTmp4)
      BEGIN

        PRINT 'The search result is not the exact root-folder/filename requested; no file was saved.'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'folder_search_count'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_result_name'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_parent_id'
        EXEC sp_OAMethod @curl, 'ClearVar', NULL, 'file_search_count'
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        EXEC @hr = sp_OADestroy @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        EXEC @hr = sp_OADestroy @authorizedCommand2
        RETURN
      END
    --  Validation is after the network download but before writing the destination.
    --  This in-memory example is intended for small files such as penguins.jpg.
    --  Create qa_output before running it.
    DECLARE @contents int
    EXEC @hr = sp_OACreate 'Chilkat.BinData', @contents OUT

    EXEC sp_OAMethod @curl, 'GetResponseBd', @success OUT, @contents
    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 @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        EXEC @hr = sp_OADestroy @authorizedCommand2
        EXEC @hr = sp_OADestroy @contents
        RETURN
      END
    EXEC sp_OAMethod @contents, 'WriteFile', @success OUT, @localPath
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @contents, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @curl
        EXEC @hr = sp_OADestroy @setupCommand
        EXEC @hr = sp_OADestroy @queryText
        EXEC @hr = sp_OADestroy @plan
        EXEC @hr = sp_OADestroy @authorizedCommand1
        EXEC @hr = sp_OADestroy @authorizedCommand2
        EXEC @hr = sp_OADestroy @contents
        RETURN
      END

    PRINT 'Downloaded to ' + @localPath

    EXEC @hr = sp_OADestroy @curl
    EXEC @hr = sp_OADestroy @setupCommand
    EXEC @hr = sp_OADestroy @queryText
    EXEC @hr = sp_OADestroy @plan
    EXEC @hr = sp_OADestroy @authorizedCommand1
    EXEC @hr = sp_OADestroy @authorizedCommand2
    EXEC @hr = sp_OADestroy @contents


END
GO