(SQL Server) ETrade Place Order

The Place Order API is used to submit an order after it has been successfully previewed.

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

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/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
    -- 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', '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

    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

    -- <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


END
GO