E*Trade
Interact with the E*Trade API using SQL Server
- Install the SQLHTTP database on your SQL Server
- Obtain a Consumer Key and a Consumer Secret from ETrade
- Once you receive the above consumer key and secret, proceed to request a callback URL to a localhost and port. For example, send ETrade a message to set up a callback URL to “http://localhost:8080”
- Create the stored procedures documented below
- Execute the following SQL statement:
|
1 2 3 4 5 6 7 |
EXEC usp_Etrade_Auth_Init @Profile = 'enter-profile-name-of-your-choosing', @ConsumerKey = 'your-etrade-consumer-key', @ConsumerSecret = 'your-etrade-consumer-secret', @Sandbox = 1, -- 0 = Live, 1 = Sandbox @RedirectURL = 'your-etrade-redirecturl' --example: http://localhost:53200 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 |
CREATE PROCEDURE usp_ETrade_Auth_Init( @Profile nvarchar(100), @ConsumerKey varchar(100), @ConsumerSecret varchar(100), @Sandbox bit, @RedirectURL varchar(100)) AS SET NOCOUNT ON DECLARE @UserBrowseToURL varchar(MAX) DECLARE @QueryString nvarchar(MAX) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ConsumerKey', @Value = @ConsumerKey EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'ConsumerSecret', @Value = @ConsumerSecret EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Sandbox', @Value = @Sandbox DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Token varchar(100) DECLARE @TokenSecret varchar(100) DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) IF @Sandbox = 1 BEGIN SET @URL = 'https://etwssandbox.etrade.com/oauth/request_token?oauth_callback=oob' END ELSE BEGIN SET @URL = 'https://etws.etrade.com/oauth/request_token?oauth_callback=oob' END EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.OAuthHeader @HttpSessionID = @HttpSessionID, @URL = @URL, @Method = 'GET', @ConsumerKey = @ConsumerKey, @ConsumerSecret = @ConsumerSecret, @Token = '', @TokenSecret = '' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 BEGIN EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response RETURN END SET @Token = SQLHTTP.net.MidText(@Response, 'oauth_token=', '&', 1) SET @TokenSecret = SQLHTTP.net.MidText(@Response, 'oauth_token_secret=', '&', 1) SET @UserBrowseToURL = 'https://us.etrade.com/e/t/etws/authorize' SET @Token = SQLHTTP.net.URLDecode(@Token) SET @TokenSecret = SQLHTTP.net.URLDecode(@TokenSecret) EXEC SQLHTTP.net.QueryStringBuilder @QueryString OUTPUT, @Profile, 'key', @ConsumerKey, 'token', @Token SET @UserBrowseToURL = @UserBrowseToURL + @QueryString DECLARE @Verifier varchar(50) 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 @Verifier = SQLHTTP.net.Split(@QueryString, 'oauth_verifier=', 2) SET @Verifier = SQLHTTP.net.Split(@Verifier, '&', 1) IF @Sandbox = 1 BEGIN SET @URL = 'https://etwssandbox.etrade.com/oauth/access_token?oauth_verifier=' COLLATE SQL_Latin1_General_CP1_CI_AS + @Verifier END ELSE BEGIN SET @URL = 'https://etws.etrade.com/oauth/access_token?oauth_verifier=' COLLATE SQL_Latin1_General_CP1_CI_AS + @Verifier END EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC SQLHTTP.net.OAuthHeader @HttpSessionID = @HttpSessionID, @URL = @URL, @Method = 'GET', @ConsumerKey = @ConsumerKey, @ConsumerSecret = @ConsumerSecret, @Token = @Token, @TokenSecret = @TokenSecret EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 BEGIN EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response RETURN END SET @Token = SQLHTTP.net.MidText(@Response, 'oauth_token=', '&', 1) SET @TokenSecret = SQLHTTP.net.Split(@Response, 'oauth_token_secret=', 2) SET @Token = SQLHTTP.net.URLDecode(@Token) SET @TokenSecret = SQLHTTP.net.URLDecode(@TokenSecret) EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'Token', @Value = @Token EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenSecret', @Value = @TokenSecret DECLARE @TokenCreatedDateTime nvarchar(100) SET @TokenCreatedDateTime = GetDate() EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenCreatedDateTime', @Value = @TokenCreatedDateTime GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
CREATE PROCEDURE usp_ETrade_Auth_Header(@Profile nvarchar(100), @HttpSessionID uniqueidentifier, @URL nvarchar(4000), @Method varchar(10)) AS DECLARE @ConsumerKey nvarchar(100) DECLARE @ConsumerSecret nvarchar(100) DECLARE @Token nvarchar(100) DECLARE @TokenSecret nvarchar(100) SET @ConsumerKey = SQLHTTP.net.AuthParam(@Profile, 'ConsumerKey') SET @ConsumerSecret = SQLHTTP.net.AuthParam(@Profile, 'ConsumerSecret') SET @Token = SQLHTTP.net.AuthParam(@Profile, 'Token') SET @TokenSecret = SQLHTTP.net.AuthParam(@Profile, 'TokenSecret') EXEC SQLHTTP.net.OAuthHeader @HttpSessionID = @HttpSessionID, @URL = @URL, @Method = @Method, @ConsumerKey = @ConsumerKey, @ConsumerSecret = @ConsumerSecret, @Token = @Token, @TokenSecret = @TokenSecret GO |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
CREATE PROCEDURE usp_ETrade_Auth_Refresh( @Profile nvarchar(100)) AS DECLARE @TokenCreatedDateTime nvarchar(100) SET @TokenCreatedDateTime = SQLHTTP.net.AuthParam(@Profile, 'TokenCreatedDateTime') IF DATEDIFF(minute, @TokenCreatedDateTime, GetDate()) < 90 RETURN --Too early for a token refresh DECLARE @URL nvarchar(MAX) DECLARE @HTTPSessionID uniqueidentifier DECLARE @Body nvarchar(MAX) DECLARE @Response nvarchar(MAX) DECLARE @StatusCode int DECLARE @StatusDescription nvarchar(MAX) SET @URL = 'https://etws.etrade.com/oauth/renew_access_token' EXEC SQLHTTP.net.HTTPSession @HTTPSessionID OUTPUT EXEC usp_Etrade_Auth_Header @Profile, @HTTPSessionID, @URL, 'GET' EXEC SQLHTTP.net.HTTPRequest @HttpSessionID, @URL = @URL, @Method = 'GET', @StatusCode = @StatusCode OUTPUT, @StatusDescription = @StatusDescription OUTPUT, @Response = @Response OUTPUT IF @StatusCode >= 400 EXEC SQLHTTP.net.RaiseHttpError @StatusCode, @StatusDescription, @Response ELSE BEGIN --documentation incorrect at: https://us.etrade.com/ctnt/dev-portal/getDetail?contentUri=V0_Documentation-AuthorizationAPI-RenewAccessToken IF @Response = 'Access Token has been renewed' BEGIN SET @TokenCreatedDateTime = GetDate() EXEC SQLHTTP.net.AuthParamSet @Profile = @Profile, @Name = 'TokenCreatedDateTime', @Value = @TokenCreatedDateTime END ELSE BEGIN RAISERROR(@Response, 16, 1) RETURN END END GO |
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.