SQL Server
SQL Server
Get File Metadata
See more Google Drive Examples
Gets a file's metadata or content by ID.See Google Drive Files get for additional details.
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
DECLARE @iTmp0 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
SELECT @success = 1
-- It 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
-- Google Drive scope.
DECLARE @gAuth int
EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @gAuth OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @gAuth, 'AccessToken', 'GOOGLE-DRIVE-ACCESS-TOKEN'
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Connect using TLS.
-- A single REST object, once connected, can be used for many Google Drive REST API calls.
-- The auto-reconnect indicates that if the already-established HTTPS connection is closed,
-- then it will be automatically re-established as needed.
DECLARE @bAutoReconnect int
SELECT @bAutoReconnect = 1
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'www.googleapis.com', 443, 1, @bAutoReconnect
-- Provide the authentication credentials (i.e. the access token)
EXEC sp_OAMethod @rest, 'SetAuthGoogle', @success OUT, @gAuth
-- ------------------------------------------------------------------------------
-- We need to send a GET request like this:
-- GET https://www.googleapis.com/drive/v3/files/fileId
-- The fileId is part of the path.
DECLARE @sbPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbPath OUT
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '/drive/v3/files/'
-- Assume we previously obtained the fileId for the file of interest...
EXEC sp_OAMethod @sbPath, 'Append', @success OUT, '0B53Q6OSTWYolbUF6WS1Gei1oalk'
-- Indicate that we want ALL possible fields.
-- If no fields are indicated, then only the basic fields are returned.
DECLARE @allFields nvarchar(4000)
SELECT @allFields = 'appProperties,capabilities,contentHints,createdTime,description,explicitlyTrashed,fileExtension,folderColorRgb,fullFileExtension,headRevisionId,iconLink,id,imageMediaMetadata,isAppAuthorized,kind,lastModifyingUser,md5Checksum,mimeType,modifiedByMeTime,modifiedTime,name,originalFilename,ownedByMe,owners,parents,permissions,properties,quotaBytesUsed,shared,sharedWithMeTime,sharingUser,size,spaces,starred,thumbnailLink,trashed,version,videoMediaMetadata,viewedByMe,viewedByMeTime,viewersCanCopyContent,webContentLink,webViewLink,writersCanShare'
EXEC sp_OAMethod @rest, 'AddQueryParam', @success OUT, 'fields', @allFields
DECLARE @jsonResponse nvarchar(4000)
EXEC sp_OAMethod @sbPath, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', @sTmp0
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
-- A successful response will have a status code equal to 200.
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
PRINT 'response status code = ' + @iTmp0
EXEC sp_OAGetProperty @rest, 'ResponseStatusText', @sTmp0 OUT
PRINT 'response status text = ' + @sTmp0
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT 'response header: ' + @sTmp0
PRINT 'response JSON: ' + @jsonResponse
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
RETURN
END
-- ---------------------------------------------------
-- Sample code for parsing this JSON is shown below...
-- ---------------------------------------------------
-- A successful response looks like this:
-- {
-- "kind": "drive#file",
-- "id": "0B53Q6OSTWYolbUF6WS1Gei1oalk",
-- "name": "penguins.jpg",
-- "mimeType": "image/jpeg",
-- "starred": false,
-- "trashed": false,
-- "explicitlyTrashed": false,
-- "parents": [
-- "0B53Q6OSTWYola296ODZUSm5GYU0"
-- ],
-- "spaces": [
-- "drive"
-- ],
-- "version": "672",
-- "webContentLink": "https://docs.google.com/a/chilkatcloud.com/uc?id=0B53Q6OSTWYolbUF6WS1Gei1oalk&export=download",
-- "webViewLink": "https://drive.google.com/a/chilkatcloud.com/file/d/0B53Q6OSTWYolbUF6WS1Gei1oalk/view?usp=drivesdk",
-- "iconLink": "https://ssl.gstatic.com/docs/doclist/images/icon_11_image_list.png",
-- "thumbnailLink": "https://lh5.googleusercontent.com/uIV1dJdc8vsAQzaaIInpc9AV55AcdHhpM7fQeI2RN2qbCSnhjK9IDdNQAOTtkvjS9ZObqw=s220",
-- "viewedByMe": true,
-- "viewedByMeTime": "2016-10-08T12:38:02.258Z",
-- "createdTime": "2016-10-08T12:38:02.258Z",
-- "modifiedTime": "2016-10-08T12:38:02.258Z",
-- "modifiedByMeTime": "2016-10-08T12:38:02.258Z",
-- "owners": [
-- {
-- "kind": "drive#user",
-- "displayName": "Matthew Smith",
-- "me": true,
-- "permissionId": "08366696655122259092",
-- "emailAddress": "support@chilkatcloud.com"
-- }
-- ],
-- "lastModifyingUser": {
-- "kind": "drive#user",
-- "displayName": "Matthew Smith",
-- "me": true,
-- "permissionId": "08366696655122259092",
-- "emailAddress": "support@chilkatcloud.com"
-- },
-- "shared": false,
-- "ownedByMe": true,
-- "capabilities": {
-- "canEdit": true,
-- "canComment": true,
-- "canShare": true,
-- "canCopy": true,
-- "canReadRevisions": true
-- },
-- "viewersCanCopyContent": true,
-- "writersCanShare": true,
-- "permissions": [
-- {
-- "kind": "drive#permission",
-- "id": "08366696655122259092",
-- "type": "user",
-- "emailAddress": "support@chilkatcloud.com",
-- "role": "owner",
-- "displayName": "Matthew Smith"
-- }
-- ],
-- "originalFilename": "penguins.jpg",
-- "fullFileExtension": "jpg",
-- "fileExtension": "jpg",
-- "md5Checksum": "9d377b10ce778c4938b3c7e2c63a229a",
-- "size": "777835",
-- "quotaBytesUsed": "777835",
-- "headRevisionId": "0B53Q6OSTWYolUUlHVFhHdFlXQ0R1aHZuT0tORTVzc2h5NnZjPQ",
-- "imageMediaMetadata": {
-- "width": 1024,
-- "height": 768,
-- "rotation": 0,
-- "time": "2009:03:12 13:48:35"
-- },
-- "isAppAuthorized": false
-- }
-- Iterate over each file in the response and show the name, id, and mimeType.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @jsonResponse
-- Show the full JSON response.
EXEC sp_OASetProperty @json, 'EmitCompact', 0
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT '-'
-- Demonstrate how to get pieces of information:
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'originalFilename'
PRINT 'Original Filename: ' + @sTmp0
DECLARE @wasLastModifiedUserMe int
EXEC sp_OAMethod @json, 'BoolOf', @wasLastModifiedUserMe OUT, 'lastModifyingUser.me'
PRINT 'Was Last Modified by Me: ' + @wasLastModifiedUserMe
-- Assuming this is an image file...
DECLARE @width int
EXEC sp_OAMethod @json, 'IntOf', @width OUT, 'imageMediaMetadata.width'
PRINT 'Image Width: ' + @width
-- Iterate over parents...
DECLARE @i int
DECLARE @numParents int
EXEC sp_OAMethod @json, 'SizeOfArray', @numParents OUT, 'parents'
SELECT @i = 0
WHILE @i < @numParents
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'parents[i]'
PRINT 'Parent ' + @i + ': ' + @sTmp0
SELECT @i = @i + 1
END
-- Iterate over owners...
DECLARE @numOwners int
EXEC sp_OAMethod @json, 'SizeOfArray', @numOwners OUT, 'owners'
SELECT @i = 0
WHILE @i < @numOwners
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'owners[i].kind'
EXEC sp_OAMethod @json, 'StringOf', @sTmp1 OUT, 'owners[i].displayName'
PRINT 'Owner ' + @i + ': kind=' + @sTmp0 + ' displayName=' + @sTmp1
SELECT @i = @i + 1
END
-- Iterate over permissions...
DECLARE @numPermissions int
EXEC sp_OAMethod @json, 'SizeOfArray', @numPermissions OUT, 'permissions'
SELECT @i = 0
WHILE @i < @numPermissions
BEGIN
EXEC sp_OASetProperty @json, 'I', @i
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'permissions[i].kind'
EXEC sp_OAMethod @json, 'StringOf', @sTmp1 OUT, 'permissions[i].id'
PRINT 'Permission ' + @i + ': kind=' + @sTmp0 + ' id=' + @sTmp1
SELECT @i = @i + 1
END
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @sbPath
EXEC @hr = sp_OADestroy @json
END
GO