(SQL Server) ETrade Preview Order

The Preview Order API is used to submit an order request for preview before placing it.

For more information, see https://apisb.etrade.com/docs/api/order/api-order-v1.html#/definition/orderPreview

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- 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)
    -- This requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    DECLARE @http int
    -- Use "Chilkat_9_5_0.Http" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.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

    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
    -- Use "Chilkat_9_5_0.Xml" for versions of Chilkat < 10.0.0
    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

    EXEC sp_OAMethod @http, 'SetRequestHeader', NULL, 'Content-Type', 'application/xml'

    DECLARE @resp int
    EXEC sp_OAMethod @xml, 'GetXml', @sTmp0 OUT
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @sandboxUrl, @sTmp0, 'utf-8'
    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 @xml
        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
        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


END
GO