SQL Server
SQL Server
ETrade Preview Order
See more ETrade Examples
The Preview Order API is used to submit an order request for preview before placing it.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/preview'
DECLARE @liveUrl nvarchar(4000)
SELECT @liveUrl = 'https://api.etrade.com/v1/accounts/{$accountIdKey}/orders/preview'
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
-- <?xml version="1.0" encoding="UTF-8"?>
-- <PreviewOrderRequest>
-- <orderType>EQ</orderType>
-- <clientOrderId>sdfer333</clientOrderId>
-- <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>10</quantity>
-- </Instrument>
-- </Order>
-- </PreviewOrderRequest>
DECLARE @xml int
EXEC @hr = sp_OACreate 'Chilkat.Xml', @xml OUT
EXEC sp_OASetProperty @xml, 'Tag', 'PreviewOrderRequest'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'orderType', 'EQ'
EXEC sp_OAMethod @xml, 'UpdateChildContent', NULL, 'clientOrderId', 'sdfer333'
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', '10'
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
-- <?xml version="1.0" encoding="UTF-8"?>
-- <PreviewOrderResponse>
-- <orderType>EQ</orderType>
-- <totalOrderValue>1892.05</totalOrderValue>
-- <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>10</quantity>
-- <cancelQuantity>0.0</cancelQuantity>
-- <reserveOrder>true</reserveOrder>
-- <reserveQuantity>0.0</reserveQuantity>
-- </Instrument>
-- <messages>
-- <Message>
-- <code>1042</code>
-- <description>200|You have an existing open order for this security on the same side of the market. If you did not intend to place a second order for this security, please modify your order now.</description>
-- <type>WARNING</type>
-- </Message>
-- <Message>
-- <code>3093</code>
-- <description>Position Concentrated.</description>
-- <type>WARNING</type>
-- </Message>
-- </messages>
-- <egQual>EG_QUAL_NOT_A_MARKET_ORDER</egQual>
-- <estimatedCommission>6.95</estimatedCommission>
-- <estimatedTotalAmount>1892.05</estimatedTotalAmount>
-- <netPrice>0</netPrice>
-- <netBid>0</netBid>
-- <netAsk>0</netAsk>
-- <gcd>0</gcd>
-- <ratio />
-- </Order>
-- <PreviewIds>
-- <previewId>1020563279</previewId>
-- </PreviewIds>
-- <previewTime>1529018458516</previewTime>
-- <dstFlag>true</dstFlag>
-- <accountId>84246841</accountId>
-- <optionLevelCd>4</optionLevelCd>
-- <marginLevelCd>MARGIN_TRADING_ALLOWED</marginLevelCd>
-- <Disclosure>
-- <ahDisclosureFlag>false</ahDisclosureFlag>
-- <aoDisclosureFlag>false</aoDisclosureFlag>
-- <conditionalDisclosureFlag>true</conditionalDisclosureFlag>
-- <ehDisclosureFlag>false</ehDisclosureFlag>
-- </Disclosure>
-- <cashBpDetails>
-- <settled>
-- <currentBp>5000.00</currentBp>
-- <currentNetBp>5000.00</currentNetBp>
-- <currentOor>0.00</currentOor>
-- <currentOrderImpact>64.95</currentOrderImpact>
-- <netBp>4935.05</netBp>
-- </settled>
-- <settledUnsettled>
-- <currentBp>5000.00</currentBp>
-- <currentNetBp>5000.00</currentNetBp>
-- <currentOor>0.00</currentOor>
-- <currentOrderImpact>64.95</currentOrderImpact>
-- <netBp>4935.05</netBp>
-- </settledUnsettled>
-- </cashBpDetails>
-- </PreviewOrderResponse>
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 @totalOrderValue 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 @i int
DECLARE @count_i int
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 @previewId int
DECLARE @previewTime nvarchar(4000)
DECLARE @dstFlag nvarchar(4000)
DECLARE @accountId int
DECLARE @optionLevelCd int
DECLARE @marginLevelCd nvarchar(4000)
DECLARE @ahDisclosureFlag nvarchar(4000)
DECLARE @aoDisclosureFlag nvarchar(4000)
DECLARE @conditionalDisclosureFlag nvarchar(4000)
DECLARE @ehDisclosureFlag nvarchar(4000)
DECLARE @currentBp nvarchar(4000)
DECLARE @currentNetBp nvarchar(4000)
DECLARE @currentOor nvarchar(4000)
DECLARE @currentOrderImpact nvarchar(4000)
DECLARE @netBp nvarchar(4000)
EXEC sp_OAMethod @xml, 'GetChildContent', @orderType OUT, 'orderType'
EXEC sp_OAMethod @xml, 'GetChildContent', @totalOrderValue OUT, 'totalOrderValue'
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'
SELECT @i = 0
EXEC sp_OAMethod @xml, 'NumChildrenHavingTag', @count_i OUT, 'Order|messages|Message'
WHILE @i < @count_i
BEGIN
EXEC sp_OASetProperty @xml, 'I', @i
EXEC sp_OAMethod @xml, 'GetChildIntValue', @code OUT, 'Order|messages|Message[i]|code'
EXEC sp_OAMethod @xml, 'GetChildContent', @description OUT, 'Order|messages|Message[i]|description'
EXEC sp_OAMethod @xml, 'GetChildContent', @v_type OUT, 'Order|messages|Message[i]|type'
SELECT @i = @i + 1
END
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, 'GetChildIntValue', @previewId OUT, 'PreviewIds|previewId'
EXEC sp_OAMethod @xml, 'GetChildContent', @previewTime OUT, 'previewTime'
EXEC sp_OAMethod @xml, 'GetChildContent', @dstFlag OUT, 'dstFlag'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @accountId OUT, 'accountId'
EXEC sp_OAMethod @xml, 'GetChildIntValue', @optionLevelCd OUT, 'optionLevelCd'
EXEC sp_OAMethod @xml, 'GetChildContent', @marginLevelCd OUT, 'marginLevelCd'
EXEC sp_OAMethod @xml, 'GetChildContent', @ahDisclosureFlag OUT, 'Disclosure|ahDisclosureFlag'
EXEC sp_OAMethod @xml, 'GetChildContent', @aoDisclosureFlag OUT, 'Disclosure|aoDisclosureFlag'
EXEC sp_OAMethod @xml, 'GetChildContent', @conditionalDisclosureFlag OUT, 'Disclosure|conditionalDisclosureFlag'
EXEC sp_OAMethod @xml, 'GetChildContent', @ehDisclosureFlag OUT, 'Disclosure|ehDisclosureFlag'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentBp OUT, 'cashBpDetails|settled|currentBp'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentNetBp OUT, 'cashBpDetails|settled|currentNetBp'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentOor OUT, 'cashBpDetails|settled|currentOor'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentOrderImpact OUT, 'cashBpDetails|settled|currentOrderImpact'
EXEC sp_OAMethod @xml, 'GetChildContent', @netBp OUT, 'cashBpDetails|settled|netBp'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentBp OUT, 'cashBpDetails|settledUnsettled|currentBp'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentNetBp OUT, 'cashBpDetails|settledUnsettled|currentNetBp'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentOor OUT, 'cashBpDetails|settledUnsettled|currentOor'
EXEC sp_OAMethod @xml, 'GetChildContent', @currentOrderImpact OUT, 'cashBpDetails|settledUnsettled|currentOrderImpact'
EXEC sp_OAMethod @xml, 'GetChildContent', @netBp OUT, 'cashBpDetails|settledUnsettled|netBp'
PRINT 'Success.'
EXEC @hr = sp_OADestroy @http
EXEC @hr = sp_OADestroy @jsonToken
EXEC @hr = sp_OADestroy @xml
EXEC @hr = sp_OADestroy @resp
END
GO