SQL Server
SQL Server
Firebase GET - Reading Data
See more Firebase Examples
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 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
-- 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.
-- 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 <> 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.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.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.StringBuilder', @piggyPath OUT
DECLARE @shallow int
EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @shallow OUT
DECLARE @piggyData int
EXEC @hr = sp_OACreate 'Chilkat.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