(SQL Server) ETrade v1 Place Order

Submits an order after it has been successfully previewed. (Your application must first send the POST to preview the order. Then it can send this POST to place the order.)

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 example 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 3-Legged Authorization examples Step1 and Step2.
    DECLARE @json int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT

    DECLARE @success int
    EXEC sp_OAMethod @json, '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 @json
        RETURN
      END

    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token'
    EXEC sp_OASetProperty @http, 'OAuthToken', @sTmp0
    EXEC sp_OAMethod @json, 'StringOf', @sTmp0 OUT, 'oauth_token_secret'
    EXEC sp_OASetProperty @http, 'OAuthTokenSecret', @sTmp0

    -- See the ETrade v1 API documentation HERE.

    -- Sample XML Request
    -- Use this online tool to generate the code from sample XML: 
    -- Generate Code to Create XML

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <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'

    DECLARE @xmlContent nvarchar(4000)
    EXEC sp_OAMethod @xml, 'GetXml', @xmlContent OUT
    DECLARE @endpointUrl nvarchar(4000)
    SELECT @endpointUrl = 'https://apisb.etrade.com/v1/accounts/{accountIdKey}/orders/place'

    DECLARE @resp int
    EXEC sp_OAMethod @http, 'PostXml', @resp OUT, @endpointUrl, @xmlContent, '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 @json
        EXEC @hr = sp_OADestroy @xml
        RETURN
      END

    -- A 200 status code indicates success.
    DECLARE @statusCode int
    EXEC sp_OAGetProperty @resp, 'StatusCode', @statusCode OUT

    PRINT 'statusCode = ' + @statusCode

    -- Use the following online tool to generate parsing code from sample XML: 
    -- Generate Parsing Code from XML

    -- A sample XML response is shown below...

    EXEC sp_OAGetProperty @resp, 'BodyStr', @sTmp0 OUT
    EXEC sp_OAMethod @xml, 'LoadXml', @success OUT, @sTmp0
    EXEC @hr = sp_OADestroy @resp

    DECLARE @tagPath nvarchar(4000)

    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'

    -- Sample XML Response

    -- <?xml version="1.0" encoding="UTF-8"?>
    -- <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 @hr = sp_OADestroy @http
    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @xml


END
GO