SQL Server Requires Chilkat v11.0.0+
SQL Server
ETrade - List Orders (JSON)
Shows how to retrieve a list of orders for an ETrade account, and to iterate through the response.See https://developer.etrade.com/ctnt/dev-portal/getDetail?contentUri=V0_Documentation-OrderAPI-ListOrders for more information.
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 @sTmp0 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- This example assumes the Chilkat HTTP 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
DECLARE @jsonToken int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonToken OUT
EXEC sp_OAMethod @jsonToken, '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 @jsonToken
RETURN
END
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'oauth_token'
EXEC sp_OASetProperty @http, 'OAuthToken', @sTmp0
EXEC sp_OAMethod @jsonToken, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @sTmp0
-- Tell ETrade we want a JSON response:
EXEC sp_OASetProperty @http, 'Accept', 'application/json'
-- The live URL is https://etws.etrade.com/order/rest/orderlist/{accountId}
-- We're using the sandbox URL..
DECLARE @sbUrl int
EXEC @hr = sp_OACreate 'Chilkat.StringBuilder', @sbUrl OUT
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'https://etwssandbox.etrade.com/order/sandbox/rest/orderlist/'
EXEC sp_OAMethod @sbUrl, 'Append', @success OUT, 'MY_ETRADE_ACCOUNT_ID'
DECLARE @respStr nvarchar(4000)
EXEC sp_OAMethod @sbUrl, 'GetAsString', @sTmp0 OUT
EXEC sp_OAMethod @http, 'QuickGetStr', @respStr OUT, @sTmp0
EXEC sp_OAGetProperty @http, 'LastMethodSuccess', @iTmp0 OUT
IF @iTmp0 = 0
BEGIN
EXEC sp_OAGetProperty @http, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbUrl
RETURN
END
-- Examine the response status code.
DECLARE @statusCode int
EXEC sp_OAGetProperty @http, 'LastStatus', @statusCode OUT
PRINT 'Status Code = ' + @statusCode
-- The response is JSON. A sample response is shown below.
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'Load', @success OUT, @respStr
EXEC sp_OASetProperty @json, 'EmitCompact', 0
-- If the status code was not 200, then it was an error..
IF @statusCode <> 200
BEGIN
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
PRINT @sTmp0
PRINT 'List orders failed.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @json
RETURN
END
-- Iterate over the orders.
DECLARE @i int
SELECT @i = 0
DECLARE @numOrders int
EXEC sp_OAMethod @json, 'SizeOfArray', @numOrders OUT, 'GetOrderListResponse.orderListResponse.orderDetails'
DECLARE @orderList int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @orderList OUT
EXEC sp_OAMethod @json, 'ObjectOf2', @success OUT, 'GetOrderListResponse.orderListResponse', @orderList
WHILE @i < @numOrders
BEGIN
EXEC sp_OASetProperty @orderList, 'I', @i
EXEC sp_OAMethod @orderList, 'IntOf', @iTmp0 OUT, 'orderDetails[i].order.orderId'
PRINT 'orderId: ' + @iTmp0
EXEC sp_OAMethod @orderList, 'StringOf', @sTmp0 OUT, 'orderDetails[i].order.orderStatus'
PRINT 'orderStatus: ' + @sTmp0
EXEC sp_OAMethod @orderList, 'HasMember', @iTmp0 OUT, 'orderDetails[i].order.legDetails.executedPrice'
IF @iTmp0 = 1
BEGIN
EXEC sp_OAMethod @orderList, 'StringOf', @sTmp0 OUT, 'orderDetails[i].order.legDetails.executedPrice'
PRINT 'executedPrice: ' + @sTmp0
END
-- Is the legDetails an array? If so, then iterate over it..
DECLARE @szLegDetails int
EXEC sp_OAMethod @orderList, 'SizeOfArray', @szLegDetails OUT, 'orderDetails[i].order.legDetails'
IF @szLegDetails > 0
BEGIN
DECLARE @j int
SELECT @j = 0
WHILE @j < @szLegDetails
BEGIN
EXEC sp_OASetProperty @orderList, 'J', @j
EXEC sp_OAMethod @orderList, 'IntOf', @iTmp0 OUT, 'orderDetails[i].order.legDetails[j].legNumber'
PRINT '-- legNumber: ' + @iTmp0
EXEC sp_OAMethod @orderList, 'StringOf', @sTmp0 OUT, 'orderDetails[i].order.legDetails[j].orderAction'
PRINT ' orderAction: ' + @sTmp0
EXEC sp_OAMethod @orderList, 'IntOf', @iTmp0 OUT, 'orderDetails[i].order.legDetails[j].orderedQuantity'
PRINT ' orderedQuantity: ' + @iTmp0
SELECT @j = @j + 1
END
END
PRINT '----'
SELECT @i = @i + 1
END
PRINT 'success.'
-- Sample JSON response:
-- {
-- "GetOrderListResponse": {
-- "orderListResponse": {
-- "count": 11,
-- "marker": "",
-- "orderDetails": [
-- {
-- "order": {
-- "orderId": 208,
-- "orderPlacedTime": 1267660551466,
-- "orderExecutedTime": 1267660558000,
-- "orderValue": 0,
-- "orderStatus": "EXECUTED",
-- "orderType": "EQ",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "MARKET",
-- "limitPrice": 0,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "CSCO"
-- },
-- "symbolDescription": "CISCO SYS INC COM",
-- "orderAction": "SELL",
-- "orderedQuantity": 1,
-- "filledQuantity": 1,
-- "executedPrice": 24.84,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 205,
-- "orderPlacedTime": 1267099216308,
-- "orderValue": 4999994.21,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 50000,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "MSQ",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 4,
-- "expDay": 17,
-- "strikePrice": 26
-- },
-- "symbolDescription": "MSFT APR 26 Call",
-- "orderAction": "SELL_OPEN",
-- "orderedQuantity": 1,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5.75,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 200,
-- "orderPlacedTime": 1267095453128,
-- "orderValue": 210.79,
-- "orderStatus": "CANCEL_REQUESTED",
-- "orderType": "BUY_WRITES",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "NET_DEBIT",
-- "limitPrice": 2,
-- "stopPrice": 0,
-- "legDetails": [
-- {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "IBM"
-- },
-- "symbolDescription": "INTERNATIONAL BUSINESS MACHS COM\n\t\t\t\t\t\t\t",
-- "orderAction": "BUY",
-- "orderedQuantity": 100,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- {
-- "legNumber": 2,
-- "symbolInfo": {
-- "symbol": "IBM",
--
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 4,
-- "expDay": 17,
-- "strikePrice": 115
-- },
-- "symbolDescription": "IBM APR 115 Call",
-- "orderAction": "SELL_OPEN",
-- "orderedQuantity": 1,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5.75,
-- "estimatedFees": 0
-- }
-- ],
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 192,
-- "orderPlacedTime": 1267089996809,
-- "orderValue": 10.79,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 0.05,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "QQQ",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 3,
-- "expDay": 20,
-- "strikePrice": 43
-- },
-- "symbolDescription": "QQQQ MAR 43 Call",
-- "orderAction": "BUY_OPEN",
-- "orderedQuantity": 1,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5.75,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 191,
-- "orderPlacedTime": 1267089623587,
-- "orderValue": 15,
-- "orderStatus": "OPEN",
-- "orderType": "EQ",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 1,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "MSFT"
-- },
-- "symbolDescription": "MICROSOFT CORP COM\n\t\t\t\t\t\t\t",
-- "orderAction": "BUY",
-- "orderedQuantity": 10,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 190,
-- "orderPlacedTime": 1267089606681,
-- "orderValue": 15,
-- "orderStatus": "OPEN",
-- "orderType": "EQ",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 1,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "MSFT"
-- },
-- "symbolDescription": "MICROSOFT CORP COM\n\t\t\t\t\t\t\t",
-- "orderAction": "BUY",
-- "orderedQuantity": 10,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 189,
-- "orderPlacedTime": 1267089589812,
-- "orderValue": 15,
-- "orderStatus": "OPEN",
-- "orderType": "EQ",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 1,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "IBM"
-- },
-- "symbolDescription": "INTERNATIONAL BUSINESS MACHS COM\n\t\t\t\t\t\t\t",
-- "orderAction": "BUY",
-- "orderedQuantity": 10,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 188,
-- "orderPlacedTime": 1267089356773,
-- "orderValue": 132.59,
-- "orderStatus": "OPEN",
-- "orderType": "EQ",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "MARKET",
-- "limitPrice": 0,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "IBM"
-- },
-- "symbolDescription": "INTERNATIONAL BUSINESS MACHS COM\n\t\t\t\t\t\t\t",
-- "orderAction": "BUY",
-- "orderedQuantity": 1,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 187,
-- "orderPlacedTime": 1266904795303,
-- "orderValue": 10.79,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 0.05,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "MSQ",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 4,
-- "expDay": 17,
-- "strikePrice": 27
-- },
-- "symbolDescription": "MSFT APR 27 Call",
-- "orderAction": "BUY_OPEN",
-- "orderedQuantity": 1,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 5.75,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 170,
-- "orderPlacedTime": 1266584434221,
-- "orderValue": 391.944,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 1,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "IBM",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 2,
-- "expDay": 20,
-- "strikePrice": 130
-- },
-- "symbolDescription": "IBM FEB 130 Call",
-- "orderAction": "SELL_OPEN",
-- "orderedQuantity": 4,
-- "filledQuantity": 0,
-- "executedPrice": 0,
-- "estimatedCommission": 8,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 166,
-- "orderPlacedTime": 1266583451241,
-- "orderValue": 4020.28,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 2,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "IBM",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 2,
-- "expDay": 20,
-- "strikePrice": 130
-- },
-- "symbolDescription": "IBM FEB 130 Call",
-- "orderAction": "BUY_CLOSE",
-- "orderedQuantity": 20,
-- "filledQuantity": 10,
-- "executedPrice": 2,
-- "estimatedCommission": 20,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- },
-- {
-- "order": {
-- "orderId": 162,
-- "orderPlacedTime": 1266581179588,
-- "orderValue": 4020.28,
-- "orderStatus": "OPEN",
-- "orderType": "OPTN",
-- "orderTerm": "GOOD_FOR_DAY",
-- "priceType": "LIMIT",
-- "limitPrice": 2,
-- "stopPrice": 0,
-- "legDetails": {
-- "legNumber": 1,
-- "symbolInfo": {
-- "symbol": "GOP",
-- "callPut": "CALL",
-- "expYear": 2010,
-- "expMonth": 3,
-- "expDay": 20,
-- "strikePrice": 520
-- },
-- "symbolDescription": "GOOG MAR 520 Call",
-- "orderAction": "BUY_CLOSE",
-- "orderedQuantity": 20,
-- "filledQuantity": 10,
-- "executedPrice": 2,
-- "estimatedCommission": 20,
-- "estimatedFees": 0
-- },
-- "allOrNone": false
-- }
-- }
-- ]
-- }
-- }
-- }
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @sbUrl
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @orderList
END
GO