SQL Server
SQL Server
Subscribe to Multiple Bitfinex Tickers on One Connection
See more WebSocket Examples
Subscribes to multiple ticker symbols on the public Bitfinex websocket ticker channel and receives ticker updates.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 api.bitfinex.com
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'api.bitfinex.com', 443, 1, 0
EXEC sp_OAMethod @ws, 'UseConnection', @success OUT, @rest
EXEC sp_OAMethod @ws, 'AddClientHeaders', @success OUT
DECLARE @responseBody nvarchar(4000)
EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/ws'
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
-- After connecting, the bitfinex websocket server will send
-- an info message that contains the actual version of the websocket stream.
-- Receive that message..
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
RETURN
END
-- We should get this:
-- {"event":"info","version":1.1,"platform":{"status":1}}
EXEC sp_OAMethod @ws, 'GetFrameData', @sTmp0 OUT
PRINT @sTmp0
-- Subscribe to the BTCUSD ticker feed.
-- See https://docs.bitfinex.com/docs for more information.
DECLARE @json1 int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json1 OUT
EXEC sp_OAMethod @json1, 'AppendString', @success OUT, 'event', 'subscribe'
EXEC sp_OAMethod @json1, 'AppendString', @success OUT, 'channel', 'ticker'
EXEC sp_OAMethod @json1, 'AppendString', @success OUT, 'pair', 'BTCUSD'
DECLARE @finalFrame int
SELECT @finalFrame = 1
EXEC sp_OAMethod @json1, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @ws, 'SendFrame', @success OUT, @sTmp0, @finalFrame
-- Subscribe to the ETHUSD ticker feed.
DECLARE @json2 int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json2 OUT
EXEC sp_OAMethod @json2, 'AppendString', @success OUT, 'event', 'subscribe'
EXEC sp_OAMethod @json2, 'AppendString', @success OUT, 'channel', 'ticker'
EXEC sp_OAMethod @json2, 'AppendString', @success OUT, 'pair', 'ETHUSD'
EXEC sp_OAMethod @json2, 'Emit', @sTmp0 OUT
EXEC sp_OAMethod @ws, 'SendFrame', @success OUT, @sTmp0, @finalFrame
-- Begin reading the ticker feed.
-- / This will include a single confirmation response for each subscription.
-- We'll just read the 1st 5 updates and then exit..
DECLARE @receivedFinalFrame int
SELECT @receivedFinalFrame = 0
DECLARE @numUpdatesReceived int
SELECT @numUpdatesReceived = 0
WHILE @numUpdatesReceived < 7
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 @json1
EXEC @hr = sp_OADestroy @json2
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 @receivedText nvarchar(4000)
EXEC sp_OAMethod @ws, 'GetFrameData', @receivedText OUT
PRINT @receivedText
-- Should receive a line of text such as this:
-- [2751,7349,36.34269559,7349.1,41.01777063,-116.2,-0.0156,7349.1,22188.26055319,7560,7270.5]
-- Initially, a single confirmation response for each subscription would be received, such as this:
-- {"event":"subscribed","channel":"ticker","chanId":810,"pair":"BTCUSD"}
SELECT @numUpdatesReceived = @numUpdatesReceived + 1
END
END
-- This is sample output:
-- You can see that the 1st message received is the subscription confirmation for the BTCUSD subscribe request.
-- The next message is a BTCUSD ticker update.
-- The next message is the confirmation for the ETHUSD subscription.
-- The next message is an ETHUSD update.
-- The BTCUSD channel ID is "537", and the ETHUSD channel ID is "700". The channel IDs are provided in the
-- subscription confirmation messages. Each ticker update begins with the channel ID..
-- {"event":"subscribed","channel":"ticker","chanId":537,"pair":"BTCUSD"}
-- [537,6457.9,79.59102302,6458,22.49395741,-24.1,-0.0037,6457.9,13644.52820029,6540,6370.76958739]
-- {"event":"subscribed","channel":"ticker","chanId":700,"pair":"ETHUSD"}
-- [700,496.9,247.24493326,496.92,284.09636979,-2.47,-0.0049,496.9,74154.05945261,502.85,486.96]
-- [537,"hb"]
-- [700,"hb"]
-- [700,496.92,233.34338276,496.93,431.78867277,-2.45,-0.0049,496.92,74167.99173586,502.85,486.96]
-- 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 @json1
EXEC @hr = sp_OADestroy @json2
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 @json1
EXEC @hr = sp_OADestroy @json2
RETURN
END
PRINT 'Success.'
EXEC @hr = sp_OADestroy @ws
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @json1
EXEC @hr = sp_OADestroy @json2
END
GO