(SQL Server) Get GMail Message Metadata

Demonstrates how to get the metadata for each message in a list of message IDs.

For more information, see https://developers.google.com/gmail/api/v1/reference/users/messages/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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    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
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    EXEC sp_OASetProperty @http, 'AuthToken', 'ACCESS_TOKEN'

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

    -- Let's say we have the following JSON containing a list of message ID's

    -- {
    --   "messages": [
    --     {
    --       "id": "1712bc1dc22da2a2",
    --       "threadId": "1712bc1dc22da2a2"
    --     },
    --     {
    --       "id": "1712b94fa2168040",
    --       "threadId": "1712b94fa2168040"
    --     },
    --     {
    --       "id": "1711e4bd96ded05e",
    --       "threadId": "1711e4bd96ded05e"
    --     },
    --     {
    --       "id": "1711de0d119e751e",
    --       "threadId": "1711de0d119e751e"
    --     },
    --     {
    --       "id": "1711c81a43050f59",
    --       "threadId": "1711c81a43050f59"
    --     },
    --     {
    --       "id": "1711c2c1516abce5",
    --       "threadId": "1711c2c1516abce5"
    --     },
    --     {
    --       "id": "1711c161e655c915",
    --       "threadId": "1711c161e655c915"
    --     },
    --     {
    --       "id": "171199c6e454558c",
    --       "threadId": "171199c6e454558c"
    --     },
    --     {
    --       "id": "17119474c8aa248e",
    --       "threadId": "17119474c8aa248e"
    --     }
    --   ],
    --   "nextPageToken": "16710921492249671162",
    --   "resultSizeEstimate": 309
    -- }

    -- Normally your program would have the above JSON in a string variable (obtained from a previous call to get
    -- a list of message IDs from a GMail mailbox).  In this example, we'll load the JSON from a file.
    DECLARE @jsonMsgIds int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonMsgIds OUT

    DECLARE @success int
    EXEC sp_OAMethod @jsonMsgIds, 'LoadFile', @success OUT, 'qa_data/json/gmail_message_ids.json'

    DECLARE @id nvarchar(4000)

    DECLARE @i int
    SELECT @i = 0
    DECLARE @count_i int
    EXEC sp_OAMethod @jsonMsgIds, 'SizeOfArray', @count_i OUT, 'messages'
    WHILE @i < @count_i
      BEGIN
        EXEC sp_OASetProperty @jsonMsgIds, 'I', @i
        EXEC sp_OAMethod @jsonMsgIds, 'StringOf', @id OUT, 'messages[i].id'

        -- Get the metadata for this message ID.
        EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'id', @id

        DECLARE @resp int
        EXEC sp_OAMethod @http, 'QuickRequest', @resp OUT, 'GET', 'https://www.googleapis.com/gmail/v1/users/userId/messages/{$id}?format=metadata'
        EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @jsonMsgIds
            RETURN
          END


        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        PRINT 'Response Status Code: ' + @iTmp0

        DECLARE @jsonResponse int
        -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
        EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonResponse OUT

        EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
        EXEC sp_OAMethod @jsonResponse, 'Load', @success OUT, @sTmp0
        EXEC sp_OASetProperty @jsonResponse, 'EmitCompact', 0
        EXEC sp_OAMethod @jsonResponse, 'Emit', @sTmp0 OUT
        PRINT @sTmp0

        EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
        IF @iTmp0 <> 200
          BEGIN

            PRINT 'Failed.'
            EXEC @hr = sp_OADestroy @resp

            EXEC @hr = sp_OADestroy @http
            EXEC @hr = sp_OADestroy @jsonMsgIds
            EXEC @hr = sp_OADestroy @jsonResponse
            RETURN
          END

        EXEC @hr = sp_OADestroy @resp

        SELECT @i = @i + 1
      END

    -- Use this online tool to generate parsing code from sample JSON: 
    -- Generate Parsing Code from JSON

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonMsgIds
    EXEC @hr = sp_OADestroy @jsonResponse


END
GO