SQL Server
SQL Server
ETrade Place Order
See more ETrade Examples
The Place Order API is used to submit an order after it has been successfully previewed.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 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 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
DECLARE @sandboxUrl nvarchar(4000)
SELECT @sandboxUrl = 'https://apisb.etrade.com/v1/accounts/{$accountIdKey}/orders/place'
DECLARE @liveUrl nvarchar(4000)
SELECT @liveUrl = 'https://api.etrade.com/v1/accounts/{$accountIdKey}/orders/place'
EXEC sp_OAMethod @http, 'SetUrlVar', @success OUT, 'accountIdKey', '6_Dpy0rmuQ9cu9IbTfvF2A'
-- Send a POST with the following XML body
-- Use this online tool to generate the code from sample XML:
-- Generate Code to Create XML
-- <PlaceOrderRequest>
-- <orderType>EQ</orderType>
-- <clientOrderId>sd464333</clientOrderId>
-- <PreviewIds>
-- <previewId>730206520</previewId>
-- </PreviewIds>
-- <Order>
-- <allOrNone>false</allOrNone>
-- <priceType>LIMIT</priceType>
-- <orderTerm>GOOD_FOR_DAY</orderTerm>
-- <marketSession>REGULAR</marketSession>
-- <stopPrice />
-- <limitPrice>188.51</limitPrice>
-- <Instrument>
-- <Product>
-- <securityType>EQ</securityType>
-- <symbol>FB</symbol>
-- </Product>
-- <orderAction>BUY</orderAction>
-- <quantityType>QUANTITY</quantityType>
-- <quantity>150</quantity>
-- </Instrument>
-- </Order>
-- </PlaceOrderRequest>
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'PlaceOrderRequest'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'orderType', 'EQ'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'clientOrderId', 'sd464333'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'PreviewIds|previewId', '730206520'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|allOrNone', 'false'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|priceType', 'LIMIT'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|orderTerm', 'GOOD_FOR_DAY'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|marketSession', 'REGULAR'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|stopPrice', ''
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|limitPrice', '188.51'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|Instrument|Product|securityType', 'EQ'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|Instrument|Product|symbol', 'FB'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|Instrument|orderAction', 'BUY'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|Instrument|quantityType', 'QUANTITY'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'Order|Instrument|quantity', '150'
EXEC sp_OASetProperty @xml, 'EmitCompact', 1
DECLARE @resp int
EXEC @hr = sp_OACreate 'Chilkat.HttpResponse', @resp OUT
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
EXEC sp_OAMethod @http, 'HttpStr', @success OUT, 'POST', @sandboxUrl, @sTmp0, 'utf-8', 'application/xml', @resp
IF @success = 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 @xml
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 @jsonToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @resp
RETURN
END
-- Sample XML response:
-- Use this online tool to generate parsing code from sample XML:
-- Generate Parsing Code from XML
-- <PlaceOrderResponse>
-- <orderType>EQ</orderType>
-- <Order>
-- <orderTerm>GOOD_FOR_DAY</orderTerm>
-- <priceType>LIMIT</priceType>
-- <limitPrice>188.51</limitPrice>
-- <stopPrice>0</stopPrice>
-- <marketSession>REGULAR</marketSession>
-- <allOrNone>false</allOrNone>
-- <Instrument>
-- <Product>
-- <symbol>FB</symbol>
-- <securityType>EQ</securityType>
-- </Product>
-- <symbolDescription>FACEBOOK INC CL A</symbolDescription>
-- <orderAction>BUY</orderAction>
-- <quantityType>QUANTITY</quantityType>
-- <quantity>150</quantity>
-- <cancelQuantity>0.0</cancelQuantity>
-- <reserveOrder>true</reserveOrder>
-- <reserveQuantity>0.0</reserveQuantity>
-- </Instrument>
-- <messages>
-- <Message>
-- <code>1027</code>
-- <description>200|The market was closed when we received your order. It has been entered into our system and will be reviewed prior to market open on the next regular trading day. After market open, please check to make sure your order was accepted.</description>
-- <type>WARNING</type>
-- </Message>
-- </messages>
-- <egQual>EG_QUAL_NOT_A_MARKET_ORDER</egQual>
-- <estimatedCommission>6.95</estimatedCommission>
-- <estimatedTotalAmount>28283.45</estimatedTotalAmount>
-- <netPrice>0</netPrice>
-- <netBid>0</netBid>
-- <netAsk>0</netAsk>
-- <gcd>0</gcd>
-- <ratio />
-- </Order>
-- <dstFlag>true</dstFlag>
-- <optionLevelCd>4</optionLevelCd>
-- <marginLevelCd>MARGIN_TRADING_ALLOWED</marginLevelCd>
-- <OrderIds>
-- <orderId>5</orderId>
-- </OrderIds>
-- <placedTime>1528764717641</placedTime>
-- <accountId>84312767</accountId>
-- </PlaceOrderResponse>
EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
PRINT @sTmp0
DECLARE @orderType nvarchar(4000)
DECLARE @orderTerm nvarchar(4000)
DECLARE @priceType nvarchar(4000)
DECLARE @limitPrice nvarchar(4000)
DECLARE @stopPrice int
DECLARE @marketSession nvarchar(4000)
DECLARE @allOrNone nvarchar(4000)
DECLARE @symbol nvarchar(4000)
DECLARE @securityType nvarchar(4000)
DECLARE @symbolDescription nvarchar(4000)
DECLARE @orderAction nvarchar(4000)
DECLARE @quantityType nvarchar(4000)
DECLARE @quantity int
DECLARE @cancelQuantity nvarchar(4000)
DECLARE @reserveOrder nvarchar(4000)
DECLARE @reserveQuantity nvarchar(4000)
DECLARE @code int
DECLARE @description nvarchar(4000)
DECLARE @v_type nvarchar(4000)
DECLARE @egQual nvarchar(4000)
DECLARE @estimatedCommission nvarchar(4000)
DECLARE @estimatedTotalAmount nvarchar(4000)
DECLARE @netPrice int
DECLARE @netBid int
DECLARE @netAsk int
DECLARE @gcd int
DECLARE @dstFlag nvarchar(4000)
DECLARE @optionLevelCd int
DECLARE @marginLevelCd nvarchar(4000)
DECLARE @orderId int
DECLARE @placedTime nvarchar(4000)
DECLARE @accountId int
EXEC sp_OAMethod @xml, 'GetChildContent', @orderType OUT, 'orderType'
EXEC sp_OAMethod @xml, 'GetChildContent', @orderTerm OUT, 'Order|orderTerm'
EXEC sp_OAMethod @xml, 'GetChildContent', @priceType OUT, 'Order|priceType'
EXEC sp_OAMethod @xml, 'GetChildContent', @limitPrice OUT, 'Order|limitPrice'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @stopPrice OUT, 'Order|stopPrice'
EXEC sp_OAMethod @xml, 'GetChildContent', @marketSession OUT, 'Order|marketSession'
EXEC sp_OAMethod @xml, 'GetChildContent', @allOrNone OUT, 'Order|allOrNone'
EXEC sp_OAMethod @xml, 'GetChildContent', @symbol OUT, 'Order|Instrument|Product|symbol'
EXEC sp_OAMethod @xml, 'GetChildContent', @securityType OUT, 'Order|Instrument|Product|securityType'
EXEC sp_OAMethod @xml, 'GetChildContent', @symbolDescription OUT, 'Order|Instrument|symbolDescription'
EXEC sp_OAMethod @xml, 'GetChildContent', @orderAction OUT, 'Order|Instrument|orderAction'
EXEC sp_OAMethod @xml, 'GetChildContent', @quantityType OUT, 'Order|Instrument|quantityType'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @quantity OUT, 'Order|Instrument|quantity'
EXEC sp_OAMethod @xml, 'GetChildContent', @cancelQuantity OUT, 'Order|Instrument|cancelQuantity'
EXEC sp_OAMethod @xml, 'GetChildContent', @reserveOrder OUT, 'Order|Instrument|reserveOrder'
EXEC sp_OAMethod @xml, 'GetChildContent', @reserveQuantity OUT, 'Order|Instrument|reserveQuantity'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @code OUT, 'Order|messages|Message|code'
EXEC sp_OAMethod @xml, 'GetChildContent', @description OUT, 'Order|messages|Message|description'
EXEC sp_OAMethod @xml, 'GetChildContent', @v_type OUT, 'Order|messages|Message|type'
EXEC sp_OAMethod @xml, 'GetChildContent', @egQual OUT, 'Order|egQual'
EXEC sp_OAMethod @xml, 'GetChildContent', @estimatedCommission OUT, 'Order|estimatedCommission'
EXEC sp_OAMethod @xml, 'GetChildContent', @estimatedTotalAmount OUT, 'Order|estimatedTotalAmount'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @netPrice OUT, 'Order|netPrice'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @netBid OUT, 'Order|netBid'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @netAsk OUT, 'Order|netAsk'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @gcd OUT, 'Order|gcd'
EXEC sp_OAMethod @xml, 'GetChildContent', @dstFlag OUT, 'dstFlag'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @optionLevelCd OUT, 'optionLevelCd'
EXEC sp_OAMethod @xml, 'GetChildContent', @marginLevelCd OUT, 'marginLevelCd'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @orderId OUT, 'OrderIds|orderId'
EXEC sp_OAMethod @xml, 'GetChildContent', @placedTime OUT, 'placedTime'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountId OUT, 'accountId'
PRINT 'Success.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @resp
END
GO