SQL Server
SQL Server
Upload Binary File from Memory
See more Google Drive Examples
This example demonstrates how to upload a binary file from memory. It uploads the file to a subdirectory of our choosing.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
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
SELECT @success = 1
-- 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
-- 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.
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
-- -------------------------------------------------------------------------
-- A multipart upload to Google Drive needs a multipart/related Content-Type
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'multipart/related'
-- Specify each part of the request.
-- The 1st part is JSON with information about the file.
EXEC sp_OASetProperty @rest, 'PartSelector', '1'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'application/json; charset=UTF-8'
-- Construct the JSON that will contain the metadata about the file data to be uploaded...
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'name', 'hedgehogs.jpg'
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'description', 'A cute photo of two hedgehogs.'
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'mimeType', 'image/jpeg'
-- To place the file in a folder, we must add a parents[] array to the JSON
-- and add the folder ID.
-- In a previous example (see Build Local Metadata Cache
-- we built a local cache to make it easy to lookup file IDs given a file path.
-- Let's find the file ID for the folder "testFolder/abc/123"
DECLARE @gdCache int
EXEC @hr = sp_OACreate 'Chilkat.Cache', @gdCache OUT
EXEC sp_OASetProperty @gdCache, 'Level', 0
EXEC sp_OAMethod @gdCache, 'AddRoot', NULL, 'C:/ckCache/googleDrive'
DECLARE @folderId nvarchar(4000)
EXEC sp_OAMethod @gdCache, 'FetchText', @folderId OUT, 'testFolder/abc/123'
EXEC sp_OAGetProperty @gdCache, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
PRINT 'Filepath not found in cache.'
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @gdCache
RETURN
END
DECLARE @parents int
EXEC @hr = sp_OACreate 'Chilkat.JsonArray', @parents OUT
EXEC sp_OAMethod @json, 'AppendArray2', @success OUT, 'parents', @parents
EXEC sp_OAMethod @parents, 'AddStringAt', @success OUT, -1, @folderId
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @rest, 'SetMultipartBodyString', @success OUT, @sTmp0
-- The 2nd part is the file content, which will contain the binary image data.
EXEC sp_OASetProperty @rest, 'PartSelector', '2'
EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Content-Type', 'image/jpeg'
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
-- Read a JPG file from the local filesystem.
EXEC sp_OAMethod @fac, 'ReadEntireFile', @jpgBytes OUT, 'qa_data/jpg/hedgehogs.jpg'
-- Add the bytes to our upload
EXEC sp_OAMethod @rest, 'SetMultipartBodyBinary', @success OUT, @jpgBytes
DECLARE @jsonResponse nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestMultipart', @jsonResponse OUT, 'POST', '/upload/drive/v3/files?uploadType=multipart'
EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @gdCache
EXEC @hr = sp_OADestroy @parents
EXEC @hr = sp_OADestroy @fac
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 @json
EXEC @hr = sp_OADestroy @gdCache
EXEC @hr = sp_OADestroy @parents
EXEC @hr = sp_OADestroy @fac
RETURN
END
-- Show the JSON response.
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
-- A successful response looks like this:
-- {
-- "kind": "drive#file",
-- "id": "0B53Q6OSTWYoldmJ0Z3ZqT2x5MFk",
-- "name": "hedgehogs.jpg",
-- "mimeType": "image/jpeg"
-- }
-- Get the fileId:
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'id'
PRINT 'fileId: ' + @sTmp0
EXEC @hr = sp_OADestroy @gAuth
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @gdCache
EXEC @hr = sp_OADestroy @parents
EXEC @hr = sp_OADestroy @fac
END
GO