(SQL Server) Subscribe to Multiple Bitfinex Tickers on One Connection

Subscribes to multiple ticker symbols on the public Bitfinex websocket ticker channel and receives ticker updates.

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 @ws int
    -- Use "Chilkat_9_5_0.WebSocket" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- Connect to api.bitfinex.com
    DECLARE @success int
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    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