(SQL Server) Firebase Receive Server-Sent Events (text/event-stream)

Demonstrates how to start receiving server-sent events and update your JSON database with each event.

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)
    DECLARE @sTmp1 nvarchar(4000)
    -- Demonstrates how to begin receiving server-sent events, and to update
    -- your JSON database for each event.

    -- This example requires the Chilkat API to have been previously unlocked.
    -- See Global Unlock Sample for sample code.

    -- This example assumes a JWT authentication token, if required, has been previously obtained.
    -- See Get Firebase Access Token from JSON Service Account Private Key for sample code.

    -- Load the previously obtained Firebase access token into a string.
    DECLARE @fac int
    -- Use "Chilkat_9_5_0.FileAccess" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.FileAccess', @fac OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @accessToken nvarchar(4000)
    EXEC sp_OAMethod @fac, 'ReadEntireTextFile', @accessToken OUT, 'qa_data/tokens/firebaseToken.txt', 'utf-8'
    EXEC sp_OAGetProperty @fac, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        RETURN
      END

    DECLARE @rest int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest OUT

    -- Make the initial connection (without sending a request yet).
    -- Once connected, any number of requests may be sent.  It is not necessary to explicitly
    -- call Connect before each request.  
    DECLARE @success int
    EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat.firebaseio.com', 443, 1, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END
    DECLARE @authGoogle int
    -- Use "Chilkat_9_5_0.AuthGoogle" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.AuthGoogle', @authGoogle OUT

    EXEC sp_OASetProperty @authGoogle, 'AccessToken', @accessToken
    EXEC sp_OAMethod @rest, 'SetAuthGoogle', @success OUT, @authGoogle

    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Accept', 'text/event-stream'
    EXEC sp_OAMethod @rest, 'AddHeader', @success OUT, 'Cache-Control', 'no-cache'

    DECLARE @responseBody nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @responseBody OUT, 'GET', '/.json'

    -- A 307 redirect response is expected.
    EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
    IF @iTmp0 <> 307
      BEGIN

        EXEC sp_OAGetProperty @rest, 'ResponseStatusCode', @iTmp0 OUT
        PRINT 'Unexpected response code: ' + @iTmp0

        PRINT @responseBody

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        RETURN
      END

    -- Get the redirect URL
    DECLARE @url int
    EXEC sp_OAMethod @rest, 'RedirectUrl', @url OUT
    EXEC sp_OAGetProperty @rest, 'LastMethodSuccess', @iTmp0 OUT
    IF @iTmp0 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        RETURN
      END

    EXEC sp_OAGetProperty @url, 'Host', @sTmp0 OUT
    PRINT 'redirect URL domain: ' + @sTmp0

    EXEC sp_OAGetProperty @url, 'Path', @sTmp0 OUT
    PRINT 'redirect URL path: ' + @sTmp0

    EXEC sp_OAGetProperty @url, 'Query', @sTmp0 OUT
    PRINT 'redirect URL query params: ' + @sTmp0

    EXEC sp_OAGetProperty @url, 'PathWithQueryParams', @sTmp0 OUT
    PRINT 'redirect URL path with query params: ' + @sTmp0

    -- Our text/event-stream will be obtained from the redirect URL...
    DECLARE @rest2 int
    -- Use "Chilkat_9_5_0.Rest" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Rest', @rest2 OUT

    EXEC sp_OAGetProperty @url, 'Host', @sTmp0 OUT
    EXEC sp_OAMethod @rest2, 'Connect', @success OUT, @sTmp0, 443, 1, 1
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @url

        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        EXEC @hr = sp_OADestroy @rest2
        RETURN
      END

    EXEC sp_OAMethod @rest2, 'AddHeader', @success OUT, 'Accept', 'text/event-stream'
    EXEC sp_OAMethod @rest2, 'AddHeader', @success OUT, 'Cache-Control', 'no-cache'

    -- Add the redirect query params to the request
    EXEC sp_OAGetProperty @url, 'Query', @sTmp0 OUT
    EXEC sp_OAMethod @rest2, 'AddQueryParams', @success OUT, @sTmp0

    -- In our case, we don't actually need the auth query param,
    -- so remove it.
    EXEC sp_OAMethod @rest2, 'RemoveQueryParam', @success OUT, 'auth'

    -- Send the request.  (We are only sending the request here.
    -- We are not yet getting the response because the response
    -- will be a text/event-stream.)
    EXEC sp_OAGetProperty @url, 'Path', @sTmp0 OUT
    EXEC sp_OAMethod @rest2, 'SendReqNoBody', @success OUT, 'GET', @sTmp0
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @url

        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        EXEC @hr = sp_OADestroy @rest2
        RETURN
      END

    EXEC @hr = sp_OADestroy @url

    -- Read the response header.  
    -- We want to first get the response header to see if it's a successful
    -- response status code.  If not, then the response will not be a text/event-stream
    -- and we should read the response body normally.
    DECLARE @responseStatusCode int
    EXEC sp_OAMethod @rest2, 'ReadResponseHeader', @responseStatusCode OUT
    IF @responseStatusCode < 0
      BEGIN
        EXEC sp_OAGetProperty @rest2, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        EXEC @hr = sp_OADestroy @rest2
        RETURN
      END

    -- If successful, a 200 response code is expected.
    -- If the reponse code is not 200, then read the response body and fail..
    IF @responseStatusCode <> 200
      BEGIN

        PRINT 'Response Code: ' + @responseStatusCode

        EXEC sp_OAGetProperty @rest2, 'ResponseStatusText', @sTmp0 OUT
        PRINT 'Response Status Text: ' + @sTmp0

        EXEC sp_OAGetProperty @rest2, 'ResponseHeader', @sTmp0 OUT
        PRINT 'Response Header: ' + @sTmp0
        EXEC sp_OAMethod @rest2, 'ReadRespBodyString', @responseBody OUT
        EXEC sp_OAGetProperty @rest2, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 = 1
          BEGIN

            PRINT 'Error Response Body: ' + @responseBody
          END

        PRINT 'Failed.'
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        EXEC @hr = sp_OADestroy @authGoogle
        EXEC @hr = sp_OADestroy @rest2
        RETURN
      END

    -- For this example, our JSON database will be empty at the beginning.
    -- The incoming events (put and patch) will be applied to this database.
    DECLARE @jsonDb int
    -- Use "Chilkat_9_5_0.JsonObject" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @jsonDb OUT

    -- Make sure to set the JSON path delimiter to "/".  The default is "." and this
    -- is not compatible with Firebase paths.
    EXEC sp_OASetProperty @jsonDb, 'DelimiterChar', '/'

    -- At this point, we've received the response header.  Now it's time to begin
    -- receiving the event stream.  We'll start a background thread to read the 
    -- stream.  (Our main application (foreground) thread can cancel it at any time.)  
    -- While receiving in the background thread, our foreground thread can read the stream
    -- as it desires..
    DECLARE @eventStream int
    -- Use "Chilkat_9_5_0.Stream" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.Stream', @eventStream OUT

    -- This sse object will be used as a helper to parse the server-sent event stream.
    DECLARE @sse int
    -- Use "Chilkat_9_5_0.ServerSentEvent" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.ServerSentEvent', @sse OUT

    DECLARE @task int
    EXEC sp_OAMethod @rest2, 'ReadRespBodyStreamAsync', @task OUT, @eventStream, 1
    EXEC sp_OAMethod @task, 'Run', @success OUT

    -- For this example, we'll just read a few events, and then cancel the
    -- async task.
    DECLARE @count int
    SELECT @count = 0
    EXEC sp_OAGetProperty @task, 'Finished', @iTmp0 OUT
    WHILE (@count < 3) and (@iTmp0 = 0)
      BEGIN

        -- Get the next event, which is a series of text lines ending with
        -- a blank line. 
        -- Note: This method blocks the calling thread until a message arrives.
        -- a program might instead periodically check the availability of
        -- data via the stream's DataAvailable property, and then do the read.

        -- An alternative to writing a while loop to read the event stream
        -- would be to setup some sort of timer event in your program (using whatever timer functionality
        -- is provided in a programming language/environment), to periodically check the eventStream's
        -- DataAvailable property and consume the incoming event.
        DECLARE @eventStr nvarchar(4000)
        EXEC sp_OAMethod @eventStream, 'ReadUntilMatch', @eventStr OUT, CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
        EXEC sp_OAGetProperty @eventStream, 'LastMethodSuccess', @iTmp0 OUT
        IF @iTmp0 <> 1
          BEGIN
            EXEC sp_OAGetProperty @eventStream, 'LastErrorText', @sTmp0 OUT
            PRINT @sTmp0
            -- Force the loop to exit by setting the count to a high number.
            SELECT @count = 99999
          END
        ELSE
          BEGIN


            PRINT 'Event: [' + @eventStr + ']'

            -- We have an event. Let's update our local copy of the JSON database.
            EXEC sp_OAMethod @sse, 'LoadEvent', @success OUT, @eventStr
            IF @success <> 1
              BEGIN

                PRINT 'Failed to load sse event: ' + @eventStr
              END
            ELSE
              BEGIN
                -- Now we can easily access the event name and data, and apply it to our JSON database:
                EXEC sp_OAGetProperty @sse, 'EventName', @sTmp0 OUT
                EXEC sp_OAGetProperty @sse, 'Data', @sTmp1 OUT
                EXEC sp_OAMethod @jsonDb, 'FirebaseApplyEvent', @success OUT, @sTmp0, @sTmp1
                IF @success <> 1
                  BEGIN

                    EXEC sp_OAGetProperty @sse, 'EventName', @sTmp0 OUT

                    EXEC sp_OAGetProperty @sse, 'Data', @sTmp1 OUT
                    PRINT 'Failed to apply event: ' + @sTmp0 + ': ' + @sTmp1
                  END
                ELSE
                  BEGIN

                    EXEC sp_OAGetProperty @sse, 'EventName', @sTmp0 OUT

                    EXEC sp_OAGetProperty @sse, 'Data', @sTmp1 OUT
                    PRINT 'Successfully applied event: ' + @sTmp0 + ': ' + @sTmp1
                  END
              END
          END
        SELECT @count = @count + 1
      END

    -- Make sure the background task is cancelled if still running.
    EXEC sp_OAMethod @task, 'Cancel', @success OUT

    EXEC @hr = sp_OADestroy @task

    -- Examine the JSON database after applying events..
    EXEC sp_OASetProperty @jsonDb, 'EmitCompact', 0

    PRINT '----'
    EXEC sp_OAMethod @jsonDb, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authGoogle
    EXEC @hr = sp_OADestroy @rest2
    EXEC @hr = sp_OADestroy @jsonDb
    EXEC @hr = sp_OADestroy @eventStream
    EXEC @hr = sp_OADestroy @sse


END
GO