SQL Server

Google Cloud Storage List Buckets

See more Google Cloud Storage Examples

Demonstrates how to retrieve a list of buckets for a given project. SQL Server

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

    -- This example uses a previously obtained access token having permission for the 
    -- scope "https://www.googleapis.com/auth/cloud-platform"

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

    EXEC sp_OAMethod @jsonToken, 'LoadFile', @success OUT, 'qa_data/tokens/googleCloudStorage.json'
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @jsonToken, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        RETURN
      END

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT

    EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'access_token'
    EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0

    -- For more info see Cloud Storage Documentation - Buckets: list 
    -- 
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'PROJECT_ID', 'chilkattest-1050'
    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://www.googleapis.com/storage/v1/b?project={$PROJECT_ID}', @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    DECLARE @responseCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @responseCode OUT
    IF @responseCode = 401
      BEGIN
        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        PRINT @sTmp0

        PRINT 'If invalid credentials, then it is likely the access token expired.'

        PRINT 'Your app should automatically fetch a new access token and re-try.'
        EXEC @hr = sp_OADestroy @jsonToken
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END


    PRINT 'Response code: ' + @responseCode

    PRINT 'Response body'

    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @json, 'Load', @success OUT, @sTmp0

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

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

    -- A response code = 200 indicates success, and the response body contains JSON such as this:

    -- {
    --   "kind": "storage#buckets",
    --   "items": [
    --     {
    --       "kind": "storage#bucket",
    --       "id": "chilkat-bucket",
    --       "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-bucket",
    --       "projectNumber": "5332332985",
    --       "name": "chilkat-bucket",
    --       "timeCreated": "2018-10-23T00:04:44.507Z",
    --       "updated": "2018-10-23T00:04:44.507Z",
    --       "metageneration": "1",
    --       "iamConfiguration": {
    --         "bucketPolicyOnly": {
    --           "enabled": false
    --         }
    --       },
    --       "location": "US",
    --       "storageClass": "MULTI_REGIONAL",
    --       "etag": "CAE="
    --     },
    --     {
    --       "kind": "storage#bucket",
    --       "id": "chilkat-images",
    --       "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-images",
    --       "projectNumber": "5332332985",
    --       "name": "chilkat-images",
    --       "timeCreated": "2018-10-23T11:24:43.000Z",
    --       "updated": "2018-10-23T11:24:43.000Z",
    --       "metageneration": "1",
    --       "iamConfiguration": {
    --         "bucketPolicyOnly": {
    --           "enabled": false
    --         }
    --       },
    --       "location": "US",
    --       "storageClass": "MULTI_REGIONAL",
    --       "etag": "CAE="
    --     }
    --   ]
    -- }

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

    DECLARE @kind nvarchar(4000)

    DECLARE @i int

    DECLARE @count_i int

    DECLARE @id nvarchar(4000)

    DECLARE @selfLink nvarchar(4000)

    DECLARE @projectNumber nvarchar(4000)

    DECLARE @name nvarchar(4000)

    DECLARE @timeCreated nvarchar(4000)

    DECLARE @updated nvarchar(4000)

    DECLARE @metageneration nvarchar(4000)

    DECLARE @iamConfigurationBucketPolicyOnlyEnabled int

    DECLARE @location nvarchar(4000)

    DECLARE @storageClass nvarchar(4000)

    DECLARE @etag nvarchar(4000)

    EXEC sp_OAMethod @json, 'StringOf', @kind OUT, 'kind'
    SELECT @i = 0
    EXEC sp_OAMethod @json, 'SizeOfArray', @count_i OUT, 'items'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @json, 'I', @i
        EXEC sp_OAMethod @json, 'StringOf', @kind OUT, 'items[i].kind'
        EXEC sp_OAMethod @json, 'StringOf', @id OUT, 'items[i].id'
        EXEC sp_OAMethod @json, 'StringOf', @selfLink OUT, 'items[i].selfLink'
        EXEC sp_OAMethod @json, 'StringOf', @projectNumber OUT, 'items[i].projectNumber'
        EXEC sp_OAMethod @json, 'StringOf', @name OUT, 'items[i].name'
        EXEC sp_OAMethod @json, 'StringOf', @timeCreated OUT, 'items[i].timeCreated'
        EXEC sp_OAMethod @json, 'StringOf', @updated OUT, 'items[i].updated'
        EXEC sp_OAMethod @json, 'StringOf', @metageneration OUT, 'items[i].metageneration'
        EXEC sp_OAMethod @json, 'BoolOf', @iamConfigurationBucketPolicyOnlyEnabled OUT, 'items[i].iamConfiguration.bucketPolicyOnly.enabled'
        EXEC sp_OAMethod @json, 'StringOf', @location OUT, 'items[i].location'
        EXEC sp_OAMethod @json, 'StringOf', @storageClass OUT, 'items[i].storageClass'
        EXEC sp_OAMethod @json, 'StringOf', @etag OUT, 'items[i].etag'
        SELECT @i = @i + 1
      END

    EXEC @hr = sp_OADestroy @jsonToken
    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @json


END
GO