Gmail (Google)

Interact with the Gmail API using SQL Server

Install the SQLHTTP database on your SQL Server
Select and enable a Google API
Proceed to select or create a new project and a new application
Add new “OAuth2 Client ID” Web application credentials and set the “Authorized redirect URIs” to http://localhost:53200 (or another port)
Record your Client ID and Client Secret.
Create the stored procedures documented below
Determine which Permissions Scope will be needed. Permissions Scopes are incremental
Execute the following SQL statement:


EXEC usp_Google_Auth_Init
		@Profile = 'enter-profile-name-of-your-choosing',
		@ClientID = 'enter-your-client-id',
		@ClientSecret = 'enter-your-client-secret',
		@Scope = 'https://mail.google.com/',
		@RedirectURL = 'enter-redirecturl' --example: http://localhost:53200

EXEC usp_Google_Auth_Init

@Profile = 'enter-profile-name-of-your-choosing',

@ClientID = 'enter-your-client-id',

@ClientSecret = 'enter-your-client-secret',

@Scope = 'https://mail.google.com/',

@RedirectURL = 'enter-redirecturl' --example: http://localhost:53200



Stored Procedures:


CREATE PROCEDURE usp_Google_Auth_Init(	@Profile nvarchar(100),
					@ClientID varchar(500),
					@ClientSecret varchar(50),
					@Scope varchar(MAX),
					@RedirectURL varchar(100))
AS

DECLARE @UserBrowseToURL varchar(MAX)
DECLARE @QueryString nvarchar(MAX)

SET @UserBrowseToURL = 'https://accounts.google.com/o/oauth2/v2/auth'

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientID', @Value = @ClientID
EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret
EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RedirectURL', @Value = @RedirectURL

EXEC SQLHTTP.net.QueryStringBuilder     @QueryString OUTPUT,
					@Profile,
					'scope', @Scope,
					'prompt', 'consent',
					'access_type', 'offline',
					'include_granted_scopes', 'true',
					'response_type', 'code',
					'client_id', @ClientID
					
--parameter that should not be encoded
SET @QueryString = @QueryString
				+ '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS
				+ @RedirectURL 

SET @UserBrowseToURL = @UserBrowseToURL + @QueryString

DECLARE @URL nvarchar(MAX)
DECLARE @Body nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier
DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)
DECLARE @RedirectURLCode varchar(100)

DECLARE @Timeout int
DECLARE @TimeoutReached bit = 0

SET @Timeout = 180 --three minutes wait

EXEC SQLHTTP.net.AuthListener @UserBrowseToURL = @UserBrowseToURL,
				@RedirectURL = @RedirectURL,
				@Timeout = @Timeout,
				@QueryString = @QueryString OUTPUT, 
				@TimeoutReached = @TimeoutReached OUTPUT

IF @TimeoutReached = 1
	BEGIN
		RAISERROR('Timeout reached waiting for browser authentication', 16, 1)
		RETURN
	END

SET @RedirectURLCode = SQLHTTP.net.Split(@QueryString, 'code=', 2)
SET @RedirectURLCode = SQLHTTP.net.Split(@RedirectURLCode, '&', 1)

SET @URL = 'https://www.googleapis.com/oauth2/v4/token'

EXEC SQLHTTP.net.FormDataBuilder        @Body OUTPUT,
					@Profile,
					'code', @RedirectURLCode,
					'client_id', @ClientID,
					'client_secret', @ClientSecret,
					'grant_type', 'authorization_code'

--parameter that should not be encoded
SET @Body = @Body
				+ '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS
				+ @RedirectURL 
					
EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest 	@HttpSessionID,
				@URL = @URL,
				@Method = 'POST',
				@Body = @Body,
				@StatusCode = @StatusCode OUTPUT,
				@StatusDescription = @StatusDescription OUTPUT,
				@Response = @Response OUTPUT

IF @StatusCode >= 400
	EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
ELSE
	BEGIN
		DECLARE @BearerToken varchar(MAX)
		DECLARE @RefreshToken varchar(MAX)

		SELECT @BearerToken = [value]
		FROM SQLHTTP.net.Json_To_NodeTable(@Response)
		WHERE [Name] = 'access_token'

		SELECT @RefreshToken = [value]
		FROM SQLHTTP.net.Json_To_NodeTable(@Response)
		WHERE [Name] = 'refresh_token'

		EXEC SQLHTTP.net.AuthParamSet	@Profile = @Profile, 
						@Name = 'BearerToken', 
						@value = @BearerToken

		EXEC SQLHTTP.net.AuthParamSet	@Profile = @Profile, 
						@Name = 'RefreshToken', 
						@Value = @RefreshToken

		DECLARE @TokenCreatedDateTime nvarchar(100)
		SET @TokenCreatedDateTime = GetDate()

		EXEC SQLHTTP.net.AuthParamSet	@Profile = @Profile, 
						@Name = 'TokenCreatedDateTime', 
						@Value = @TokenCreatedDateTime

	END
GO

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

CREATE PROCEDURE usp_Google_Auth_Init( @Profile nvarchar(100),

@ClientID varchar(500),

@ClientSecret varchar(50),

@Scope varchar(MAX),

@RedirectURL varchar(100))

DECLARE @UserBrowseToURL varchar(MAX)

DECLARE @QueryString nvarchar(MAX)

SET @UserBrowseToURL = 'https://accounts.google.com/o/oauth2/v2/auth'

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientID', @Value = @ClientID

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ClientSecret', @Value = @ClientSecret

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RedirectURL', @Value = @RedirectURL

EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT,

@Profile,

'scope', @Scope,

'prompt', 'consent',

'access_type', 'offline',

'include_granted_scopes', 'true',

'response_type', 'code',

'client_id', @ClientID

--parameter that should not be encoded

SET @QueryString = @QueryString

+ '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS

+ @RedirectURL

SET @UserBrowseToURL = @UserBrowseToURL + @QueryString

DECLARE @URL nvarchar(MAX)

DECLARE @Body nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

DECLARE @RedirectURLCode varchar(100)

DECLARE @Timeout int

DECLARE @TimeoutReached bit = 0

SET @Timeout = 180 --three minutes wait

EXEC SQLHTTP.net.AuthListener @UserBrowseToURL = @UserBrowseToURL,

@RedirectURL = @RedirectURL,

@Timeout = @Timeout,

@QueryString = @QueryString OUTPUT,

@TimeoutReached = @TimeoutReached OUTPUT

IF @TimeoutReached = 1

BEGIN

RAISERROR('Timeout reached waiting for browser authentication', 16, 1)

RETURN

END

SET @RedirectURLCode = SQLHTTP.net.Split(@QueryString, 'code=', 2)

SET @RedirectURLCode = SQLHTTP.net.Split(@RedirectURLCode, '&', 1)

SET @URL = 'https://www.googleapis.com/oauth2/v4/token'

EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT,

@Profile,

'code', @RedirectURLCode,

'client_id', @ClientID,

'client_secret', @ClientSecret,

'grant_type', 'authorization_code'

--parameter that should not be encoded

SET @Body = @Body

+ '&redirect_uri=' COLLATE SQL_Latin1_General_CP1_CI_AS

+ @RedirectURL

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'POST',

@Body = @Body,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @BearerToken varchar(MAX)

DECLARE @RefreshToken varchar(MAX)

SELECT @BearerToken = [value]

FROM SQLHTTP.net.Json_To_NodeTable(@Response)

WHERE [Name] = 'access_token'

SELECT @RefreshToken = [value]

FROM SQLHTTP.net.Json_To_NodeTable(@Response)

WHERE [Name] = 'refresh_token'

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile,

@Name = 'BearerToken',

@value = @BearerToken

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile,

@Name = 'RefreshToken',

@Value = @RefreshToken

DECLARE @TokenCreatedDateTime nvarchar(100)

SET @TokenCreatedDateTime = GetDate()

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile,

@Name = 'TokenCreatedDateTime',

@Value = @TokenCreatedDateTime

END


CREATE PROCEDURE usp_Google_Auth_Header( @Profile nvarchar(100),
					@HttpSessionID uniqueidentifier)
AS

DECLARE @AuthorizationHeaderValue varchar(MAX)
SET @AuthorizationHeaderValue = 'Bearer ' COLLATE SQL_Latin1_General_CP1_CI_AS
				+ SQLHTTP.net.AuthParam(@Profile, 'BearerToken')

EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue
GO

CREATE PROCEDURE usp_Google_Auth_Header( @Profile nvarchar(100),

@HttpSessionID uniqueidentifier)

DECLARE @AuthorizationHeaderValue varchar(MAX)

SET @AuthorizationHeaderValue = 'Bearer ' COLLATE SQL_Latin1_General_CP1_CI_AS

+ SQLHTTP.net.AuthParam(@Profile, 'BearerToken')

EXEC SQLHTTP.net.RequestHeaderSet @HTTPSessionID, 'Authorization', @AuthorizationHeaderValue


CREATE PROCEDURE usp_Google_Auth_Refresh(@Profile nvarchar(100))
AS

DECLARE @URL nvarchar(MAX)
DECLARE @HTTPSessionID uniqueidentifier
DECLARE @Body nvarchar(MAX)
DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)
DECLARE @BearerTokenExpiration smalldatetime

SET @BearerTokenExpiration = SQLHTTP.net.AuthParam(@Profile, 'BearerTokenExpiration')

IF ISNULL(@BearerTokenExpiration, '1/1/2050') > DATEADD(minute, -10, GetDate())
	BEGIN
		SET @URL = 'https://www.googleapis.com/oauth2/v4/token'

		EXEC SQLHTTP.net.FormDataBuilder        @Body OUTPUT,
							@Profile,
							'grant_type', 'refresh_token',
							'refresh_token', '#RefreshToken',
							'client_id', '#ClientID',
							'client_secret', '#ClientSecret'

		EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

		EXEC SQLHTTP.net.HTTPRequest	@HttpSessionID,
						@URL = @URL,
						@Method = 'POST',
						@Body = @Body,
						@StatusCode = @StatusCode OUTPUT,
						@StatusDescription = @StatusDescription OUTPUT,
						@Response = @Response OUTPUT

		IF @StatusCode >= 400
			EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response
		ELSE
			BEGIN
				
				DECLARE @BearerToken varchar(MAX)
				DECLARE @ExpiresIn int
		
				SELECT @BearerToken = [value]
				FROM SQLHTTP.net.Json_To_NodeTable(@Response)
				WHERE [Name] = 'access_token'

				SELECT @ExpiresIn = CONVERT(int, [value])
				FROM SQLHTTP.net.Json_To_NodeTable(@Response)
				WHERE [Name] = 'expires_in'

				EXEC SQLHTTP.net.AuthParamSet	@Profile = @Profile, 
								@Name = 'BearerToken', 
								@value = @BearerToken

				SET @BearerTokenExpiration = DATEADD(second, @ExpiresIn, GetDate())

				EXEC SQLHTTP.net.AuthParamSet	@Profile = @Profile, 
								@Name = 'BearerTokenExpiration', 
								@Value = @BearerTokenExpiration
			END
	END
GO

CREATE PROCEDURE usp_Google_Auth_Refresh(@Profile nvarchar(100))

DECLARE @URL nvarchar(MAX)

DECLARE @HTTPSessionID uniqueidentifier

DECLARE @Body nvarchar(MAX)

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

DECLARE @BearerTokenExpiration smalldatetime

SET @BearerTokenExpiration = SQLHTTP.net.AuthParam(@Profile, 'BearerTokenExpiration')

IF ISNULL(@BearerTokenExpiration, '1/1/2050') > DATEADD(minute, -10, GetDate())

BEGIN

SET @URL = 'https://www.googleapis.com/oauth2/v4/token'

EXEC SQLHTTP.net.FormDataBuilder @Body OUTPUT,

@Profile,

'grant_type', 'refresh_token',

'refresh_token', '#RefreshToken',

'client_id', '#ClientID',

'client_secret', '#ClientSecret'

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

EXEC SQLHTTP.net.HTTPRequest @HttpSessionID,

@URL = @URL,

@Method = 'POST',

@Body = @Body,

@StatusCode = @StatusCode OUTPUT,

@StatusDescription = @StatusDescription OUTPUT,

@Response = @Response OUTPUT

IF @StatusCode >= 400

EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response

ELSE

BEGIN

DECLARE @BearerToken varchar(MAX)

DECLARE @ExpiresIn int

SELECT @BearerToken = [value]

FROM SQLHTTP.net.Json_To_NodeTable(@Response)

WHERE [Name] = 'access_token'

SELECT @ExpiresIn = CONVERT(int, [value])

FROM SQLHTTP.net.Json_To_NodeTable(@Response)

WHERE [Name] = 'expires_in'

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile,

@Name = 'BearerToken',

@value = @BearerToken

SET @BearerTokenExpiration = DATEADD(second, @ExpiresIn, GetDate())

EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile,

@Name = 'BearerTokenExpiration',

@Value = @BearerTokenExpiration

END



Available API Calls:

API Library > Gmail (Google)

Install Now Free

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.