SQL Server
SQL Server
ETrade v1 List Transactions
See more HTTP Misc Examples
Get information about the transactions in an ETrade brokerage account.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
-- 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 @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, 'OAuthVerifier', ''
EXEC sp_OASetProperty @http, 'OAuthConsumerKey', 'ETRADE_CONSUMER_KEY'
EXEC sp_OASetProperty @http, 'OAuthConsumerSecret', 'ETRADE_CONSUMER_SECRET'
-- Load the access token previously obtained via the OAuth1 3-Legged Authorization examples Step1 and Step2.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'LoadFile', @success OUT, 'qa_data/tokens/etrade.json'
IF @success <> 1
BEGIN
PRINT 'Failed to load OAuth1 token'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token'
EXEC sp_OASetProperty @http, 'OAuthToken', @sTmp0
EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @sTmp0
-- See the ETrade v1 API documentation HERE.
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'accountIdKey', 'vsnhtF7d9jXxBy6HyaAC4vQ'
DECLARE @respStr nvarchar(4000)
EXEC sp_OAMethod @http, 'QuickGetStr', @respStr OUT, 'https://apisb.etrade.com/v1/accounts/{$accountIdKey}/transactions'
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 <> 1
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
RETURN
END
-- A 200 status code indicates success.
DECLARE @statusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT
PRINT 'statusCode = ' + @statusCode
-- Use the following online tool to generate parsing code from sample XML:
-- Generate Parsing Code from XML
-- A sample XML response is shown below...
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @respStr
DECLARE @i int
DECLARE @count_i int
DECLARE @tagPath nvarchar(4000)
DECLARE @transactionId nvarchar(4000)
DECLARE @accountId int
DECLARE @transactionDate nvarchar(4000)
DECLARE @postDate nvarchar(4000)
DECLARE @amount int
DECLARE @description nvarchar(4000)
DECLARE @description2 int
DECLARE @transactionType nvarchar(4000)
DECLARE @imageFlag nvarchar(4000)
DECLARE @instType nvarchar(4000)
DECLARE @quantity int
DECLARE @price int
DECLARE @settlementCurrency nvarchar(4000)
DECLARE @paymentCurrency nvarchar(4000)
DECLARE @fee int
DECLARE @settlementDate nvarchar(4000)
DECLARE @detailsURI nvarchar(4000)
DECLARE @pageMarkers nvarchar(4000)
DECLARE @moreTransactions nvarchar(4000)
DECLARE @transactionCount int
DECLARE @totalCount int
SELECT @i = 0
EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'Transaction'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildContent', @transactionId OUT, 'Transaction[i]|transactionId'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountId OUT, 'Transaction[i]|accountId'
EXEC sp_OAMethod @xml, 'GetChildContent', @transactionDate OUT, 'Transaction[i]|transactionDate'
EXEC sp_OAMethod @xml, 'GetChildContent', @postDate OUT, 'Transaction[i]|postDate'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @amount OUT, 'Transaction[i]|amount'
EXEC sp_OAMethod @xml, 'GetChildContent', @description OUT, 'Transaction[i]|description'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @description2 OUT, 'Transaction[i]|description2'
EXEC sp_OAMethod @xml, 'GetChildContent', @transactionType OUT, 'Transaction[i]|transactionType'
EXEC sp_OAMethod @xml, 'GetChildContent', @imageFlag OUT, 'Transaction[i]|imageFlag'
EXEC sp_OAMethod @xml, 'GetChildContent', @instType OUT, 'Transaction[i]|instType'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @quantity OUT, 'Transaction[i]|brokerage|quantity'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @price OUT, 'Transaction[i]|brokerage|price'
EXEC sp_OAMethod @xml, 'GetChildContent', @settlementCurrency OUT, 'Transaction[i]|brokerage|settlementCurrency'
EXEC sp_OAMethod @xml, 'GetChildContent', @paymentCurrency OUT, 'Transaction[i]|brokerage|paymentCurrency'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @fee OUT, 'Transaction[i]|brokerage|fee'
EXEC sp_OAMethod @xml, 'GetChildContent', @settlementDate OUT, 'Transaction[i]|brokerage|settlementDate'
EXEC sp_OAMethod @xml, 'GetChildContent', @detailsURI OUT, 'Transaction[i]|detailsURI'
SELECT @i = @i + 1
END
EXEC sp_OAMethod @xml, 'GetChildContent', @pageMarkers OUT, 'pageMarkers'
EXEC sp_OAMethod @xml, 'GetChildContent', @moreTransactions OUT, 'moreTransactions'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @transactionCount OUT, 'transactionCount'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @totalCount OUT, 'totalCount'
-- <?xml version="1.0" encoding="UTF-8"?>
-- <TransactionListResponse>
-- <Transaction>
-- <transactionId>18165100001766</transactionId>
-- <accountId>83564979</accountId>
-- <transactionDate>1528948800000</transactionDate>
-- <postDate>1528948800000</postDate>
-- <amount>-2</amount>
-- <description>ACH WITHDRAWL REFID:109187276;</description>
-- <description2>109187276</description2>
-- <transactionType>Transfer</transactionType>
-- <memo />
-- <imageFlag>false</imageFlag>
-- <instType>BROKERAGE</instType>
-- <brokerage>
-- <product />
-- <quantity>0</quantity>
-- <price>0</price>
-- <settlementCurrency>USD</settlementCurrency>
-- <paymentCurrency>USD</paymentCurrency>
-- <fee>0</fee>
-- <settlementDate>1528948800000</settlementDate>
-- </brokerage>
-- <detailsURI>https://api.etrade.com/v1/accounts/yIFaUoJ81qyAhgxLWRQ42g/transactions/18165100001766</detailsURI>
-- </Transaction>
-- <Transaction>
-- <transactionId>18158100000983</transactionId>
-- <accountId>83564979</accountId>
-- <transactionDate>1528344000000</transactionDate>
-- <postDate>1528344000000</postDate>
-- <amount>-2</amount>
-- <description>ACH WITHDRAWL REFID:98655276;</description>
-- <description2>98655276</description2>
-- <transactionType>Transfer</transactionType>
-- <memo />
-- <imageFlag>false</imageFlag>
-- <instType>BROKERAGE</instType>
-- <brokerage>
-- <product />
-- <quantity>0</quantity>
-- <price>0</price>
-- <settlementCurrency>USD</settlementCurrency>
-- <paymentCurrency>USD</paymentCurrency>
-- <fee>0</fee>
-- <settlementDate>1528344000000</settlementDate>
-- </brokerage>
-- <detailsURI>https://api.etrade.com/v1/accounts/yIFaUoJ81qyAhgxLWRQ42g/transactions/18158100000983</detailsURI>
-- </Transaction>
-- <pageMarkers>eNpTsAlITE91zi%2FNK%2FHMc04syi8tTs2xM7TRxybMpWATkl%2BSmBOUmpxflAKWtTO10ccQg6mDmwEyEE0EqAbE8SvNTUotCk4tLE3NS061M9Ax0DEEYgOIA9BkuRQgmjxTfDKLQUYoQAV8E4uyU4vsDC0MzUwNDYDA0NzMrKamBmIKVJYLphpiKsyTUB7IbH1kwwFa7F0D</pageMarkers>
-- <moreTransactions>false</moreTransactions>
-- <transactionCount>5</transactionCount>
-- <totalCount>5</totalCount>
-- </TransactionListResponse
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @xml
END
GO