(SQL Server) Firebase GET - Reading Data

Demonstrates how to read parts of a Firebase JSON database. The data used in this example is at Chilkat Firebase Pigs Database, and is shown here:

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
    DECLARE @sTmp0 nvarchar(4000)
    -- Demonstrates how to read parts of a Firebase JSON database.

    -- 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.

    DECLARE @success int

    -- Load the previously obtained Firebase access token into a string.
    DECLARE @fac int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.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 <> 1
      BEGIN
        EXEC sp_OAGetProperty @rest, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @fac
        EXEC @hr = sp_OADestroy @rest
        RETURN
      END
    -- If authentication is required...
    DECLARE @authGoogle int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.AuthGoogle', @authGoogle OUT

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

    -- Chilkat's sample data (pig-rescue data) is publicly readable at: https://chilkat.firebaseio.com/.json

    -- Let's get the animals with the shallow parameter so we can see how many pigs exist.
    DECLARE @jsonResponse nvarchar(4000)
    EXEC sp_OAMethod @rest, 'FullRequestNoBody', @jsonResponse OUT, 'GET', '/pig-rescue/animal.json?shallow=true'
    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

    -- The JSON returned should look like this:  
    -- {"-KI3bD-FU_Dake7sYOiP":true,"-KI3bD-FU_Dake7sYOiT":true,"-KI3bD-FU_Dake7sYOiS":true,"-KI3bD-FU_Dake7sYOiU":true,"-KI3bD-FU_Dake7sYOiV":true,"-KI3bD-FU_Dake7sYOiR":true,"-KI3bD-FU_Dake7sYOiQ":true}

    PRINT @jsonResponse

    -- Parse the response so we can iterate over each pig in the database..
    DECLARE @piggyPath int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.StringBuilder', @piggyPath OUT

    DECLARE @shallow int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @shallow OUT

    DECLARE @piggyData int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.JsonObject', @piggyData OUT

    EXEC sp_OAMethod @shallow, 'Load', @success OUT, @jsonResponse
    DECLARE @count int
    EXEC sp_OAGetProperty @shallow, 'Size', @count OUT
    DECLARE @i int
    SELECT @i = 0
    WHILE @i < @count
      BEGIN

        -- Get each individual pig's data.
        EXEC sp_OAMethod @piggyPath, 'Clear', NULL
        EXEC sp_OAMethod @piggyPath, 'Append', @success OUT, '/pig-rescue/animal/'
        EXEC sp_OAMethod @shallow, 'NameAt', @sTmp0 OUT, @i
        EXEC sp_OAMethod @piggyPath, 'Append', @success OUT, @sTmp0
        EXEC sp_OAMethod @piggyPath, 'Append', @success OUT, '/.json'

        DECLARE @piggyJson nvarchar(4000)
        EXEC sp_OAMethod @piggyPath, 'GetAsString', @sTmp0 OUT
        EXEC sp_OAMethod @rest, 'FullRequestNoBody', @piggyJson OUT, 'GET', @sTmp0
        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
            EXEC @hr = sp_OADestroy @piggyPath
            EXEC @hr = sp_OADestroy @shallow
            EXEC @hr = sp_OADestroy @piggyData
            RETURN
          END

        -- Show this piggy's data...
        -- An example of one pig's data is shown here:
        -- {"birth":"February, 1998","from":"Middle Ave.","gender":"F","in-date":"January, 2000",
        --   "name":"Molly II","picture":{"caption":"Molly in the Pasture","description":"Black pig","file":"molly_th.jpg"},
        --   "species":"pot belly pig","type":"Cathy's Herd"}


        PRINT '---- ' + @i + ' ----'

        PRINT @piggyJson

        -- Let's get the pig's name, and the caption of the picture.
        EXEC sp_OAMethod @piggyData, 'Load', @success OUT, @piggyJson

        EXEC sp_OAMethod @piggyData, 'StringOf', @sTmp0 OUT, 'name'
        PRINT 'name: ' + @sTmp0

        EXEC sp_OAMethod @piggyData, 'StringOf', @sTmp0 OUT, 'picture.caption'
        PRINT 'caption: ' + @sTmp0

        SELECT @i = @i + 1
      END

    -- Note: In many of the Chilkat examples, you may notice strange ways
    -- of doing something that should be simpler and shorter.  For example,
    -- building the piggyPath (above) could've been written differently,
    -- with some simple string concatenation.
    -- 
    -- The reason is that the Chilkat examples are written in a 
    -- proprietary "example code" scripting language,
    -- and then automatically generated to each of the different programming
    -- languages you see on example-code.com.  The code generation is
    -- limited in what it can do.  For example, string concatentation
    -- is not yet a feature of the "example code" scripting language (as of May 2016), 
    -- and therefore you won't see the use of a programming language's string
    -- concatentation operators in any example.  
    -- 

    EXEC @hr = sp_OADestroy @fac
    EXEC @hr = sp_OADestroy @rest
    EXEC @hr = sp_OADestroy @authGoogle
    EXEC @hr = sp_OADestroy @piggyPath
    EXEC @hr = sp_OADestroy @shallow
    EXEC @hr = sp_OADestroy @piggyData


END
GO