SQL Server

ETrade OAuth1 Authorization (3-legged) Step 2

See more ETrade Examples

Demonstrates the final step in 3-legged OAuth1 authorization for the ETrade REST API. Example uses the OAuth1 verifier code that was copy-and-pasted from the browser in the 1st step. The end result of this final OAuth1 step is an access token that can be used to make ETrade REST API calls.

See https://apisb.etrade.com/docs/api/authorization/get_access_token.html

SQL Server

-- 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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int
    SELECT @success = 0

    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @consumerKey nvarchar(4000)
    SELECT @consumerKey = 'ETRADE_CONSUMER_KEY'
    DECLARE @consumerSecret nvarchar(4000)
    SELECT @consumerSecret = 'ETRADE_CONSUMER_SECRET'

    DECLARE @requestTokenUrl nvarchar(4000)
    SELECT @requestTokenUrl = 'https://apisb.etrade.com/oauth/request_token'
    DECLARE @authorizeUrl nvarchar(4000)
    SELECT @authorizeUrl = 'https://us.etrade.com/e/t/etws/authorize'
    DECLARE @accessTokenUrl nvarchar(4000)
    SELECT @accessTokenUrl = 'https://apisb.etrade.com/oauth/access_token'

    DECLARE @http int
    EXEC @hr = sp_OACreate 'Chilkat.Http', @http OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    SELECT @success = 1

    EXEC sp_OASetProperty @http, 'OAuth1', 1
    EXEC sp_OASetProperty @http, 'OAuthConsumerKey', @consumerKey
    EXEC sp_OASetProperty @http, 'OAuthConsumerSecret', @consumerSecret
    EXEC sp_OASetProperty @http, 'OAuthCallback', 'oob'

    DECLARE @jsonRequestToken int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonRequestToken OUT

    EXEC sp_OAMethod @jsonRequestToken, 'LoadFile', @success OUT, 'qa_data/tokens/etrade_request_token.json'
    DECLARE @requestToken nvarchar(4000)
    EXEC sp_OAMethod @jsonRequestToken, 'StringOf', @requestToken OUT, 'oauth_token'
    DECLARE @requestTokenSecret nvarchar(4000)
    EXEC sp_OAMethod @jsonRequestToken, 'StringOf', @requestTokenSecret OUT, 'oauth_token_secret'

    -- ------------------------------------------------------------------------------
    -- Exchange the OAuth Request Token for an OAuth Access Token.

    EXEC sp_OASetProperty @http, 'OAuthToken', @requestToken
    EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @requestTokenSecret

    -- This is the verifier that was interactively copy-and-pasted from the browser back to our app.
    EXEC sp_OASetProperty @http, 'OAuthVerifier', 'NJ07S'

    -- Use the explicit string "INCLUDE_OAUTH_TOKEN" to tell Chilkat to include the "oauth_token" param in the Authorization header field
    EXEC sp_OASetProperty @http, 'UncommonOptions', 'INCLUDE_OAUTH_TOKEN'

    DECLARE @resp int
    EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT

    EXEC sp_OAMethod @http, 'HttpNoBody', @success OUT, 'GET', @accessTokenUrl, @resp
    IF @success = 0
      BEGIN
        EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @http
        EXEC @hr = sp_OADestroy @jsonRequestToken
        EXEC @hr = sp_OADestroy @resp
        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 @jsonRequestToken
        EXEC @hr = sp_OADestroy @resp
        RETURN
      END

    -- If successful, the resp.BodyStr contains something like this:
    -- oauth_token=85123455-fF41296Bi3daM8eCo9Y5vZabcdxXpRv864plYPOjr&oauth_token_secret=afiYJOgabcdSfGae7BDvJVVTwys8fUGpra5guZxbmFBZo
    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 @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/etrade.json', @sTmp0, 'utf-8', 0


    PRINT 'Success.'

    EXEC @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @jsonRequestToken
    EXEC @hr = sp_OADestroy @resp
    EXEC @hr = sp_OADestroy @hashTab
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @fac


END
GO