SQL Server
SQL Server
Dropbox: Access Your Own Account
See more Dropbox Examples
To programmatically interact with your own Dropbox account, such as for uploading, downloading, listing files, etc., go to the Dropbox app console and create an application. Then generate an access token in the online app console as shown in the screenshot below. The access token does not expire, and can be used to access your own account from your own non-web application.The example code, located below the screenshot, shows how to list the files in the root folder.
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 @iTmp1 int
DECLARE @iTmp2 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- Connect to the www.dropbox.com endpoint.
DECLARE @bTls int
SELECT @bTls = 1
DECLARE @port int
SELECT @port = 443
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'api.dropboxapi.com', @port, @bTls, @bAutoReconnect
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
RETURN
END
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', 'Bearer DROPBOX-ACCESS-TOKEN'
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
-- The root folder should be an empty string, not "/"
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'path', ''
EXEC sp_OAMethod @json, 'AppendBool', @success OUT, 'recursive', 0
EXEC sp_OAMethod @json, 'AppendBool', @success OUT, 'include_media_info', 0
EXEC sp_OAMethod @json, 'AppendBool', @success OUT, 'include_deleted', 0
EXEC sp_OAMethod @json, 'AppendBool', @success OUT, 'include_has_explicit_shared_members', 0
DECLARE @responseStr nvarchar(4000)
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestString', @responseStr OUT, 'POST', '/2/files/list_folder', @sTmp0
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
RETURN
END
-- Success is indicated by a 200 response status code.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
-- Examine the request/response to see what happened.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response body (if any): ' + @responseStr
PRINT '---'
EXEC sp_OAGetProperty @rest, 'LastRequestStartLine', @sTmp0 OUT
PRINT 'LastRequestStartLine: ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'LastRequestHeader', @sTmp0 OUT
PRINT 'LastRequestHeader: ' + @sTmp0
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
RETURN
END
DECLARE @jsonResponse int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT
EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @responseStr
EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
PRINT @sTmp0
-- A sample JSON response is shown at the end of this example.
-- The following code iterates over the entries.
DECLARE @dt int
EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT
DECLARE @numEntries int
EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @numEntries OUT, 'entries'
DECLARE @i int
SELECT @i = 0
WHILE @i < @numEntries
BEGIN
EXEC sp_OASetProperty @jsonResponse, 'I', @i
PRINT '----'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].name'
PRINT 'name: ' + @sTmp0
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].path_lower'
PRINT 'path_lower: ' + @sTmp0
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].path_display'
PRINT 'path_display: ' + @sTmp0
EXEC sp_OAMethod @jsonResponse, 'HasMember', @iTmp0 OUT, 'entries[i].sharing_info'
IF @iTmp0 = 1
BEGIN
PRINT 'has sharing_info...'
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].sharing_info.read_only'
PRINT 'read_only: ' + @sTmp0
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].sharing_info.shared_folder_id'
PRINT 'shared_folder_id: ' + @sTmp0
END
EXEC sp_OAMethod @jsonResponse, 'HasMember', @iTmp0 OUT, 'entries[i].client_modified'
IF @iTmp0 = 1
BEGIN
-- Demonstrate how to parse a date/time:
DECLARE @ckdt int
EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @ckdt OUT
EXEC sp_OAMethod @jsonResponse, 'StringOf', @sTmp0 OUT, 'entries[i].client_modified'
EXEC sp_OAMethod @ckdt, 'SetFromTimestamp', @success OUT, @sTmp0
-- The date/time can now be converted to many other formats, or the individual parts
-- can be accessed.
DECLARE @bLocalDateTime int
SELECT @bLocalDateTime = 1
EXEC sp_OAMethod @ckdt, 'ToDtObj', NULL, @bLocalDateTime, @dt
EXEC sp_OAGetProperty @dt, 'Year', @iTmp0 OUT
EXEC sp_OAGetProperty @dt, 'Month', @iTmp1 OUT
EXEC sp_OAGetProperty @dt, 'Day', @iTmp2 OUT
PRINT @iTmp0 + '-' + @iTmp1 + '-' + @iTmp2
END
SELECT @i = @i + 1
END
PRINT 'Success.'
-- {
-- "entries": [
-- {
-- ".tag": "folder",
-- "name": "chilkat Team Folder",
-- "path_lower": "/chilkat team folder",
-- "path_display": "/chilkat Team Folder",
-- "id": "id:2VqX9RLr-JAAAAAAAAAAAQ",
-- "shared_folder_id": "1210954290",
-- "sharing_info": {
-- "read_only": false,
-- "shared_folder_id": "1210954290"
-- }
-- },
-- {
-- ".tag": "file",
-- "name": "Get Started with Dropbox.pdf",
-- "path_lower": "/get started with dropbox.pdf",
-- "path_display": "/Get Started with Dropbox.pdf",
-- "id": "id:oxE2oMDqH4AAAAAAAAAAAQ",
-- "client_modified": "2016-05-07T11:47:47Z",
-- "server_modified": "2016-05-07T11:47:47Z",
-- "rev": "1483db13f",
-- "size": 692088
-- }
-- ],
-- "cursor": "AAEnZXciJyS4gzC_tlX6K2na4c8o7_09-dxmXKHpkPPyf3Kl0H4N-VYnz424nCrFOJuhMiM5_RgNAersumx9qe7NgCSdlppr80iFk0gf6vPlecM6SBtLcs6OYXL8ILWiZ62pfnOvgC3WKGlG6dqZ-VXH",
-- "has_more": false
-- }
--
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonResponse
EXEC @hr = sp_OADestroy @dt
EXEC @hr = sp_OADestroy @ckdt
END
GO