Chilkat HOME .NET Core C# Android™ AutoIt C C# C++ Chilkat2-Python CkPython Classic ASP DataFlex Delphi ActiveX Delphi DLL Go Java Lianja Mono C# Node.js Objective-C PHP ActiveX PHP Extension Perl PowerBuilder PowerShell PureBasic Ruby SQL Server Swift 2 Swift 3,4,5... Tcl Unicode C Unicode C++ VB.NET VBScript Visual Basic 6.0 Visual FoxPro Xojo Plugin
(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.
// 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) -- 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_9_5_0.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_9_5_0.JsonObject', @jsonToken OUT DECLARE @success int 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_9_5_0.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 <> 1 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_9_5_0.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 sp_OAMethod @json, 'ObjectOf', @orderList OUT, 'GetOrderListResponse.orderListResponse' 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 EXEC @hr = sp_OADestroy @orderList 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 END GO |
© 2000-2024 Chilkat Software, Inc. All Rights Reserved.