SQL Server
SQL Server
Firebase Receive Server-Sent Events (text/event-stream)
See more Firebase Examples
Demonstrates how to start receiving server-sent events and update your JSON database with each event.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 @sTmp1 nvarchar(4000)
DECLARE @success int
SELECT @success = 0
-- 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
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 = 0
BEGIN
EXEC sp_OAGetProperty @fac, 'LastErrorText', @sTmp0 OUT
PRINT @sTmp0
EXEC @hr = sp_OADestroy @fac
RETURN
END
DECLARE @rest int
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.
EXEC sp_OAMethod @rest, 'Connect', @success OUT, 'chilkat.firebaseio.com', 443, 1, 1
IF @success = 0
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
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 @urlStr nvarchar(4000)
EXEC sp_OAGetProperty @rest, 'LastRedirectUrl', @urlStr OUT
DECLARE @url int
EXEC @hr = sp_OACreate 'Chilkat.Url', @url OUT
EXEC sp_OAMethod @url, 'ParseUrl', @success OUT, @urlStr
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
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 @fac
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authGoogle
EXEC @hr = sp_OADestroy @url
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 @fac
EXEC @hr = sp_OADestroy @rest
EXEC @hr = sp_OADestroy @authGoogle
EXEC @hr = sp_OADestroy @url
EXEC @hr = sp_OADestroy @rest2
RETURN
END
-- 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 @url
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 @url
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
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
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
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 @url
EXEC @hr = sp_OADestroy @rest2
EXEC @hr = sp_OADestroy @jsonDb
EXEC @hr = sp_OADestroy @eventStream
EXEC @hr = sp_OADestroy @sse
END
GO