Synchronize Gmail message IDs and labels between runs
See more GMail REST API Examples
Maintain gmail-sync.json containing the mailbox address, a string historyId, and a small local cache of message IDs, thread IDs, and labels. The first run scans the complete mailbox including Spam and Trash. Later runs follow all history pages and refresh only messages affected by additions, deletions, or label changes. A message GET returning 404 removes that message from the local cache.
An expired history checkpoint causes a full rebuild. The full scan records a checkpoint before listing messages, so the next run also reconciles changes made during the scan. Incremental runs save the final history response checkpoint only after every affected message has been processed. The cache and checkpoint are committed together, with a previous-file recovery path. History IDs remain strings throughout.
Run in a private directory and serialize runs sharing the cache. The sample holds the cache in memory for clarity. Larger applications can use database upserts and deletions, committing the checkpoint in the same transaction. The cache contains message identifiers and labels. Fetch MIME or selected headers when the application needs message content.
Authorize the application with https://www.googleapis.com/auth/gmail.readonly. The example reads the access token from gmail-token.json in a private working directory. Authorize a desktop application.
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 @iTmp1 int
DECLARE @iTmp2 int
DECLARE @sTmp0 nvarchar(4000)
DECLARE @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- Assumes Chilkat is unlocked. See Global Unlock Sample.
DECLARE @token int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @token OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OAMethod @token, 'LoadFile', @success OUT, 'gmail-token.json'
IF @success <> 1
BEGIN
PRINT 'Could not read gmail-token.json from the private working directory.'
EXEC @hr = sp_OADestroy @token
RETURN
END
EXEC sp_OAMethod @token, 'JsonTypeOf', @iTmp0 OUT, 'access_token'
EXEC sp_OAMethod @token, 'StringOfEquals', @iTmp1 OUT, 'access_token', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'An OAuth access token is required.'
EXEC @hr = sp_OADestroy @token
RETURN
END
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
EXEC sp_OASetProperty @http, 'RequireSslCertVerify', 1
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OASetProperty @http, 'AuthToken', @sTmp0
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
DECLARE @reply int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @reply OUT
DECLARE @files int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @files OUT
-- Recover the previous complete file if a run stopped during replacement.
DECLARE @fileStatus int
EXEC sp_OAMethod @files, 'FileExists3', @fileStatus OUT, 'gmail-sync.json'
IF @fileStatus < 0
BEGIN
PRINT 'Could not inspect gmail-sync.json.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
RETURN
END
IF @fileStatus = 0
BEGIN
DECLARE @backupStatus int
EXEC sp_OAMethod @files, 'FileExists3', @backupStatus OUT, 'gmail-sync.json.previous'
IF @backupStatus < 0
BEGIN
PRINT 'Could not inspect the backup file.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
RETURN
END
IF @backupStatus = 1
BEGIN
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-sync.json.previous', 'gmail-sync.json'
IF @success <> 1
BEGIN
PRINT 'Could not restore the previous complete file.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
RETURN
END
SELECT @fileStatus = 1
END
END
DECLARE @state int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @state OUT
IF @fileStatus = 1
BEGIN
EXEC sp_OAMethod @state, 'LoadFile', @success OUT, 'gmail-sync.json'
IF @success <> 1
BEGIN
PRINT 'Could not read the existing synchronization state.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
EXEC sp_OAMethod @state, 'JsonTypeOf', @iTmp0 OUT, 'historyId'
EXEC sp_OAMethod @state, 'JsonTypeOf', @iTmp1 OUT, 'messages'
EXEC sp_OAMethod @state, 'JsonTypeOf', @iTmp2 OUT, 'emailAddress'
IF (@iTmp0 <> 1) or (@iTmp1 <> 3) or (@iTmp2 <> 1)
BEGIN
PRINT 'The synchronization state is incomplete. Restore its previous complete copy.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
END
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://gmail.googleapis.com/gmail/v1/users/me/profile', @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'emailAddress'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'emailAddress', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Expected emailAddress in the response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'historyId'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'historyId', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Expected historyId in the response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
DECLARE @mailbox nvarchar(4000)
EXEC sp_OAMethod @reply, 'StringOf', @mailbox OUT, 'emailAddress'
DECLARE @baseline nvarchar(4000)
EXEC sp_OAMethod @reply, 'StringOf', @baseline OUT, 'historyId'
IF @fileStatus = 1
BEGIN
EXEC sp_OAMethod @state, 'StringOfEquals', @iTmp0 OUT, 'emailAddress', @mailbox, 0
IF @iTmp0 <> 1
BEGIN
PRINT 'Use a separate synchronization file for this mailbox.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
RETURN
END
END
DECLARE @fullSync int
SELECT @fullSync = @fileStatus = 0
DECLARE @checkpoint nvarchar(4000)
SELECT @checkpoint = @baseline
DECLARE @ids int
EXEC @hr = sp_OACreate 'Chilkat.StringArray', @ids OUT
EXEC sp_OASetProperty @ids, 'Unique', 1
DECLARE @baseUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @baseUrl OUT
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
DECLARE @pageToken nvarchar(4000)
SELECT @pageToken = ''
DECLARE @more int
SELECT @more = 1
IF @fullSync <> 1
BEGIN
EXEC sp_OAMethod @baseUrl, 'SetString', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/history?maxResults=500&startHistoryId='
EXEC sp_OAMethod @state, 'StringOf', @sTmp1 OUT, 'historyId'
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, @sTmp0
DECLARE @changeTypes int
EXEC @hr = sp_OACreate 'Chilkat.StringTable', @changeTypes OUT
EXEC sp_OAMethod @changeTypes, 'Append', @success OUT, 'messagesAdded'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @changeTypes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
RETURN
END
EXEC sp_OAMethod @changeTypes, 'Append', @success OUT, 'messagesDeleted'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @changeTypes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
RETURN
END
EXEC sp_OAMethod @changeTypes, 'Append', @success OUT, 'labelsAdded'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @changeTypes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
RETURN
END
EXEC sp_OAMethod @changeTypes, 'Append', @success OUT, 'labelsRemoved'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @changeTypes, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
RETURN
END
DECLARE @record int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @record OUT
DECLARE @eventPath int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @eventPath OUT
WHILE (@more = 1)
BEGIN
EXEC sp_OAMethod @baseUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @url, 'SetString', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'Append', @success OUT, '&pageToken='
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @pageToken
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 404
BEGIN
PRINT 'History checkpoint expired. Rebuilding the cache.'
SELECT @fullSync = 1
SELECT @more = 0
END
ELSE
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
DECLARE @h int
SELECT @h = 0
EXEC sp_OAMethod @reply, 'SizeOfArray', @iTmp0 OUT, 'history'
WHILE (@h < @iTmp0)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @h
EXEC sp_OAMethod @reply, 'ObjectOf2', @success OUT, 'history[i]', @record
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @reply, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
DECLARE @t int
SELECT @t = 0
EXEC sp_OAGetProperty @changeTypes, 'Count', @iTmp0 OUT
WHILE (@t < @iTmp0)
BEGIN
DECLARE @j int
SELECT @j = 0
EXEC sp_OAMethod @changeTypes, 'StringAt', @sTmp0 OUT, @t
EXEC sp_OAMethod @record, 'SizeOfArray', @iTmp0 OUT, @sTmp0
WHILE (@j < @iTmp0)
BEGIN
EXEC sp_OAMethod @changeTypes, 'StringAt', @sTmp0 OUT, @t
EXEC sp_OAMethod @eventPath, 'SetString', @success OUT, @sTmp0
EXEC sp_OAMethod @eventPath, 'Append', @success OUT, '['
EXEC sp_OAMethod @eventPath, 'AppendInt', @success OUT, @j
EXEC sp_OAMethod @eventPath, 'Append', @success OUT, '].message.id'
EXEC sp_OAMethod @eventPath, 'GetAsString', @sTmp1 OUT
EXEC sp_OAMethod @record, 'StringOf', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @ids, 'Append', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ids, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
SELECT @j = @j + 1
END
SELECT @t = @t + 1
END
SELECT @h = @h + 1
END
SELECT @pageToken = ''
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'nextPageToken'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @pageToken OUT, 'nextPageToken'
END
SELECT @more = @pageToken <> ''
IF @more <> 1
BEGIN
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'historyId'
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp1 OUT, 'historyId', '', 1
IF (@iTmp0 <> 1) or (@iTmp1 = 1)
BEGIN
PRINT 'Expected historyId in the response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAMethod @reply, 'StringOf', @checkpoint OUT, 'historyId'
END
END
END
END
IF @fullSync = 1
BEGIN
-- Rebuild in memory. Any failure leaves the committed file available for a retry.
EXEC sp_OAMethod @ids, 'Clear', NULL
EXEC sp_OAMethod @state, 'Clear', NULL
EXEC sp_OAMethod @state, 'UpdateString', @success OUT, 'emailAddress', @mailbox
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAMethod @state, 'UpdateNewObject', @success OUT, 'messages'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
SELECT @checkpoint = @baseline
SELECT @pageToken = ''
SELECT @more = 1
WHILE (@more = 1)
BEGIN
EXEC sp_OAMethod @url, 'SetString', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages?maxResults=500&includeSpamTrash=true&pageToken='
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @pageToken
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
DECLARE @m int
SELECT @m = 0
EXEC sp_OAMethod @reply, 'SizeOfArray', @iTmp0 OUT, 'messages'
WHILE (@m < @iTmp0)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @m
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[i].id'
EXEC sp_OAMethod @ids, 'Append', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ids, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
RETURN
END
SELECT @m = @m + 1
END
SELECT @pageToken = ''
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'nextPageToken'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @pageToken OUT, 'nextPageToken'
END
SELECT @more = @pageToken <> ''
END
END
DECLARE @messages int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @messages OUT
EXEC sp_OAMethod @state, 'ObjectOf2', @success OUT, 'messages', @messages
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
RETURN
END
DECLARE @cachedMessage int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @cachedMessage OUT
DECLARE @i int
SELECT @i = 0
EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
WHILE (@i < @iTmp0)
BEGIN
DECLARE @messageId nvarchar(4000)
EXEC sp_OAMethod @ids, 'GetString', @messageId OUT, @i
EXEC sp_OAMethod @url, 'SetString', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages/'
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @messageId
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @url, 'Append', @success OUT, '?format=minimal&fields=id,threadId,labelIds'
EXEC sp_OAMethod @url, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @sTmp0, @resp
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 404
BEGIN
EXEC sp_OAMethod @messages, 'HasMember', @iTmp0 OUT, @messageId
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @messages, 'Delete', @success OUT, @messageId
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @messages, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
END
PRINT 'Removed from cache: ' + @messageId
END
ELSE
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
PRINT 'Gmail did not return an HTTP response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp1 OUT
IF (@success <> 1) or (@iTmp0 < 200) or (@iTmp1 >= 300)
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
PRINT 'Gmail HTTP ' + @iTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'error.message'
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @reply, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Expected a Gmail JSON response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAMethod @reply, 'StringOfEquals', @iTmp0 OUT, 'id', @messageId, 1
IF @iTmp0 <> 1
BEGIN
PRINT 'Unexpected message ID in the response.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAMethod @messages, 'UpdateNewObject', @success OUT, @messageId
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @messages, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAMethod @messages, 'ObjectOf2', @success OUT, @messageId, @cachedMessage
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @messages, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAMethod @reply, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @cachedMessage, 'Load', @success OUT, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @cachedMessage, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
PRINT 'Cached message: ' + @messageId
END
SELECT @i = @i + 1
END
EXEC sp_OAMethod @state, 'UpdateString', @success OUT, 'historyId', @checkpoint
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @state, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
-- Prepare the complete replacement before moving the last committed file.
EXEC sp_OAMethod @state, 'WriteFile', @success OUT, 'gmail-sync.json.new'
IF @success <> 1
BEGIN
PRINT 'Could not write the replacement file.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAMethod @files, 'FileExists', @iTmp0 OUT, 'gmail-sync.json'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @files, 'FileExists', @iTmp0 OUT, 'gmail-sync.json.previous'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @files, 'FileDelete', @success OUT, 'gmail-sync.json.previous'
IF @success <> 1
BEGIN
PRINT 'Could not rotate the previous backup.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
END
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-sync.json', 'gmail-sync.json.previous'
IF @success <> 1
BEGIN
PRINT 'Could not retain the previous complete file.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
END
EXEC sp_OAMethod @files, 'FileRename', @success OUT, 'gmail-sync.json.new', 'gmail-sync.json'
IF @success <> 1
BEGIN
PRINT 'Could not commit the replacement. The previous complete file is retained.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
RETURN
END
EXEC sp_OAGetProperty @messages, 'Size', @iTmp0 OUT
PRINT 'Synchronization committed. Cached messages: ' + @iTmp0
PRINT 'Saved history ID: ' + @checkpoint
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @files
EXEC @hr = sp_OADestroy @state
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @changeTypes
EXEC @hr = sp_OADestroy @record
EXEC @hr = sp_OADestroy @eventPath
EXEC @hr = sp_OADestroy @messages
EXEC @hr = sp_OADestroy @cachedMessage
END
GO