SQL Server Requires Chilkat v11.7.0+
SQL Server
Box: Set Up Reusable HttpCurl Requests and Outputs
See more Box Examples
Register searchFolder and searchFile once, with deferred folder_name and file_name output rules. This review example resolves a folder directly under root and a file directly inside it. The download operation requires exactly one search result at each step and validates exact names and parent IDs. Registration and ExaminePlan send no requests. Other Box examples still have their existing standalone setups.
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 @sTmp0 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.
-- Examine the plan without running any HTTP requests.
DECLARE @setupPlan int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @setupPlan OUT
EXEC sp_OASetProperty @setupPlan, '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, @setupPlan
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 @setupPlan
EXEC @hr = sp_OADestroy @authorizedCommand1
RETURN
END
EXEC sp_OAMethod @setupPlan, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Keep this HttpCurl object for the search-based download operation.'
EXEC @hr = sp_OADestroy @curl
EXEC @hr = sp_OADestroy @setupCommand
EXEC @hr = sp_OADestroy @queryText
EXEC @hr = sp_OADestroy @setupPlan
EXEC @hr = sp_OADestroy @authorizedCommand1
END
GO