![]() |
Chilkat HOME Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi DLL Go Java Node.js Objective-C PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(SQL Server) Trello OAuth1 AuthorizationDemonstrates OAuth1 authentication for Trello. Note: This example requires Chilkat v11.0.0 or greater.
-- 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 -- This example requires the Chilkat API to have been previously unlocked. -- See Global Unlock Sample for sample code. DECLARE @consumerKey nvarchar(4000) SELECT @consumerKey = 'TRELLO_CONSUMER_KEY' DECLARE @consumerSecret nvarchar(4000) SELECT @consumerSecret = 'TRELLO_CONSUMER_SECRET' DECLARE @requestTokenUrl nvarchar(4000) SELECT @requestTokenUrl = 'https://trello.com/1/OAuthGetRequestToken' DECLARE @authorizeUrl nvarchar(4000) SELECT @authorizeUrl = 'https://trello.com/1/OAuthAuthorizeToken' DECLARE @accessTokenUrl nvarchar(4000) SELECT @accessTokenUrl = 'https://trello.com/1/OAuthGetAccessToken' -- 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: -- oauth_token=c173ff088a09a67389a42b1ee22366a4&oauth_token_secret=717e6015c6749fe050a923516e739dbb&oauth_callback_confirmed=true EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT PRINT @sTmp0 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, '?oauth_token=' EXEC sp_OAMethod @sbUrlForBrowser, 'Append', @success OUT, @requestToken EXEC sp_OAMethod @sbUrlForBrowser, 'Append', @success OUT, '&scope=read,write,account' DECLARE @url nvarchar(4000) EXEC sp_OAMethod @sbUrlForBrowser, 'GetAsString', @url OUT PRINT 'url = ' + @url -- When the urlForBrowser is loaded into a browser, the response from Trello 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 something like this: -- GET /?oauth_token=c173ff088a09a67389b42b1ee32366a4&oauth_verifier=c65bc8eed882e04bb94023bb12c0dbef 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=c173ff088a09a67389b42b1ee32366a4&oauth_verifier=c65bc8eed882e04bb94023bb12c0dbef 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, '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=4618e19f5101b7199f75aA9e678d1585576ad84fb89fa40c85c4da13589010d5&oauth_token_secret=64a997b26ea1f47105eca36ce1a5d22e EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT PRINT 'response BodyStr = ' + @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 the access token for subsequent REST API calls. 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/trello.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 |
© 2000-2025 Chilkat Software, Inc. All Rights Reserved.