MailMessages
Create, update, delete, and query Gmail messages.
NOTE: This schema is defined for all tables. Each table name is a mailbox folder on the server.
NOTE:: If the mail contains special characters, the content in the message body is base64 encoded. To retrieve the decoded message, you can either decode the content part of the message body or retrieve it using the GetPart stored procedure.
Table Specific Information
Email address fields contained in this table accept the following formats:
- 'Friendly Name' <[email protected]>
- [email protected]
SELECT * FROM [Gmail/All Mail]
Select
The results of a query to this table conform to the defaults below:- The MessageBody and Headers fields are not returned when listing multiple messages.
- The default number of messages returned is 100. You can change this by setting either LIMIT or MaxItems. If you want to return all mail within a mailbox, specify a value of 0 or -1.
List Messages
To list messages within a specific mailbox, specify the mailbox as the table. The emails are listed from most recent to oldest. Alternatively, you can specify a range of message Ids. The available formats for ranges are below:
- Return only the message with an Id of 10:
SELECT * FROM [Inbox] WHERE Id = 10
- Return all messages from 10 to 20:
SELECT * FROM [Inbox] WHERE Id BETWEEN 10 AND 20
- Return messages 1, 3, 5
SELECT * FROM [Inbox] WHERE Id IN (1, 3, 5)
Apply Search Criteria
The connector offers several possibilities for searching through email. You can use search criteria from the following sources in the WHERE clause of a SELECT query:
- Message fields: To search through mail, specify a value for any of the following columns in the WHERE clause of a SELECT query: To, From, BCC, CC, Subject, MessageBody, Flags, Labels, Size, or Date.
Complex statements, nested with parentheses, are supported.
Filter To, From, BCC, CC, Subject, and MessageBody with the following operators: =, !=, and CONTAINS.
Flags and Labels support the = and != operators only.
Filter Date with the following operators: =, <, <=, >, and >=.
Size supports the < and > operators.
Note that the CONTAINS function requires full words (For example Test, not Tes), similar to the search bar from the UI. For example, the query below returns all messages from either [email protected] or [email protected] that contain the text 'Test':
SELECT * FROM [Gmail/All Mail] WHERE CONTAINS(MessageBody, "Test") AND ([From] = [email protected] OR [From] = [email protected]) AND Date > '1-1-2012'
- Attachments: In addition to searching by standard columns, you can also filter by attachment file names and by whether an email has an attachment:
SELECT * FROM [Gmail/All Mail] WHERE HasAttachment = 'True' AND Attachments = '.txt'
- Gmail-specific: You can use ThreadId and MsgId for filtering with the = and != operators. You can also use the IN operator for the MsgId columns (the operator is translated internally to multiple statements with the = operator).
- IMAP: As an alternative to the above method of creating search criteria, you can use the SearchCriteria pseudo column to search using any valid IMAP search criteria as specified by RFC.
Insert
Insert statement is used to send mail. The Subject, To, and MessageBody fields are required:
INSERT INTO [Gmail/All Mail] (Subject, MessageBody, To) VALUES ('Test Subject', 'Body Text', '[email protected]')
Update
- To move an email from one mailbox to another, specify the mailbox in the update:
UPDATE [Inbox] SET Mailbox = 'NewMailboxName' WHERE Id = 'MessageId'
The Id may consist of a single message number, a range of messages specified by two message numbers separated by ':' (e.g., "1:5"), or individual message numbers separated by ',' (e.g., "1:5,7,10"). - When moving an email from one mailbox to another, you may not specify any additional updates.
- To update the flags, specify one or a list of flags in the update:
UPDATE [Inbox] SET Flags = 'Seen' WHERE Id = 'MessageId'
The standard values of flags as specified by the IMAP RFCs are : 'Answered', 'Flagged', 'Deleted', 'Seen' and 'Draft'.
The flags specified in query override all the email's current flags.
Delete
When you delete messages from Gmail through IMAP, they are not removed from the server. Instead, Gmail removes the current inbox label and marks the mail as "archived." If you want to permanently delete a message from all folders:
- Move it to the [Gmail]/Trash.
- Delete it from the [Gmail]/Trash folder.
The Id field is required:
DELETE FROM [Inbox] WHERE Id = '2'
Additional Notes
- By default, the max number of messages returned is 100. You can change this by setting either LIMIT or MaxItems. If you want to return all emails in a mailbox, you must specify a value of 0 or -1.
- By default, the number of messages returned per page is 25. To change this, you can set the ItemsPerPage pseudo column.
- All message Ids returned are temporary Ids and may change in subsequent requests to the server.
Columns
| Name | Type | ReadOnly | References | Description |
| Id [KEY] | String | True |
The identifier of the mail message. | |
| Mailbox | String | False |
The current mailbox that the item is listed in. Updating this value moves the message to a new mailbox. | |
| Subject | String | False |
The subject of the current message. | |
| FullFrom | String | True |
The sender name and email address of the current message. | |
| From | String | True |
The sender email address of the current message. | |
| FullTo | String | True |
A semicolon-separated list of names and email addresses of the recipients. | |
| To | String | False |
A semicolon-separated list of the email addresses of the recipients. | |
| FullCC | String | True |
A semicolon-separated list of names and email addresses of the CC'd recipients. | |
| CC | String | False |
A semicolon-separated list of the email addresses of the CC'd recipients. | |
| FullBCC | String | True |
A semicolon-separated list of the names and email addresses of the BCC'd recipients. This field is only applicable for sent mail. | |
| BCC | String | False |
A semicolon-separated list of the email addresses of the BCC'd recipients. This field is only applicable for sent mail. | |
| Date | Datetime | True |
The date and time the current message was sent. | |
| MessageBody | String | False |
The message body. This field can only be set if you specify a single Id in SELECT. | |
| Attachments | String | False |
A semicolon-separated list of the attachment file names (with the path specified if reading from a file) included in the message. | |
| AttachmentData | String | False |
A semicolon-separated list of the base-64-encoded attachment data included in the message. The file name in the Attachments field must also be specified to use this field. | |
| Size | Int | True |
The size in bytes of the current message. | |
| Flags | String | False |
The flags set on the current message. | |
| Labels | String | False |
The labels of the message, separated by spaces. Gmail treats labels as mailboxes. | |
| ThreadId | String | True |
The Gmail thread Id associated with the current message. Several messages may have the same thread Id. | |
| MsgId | String | True |
The Gmail message Id associated with the current message. This is a globally unique Id assigned by Google separate from the IMAP Id/UID. | |
| PartIds | String | True |
The Id of the message part. | |
| PartFilenames | String | True |
The file name of the message part. | |
| PartContentTypes | String | True |
The content type of the message part. | |
| PartSizes | String | True |
The size of the message part. | |
| Headers | String | True |
The message headers for the specified mail message. This field is only available if you specify a single Id in SELECT or set IncludeMessage connection property to true. | |
| AttachmentHeaders | String | True |
The attachment headers for the specified mail message. This field is only available if you specify a single Id in SELECT or set IncludeMessage connection property to true. | |
| ContentIds | String | True |
The content id headers for the specified mail message. This field is only available if you specify a single Id in SELECT or set IncludeMessage connection property to true. |
Pseudo-Columns
Pseudo-column fields are used in the WHERE clause of SELECT statements and offer a more granular control over the tuples that are returned from the data source.
| Name | Type | Description |
| SearchCriteria | String |
The search criteria to use for the search operation (i.e., SENTSINCE dd-MMM-yyyy). The IMAP RFCs specifies the exact format of the search criteria. The string consists of one or more search keys and their corresponding values, if any, separated by spaces. For example: 'SINCE 1-Feb-1994 NOT FROM Smith'. |
| ItemsPerPage | String |
The maximum number of items to return per page. The default value is 25. |
| IsHTML | String |
This field sets whether the email has an HTML or plain-text encoding. The default value is TRUE. |
| Importance | String |
The importance of the mail message. The allowed values are UNSPECIFIED, LOW, NORMAL, HIGH. The default value is UNSPECIFIED. |
| Priority | String |
The priority of the mail message. The allowed values are UNSPECIFIED, NONURGENT, URGENT, NORMAL. The default value is UNSPECIFIED. |
| Sensitivity | String |
The sensitivity of the mail message. The allowed values are UNSPECIFIED, PERSONAL, PRIVATE, COMPANYCONFIDENTIAL. The default value is UNSPECIFIED. |
| DeliveryNotification | String |
The email address to send a delivery notification to. |
| ReadReceipt | String |
The email address to send a read receipt to. |
| ShowLabels | String |
This field sets whether to show labels or not. The default value is TRUE. |
| ShowThreadId | String |
This field sets whether to show the thread Id or not. The default value is TRUE. |
| ShowMsgId | String |
This field sets whether to show the unique Gmail Id or not. The default value is TRUE. |
| UIDMode | String |
If this value is set to true, permanent message Ids are used instead of the default temporary Ids. The default value is FALSE. |
| HasAttachments | String |
If this value is set to true, only messages with attachments are listed. If false, all messages are listed. |
| DownloadAttachments | String |
If true, attachments are downloaded with the messages. Available only when specifying a single message Id or the Include Messages connection property is true. By default organizes attachments in subfolders with the message Id as name. The default value is FALSE. |
| AttachmentFolder | String |
The folder that is used for downloading attachments. If this is set, the AttachmentData column does not return any data (that is, returns null) and the attachments are instead be written to disk. NOTE: An error is thrown if filters that cannot be supported server-side are included in the criteria. Please refer to the 'Apply Search Criteria' section for a list of supported filters. |
| EMLFolder | String |
The folder where the complete MIME message, which is saved as an EML file, is downloaded to. If multiple messages are selected, subfolders are created for each message. |
| Overwrite | String |
This field sets whether to overwrite attachments and EML files. The default value is FALSE. |
| IncludeMessage | String |
This field sets whether to include the message content in the response. The default value is FALSE. |