SQL Server Requires Chilkat v11.0.0+
SQL Server
Apply a Gmail label to search results in batches
See more GMail REST API Examples
Choose a Gmail search query and an existing label name. Resolve the label ID, collect all matching message IDs before changing any labels, then submit at most 1,000 IDs per batchModify call. Empty searches send no updates. For large mailboxes, spool IDs to persistent storage. Completed batches stay applied if a later request fails; adding the same label again is safe.
Authorize the application with https://www.googleapis.com/auth/gmail.modify. 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 @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
EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', 'https://gmail.googleapis.com/gmail/v1/users/me/labels', @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
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
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
RETURN
END
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'labels'
IF @iTmp0 <> 4
BEGIN
PRINT 'Expected a list of labels.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
RETURN
END
DECLARE @label int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @label OUT
EXEC sp_OAMethod @reply, 'FindRecord2', @success OUT, 'labels', 'name', 'To review', 1, @label
IF @success <> 1
BEGIN
PRINT 'No label with that exact name. Check the name or create the label first.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @label
RETURN
END
DECLARE @labelId int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @labelId OUT
EXEC sp_OAMethod @label, 'StringOfSb', @success OUT, 'id', @labelId
IF @success <> 1
BEGIN
PRINT 'Could not read the label ID.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @label
EXEC @hr = sp_OADestroy @labelId
RETURN
END
DECLARE @ids int
EXEC @hr = sp_OACreate 'Chilkat.StringTable', @ids OUT
DECLARE @baseUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @baseUrl OUT
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, 'https://gmail.googleapis.com/gmail/v1/users/me/messages?maxResults=500'
DECLARE @searchQuery nvarchar(4000)
SELECT @searchQuery = 'has:attachment'
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, '&q='
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @searchQuery
EXEC sp_OAMethod @baseUrl, 'Append', @success OUT, @sTmp0
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @url OUT
DECLARE @pageToken int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @pageToken OUT
DECLARE @more int
SELECT @more = 1
WHILE (@more = 1)
BEGIN
EXEC sp_OAMethod @baseUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @url, 'SetString', @success OUT, @sTmp0
EXEC sp_OAGetProperty @pageToken, 'Length', @iTmp0 OUT
IF @iTmp0 > 0
BEGIN
EXEC sp_OAMethod @url, 'Append', @success OUT, '&pageToken='
EXEC sp_OAMethod @pageToken, 'GetAsString', @sTmp1 OUT
EXEC sp_OAMethod @http, 'UrlEncode', @sTmp0 OUT, @sTmp1
EXEC sp_OAMethod @url, 'Append', @success OUT, @sTmp0
END
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 @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
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 @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
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 @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
RETURN
END
-- Gmail can omit messages when the search or mailbox is empty.
DECLARE @count int
EXEC sp_OAMethod @reply, 'SizeOfArray', @count OUT, 'messages'
DECLARE @i int
SELECT @i = 0
WHILE (@i < @count)
BEGIN
EXEC sp_OASetProperty @reply, 'I', @i
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'messages[i].id'
EXEC sp_OAMethod @ids, 'Append', @success OUT, @sTmp0
IF @success <> 1
BEGIN
PRINT 'Could not retain a message ID.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
RETURN
END
SELECT @i = @i + 1
END
SELECT @more = 0
EXEC sp_OAMethod @reply, 'JsonTypeOf', @iTmp0 OUT, 'nextPageToken'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @reply, 'StringOf', @sTmp0 OUT, 'nextPageToken'
EXEC sp_OAMethod @pageToken, 'SetString', @success OUT, @sTmp0
EXEC sp_OAGetProperty @pageToken, 'Length', @iTmp0 OUT
SELECT @more = @iTmp0 > 0
END
END
-- Search is complete. Apply the label to each group and track confirmed progress.
DECLARE @request int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @request OUT
DECLARE @done int
SELECT @done = 0
EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
WHILE (@done < @iTmp0)
BEGIN
EXEC sp_OAMethod @request, 'Clear', NULL
EXEC sp_OAMethod @labelId, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @request, 'UpdateString', @success OUT, 'addLabelIds[0]', @sTmp0
DECLARE @n int
SELECT @n = 0
EXEC sp_OAGetProperty @ids, 'Count', @iTmp0 OUT
WHILE ((@n < 1000) and ((@done + @n) < @iTmp0))
BEGIN
EXEC sp_OASetProperty @request, 'I', @n
EXEC sp_OAMethod @ids, 'StringAt', @sTmp0 OUT, @done + @n
EXEC sp_OAMethod @request, 'UpdateString', @success OUT, 'ids[i]', @sTmp0
SELECT @n = @n + 1
END
EXEC sp_OAMethod @http, 'HttpJson', @success OUT, 'POST', 'https://gmail.googleapis.com/gmail/v1/users/me/messages/batchModify', @request, 'application/json', @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 @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
EXEC @hr = sp_OADestroy @request
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 @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
EXEC @hr = sp_OADestroy @request
RETURN
END
SELECT @done = @done + @n
PRINT 'Messages labeled so far: ' + @done
END
PRINT 'Total messages labeled: ' + @done
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @reply
EXEC @hr = sp_OADestroy @label
EXEC @hr = sp_OADestroy @labelId
EXEC @hr = sp_OADestroy @ids
EXEC @hr = sp_OADestroy @baseUrl
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @pageToken
EXEC @hr = sp_OADestroy @request
END
GO