Using the SQL Connector to query Layer data in SQL Server

The SQL Connector is a Layer extension for SQL Server which allows users with database experience query data available on our API with local data.

This allows customers to extend & customise their Layer reporting set, expose data to customers via their own applications, and much more.

Getting Started

In order to use the SQL Server Connector for Layer data, you'll need to be running SQL Server 2016 or newer. You can download the developer edition for free in order to start exploring your Layer data today.

Once you've got a SQL Server instance set up, download the SQL Connector here.

Next, right click SQLServerAPI.zip and extract to a directory, e.g. C:\temp\SQLTools

Run the install script below in SQL Server Management Studio and change all references to TempDB to a database on your server

/* =============================== */
/*  Begin One-Time Install Script  */
/* =============================== */ 

USE TestDB -- Replace this with your DB 

GO 

sp_configure 'clr enabled', 1 -- The common language runtime integration feature is off by default, and must be enabled in order to use this component

RECONFIGURE 

GO 

ALTER DATABASE TESTDB -- Replace this with your DB

SET TRUSTWORTHY ON

GO 

DROP PROCEDURE IF EXISTS [Layer_SQLServerAPI_APIGet]

DROP ASSEMBLY IF EXISTS [Layer.SQLServerAPI]

DROP ASSEMBLY IF EXISTS [Newtonsoft.Json]

DROP ASSEMBLY IF EXISTS [System.Runtime.Serialization] 

GO

CREATE ASSEMBLY [System.Runtime.Serialization] AUTHORIZATION dbo FROM N'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE 

GO

CREATE ASSEMBLY [Newtonsoft.Json] AUTHORIZATION dbo FROM N'C:\temp\SQLTools\Newtonsoft.Json.dll' WITH PERMISSION_SET = UNSAFE 
    
GO 

CREATE ASSEMBLY [Layer.SQLServerAPI] AUTHORIZATION dbo FROM N'C:\temp\SQLTools\Layer.SQLServerAPI.dll' WITH PERMISSION_SET = UNSAFE

GO

CREATE PROCEDURE [dbo].[Layer_SQLServerAPI_APIGet] @URL NVARCHAR(MAX) NULL
,@Headers NVARCHAR(MAX) NULL

AS

EXTERNAL NAME [Layer.SQLServerAPI].[StoredProcedures].[APIGet]

GO

/* ==================== */
/*  End Install Script  */
/* ==================== */

If you extracted to a different directory to the one above, change references of c:\Temp\SQLTools to the directory you copied the contents of sqlserverapi.zip to

Run the install script below to register the scripts on your SQL Server instance

Grab your API tokens from The Layer API

Add these to the sample below and start writing SQL API queries within your own environment:

--------------------------------------------------------------
-- Author       Nick Kewney [[email protected]]
-- Created      06/07/2020
-- Purpose      Sample script for Layer SQL API Connector
-- Copyright © 2020, Layer Systems Ltd, All Rights Reserved

--------------------------------------------------------------

/* ========================= */
/*  Begin API Query Example  */
/* ========================= */

use TestDB

-- Grab the report URL from the custom report & modify the querystring parameters if required

DECLARE @apiUrl VARCHAR(max) = 'https://webapi.thelayer.com/api/CustomReport/Get?reportId=report_guid&DateFrom=01/06/2020&DateTo=03/07/2020&RawJsonResponse=true&TableIndex=0'

DECLARE @apiHeaders NVARCHAR(max) = '[ 
    {
        "Name": "Token1",
        "Value" :"xxx"
    },{
        "Name": "Token2",
        "Value" :"xxx"
    }]';

DECLARE @response AS TABLE ([json] NVARCHAR(max)) 

INSERT INTO @response

EXEC [dbo].[Layer_SQLServerAPI_APIGet] @apiUrl, @apiHeaders

SELECT * FROM OPENJSON((SELECT [json] FROM @response)) WITH (
[Action] NVARCHAR(max) '$."Action"',
[Sales Order Reference] NVARCHAR(max) '$."Sales Order Reference"',
[Service Description] NVARCHAR(max) '$."Service Description"') a 

/* ======================= */
/*  End API Query Example  */
/* ======================= */