Reddit - SQLHTTP

Interact with the Reddit API using SQL Server

Install the SQLHTTP database on your SQL Server
Read and follow the Reddit API Access page
Create your Reddit SCRIPT application to obtain your Client ID and a Client Secret
Create the stored procedures documented below
Execute the following SQL statement:


EXEC usp_Reddit_Auth_Init 	@Profile = 'enter-profile-name-of-your-choosing',
				@ClientID = 'your-reddit-client-id',
				@ClientSecret = 'your-reddit-client-secret',
				@RedditUserName = 'your-reddit-user-name',
				@RedditPassword = 'your-reddit-password'

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

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

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

@RedditUserName = 'your-reddit-user-name',

@RedditPassword = 'your-reddit-password'



Stored Procedures:


CREATE PROCEDURE usp_Reddit_Auth_Init(  @Profile varchar(100),
					@ClientID varchar(100), 
					@ClientSecret varchar(100), 
					@RedditUserName varchar(100), 
					@RedditPassword varchar(100))
AS

SET NOCOUNT ON

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 = 'RedditUserName', @Value = @RedditUserName
EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'RedditPassword', @Value = @RedditPassword

EXEC SQLHTTP.net.AuthParamRemove @Profile, 'TokenCreatedDateTime'

GO

CREATE PROCEDURE usp_Reddit_Auth_Init( @Profile varchar(100),

@ClientID varchar(100),

@ClientSecret varchar(100),

@RedditUserName varchar(100),

@RedditPassword varchar(100))

SET NOCOUNT ON

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 = 'RedditUserName', @Value = @RedditUserName

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

EXEC SQLHTTP.net.AuthParamRemove @Profile, 'TokenCreatedDateTime'


CREATE PROCEDURE usp_Reddit_Auth_Refresh(@Profile varchar(100))
AS

SET NOCOUNT ON

DECLARE @TokenCreatedDateTime nvarchar(100)

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

--Check if too early for obtain a new bearer token
IF DATEDIFF(minute, ISNULL(@TokenCreatedDateTime, DATEADD(hour, -1, GetDate())), GetDate()) < 55 RETURN

DECLARE @ClientID varchar(100)
DECLARE @ClientSecret varchar(100)
DECLARE @RedditUserName varchar(100)
DECLARE @RedditPassword varchar(100)

SET @ClientID = SQLHTTP.net.AuthParam(@Profile, 'ClientID')
SET @ClientSecret = SQLHTTP.net.AuthParam(@Profile, 'ClientSecret')
SET @RedditUserName = SQLHTTP.net.AuthParam(@Profile, 'RedditUserName')
SET @RedditPassword = SQLHTTP.net.AuthParam(@Profile, 'RedditPassword')

DECLARE @Body nvarchar(MAX)
SET @Body = 'grant_type=password&username=' + @RedditUserName + '&password=' + @RedditPassword

DECLARE @HTTPSessionID uniqueidentifier
EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

DECLARE @URL nvarchar(MAX)
SET @URL = 'https://www.reddit.com/api/v1/access_token'

EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, @ClientID, @ClientSecret

DECLARE @Response nvarchar(MAX)
DECLARE @StatusCode int
DECLARE @StatusDescription nvarchar(MAX)

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 @X xml
		SET @X = SQLHTTP.net.Json_To_XML(@Response, 0)

		DECLARE @BearerToken varchar(100)

		SELECT  @BearerToken = T.C.value(N'@access_token', N'nvarchar(MAX)') 
		FROM @X.nodes(N'/JsonObject') T(C)

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

		SET @TokenCreatedDateTime = GetDate()

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

CREATE PROCEDURE usp_Reddit_Auth_Refresh(@Profile varchar(100))

SET NOCOUNT ON

DECLARE @TokenCreatedDateTime nvarchar(100)

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

--Check if too early for obtain a new bearer token

IF DATEDIFF(minute, ISNULL(@TokenCreatedDateTime, DATEADD(hour, -1, GetDate())), GetDate()) < 55 RETURN

DECLARE @ClientID varchar(100)

DECLARE @ClientSecret varchar(100)

DECLARE @RedditUserName varchar(100)

DECLARE @RedditPassword varchar(100)

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

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

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

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

DECLARE @Body nvarchar(MAX)

SET @Body = 'grant_type=password&username=' + @RedditUserName + '&password=' + @RedditPassword

DECLARE @HTTPSessionID uniqueidentifier

EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT

DECLARE @URL nvarchar(MAX)

SET @URL = 'https://www.reddit.com/api/v1/access_token'

EXEC SQLHTTP.net.BasicAuthHeader @HttpSessionID, @ClientID, @ClientSecret

DECLARE @Response nvarchar(MAX)

DECLARE @StatusCode int

DECLARE @StatusDescription nvarchar(MAX)

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 @X xml

SET @X = SQLHTTP.net.Json_To_XML(@Response, 0)

DECLARE @BearerToken varchar(100)

SELECT @BearerToken = T.C.value(N'@access_token', N'nvarchar(MAX)')

FROM @X.nodes(N'/JsonObject') T(C)

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

@Name = 'BearerToken',

@Value = @BearerToken

SET @TokenCreatedDateTime = GetDate()

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

@Name = 'TokenCreatedDateTime',

@Value = @TokenCreatedDateTime

END


CREATE PROCEDURE usp_Reddit_Auth_Header( @Profile varchar(100), @HttpSessionID uniqueidentifier)
AS

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

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

GO

CREATE PROCEDURE usp_Reddit_Auth_Header( @Profile varchar(100), @HttpSessionID uniqueidentifier)

DECLARE @AuthorizationHeaderValue varchar(MAX)

SET @AuthorizationHeaderValue = 'Bearer ' + SQLHTTP.net.AuthParam(@Profile, 'BearerToken')

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



Available API Calls:

API Library > Reddit

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.