Gmail IMAP: Search with Gmail Search Syntax
See more GMail SMTP/IMAP/POP Examples
Search with Gmail operators through X-GM-RAW, using QueryMbx to obtain UIDs. The example discovers All Mail and opens it read-only. It quotes the query for the IMAP command rather than URL-encoding it.
IMAP search is limited to the selected mailbox. All Mail normally covers inbox and archived messages, but excludes Spam and Trash; a Gmail search operator cannot expand an IMAP search beyond its selected mailbox. An empty result is successful. Returned IMAP UIDs are mailbox-specific and are not Gmail message IDs. Persist them only with their mailbox and UIDVALIDITY.
Use the mailbox that authorized the full-mail scope https://mail.google.com/. Obtain the protocol OAuth token and refresh it before running this example. The token is loaded from gmail-mail-token.json in a private working directory; a narrow Gmail REST token is insufficient.
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 @success int
SELECT @success = 0
DECLARE @replacements int
-- 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-mail-token.json'
IF @success <> 1
BEGIN
PRINT 'Could not load the protocol token 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 'Authorize or refresh the mailbox first.'
EXEC @hr = sp_OADestroy @token
RETURN
END
DECLARE @mailbox nvarchar(4000)
SELECT @mailbox = 'user@example.com'
DECLARE @imap int
EXEC @hr = sp_OACreate 'Chilkat.Imap', @imap OUT
EXEC sp_OASetProperty @imap, 'Ssl', 1
EXEC sp_OASetProperty @imap, 'Port', 993
EXEC sp_OASetProperty @imap, 'RequireSslCertVerify', 1
EXEC sp_OASetProperty @imap, 'AuthMethod', 'XOAUTH2'
EXEC sp_OAMethod @imap, 'Connect', @success OUT, 'imap.gmail.com'
IF @success <> 1
BEGIN
PRINT 'Could not establish the Gmail IMAP TLS connection.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
EXEC sp_OAMethod @token, 'StringOf', @sTmp0 OUT, 'access_token'
EXEC sp_OAMethod @imap, 'Login', @success OUT, @mailbox, @sTmp0
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'IMAP authentication failed. Check the mailbox, scope, expiry, and account policy.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
DECLARE @capabilities nvarchar(4000)
EXEC sp_OAMethod @imap, 'Capability', @capabilities OUT
EXEC sp_OAGetProperty @imap, 'LastMethodSuccess', @iTmp0 OUT
EXEC sp_OAMethod @imap, 'HasCapability', @iTmp1 OUT, 'X-GM-EXT-1', @capabilities
IF (@iTmp0 <> 1) or (@iTmp1 <> 1)
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Gmail IMAP extensions are unavailable.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
RETURN
END
DECLARE @folders int
EXEC @hr = sp_OACreate 'Chilkat.Mailboxes', @folders OUT
EXEC sp_OAMethod @imap, 'MbxList', @success OUT, 0, '', '*', @folders
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Could not list Gmail folders.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
RETURN
END
DECLARE @allMail nvarchar(4000)
SELECT @allMail = ''
DECLARE @folderMatches int
SELECT @folderMatches = 0
DECLARE @f int
SELECT @f = 0
EXEC sp_OAGetProperty @folders, 'Count', @iTmp0 OUT
WHILE (@f < @iTmp0)
BEGIN
EXEC sp_OAMethod @folders, 'HasFlag', @iTmp0 OUT, @f, '\All'
EXEC sp_OAMethod @folders, 'IsSelectable', @iTmp1 OUT, @f
IF (@iTmp0 = 1) and (@iTmp1 = 1)
BEGIN
EXEC sp_OAMethod @folders, 'GetName', @allMail OUT, @f
SELECT @folderMatches = @folderMatches + 1
END
SELECT @f = @f + 1
END
IF @folderMatches <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Expected one selectable folder with the \All special-use attribute. Check label visibility.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
RETURN
END
EXEC sp_OAMethod @imap, 'ExamineMailbox', @success OUT, @allMail
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Could not open All Mail read-only.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
RETURN
END
-- This search uses Gmail syntax inside a quoted IMAP search argument.
DECLARE @query nvarchar(4000)
SELECT @query = 'has:attachment from:sender@example.net'
DECLARE @quoted int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @quoted OUT
EXEC sp_OAMethod @quoted, 'Append', @success OUT, @query
EXEC sp_OAMethod @quoted, 'Contains', @iTmp0 OUT, CHAR(13), 1
EXEC sp_OAMethod @quoted, 'Contains', @iTmp1 OUT, CHAR(10), 1
IF (@iTmp0 = 1) or (@iTmp1 = 1)
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'A search query must not contain CR or LF.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @quoted
RETURN
END
EXEC sp_OAMethod @quoted, 'Replace', @replacements OUT, '\', '\\'
EXEC sp_OAMethod @quoted, 'Replace', @replacements OUT, '"', '\"'
DECLARE @joined1 int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @joined1 OUT
EXEC sp_OAMethod @joined1, 'Append', @success OUT, 'X-GM-RAW "'
EXEC sp_OAMethod @quoted, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @joined1, 'Append', @success OUT, @sTmp0
EXEC sp_OAMethod @joined1, 'Append', @success OUT, '"'
DECLARE @criteria nvarchar(4000)
EXEC sp_OAMethod @joined1, 'GetAsString', @criteria OUT
DECLARE @found int
EXEC @hr = sp_OACreate 'Chilkat.MessageSet', @found OUT
EXEC sp_OAMethod @imap, 'QueryMbx', @success OUT, @criteria, 1, @found
IF @success <> 1
BEGIN
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
PRINT 'Gmail search failed.'
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @quoted
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @found
RETURN
END
-- Preserve UIDs as text. They belong to this mailbox and its UIDVALIDITY.
EXEC sp_OAGetProperty @found, 'Count', @iTmp0 OUT
PRINT 'Matching messages: ' + @iTmp0
EXEC sp_OAMethod @found, 'ToCompactString', @sTmp0 OUT
PRINT 'UID set: ' + @sTmp0
EXEC sp_OAMethod @imap, 'Logout', @success OUT
EXEC sp_OAMethod @imap, 'Disconnect', @success OUT
EXEC @hr = sp_OADestroy @token
EXEC @hr = sp_OADestroy @imap
EXEC @hr = sp_OADestroy @folders
EXEC @hr = sp_OADestroy @quoted
EXEC @hr = sp_OADestroy @joined1
EXEC @hr = sp_OADestroy @found
END
GO