SQL Server
SQL Server
Binary.com Tick Stream
See more WebSocket Examples
Tick Stream Request -- Initiate a continuous stream of spot price updates for a given symbol.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 example requires the Chilkat API to have been previously unlocked.
-- See Global Unlock Sample for sample code.
DECLARE @ws int
EXEC @hr = sp_OACreate 'Chilkat.WebSocket', @ws OUT
IF @hr <> 0
BEGIN
PRINT 'Failed to create ActiveX component'
RETURN
END
-- For brevity, this example does not check for errors when etablishing the WebSocket connection.
-- See Establish WebSocket Connection for more complete sample code for making the connection.
DECLARE @rest int
EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT
-- Connect to ws.binaryws.com
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'ws.binaryws.com', 443, 1, 0
EXEC sp_OAMethod @ws, 'UseConnection', @success OUT, @rest
EXEC sp_OAMethod @ws, 'AddClientHeaders', @success OUT
-- My app_id is 12332. You'll wnat to use your own...
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/websockets/v3?app_id=12332'
EXEC sp_OAMethod @ws, 'ValidateServerHandshake', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ws, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
PRINT @responseBody
EXEC sp_OAGetProperty @rest, 'ResponseHeader', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
RETURN
END
-- Build and send the following message:
-- {
-- "ticks": "R_50",
-- "subscribe": 1
-- }
DECLARE @json int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
EXEC sp_OAMethod @json, 'AppendString', @success OUT, 'ticks', 'R_50'
EXEC sp_OAMethod @json, 'AppendInt', @success OUT, 'subscribe', 1
-- Send a full message in a single frame
DECLARE @finalFrame int
SELECT @finalFrame = 1
EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @ws, 'SendFrame', @success OUT, @sTmp0, @finalFrame
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ws, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
RETURN
END
DECLARE @jsonTickData int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonTickData OUT
EXEC sp_OASetProperty @jsonTickData, 'EmitCompact', 0
-- Begin reading the tick stream response.
-- We'll just read the 1st 10 updates and then exit..
DECLARE @receivedFinalFrame int
SELECT @receivedFinalFrame = 0
DECLARE @numTicksReceived int
SELECT @numTicksReceived = 0
WHILE @numTicksReceived < 5
BEGIN
EXEC sp_OAMethod @ws, 'ReadFrame', @success OUT
IF @success <> 1
BEGIN
PRINT 'Failed to receive a frame'
EXEC sp_OAGetProperty @ws, 'ReadFrameFailReason', @iTmp0 OUT
PRINT 'ReadFrame fail reason = ' + @iTmp0
EXEC sp_OAGetProperty @ws, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonTickData
RETURN
END
-- The responses we desire are in Text frames, where the opcode = 1.
EXEC sp_OAGetProperty @ws, 'FrameOpcodeInt', @iTmp0 OUT
IF @iTmp0 = 1
BEGIN
DECLARE @receivedJson nvarchar(4000)
EXEC sp_OAMethod @ws, 'GetFrameData', @receivedJson OUT
EXEC sp_OAMethod @jsonTickData, 'Load', @success OUT, @receivedJson
EXEC sp_OAMethod @jsonTickData, 'Emit', @sTmp0 OUT
PRINT @sTmp0
SELECT @numTicksReceived = @numTicksReceived + 1
END
END
-- Close the websocket connection.
EXEC sp_OAMethod @ws, 'SendClose', @success OUT, 1, 1000, 'Closing this websocket.'
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ws, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonTickData
RETURN
END
-- Read the Close response.
EXEC sp_OAMethod @ws, 'ReadFrame', @success OUT
IF @success <> 1
BEGIN
EXEC sp_OAGetProperty @ws, 'ReadFrameFailReason', @iTmp0 OUT
PRINT 'ReadFrame fail reason = ' + @iTmp0
EXEC sp_OAGetProperty @ws, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonTickData
RETURN
END
PRINT 'Success.'
-- The output of the above code is shown here:
-- {
-- "echo_req": {
-- "subscribe": 1,
-- "ticks": "R_50"
-- },
-- "msg_type": "tick",
-- "tick": {
-- "ask": "373.8725",
-- "bid": "373.8325",
-- "epoch": "1517437456",
-- "id": "2d3dedf1-45e2-66a5-d0d3-72bd7f0a6ea9",
-- "quote": "373.8525",
-- "symbol": "R_50"
-- }
-- }
--
-- {
-- "echo_req": {
-- "subscribe": 1,
-- "ticks": "R_50"
-- },
-- "msg_type": "tick",
-- "tick": {
-- "ask": "373.7952",
-- "bid": "373.7552",
-- "epoch": "1517437458",
-- "id": "2d3dedf1-45e2-66a5-d0d3-72bd7f0a6ea9",
-- "quote": "373.7752",
-- "symbol": "R_50"
-- }
-- }
--
-- {
-- "echo_req": {
-- "subscribe": 1,
-- "ticks": "R_50"
-- },
-- "msg_type": "tick",
-- "tick": {
-- "ask": "373.8638",
-- "bid": "373.8238",
-- "epoch": "1517437460",
-- "id": "2d3dedf1-45e2-66a5-d0d3-72bd7f0a6ea9",
-- "quote": "373.8438",
-- "symbol": "R_50"
-- }
-- }
--
-- {
-- "echo_req": {
-- "subscribe": 1,
-- "ticks": "R_50"
-- },
-- "msg_type": "tick",
-- "tick": {
-- "ask": "373.7636",
-- "bid": "373.7236",
-- "epoch": "1517437462",
-- "id": "2d3dedf1-45e2-66a5-d0d3-72bd7f0a6ea9",
-- "quote": "373.7436",
-- "symbol": "R_50"
-- }
-- }
--
-- {
-- "echo_req": {
-- "subscribe": 1,
-- "ticks": "R_50"
-- },
-- "msg_type": "tick",
-- "tick": {
-- "ask": "373.6870",
-- "bid": "373.6470",
-- "epoch": "1517437464",
-- "id": "2d3dedf1-45e2-66a5-d0d3-72bd7f0a6ea9",
-- "quote": "373.6670",
-- "symbol": "R_50"
-- }
-- }
--
-- Success.
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json
EXEC @hr = sp_OADestroy @jsonTickData
END
GO