Download Code Sample

Overview

The getvalues.sql code sample uses a stored proc (that you create by running http_request_json.sql) and Transact-SQL functions in Microsoft® SQL Server® 2016 to:

  • Call an Opto 22 REST API endpoint
  • Parse and objectify the returned JSON string
  • Import the values into a SQL Server temp table

The sample calls an endpoint at an Opto 22 demo controller.

Prerequisites

Steps

In SQL Server Management Studio:

  1. Ensure that Ole Automation stored procedures are enabled.
  2. Open and execute http_request_json.sql to create the stored procedure.
  3. Open and execute get_values.sql to call the endpoint at Opto 22’s restpac.groov controller and insert the returned values in a temp table.

Get values SQL

Code Samples

http_request_json.sql


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

get_values.sql


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

Enable Ole Automation Procedures in SQL Server

Some of the code samples on our website use SQL Server’s OLE Automation stored procedures. Because those procs are disabled by default, we’ve provided a Microsoft code sample to enable them. For more information, see Microsoft’s Developer Network, https://msdn.microsoft.com/en-us/library/ms191188.aspx.

To run the code sample, you must have system administrator privileges in SQL Server.

Enable OLE Procedures in SQL Server