(SQL Server) Bunny Edge Storage - List Files

See more Bunny CDN Examples

Retrieve a list of files and directories located in the given directory.

For more information, see https://docs.bunny.net/reference/get_-storagezonename-path-

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

// 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)
    -- This example assumes the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int

    -- Use the "Password" from the "FTP & HTTP API Access" console web page for your Bunny storage zone.
    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'AccessKey', 'YOUR_FTP_AND_HTTP_PASSWORD'

    -- Storage Endpoints:
    -- The storage API endpoint depends on the primary storage region of your storage zone. You can also find this in the FTP & HTTP API Information of your storage zone.

    -- Falkenstein: storage.bunnycdn.com
    -- New York: ny.storage.bunnycdn.com
    -- Los Angeles: la.storage.bunnycdn.com
    -- Singapore: sg.storage.bunnycdn.com
    -- Sydney: syd.storage.bunnycdn.com
    -- London: uk.storage.bunnycdn.com
    -- ... (possibly others??)

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

    -- Change the "storageZoneName" to your actual storage zone name.
    EXEC sp_OAMethod @http, 'QuickGetSb', @success OUT, 'https://uk.storage.bunnycdn.com/storageZoneName/testDir/', @sbResponse
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    DECLARE @statusCode int
    EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT

    PRINT 'Status code = ' + @statusCode

    IF @statusCode <> 200
      BEGIN
        EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbResponse
        RETURN
      END

    DECLARE @jarr int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonArray', @jarr OUT

    EXEC sp_OAMethod @jarr, 'LoadSb', @success OUT, @sbResponse
    EXEC sp_OASetProperty @jarr, 'EmitCompact', 0

    EXEC sp_OAMethod @jarr, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- Here is a sample response.  Parsing code is shown below..

    -- [
    --   {
    --     "Guid": "ebd9ccff-faf7-449d-9c71-8662b9f3cf41",
    --     "StorageZoneName": "chilkat",
    --     "Path": "/chilkat/testDir/",
    --     "ObjectName": "seahorse.jpg",
    --     "Length": 24388,
    --     "LastChanged": "2023-04-21T15:59:08.382",
    --     "ServerId": 586,
    --     "ArrayNumber": 0,
    --     "IsDirectory": false,
    --     "UserId": "6d26ab35-2914-422c-9378-d51f38166e53",
    --     "ContentType": "",
    --     "DateCreated": "2023-04-21T15:59:08.382",
    --     "StorageZoneId": 266805,
    --     "Checksum": "0503558E7B4D141C067BEECFD06F87F6F58B81E37918310F82C7C4077D27BD1D",
    --     "ReplicatedZones": "SE,DE,LA,SG,JH,BR,SYD,NY"
    --   },
    --   {
    --     "Guid": "e74b85f8-4d5d-459e-a2fc-0fcfa8c926aa",
    --     "StorageZoneName": "chilkat",
    --     "Path": "/chilkat/testDir/",
    --     "ObjectName": "seal.jpg",
    --     "Length": 342708,
    --     "LastChanged": "2023-04-21T15:59:08.763",
    --     "ServerId": 586,
    --     "ArrayNumber": 2,
    --     "IsDirectory": false,
    --     "UserId": "6d26ab35-2914-422c-9378-d51f38166e53",
    --     "ContentType": "",
    --     "DateCreated": "2023-04-21T15:59:08.763",
    --     "StorageZoneId": 266805,
    --     "Checksum": "C95F946A0299CE39C20A072B3DC581446B2EE2D94CA93901B58604F4EFB8AAB5",
    --     "ReplicatedZones": "DE,SE,SG,LA,JH,BR,SYD,NY"
    --   },
    --   {
    --     "Guid": "ca80cdbd-c6cf-445d-b367-0b33de7ae65a",
    --     "StorageZoneName": "chilkat",
    --     "Path": "/chilkat/testDir/",
    --     "ObjectName": "university.jpg",
    --     "Length": 277581,
    --     "LastChanged": "2023-04-21T15:59:10.142",
    --     "ServerId": 586,
    --     "ArrayNumber": 0,
    --     "IsDirectory": false,
    --     "UserId": "6d26ab35-2914-422c-9378-d51f38166e53",
    --     "ContentType": "",
    --     "DateCreated": "2023-04-21T15:59:10.142",
    --     "StorageZoneId": 266805,
    --     "Checksum": "96A10EAA1B778C066739A6233206022DB219B67A8775401D7865F40E6DD83571",
    --     "ReplicatedZones": "DE,SE,SG,JH,BR,LA,SYD,NY"
    --   }
    -- ]

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    DECLARE @json int

    DECLARE @Guid nvarchar(4000)

    DECLARE @StorageZoneName nvarchar(4000)

    DECLARE @Path nvarchar(4000)

    DECLARE @ObjectName nvarchar(4000)

    DECLARE @Length int

    DECLARE @LastChanged nvarchar(4000)

    DECLARE @ServerId int

    DECLARE @ArrayNumber int

    DECLARE @IsDirectory int

    DECLARE @UserId nvarchar(4000)

    DECLARE @ContentType nvarchar(4000)

    DECLARE @DateCreated nvarchar(4000)

    DECLARE @StorageZoneId int

    DECLARE @Checksum nvarchar(4000)

    DECLARE @ReplicatedZones nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAGetProperty @jarr, 'Size', @count_i OUT
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OAMethod @jarr, 'ObjectAt', @json OUT, @i
        EXEC sp_OAMethod @json, 'StringOf', @Guid OUT, 'Guid'
        EXEC sp_OAMethod @json, 'StringOf', @StorageZoneName OUT, 'StorageZoneName'
        EXEC sp_OAMethod @json, 'StringOf', @Path OUT, 'Path'
        EXEC sp_OAMethod @json, 'StringOf', @ObjectName OUT, 'ObjectName'
        EXEC sp_OAMethod @json, 'IntOf', @Length OUT, 'Length'
        EXEC sp_OAMethod @json, 'StringOf', @LastChanged OUT, 'LastChanged'
        EXEC sp_OAMethod @json, 'IntOf', @ServerId OUT, 'ServerId'
        EXEC sp_OAMethod @json, 'IntOf', @ArrayNumber OUT, 'ArrayNumber'
        EXEC sp_OAMethod @json, 'BoolOf', @IsDirectory OUT, 'IsDirectory'
        EXEC sp_OAMethod @json, 'StringOf', @UserId OUT, 'UserId'
        EXEC sp_OAMethod @json, 'StringOf', @ContentType OUT, 'ContentType'
        EXEC sp_OAMethod @json, 'StringOf', @DateCreated OUT, 'DateCreated'
        EXEC sp_OAMethod @json, 'IntOf', @StorageZoneId OUT, 'StorageZoneId'
        EXEC sp_OAMethod @json, 'StringOf', @Checksum OUT, 'Checksum'
        EXEC sp_OAMethod @json, 'StringOf', @ReplicatedZones OUT, 'ReplicatedZones'
        EXEC @hr = sp_OADestroy @json

        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbResponse
    EXEC @hr = sp_OADestroy @jarr


END
GO