Messages - Fetch
Retrieve Gmail messages using SQL Server
- See SQLHTTP easy setup for Gmail
- See API Call documentation for parameter values and other information
- Create the stored procedure documented below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
CREATE PROCEDURE usp_Google_User_Messages_Fetch( @Profile nvarchar(100), @userId varchar(MAX), @includeSpamTrash bit = 0, @labelIds varchar(MAX) = NULL, @maxResults int = NULL, @pageToken nvarchar(MAX) = NULL, @q nvarchar(MAX) = NULL, @Response nvarchar(MAX) OUTPUT, @StatusCode int OUTPUT, @StatusDescription nvarchar(MAX) OUTPUT) AS DECLARE @Text_includeSpamTrash varchar(5) SET @Text_includeSpamTrash = CASE WHEN @includeSpamTrash = 0 THEN 'false' ELSE 'true' END DECLARE @URL nvarchar(MAX) DECLARE @QueryString nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier SET @URL = 'https://www.googleapis.com/gmail/v1' EXEC SQLHTTP.net.UrlBuilder @URL OUTPUT, @Profile, 'users', @userId, 'messages' EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'includeSpamTrash', @Text_includeSpamTrash, 'labelIds', @labelIds, 'maxResults', @maxResults, 'pageToken', @pageToken, 'q', @q SET @URL = @URL + @QueryString EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Google_Auth_Header @Profile, @HTTPSessionID EXEC SQLHTTP.net.HTTPRequest @HTTPSessionID, @URL = @URL, @Method = 'GET', @ContentType = 'application/json', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) DECLARE @Profile nvarchar(100) SET @Profile = 'My google' --Google API Bearer Token expires after an hour EXEC usp_Google_Auth_Refresh @Profile EXEC usp_Google_User_Messages_Fetch @Profile = @Profile, @userId = 'me', @maxResults = 20, @Response = @Response OUTPUT, @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN DECLARE @X xml SET @X = SQLHTTP.net.Json_To_Xml(@Response, '0') --The XPath syntax below was easily generated by executing the following commands: --EXEC SQLHTTP.net.XQueryHelper @X --and then executing: --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject/messages/JsonObject' -- --EXEC SQLHTTP.net.XQueryHelper @X, 'JsonObject' -- SELECT T.C.value(N'@id', N'nvarchar(MAX)') AS [id] ,T.C.value(N'@threadId', N'nvarchar(MAX)') AS [threadId] FROM @X.nodes(N'/JsonObject/messages/JsonObject') T(C) --Set @pageToken to @nextPageToken) --and continue calling the above stored procedure to load additional pages DECLARE @nextPageToken nvarchar(MAX) SELECT @nextPageToken = T.C.value(N'@nextPageToken', N'nvarchar(MAX)') FROM @X.nodes(N'/JsonObject') T(C) END |
|
1 2 3 4 5 6 7 8 9 |
id threadId ------------------ ----------------- 161da0bcbe50d63f 161d8edeeaf5fc29 161d9f91733daeca 161d9f91733daeca 161d8fae19ea4be6 161d8fae19ea4be6 161d8ee3e9ea9269 161d8ee3e9ea9269 161d8edeeaf5fc29 161d8edeeaf5fc29 |
- Draft – Delete
- Draft – Fetch
- Draft – Send
- Drafts – Fetch
- Filter – Create
- Filter – Delete
- Filter – Fetch
- Filters – Fetch
- Forwarding Address – Fetch
- Forwarding Addresses – Fetch
- Gmail Notifications – Stop
- History – Fetch
- Imap Settings – Fetch
- Imap Settings – Update
- Label – Create
- Label – Delete
- Label – Fetch
- Label – Update
- Labels – Fetch
- Message – Fetch
- Message – Trash
- Message – Untrash
- Message – Update
- Messages – Delete
- Messages – Fetch
- Messages Labels – Batch Update
- Pop Settings – Fetch
- Pop Settings – Update
- Thread – Delete
- Thread – Fetch
- Thread – Trash
- Thread – Untrash
- Thread – Update
- Threads – Fetch
- User Profile – Fetch
- Vacation Responder Settings – Fetch
- Vacation Responder Settings – Update
IMPORTANT DISCLAIMER
CODE/SQL ON THESE PAGES ARE PROVIDED AS-IS AND ARE AVAILABLE FOR ILLUSTRATIVE PURPOSES ONLY.
USERS ARE REQUIRED TO ABIDE BY THE TERMS AND CONDITIONS FOR USING REFERENCED THIRD PARTY WEBSITES AND/OR APIs FROM THEIR RESPECTIVE WEBSITES. WE DO NOT CONDONE ANY VIOLATION OF THIRD PARTY WEBSITES AND/OR APIs TERMS AND CONDITIONS USING OUR SOFTWARE.
USERS SHALL BE SOLELY RESPONSIBLE AND BE SOLELY LIABLE FOR VIOLATION OF ANY RULES SPECIFIED BY THIRD PARTIES FOR USING THEIR WEBSITES AND/OR APIs, OR INFRINGEMENT OF RIGHTS OF SUCH THIRD PARTIES.