SQL Server Dropbox: Dropbox List Contents of Folder

Back to Index

Starts returning the contents of a folder. If the result's ListFolderResult.has_more field is true, call list_folder/continue with the returned ListFolderResult.cursor to retrieve more entries.

Documentation: https://www.dropbox.com/developers/documentation/http/documentation#files-list_folder

CURL Command

curl -X POST https://api.dropboxapi.com/2/files/list_folder \
    --header "Authorization: Bearer DROPBOX-ACCESS-TOKEN" \
    --header "Content-Type: application/json" \
    --data "{\"path\": \"/Homework/math\",\"recursive\": false,\"include_media_info\": false,\"include_deleted\": false,\"include_has_explicit_shared_members\": false,\"include_mounted_folders\": true}"

SQL Server Example

CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @rest int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Rest', @rest OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    --  URL: https://api.dropboxapi.com/2/files/list_folder
    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 <> 1
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ConnectFailReason', @iTmp0 OUT
        PRINT 'ConnectFailReason: ' + @iTmp0
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END

    --  See the Online Tool for Generating JSON Creation Code
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'path', '/Homework/math'
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'recursive', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'include_media_info', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'include_deleted', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'include_has_explicit_shared_members', 0
    EXEC sp_OAMethod @json, 'UpdateBool', @success OUT, 'include_mounted_folders', 1

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', 'Bearer DROPBOX-ACCESS-TOKEN'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json'

    DECLARE @sbRequestBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbRequestBody OUT

    EXEC sp_OAMethod @json, 'EmitSb', @success OUT, @sbRequestBody
    DECLARE @sbResponseBody int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbResponseBody OUT

    EXEC sp_OAMethod @rest, 'FullRequestSb', @success OUT, 'POST', '/2/files/list_folder', @sbRequestBody, @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END
    DECLARE @respStatusCode int
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @respStatusCode OUT
    IF @respStatusCode >= 400
      BEGIN

        PRINT 'Response Status Code = ' + @respStatusCode

        PRINT 'Response Header:'
        EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Response Body:'
        EXEC sp_OAMethod @sbResponseBody, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @sbRequestBody
        EXEC @hr = sp_OADestroy @sbResponseBody
        RETURN
      END

    DECLARE @jsonResponse int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @jsonResponse OUT

    EXEC sp_OAMethod @jsonResponse, 'LoadSb', @success OUT, @sbResponseBody

    --  See the Online Tool for Generating JSON Parse Code
    DECLARE @i int

    DECLARE @count_i int

    DECLARE @cursor nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @cursor OUT, 'cursor'
    DECLARE @has_more int
    EXEC sp_OAMethod @jsonResponse, 'BoolOf', @has_more OUT, 'has_more'
    SELECT @i = 0
    EXEC sp_OAMethod @jsonResponse, 'SizeOfArray', @count_i OUT, 'entries'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonResponse, 'I', @i
        DECLARE @Tag nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @Tag OUT, 'entries[i].".tag"'
        DECLARE @name nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @name OUT, 'entries[i].name'
        DECLARE @path_lower nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @path_lower OUT, 'entries[i].path_lower'
        DECLARE @path_display nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @path_display OUT, 'entries[i].path_display'
        DECLARE @id nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @id OUT, 'entries[i].id'
        DECLARE @client_modified nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @client_modified OUT, 'entries[i].client_modified'
        DECLARE @server_modified nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @server_modified OUT, 'entries[i].server_modified'
        DECLARE @rev nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @rev OUT, 'entries[i].rev'
        DECLARE @size int
        EXEC sp_OAMethod @jsonResponse, 'IntOf', @size OUT, 'entries[i].size'
        DECLARE @content_hash nvarchar(4000)
        EXEC sp_OAMethod @jsonResponse, 'StringOf', @content_hash OUT, 'entries[i].content_hash'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @sbRequestBody
    EXEC @hr = sp_OADestroy @sbResponseBody
    EXEC @hr = sp_OADestroy @jsonResponse


END
GO

Sample JSON Response Body

{
  "entries": [
    {
      ".tag": "file",
      "name": "Matrices.txt",
      "path_lower": "/homework/math/matrices.txt",
      "path_display": "/Homework/math/Matrices.txt",
      "id": "id:qk7WwvROeSAAAAAAAAAAAQ",
      "client_modified": "2016-06-02T20:41:02Z",
      "server_modified": "2016-06-02T20:41:03Z",
      "rev": "5482db15f",
      "size": 6,
      "content_hash": "5a3c776e2631edabe2ba710ac72301b3aeca821ba4f7d36b56cc6050ea6c2ba8"
    },
    {
      ".tag": "file",
      "name": "Document.docx",
      "path_lower": "/homework/math/document.docx",
      "path_display": "/Homework/math/Document.docx",
      "id": "id:JSXYsxHo1hAAAAAAAAAABw",
      "client_modified": "2018-10-22T21:59:23Z",
      "server_modified": "2018-10-22T21:59:23Z",
      "rev": "10482db15f",
      "size": 11009,
      "content_hash": "ab9c1887a653a8f42e4401425ac02839e1f76533e1d44ac8d0605d870a16c25d"
    }
  ],
  "cursor": "AAHnmmjY1IztwebFmkByuZ_Vgzz-tSYXYE3KhdkUxBfETpGjPqTBz7ZzBj1G7zkPPVanKCt0FtzqXjDQ2ggQKYZTiL4mJ346fRelmP049QjN5CZzeMHbD_lSFqA96Br36vR9YLg40VAHu42TcCG38MbUPOCKa-wdguu5C1JsgYvIaXIcjRMCfbxz98JdYhhefmu0pjoxtgtCstOrmp_pXIng",
  "has_more": false
}