Use OPENJSON to Insert Tag Values

-- Author:      Opto 22
-- Create date: September 2016
-- IMPORTANT:   The OLE Automation stored procs in SQL Server must be enabled 
--              for this code sample to work. For more information, see the 
--                 MSDN website, Ole Automation Procedures Server Configuration Option,
--              https://msdn.microsoft.com/en-us/library/ms191188.aspx
-- Description: Creates a stored proc that
--              o Calls the Opto 22 REST endpoint defined in the calling query
--              o Displays the HTTP status response 
--              o Creates a temp table for the JSON string response
--              o Displays the response  
--              o Performs error handling and clean up
--
-- You can extend this sample, for example, by inserting the records into a permanent table 
-- and adding error handling specific to the values you are retrieving
--
-- Set ISO behaviors for comparison operators and identifiers for literal strings
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Checks to see if a procedure with this name already exists.
-- If true, drops the existing procedure before creating a new one.
IF EXISTS ( SELECT * FROM sys.objects WHERE type = 'P' AND name= '__o22_http_request_json') 
DROP PROCEDURE __o22_http_request_json
GO
-- Create stored proc and variables for passed-in parameters
CREATE PROCEDURE [dbo].[__o22_http_request_json] 
      @uri			VARCHAR(2000)	= '',      
      @method_name		VARCHAR(50)	= '',	-- Input should be either GET or POST
      @request_text		VARCHAR(MAX)	= '',	-- JSON to send to endpoint
      @username			NVARCHAR(100),		-- Domain\UserName or UserName 
      @password			NVARCHAR(100),          -- Password in plain text
      @response_text		VARCHAR(MAX) OUTPUT	-- Output value: response from the call to the endpoint
AS
BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON
	
    -- Error handling (Either GET or POST must be passed in)
	IF (@method_name = '')
	BEGIN
		  SELECT FailPoint = 'Method Name must be set'
		  RETURN
	END

	SET @response_text = 'FAILED'
	
	-- Declare variables for OLE object creation (needed for handling returned codes and data)
	DECLARE 
		@object_id	INT,
		@h_result	INT,
		@source		VARCHAR(255), 
		@desc		VARCHAR(255) 

	-- Create instance of OLE object (needed for handling returned codes and data)
	EXEC @h_result = sp_OACreate 'MSXML2.ServerXMLHTTP', @object_id OUT

	-- Error handling (OLE object was not created)
	IF (@h_result <> 0 )
	BEGIN
		EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT

		SELECT      
			h_result		= CONVERT(VARBINARY(4), @h_result), 
			source			= @source, 
			[description]		= @desc, 
			fail_point		= 'Create failed', 
			method_name		= @method_name 
		
		GOTO destroy 
		RETURN
	END

	-- Open the endpoint defined in the calling query (GetValues.sql)  
	EXEC @h_result = sp_OAMethod @object_id, 'open', NULL, @method_name, @uri, 'false', @username, @password
    
	-- Error handling (Open endpoint failed)
	IF (@h_result <> 0 )
	BEGIN
		EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT

		SELECT      
			h_result		= convert(VARBINARY(4), @h_result), 
			source			= @source, 
			[description]		= @desc, 
			fail_point		= 'Open failed', 
			method_name		= @method_name 

		GOTO destroy 
		RETURN
	END
    -- Perform POST (not exercised in the Insert Tag Values into SQL Server 2016 Table code sample)
	IF (@method_name = 'POST')
	BEGIN
		-- SET request headers 
		EXEC @h_result = sp_OAMethod @object_id, 'setRequestHeader', NULL, 'Content-Type', 'application/json'

	    -- 
		IF (@h_result <> 0)
		BEGIN
			EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT
			  
			SELECT      
				h_result		= convert(VARBINARY(4), @h_result), 
				source			= @source, 
				[description]		= @desc, 
				fail_point		= 'SetRequestHeader failed', 
				method_name		= @method_name 

			GOTO destroy 
			RETURN
		END
        -- 
		DECLARE @len INT
		SET @len = len(@request_text) 

		-- 
		EXEC @h_result = sp_OAMethod @object_id, 'setRequestHeader', NULL, 'Content-Length', @len 

		-- 
		IF (@h_result <> 0)
		BEGIN
			EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT

			SELECT      
				h_result		= convert(VARBINARY(4), @h_result), 
				source			= @source, 
				[description]		= @desc, 
				fail_point		= 'SetRequestHeader failed', 
				method_name		= @method_name 

			GOTO destroy 
			RETURN
		END

	END

	-- Send the GET request defined in the calling query (GetValues.sql)
	EXEC @h_result = sp_OAMethod @object_id, 'send', NULL, @request_text 

	-- Error handling (Send failed)
	IF (@h_result <> 0)
	BEGIN

		EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT

		SELECT      
			h_result		= convert(VARBINARY(4), @h_result), 
			source			= @source, 
			[description]		= @desc, 
			fail_point		= 'Send failed', 
			method_name		= @method_name 
		
		GOTO destroy 
		RETURN

	END
    -- Declare variables for the HTTP status response
	DECLARE @statusText VARCHAR(1000), @status VARCHAR(1000) 

	-- Get and display the property values associated with the HTTP status code and status text
	EXEC sp_OAGetProperty @object_id, 'Status', @status OUT
	EXEC sp_OAGetProperty @object_id, 'StatusText', @statusText OUT
	SELECT 
		[status]	= @status, 
		status_text = @statusText, 
		method_name = @method_name 

	-- Declare a variable for the JSON response
	DECLARE @Result nvarchar(max)
	-- Create a temp table for the JSON response 
	CREATE table #temptable
	(
		responseText nvarchar(max)
	)
	-- Insert JSON response into the temp table 
	INSERT #temptable 
	( 
		responseText
	)
	-- Get and display the property value associated with the JSON response string 
	EXEC @Result = sp_OAGetProperty @object_id, 'responseText' 
	SELECT @response_text = responseText FROM #temptable

	-- TODO: If desired, insert the JSON string into a permanent table in your database

	-- Error handling
	IF (@h_result <> 0)
	BEGIN
		EXEC sp_OAGetErrorInfo @object_id, @source OUT, @desc OUT

		SELECT      
			h_result		= convert(VARBINARY(4), @h_result), 
			source			= @source, 
			[description]		= @desc, 
			fail_point		= 'ResponseText failed', 
			method_name		= @method_name 
		
		GOTO destroy 
		RETURN
	END
	-- Clean up
	destroy: 
		EXEC sp_OADestroy @object_id 

	SET NOCOUNT OFF
 
END

-- Author:      Opto 22
-- Create date: September 2016
-- NOTE: You must execute http_request_json.sql before running this script
-- Description: A static query that 
--              o Defines an endpoint on an Opto 22 demo controller and logon parameters,
--                and passes them to the http_request_json stored proc
--              o Uses the OPENJSON function to parse and objectify the returned string
--              o Creates a temp table
--              o Inserts the values from the JSON response into the temporary table
--              o Displays responses in the Results window
--
-- Ideas for extending this sample: 
--   o Call an endpoint on your own controller
--   o Insert the returned values in a permanent table in your database
--   o Perform additional error handling


-- Declare a variable for the JSON reponse from the call to the endpoint
DECLARE 
	@response_text	VARCHAR(MAX) 

-- Pass parameters to the __o22_http_request_json stored procedure
EXEC [__o22_http_request_json] 
    -- TODO: Replace the hostname with your controller's IP address or hostname 
	-- TODO: You can also change the endpoint to get different data from your controller
	@uri		= 'http://restpac.groov.com/api/v1/device/strategy/tables/floats/KPI_float_values', 
	-- This query always performs a GET 
	@method_name	= 'GET', 
	@request_text	= NULL,
	-- TODO: If you changed the hostname to your controller, replace the username and 
	-- password with the logon credentials for your implementation of the SNAP PAC REST API
	@username	= 'ro', 
	@password	= 'ro', 
	-- Parameter used to return data from the stored procedure
	@response_text	= @response_text OUT
            
-- Display the returned JSON string RecordSet in the Results window
SELECT json_response_kpi_value = @response_text

-- Declare a table variable for the data from the controller
DECLARE @kpi_value TABLE 	
(
    -- Add a column to show when the value was added to the table
	[create_timestamp] DATETIME DEFAULT (GETDATE()),
	[value]	VARCHAR(255)
)
-- Insert the parsed JSON into the @kpi_value table variable
INSERT @kpi_value
(
	[value] 
) 

-- Use OPENJSON function to parse and objectify the returned JSON string
SELECT 
	[value] 
FROM
	OPENJSON(@response_text) -- function OPENJSON() takes a JSON response and returns the JSON in rowset data format

-- TODO: Insert returned values into a permanent table in your database
-- TODO: Make sure that the data type in your database supports the returned data type 

-- Display the contents of @kpi_value in the Results window
SELECT [value], [create_timestamp] FROM @kpi_value