SQL Server
SQL Server
Etsy OAuth1 Authorization
See more Etsy Examples
Demonstrates 3-legged OAuth1 authorization for Etsy.Chilkat SQL Server Downloads
-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
DECLARE @hr int
DECLARE @iTmp0 int
DECLARE @iTmp1 int
-- Important: Do not use nvarchar(max). See the warning about using nvarchar(max).
DECLARE @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
DECLARE @consumerKey nvarchar(4000)
SELECT @consumerKey = 'keystring'
DECLARE @consumerSecret nvarchar(4000)
SELECT @consumerSecret = 'shared_secret'
-- Specify one or more SPACE separated scopes as query params in the requestTokenUrl
-- See https://www.etsy.com/developers/documentation/getting_started/oauth#section_permission_scopes
DECLARE @requestTokenUrl nvarchar(4000)
SELECT @requestTokenUrl = 'https://openapi.etsy.com/v2/oauth/request_token?scope=email_r%20listings_r%20listings_w%20listings_d'
DECLARE @authorizeUrl nvarchar(4000)
SELECT @authorizeUrl = 'https://www.etsy.com/oauth/signin'
DECLARE @accessTokenUrl nvarchar(4000)
SELECT @accessTokenUrl = 'https://openapi.etsy.com/v2/oauth/access_token'
-- The port number is picked at random. It's some unused port that won't likely conflict with anything else..
DECLARE @callbackUrl nvarchar(4000)
SELECT @callbackUrl = 'http://localhost:3017/'
DECLARE @callbackLocalPort int
SELECT @callbackLocalPort = 3017
-- The 1st step in 3-legged OAuth1.0a is to send a POST to the request token URL to obtain an OAuth Request Token
DECLARE @http int
EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
EXEC sp_OASetProperty @http, 'OAuth1', 1
EXEC sp_OASetProperty @http, 'OAuthConsumerKey', @consumerKey
EXEC sp_OASetProperty @http, 'OAuthConsumerSecret', @consumerSecret
EXEC sp_OASetProperty @http, 'OAuthCallback', @callbackUrl
DECLARE @req int
EXEC @hr = sp_OACreate 'Chilkat.HttpRequest', @req OUT
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, @requestTokenUrl, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- If successful, the resp.BodyStr contains something like this:
-- login_url=https%3A%2F%2Fwww.etsy.com%2Foauth%2Fsignin%3Foauth_consumer_key%3D9ad9l1omxzbwfr2niq0ce1ly%26oauth_token%3D7116b4d0c72c2736561853d9e50113%26service%3Dv2_prod&oauth_token=7116b4d0c72c2736561853d9e50113&oauth_token_secret=3b7612b5d3&oauth_callback_confirmed=true&oauth_consumer_key=9ad9l1omxzbwfr2niq0ce1ly&oauth_callback=http%3A%2F%2Flocalhost%3A3017%2F
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
-- We'll need this for later..
DECLARE @hashTab int
EXEC @hr = sp_OACreate 'Chilkat.Hashtable', @hashTab OUT
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @hashTab, 'AddQueryParams', @success OUT, @sTmp0
DECLARE @requestToken nvarchar(4000)
EXEC sp_OAMethod @hashTab, 'LookupStr', @requestToken OUT, 'oauth_token'
DECLARE @requestTokenSecret nvarchar(4000)
EXEC sp_OAMethod @hashTab, 'LookupStr', @requestTokenSecret OUT, 'oauth_token_secret'
EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @requestTokenSecret
PRINT 'oauth_token = ' + @requestToken
PRINT 'oauth_token_secret = ' + @requestTokenSecret
-- ---------------------------------------------------------------------------
-- The next step is to form a URL to send to the authorizeUrl
-- This is an HTTP GET that we load into a popup browser.
DECLARE @sbUrlForBrowser int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrlForBrowser OUT
EXEC sp_OAMethod @sbUrlForBrowser, 'Append', @success OUT, @authorizeUrl
EXEC sp_OAMethod @sbUrlForBrowser, 'Append', @success OUT, '?'
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @sbUrlForBrowser, 'Append', @success OUT, @sTmp0
DECLARE @url nvarchar(4000)
EXEC sp_OAMethod @sbUrlForBrowser, 'GetAsString', @url OUT
-- When the url is loaded into a browser, the response from Etsy will redirect back to localhost:3017
-- We'll need to start a socket that is listening on port 3017 for the callback from the browser.
DECLARE @listenSock int
EXEC @hr = sp_OACreate 'Chilkat.Socket', @listenSock OUT
DECLARE @backLog int
SELECT @backLog = 5
EXEC sp_OAMethod @listenSock, 'BindAndListen', @success OUT, @callbackLocalPort, @backLog
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @listenSock, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
RETURN
END
-- Wait for the browser's connection in a background thread.
-- (We'll send load the URL into the browser following this..)
-- Wait a max of 60 seconds before giving up.
DECLARE @sock int
EXEC @hr = sp_OACreate 'Chilkat.Socket', @sock OUT
DECLARE @maxWaitMs int
SELECT @maxWaitMs = 60000
DECLARE @task int
EXEC sp_OAMethod @listenSock, 'AcceptNextAsync', @task OUT, @maxWaitMs, @sock
EXEC sp_OAMethod @task, 'Run', @success OUT
-- Launch the system's default browser navigated to the URL.
DECLARE @oauth2 int
EXEC @hr = sp_OACreate 'Chilkat.OAuth2', @oauth2 OUT
EXEC sp_OAMethod @oauth2, 'LaunchBrowser', @success OUT, @url
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @oauth2, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- Wait for the listenSock's task to complete.
EXEC sp_OAMethod @task, 'Wait', @success OUT, @maxWaitMs
EXEC sp_OAGetProperty @task, 'StatusInt', @iTmp0 OUT
EXEC sp_OAGetProperty @task, 'TaskSuccess', @iTmp1 OUT
IF Not @success or (@iTmp0 <> 7) or (@iTmp1 <> 1)
BEGIN
IF Not @success
BEGIN
-- The task.LastErrorText applies to the Wait method call.
EXEC sp_OAGetProperty @task, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
END
ELSE
BEGIN
-- The ResultErrorText applies to the underlying task method call (i.e. the AcceptNextConnection)
EXEC sp_OAGetProperty @task, 'Status', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @task, 'ResultErrorText', @sTmp0 OUT
PRINT @sTmp0
END
EXEC @hr = sp_OADestroy @task
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- If we get to this point, the connection from the browser arrived and was accepted.
-- We no longer need the listen socket...
-- Stop listening on port 3017.
EXEC sp_OAMethod @listenSock, 'Close', @success OUT, 10
EXEC @hr = sp_OADestroy @task
-- Read the start line of the request..
DECLARE @startLine nvarchar(4000)
EXEC sp_OAMethod @sock, 'ReceiveUntilMatch', @startLine OUT, CHAR(13) + CHAR(10)
EXEC sp_OAGetProperty @sock, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- Read the request header.
DECLARE @requestHeader nvarchar(4000)
EXEC sp_OAMethod @sock, 'ReceiveUntilMatch', @requestHeader OUT, CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
EXEC sp_OAGetProperty @sock, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @sock, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
RETURN
END
-- The browser SHOULD be sending us a GET request, and therefore there is no body to the request.
-- Once the request header is received, we have all of it.
-- We can now send our HTTP response.
DECLARE @sbResponseHtml int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponseHtml OUT
EXEC sp_OAMethod @sbResponseHtml, 'Append', @success OUT, '<html><body><p>Chilkat thanks you!</b></body</html>'
DECLARE @sbResponse int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbResponse OUT
EXEC sp_OAMethod @sbResponse, 'Append', @success OUT, 'HTTP/1.1 200 OK' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbResponse, 'Append', @success OUT, 'Content-Length: '
EXEC sp_OAGetProperty @sbResponseHtml, 'Length', @iTmp0 OUT
EXEC sp_OAMethod @sbResponse, 'AppendInt', @success OUT, @iTmp0
EXEC sp_OAMethod @sbResponse, 'Append', @success OUT, CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbResponse, 'Append', @success OUT, 'Content-Type: text/html' + CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbResponse, 'Append', @success OUT, CHAR(13) + CHAR(10)
EXEC sp_OAMethod @sbResponse, 'AppendSb', @success OUT, @sbResponseHtml
EXEC sp_OAMethod @sbResponse, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @sock, 'SendString', @success OUT, @sTmp0
EXEC sp_OAMethod @sock, 'Close', @success OUT, 50
-- The information we need is in the startLine.
-- For example, the startLine will look like this:
-- GET /?oauth_token=a3bc8bec84acc31418b68a532e9511&oauth_verifier=b5558d37 HTTP/1.1
DECLARE @sbStartLine int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbStartLine OUT
EXEC sp_OAMethod @sbStartLine, 'Append', @success OUT, @startLine
DECLARE @numReplacements int
EXEC sp_OAMethod @sbStartLine, 'Replace', @numReplacements OUT, 'GET /?', ''
EXEC sp_OAMethod @sbStartLine, 'Replace', @numReplacements OUT, ' HTTP/1.1', ''
EXEC sp_OAMethod @sbStartLine, 'Trim', @success OUT
-- oauth_token=a3bc8bec84acc31418b68a532e9511&oauth_verifier=b5558d37
EXEC sp_OAMethod @sbStartLine, 'GetAsString', @sTmp0 OUT
PRINT 'startline: ' + @sTmp0
EXEC sp_OAMethod @hashTab, 'Clear', NULL
EXEC sp_OAMethod @sbStartLine, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @hashTab, 'AddQueryParams', @success OUT, @sTmp0
EXEC sp_OAMethod @hashTab, 'LookupStr', @requestToken OUT, 'oauth_token'
DECLARE @authVerifier nvarchar(4000)
EXEC sp_OAMethod @hashTab, 'LookupStr', @authVerifier OUT, 'oauth_verifier'
-- ------------------------------------------------------------------------------
-- Finally , we must exchange the OAuth Request Token for an OAuth Access Token.
EXEC sp_OASetProperty @http, 'OAuthToken', @requestToken
EXEC sp_OASetProperty @http, 'OAuthVerifier', @authVerifier
EXEC sp_OASetProperty @req, 'HttpVerb', 'POST'
EXEC sp_OASetProperty @req, 'ContentType', 'application/x-www-form-urlencoded'
EXEC sp_OAMethod @http, 'HttpReq', @success OUT, @accessTokenUrl, @req, @resp
IF @success = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbResponseHtml
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbStartLine
RETURN
END
-- Make sure a successful response was received.
EXEC sp_OAGetProperty @resp, 'StatusCode', @iTmp0 OUT
IF @iTmp0 <> 200
BEGIN
EXEC sp_OAGetProperty @resp, 'StatusLine', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'Header', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbResponseHtml
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbStartLine
RETURN
END
-- If successful, the resp.BodyStr contains something like this:
-- oauth_token=7898d7ba280dc791586dcfd26b37a9&oauth_token_secret=f2a7c267aa
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
PRINT @sTmp0
EXEC sp_OAMethod @hashTab, 'Clear', NULL
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @hashTab, 'AddQueryParams', @success OUT, @sTmp0
DECLARE @accessToken nvarchar(4000)
EXEC sp_OAMethod @hashTab, 'LookupStr', @accessToken OUT, 'oauth_token'
DECLARE @accessTokenSecret nvarchar(4000)
EXEC sp_OAMethod @hashTab, 'LookupStr', @accessTokenSecret OUT, 'oauth_token_secret'
-- The access token + secret is what should be saved and used for
-- subsequent REST API calls.
PRINT 'Access Token = ' + @accessToken
PRINT 'Access Token Secret = ' + @accessTokenSecret
-- Save this access token for future calls.
-- Just in case we need user_id and screen_name, save those also..
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'oauth_token', @accessToken
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'oauth_token_secret', @accessTokenSecret
DECLARE @fac int
EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @fac, 'WriteEntireTextFile', @success OUT, 'qa_data/tokens/etsy.json', @sTmp0, 'utf-8', 0
PRINT 'Success.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @req
EXEC @hr = sp_OADestroy @resp
EXEC @hr = sp_OADestroy @hashTab
EXEC @hr = sp_OADestroy @sbUrlForBrowser
EXEC @hr = sp_OADestroy @listenSock
EXEC @hr = sp_OADestroy @sock
EXEC @hr = sp_OADestroy @oauth2
EXEC @hr = sp_OADestroy @sbResponseHtml
EXEC @hr = sp_OADestroy @sbResponse
EXEC @hr = sp_OADestroy @sbStartLine
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @fac
END
GO