SQL Server
SQL Server
Bunny Edge Storage - List Files
See more Bunny CDN Examples
Retrieve a list of files and directories located in the given directory.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
-- 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 assumes the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- 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.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.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