(SQL Server) Download GMail Message Attachment by ID

Demonstrates how to download a GMail email attachment by the attachment ID.

For more information, see https://developers.google.com/gmail/api/v1/reference/users/messages/attachments/get

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

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

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

    DECLARE @success int

    EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'
    EXEC sp_OASetProperty @http, 'Accept', 'application/json'

    -- See the following example:  Get GMail Message (format=full)
    -- It shows how to download an email by message ID, and it shows how to parse the JSON
    -- response which includes attachment names, ids, and other information.

    -- This example will assume we already have the attachment id and filename.

    -- This is the attachment ID for a file named "helloWorld.pdf".
    DECLARE @attachmentId nvarchar(4000)
    SELECT @attachmentId = 'ANGjdJ-oy3aCuZISJKLAhUdaEksCEklbAPyMaWzFgqOMGbPCRkgwgeu_Kttd99C17OBTHROkDZGekibTKWXGfscB5ww7fw4E65_V1dQ-jHhb2TD1Cdm58-BbNw2iDxzptco8iILPiSnLLfFn5Ps7nsRcxHaGTt3r0yqFKCuIYNnPK1vM04BXI_cfzo-HnI4I3tD6oHNHOGVQrL01MdShFQjPELPUjXM8z1qs7Kom-QyvV1iOldUN-66UuhynsmDX-CMM5TIdB-8KD_lmdhf-0DqG8JnCA20XpXyfqwS8XFkPA-t-QSjb7SdkHQFtQ4lz2PcBREFzZ2eI5j0l0Y_dQHRPYTeMwkVl1yl4MfFT4C4iso3VSF-eqaIjiFCbXKCFNyeEIW5WFsv189dhlSqU'

    DECLARE @messageId nvarchar(4000)
    SELECT @messageId = '1712bc1dc22da2a2'

    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'messageId', @messageId
    EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'attachmentId', @attachmentId

    DECLARE @url nvarchar(4000)
    SELECT @url = 'https://www.googleapis.com/gmail/v1/users/userId/messages/{$messageId}/attachments/{$attachmentId}'

    -- When we download, the response is JSON that contains the attachment data base64url encoded, like this:
    -- {
    --  "size": 934,
    --  "data": "JVBERi0xLjM ... CiUlRU9GCg=="
    -- }

    DECLARE @outputFilePath nvarchar(4000)
    SELECT @outputFilePath = 'qa_output/helloWorld.pdf'

    -- Download into a StringBuilder.
    DECLARE @sbJson int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @sbJson OUT

    EXEC sp_OAMethod @http, 'DownloadSb', @success OUT, @url, 'utf-8', @sbJson
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    EXEC sp_OAGetProperty @http, 'LastStatus', @iTmp0 OUT
    IF @iTmp0 <> 200
      BEGIN
        -- Something failed.
        -- the JSON contains an error message.
        EXEC sp_OAMethod @sbJson, 'GetAsString', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @sbJson
        RETURN
      END

    -- Load into a Chilkat JSON object.
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @json OUT

    EXEC sp_OAMethod @json, 'LoadSb', @success OUT, @sbJson

    -- Extract the base64url data into a BinData.
    DECLARE @bd int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.BinData', @bd OUT

    EXEC sp_OAMethod @json, 'BytesOf', @success OUT, 'data', 'base64url', @bd

    -- Save the data to a file.
    EXEC sp_OAMethod @bd, 'WriteFile', @success OUT, 'qa_output/helloWorld.pdf'


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @sbJson
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @bd


END
GO