SQL Server Google Cloud Storage: Copy File (Object) to another Bucket

Back to Index

Copies a fie (also known as an object) to another Google Cloud Storage bucket. This example copies the file "starfish.jpg" from the "chilkat-test" bucket to the "chilkat-images" bucket. In Google Cloud Storage, moving a file to a new bucket is a 2-step operation: First copy the file, then delete the original.

Documentation: https://cloud.google.com/storage/docs/renaming-copying-moving-objects

CURL Command

curl -X POST https://www.googleapis.com/storage/v1/b/chilkat-bucket/o/starfish.jpg/rewriteTo/b/chilkat-images/o/starfish.jpg?project=MY_CLOUD_STORAGE_PROJECT \
    --header "Authorization: Bearer CLOUD_STORAGE_TOKEN"

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://www.googleapis.com/storage/v1/b/chilkat-bucket/o/starfish.jpg/rewriteTo/b/chilkat-images/o/starfish.jpg?project=MY_CLOUD_STORAGE_PROJECT
    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, 'www.googleapis.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

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Authorization', 'Bearer CLOUD_STORAGE_TOKEN'

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

    EXEC sp_OAMethod @rest, 'FullRequestNoBodySb', @success OUT, 'POST', '/storage/v1/b/chilkat-bucket/o/starfish.jpg/rewriteTo/b/chilkat-images/o/starfish.jpg?project=MY_CLOUD_STORAGE_PROJECT', @sbResponseBody
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @rest
        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 @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 @kind nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @kind OUT, 'kind'
    DECLARE @totalBytesRewritten nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @totalBytesRewritten OUT, 'totalBytesRewritten'
    DECLARE @objectSize nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @objectSize OUT, 'objectSize'
    DECLARE @done int
    EXEC sp_OAMethod @jsonResponse, 'BoolOf', @done OUT, 'done'
    DECLARE @resourceKind nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceKind OUT, 'resource.kind'
    DECLARE @resourceId nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceId OUT, 'resource.id'
    DECLARE @resourceSelfLink nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceSelfLink OUT, 'resource.selfLink'
    DECLARE @resourceName nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceName OUT, 'resource.name'
    DECLARE @resourceBucket nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceBucket OUT, 'resource.bucket'
    DECLARE @resourceGeneration nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceGeneration OUT, 'resource.generation'
    DECLARE @resourceMetageneration nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceMetageneration OUT, 'resource.metageneration'
    DECLARE @resourceContentType nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceContentType OUT, 'resource.contentType'
    DECLARE @resourceTimeCreated nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceTimeCreated OUT, 'resource.timeCreated'
    DECLARE @resourceUpdated nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceUpdated OUT, 'resource.updated'
    DECLARE @resourceStorageClass nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceStorageClass OUT, 'resource.storageClass'
    DECLARE @resourceTimeStorageClassUpdated nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceTimeStorageClassUpdated OUT, 'resource.timeStorageClassUpdated'
    DECLARE @resourceSize nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceSize OUT, 'resource.size'
    DECLARE @resourceMd5Hash nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceMd5Hash OUT, 'resource.md5Hash'
    DECLARE @resourceMediaLink nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceMediaLink OUT, 'resource.mediaLink'
    DECLARE @resourceCrc32c nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceCrc32c OUT, 'resource.crc32c'
    DECLARE @resourceEtag nvarchar(4000)
    EXEC sp_OAMethod @jsonResponse, 'StringOf', @resourceEtag OUT, 'resource.etag'

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


END
GO

Sample JSON Response Body

{
  "kind": "storage#rewriteResponse",
  "totalBytesRewritten": "6229",
  "objectSize": "6229",
  "done": true,
  "resource": {
    "kind": "storage#object",
    "id": "chilkat-images/starfish.jpg/1540298057547474",
    "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-images/o/starfish.jpg",
    "name": "starfish.jpg",
    "bucket": "chilkat-images",
    "generation": "1540298057547474",
    "metageneration": "1",
    "contentType": "image/jpeg",
    "timeCreated": "2018-10-23T12:34:17.547Z",
    "updated": "2018-10-23T12:34:17.547Z",
    "storageClass": "MULTI_REGIONAL",
    "timeStorageClassUpdated": "2018-10-23T12:34:17.547Z",
    "size": "6229",
    "md5Hash": "LpxZ2/JmI2fcl9/dqF2gSA==",
    "mediaLink": "https://www.googleapis.com/download/storage/v1/b/chilkat-images/o/starfish.jpg?generation=1540298057547474&alt=media",
    "crc32c": "9RjgwQ==",
    "etag": "CNL9xbTJnN4CEAE="
  }
}