posts - 88 , comments - 3 , trackbacks - 0

Using SQL to Query Gmail

The Gmail Data Provider makes it easy to search email from a Gmail account. Instead of learning the details of the IMAP search command, you can simply use the simple SQL syntax. However, the full IMAP search specification is supported. You can also search based on multiple criteria at the same time. This article will demonstrate how to use SQL to execute simple and advanced IMAP searches.

  • Message Fields

    For simple searches, such as searching for all emails from a particular person, you can use message fields in the WHERE clause. The following columns can be used in the WHERE clause: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, or Date. Below are some examples of the simple syntax used by the Gmail Data Provider:

    To search for all emails from Twitter in the Inbox from a certain date, you can use the query:

    SELECT * FROM Inbox
    WHERE (FROM='Twitter' AND Date > '11-25-2012')

    To find all emails with subject 'CData Data Provider':

    SELECT * FROM Inbox
    WHERE (Subject = 'CData ADO.NET Data Provider')

    To find all sent emails between September 23rd, 2011 and September 23rd, 2012:

    SELECT * FROM 'Gmail/Sent Mail'
    WHERE (Date > '9-23-2011' AND Date < '9-23-2012')

    To find all the mail messages in the Inbox from either Twitter or anyone with 'Microsoft' in the name:

    SELECT * FROM Inbox
    WHERE (From='Twitter' OR From LIKE '%Microsoft%')

    To find all unseen emails since October 1st, 2012 and everything from Twitter:

    SELECT * FROM Inbox
    WHERE (Flags LIKE '%UNSEEN%' AND Date > '10-1-2012' OR From='Twitter')

    Attachments

    You can also search by whether the email has an attachment and by the attachment file name.

    To find all the mail messages in the Inbox from that have attachments with the full file name including '.java':

    SELECT * FROM Inbox
    WHERE (HasAttachments='TRUE' AND Attachments='.java')

    Limit and Count

    You can also use the LIMIT and COUNT statements in SELECT queries:

    To find the first 30 mail messages in the Inbox with Cc of "Microsoft" and Bcc of "Twitter" without any attachments:

    SELECT * FROM Inbox
    WHERE (CC='Microsoft' AND BCC ='Twitter' AND HasAttachments='FALSE')
    LIMIT 30

    To find the number of messages in the Inbox:

    SELECT COUNT(Id) FROM Inbox

    Advanced IMAP Searches

    The Data Provider enables you to use any of the search terms in the syntax defined by the IMAP Search command. To search directly using the IMAP Search command, use the Search Criteria pseudo column. To directly use IMAP to find all the mail messages in the Inbox that haven't been seen and are flagged as important that were delivered before October 1st, 2012, use:

    SELECT * FROM Inbox
    WHERE (SearchCriteria='UNSEEN FLAGGED BEFORE 1-Oct-2012')
  • Note: The SearchCriteria takes the search terms in the syntax defined by the IMAP Search command.
  • As you have seen, any query you want to run on your Gmail account is possible with any of the CData Google Data Tools. CData offers Google integration as an ADO.NET Provider, JDBC Driver, SSIS Task, and an Excel Add-In.

Print | posted on Thursday, December 20, 2012 1:56 AM | Filed Under [ ado.net google cdata jdbc gmail data ssis excel add-in sql ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: