Sample code for 30+ languages & platforms
SQL Server Requires Chilkat v11.1.0+

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.

Google documentation

Chilkat SQL Server Downloads

SQL Server
-- 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