SQL Server
SQL Server
Get Object MetaData from Google Cloud Storage
See more Google Cloud Storage Examples
Demonstrates how to download a Google Cloud Storage object's metadata.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 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"
-- In this example, Get Google Cloud Storage OAuth2 Access Token,
-- the service account access token was saved to a text file. This example fetches the access token from the file..
DECLARE @sbToken int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbToken OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @sbToken, 'LoadFile', @success OUT, 'qa_data/tokens/googleCloudStorageAccessToken.txt', 'utf-8'
-- Send a GET equivalent to this curl command.
-- curl -X GET \
-- -H "Authorization: Bearer [OAUTH2_TOKEN]" \
-- -o "[SAVE_TO_LOCATION]" \
-- "https://www.googleapis.com/storage/v1/b/[BUCKET_NAME]/o/[OBJECT_NAME]?projection=full"
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OAMethod @sbToken, 'GetAsString', @sTmp0 OUT
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
-- Construct a URL to download the metadata for the object named "starfish.jpg" located in the "chilkat-ocean" bucket.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'bucket_name', 'chilkat-ocean'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'object_name', 'starfish.jpg'
DECLARE @url nvarchar(4000)
SELECT @url = 'https://www.googleapis.com/storage/v1/b/{$bucket_name}/o/{$object_name}?projection=full'
-- If there is an error response, then we didn't actually download the metadata,
-- but instead we downloaded an error response..
DECLARE @sbMetaData int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbMetaData OUT
EXEC sp_OAMethod @http, 'DownloadSb', @success OUT, @url, 'utf-8', @sbMetaData
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbMetaData
RETURN
END
DECLARE @responseCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @responseCode OUT
IF @responseCode = 401
BEGIN
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.'
END
IF @responseCode <> 200
BEGIN
PRINT 'Error response code = ' + @responseCode
PRINT 'Error:'
EXEC sp_OAMethod @sbMetaData, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbMetaData
RETURN
END
EXEC sp_OAMethod @sbMetaData, 'GetAsString', @sTmp0 OUT
PRINT @sTmp0
PRINT 'Success.'
-- Sample output:
-- {
-- "kind": "storage#object",
-- "id": "chilkat-ocean/starfish.jpg/1502670320090292",
-- "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-ocean/o/starfish.jpg",
-- "name": "starfish.jpg",
-- "bucket": "chilkat-ocean",
-- "generation": "1502670320090292",
-- "metageneration": "1",
-- "contentType": "image/jpeg",
-- "timeCreated": "2017-08-14T00:25:20.040Z",
-- "updated": "2017-08-14T00:25:20.040Z",
-- "storageClass": "MULTI_REGIONAL",
-- "timeStorageClassUpdated": "2017-08-14T00:25:20.040Z",
-- "size": "6229",
-- "md5Hash": "LpxZ2/JmI2fcl9/dqF2gSA==",
-- "mediaLink": "https://www.googleapis.com/download/storage/v1/b/chilkat-ocean/o/starfish.jpg?generation=1502670320090292&alt=media",
-- "acl": [
-- {
-- "kind": "storage#objectAccessControl",
-- "id": "chilkat-ocean/starfish.jpg/1502670320090292/project-owners-933037264567",
-- "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-ocean/o/starfish.jpg/acl/project-owners-933037264567",
-- "bucket": "chilkat-ocean",
-- "object": "starfish.jpg",
-- "generation": "1502670320090292",
-- "entity": "project-owners-933037264567",
-- "role": "OWNER",
-- "projectTeam": {
-- "projectNumber": "933037264567",
-- "team": "owners"
-- },
-- "etag": "CLT5kpW71dUCEAE="
-- },
-- {
-- "kind": "storage#objectAccessControl",
-- "id": "chilkat-ocean/starfish.jpg/1502670320090292/project-editors-933037264567",
-- "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-ocean/o/starfish.jpg/acl/project-editors-933037264567",
-- "bucket": "chilkat-ocean",
-- "object": "starfish.jpg",
-- "generation": "1502670320090292",
-- "entity": "project-editors-933037264567",
-- "role": "OWNER",
-- "projectTeam": {
-- "projectNumber": "933037264567",
-- "team": "editors"
-- },
-- "etag": "CLT5kpW71dUCEAE="
-- },
-- {
-- "kind": "storage#objectAccessControl",
-- "id": "chilkat-ocean/starfish.jpg/1502670320090292/project-viewers-933037264567",
-- "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-ocean/o/starfish.jpg/acl/project-viewers-933037264567",
-- "bucket": "chilkat-ocean",
-- "object": "starfish.jpg",
-- "generation": "1502670320090292",
-- "entity": "project-viewers-933037264567",
-- "role": "READER",
-- "projectTeam": {
-- "projectNumber": "933037264567",
-- "team": "viewers"
-- },
-- "etag": "CLT5kpW71dUCEAE="
-- },
-- {
-- "kind": "storage#objectAccessControl",
-- "id": "chilkat-ocean/starfish.jpg/1502670320090292/user-starfish@chilkat-176611.iam.gserviceaccount.com",
-- "selfLink": "https://www.googleapis.com/storage/v1/b/chilkat-ocean/o/starfish.jpg/acl/user-starfish@chilkat-176611.iam.gserviceaccount.com",
-- "bucket": "chilkat-ocean",
-- "object": "starfish.jpg",
-- "generation": "1502670320090292",
-- "entity": "user-starfish@chilkat-176611.iam.gserviceaccount.com",
-- "role": "OWNER",
-- "email": "starfish@chilkat-176611.iam.gserviceaccount.com",
-- "etag": "CLT5kpW71dUCEAE="
-- }
-- ],
-- "owner": {
-- "entity": "user-starfish@chilkat-176611.iam.gserviceaccount.com"
-- },
-- "crc32c": "9RjgwQ==",
-- "etag": "CLT5kpW71dUCEAE="
-- }
--
EXEC @hr = sp_OADestroy @sbToken
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @sbMetaData
END
GO