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 */ /* ======================= */