Práce s HTTP API pomocí databázových procedur

Práce s HTTP API pomocí databázových procedur

Pokud je třeba stáhnout obsah webové adresy z prostředí SQL Serveru, nebo se z něj připojit na webovou službu, lze to udělat Startkovou procedurou DownloadURL (případně jednou z jejích variant). Procedura(-y) je řešena jako CLR knihovna, do aplikační databáze ji lze tedy nahrát buď skriptem, nebo přímým importem DLL knihovny.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.DownloadURL (
    @URL              [nvarchar](1000), 
    @RequestMethod    [nvarchar](10), 
    @Headers          [nvarchar](max) OUTPUT, 
    @Data             [nvarchar](max), 
    @Response         [nvarchar](max) OUTPUT,
    @ResponseStatus   [int] = NULL OUTPUT ,
    @ResponseEncoding [nvarchar](10) = NULL)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.DownloadURL

• @URL - volané URL včetně případných URL parametrů, jehož obsah se stahuje.
• @RequestMethod - použitá metoda HTTP požadavku (requestu). Podporovány jsou metody GET, POST, PUT. Není-li zadáno, použije se metoda GET.
• @Headers - HTTP hlavičky ve formátu XML v syntaxi:

<Headers><Jmeno>hodnota</Jmeno></Headers>

Základní ověření (Basic authentication) se předává v hlavičce ve formátu:

<Headers><Authorization>Basic xxx</Authorization></Headers>

kde xxx je přihlašovací jméno a heslo v Base64 kódování. Získat je lze i z online generátorů např. zde: https://www.debugbear.com/basic-auth-header-generator.

• Parametr je vstupně/výstupní, tj. lze si načítat i hlavičky HTTP odpovědi. Užitečné v případě, že server vrací část či všechna data v hlavičce namísto v těle HTTP odpovědi.
• Kromě obecně uživatelských HTTP hlaviček lze explicitně nastavit tyto:
  • ACCEPT
  • CONTENT-LENGTH
  • CONTENT-TYPE
  • DATE
  • EXPECT
  • HOST
  • USER-AGENT
• @Data - data HTTP požadavku (requestu) předávaná jako řetězec.
• @Response - výstupní parametr s textem HTTP odpovědi (response). V případě chyby při volání obsahuje její text.
• @ResponseStatus - nepovinný výstupní parametr se statusem HTTP odpovědi. Pokud dojde k chybě ještě před HTTP voláním, vrací hodnotu -1 (došlo k jiné runtimě chybě).
• @ResponseEncoding - nepovinný parametr s požadovaným kódováním HTTP odpovědi. Není-li parametr uveden, bere se kódování odpovědi z její vlastnosti CharacterSet. Není-li tato vlastnost dostupná, bude kódování odpovědi napevno UTF-8.

Příklad použití

Mějme e-shop, jehož API je dostupné na adrese https:api.my-eshop.cz. Parametry na odkazu určíme oblast a akci, kterou chceme po rozhraní provést (oblast prodeje, akce stažení seznamu prodejních dokladů). V proměnné //@credentials si sestavíme přihlašovací údaje a spolu s dalšími HTTP hlavičkami (které nám dodal výrobce rozhraní e-shopu) je připravíme do proměnné @headers. Filtrovací podmínky výrobce e-shopu předepisuje předat v datech HTTP požadavku ve formátu JSON, připravíme si je tedy v požadovaném formátu do proměnné @myData. Vše máme připraveno, zavoláme tedy URL s rozhraním a stáhneme (a vypíšeme) si seznam prodejních dokladů.

DECLARE @credentials [nvarchar](4000) = 'Basic dGVzdDp0ZXN0'  --Base64 encoded name/password = test/test
DECLARE @headers     [nvarchar](4000) = '<Headers><Accept>application/x.app.v1.0+json; UTF-8</Accept><Authorization>' + @credentials + '</Authorization></Headers>'
DECLARE @myData   [nvarchar](max) = '{ "type": 0, "dateFrom": "2021-10-01T23:28:56.782Z" }'
DECLARE @Response [nvarchar](max)
DECLARE @ResponseStatus	[int]
Exec [dbo].[DownloadURL] 'https://api.my-eshop.cz/?module=sale&action=list', 'POST', @headers, @myData, @Response OUT
Select @ResponseStatus as HTTPstatus, @Response as HTTPresponse, @headers as HTTPheaders

JWT autentikace

Kromě základního ověřování (Basic Authentication) je nejrozšířenější tzv.JWT autentikace, tedy ověřování pomocí autorizačního tokenu (více např. na https://jwt.io/). Pomocí základního ověření se nejprve získá ověřovací token, který se následně používá v datové komunikaci.

Příklad použití

--první volání slouží k získání autentikačního tokenu (JWT), který potom využívám k volání datových funkcí
DECLARE @URL      NVARCHAR(255) = 'https://api.my-eshop.cz/oauth/token'
DECLARE @headers  NVARCHAR(4000) = '<Headers><Accept></Accept><Authorization>' + @credentials + '</Authorization></Headers>'
DECLARE @Data     NVARCHAR(max) = '{"role": "admin", "type": "client_credentials"}'
DECLARE @Response NVARCHAR(max)
EXEC [dbo].[DownloadURL] @URL, 'POST', @Headers, @Data, @Response OUT
SELECT @Response

--ukázka formátu výsledku (proměnná @Json) a jeho zpracování -> získání autorizačního tokenu
DECLARE @Json        NVARCHAR(max)
DECLARE @AccessToken NVARCHAR(max)  --max 8kB
SET @Json='{ "access_token": "asdASDasdASDasdASDasdASDasdASDasdASD", "token_type": "bearer", "expires_in": 3599, "role": "admin" }'
SELECT @AccessToken = access_token FROM OPENJSON(@Json)
  WITH (   
         access_token	nvarchar(max)	'$.access_token' ,  
         token_type	varchar(10)	'$.token_type',
         expires_in	int		'$.expires_in',
         scope		varchar(10)	'$.scope'
     )

--ukázka použití získaného JWT tokenu v datovém volání
DECLARE @URL      NVARCHAR(255) = 'https://api.my-eshop.cz/invoices'
DECLARE @Headers  NVARCHAR(4000) = '<Headers><Accept>*/*</Accept><Authorization>Bearer ' + @AccessToken + '</Authorization><Content-Type>application/json</Content-Type></Headers>'
DECLARE @Data     NVARCHAR(max) = '{ "data_get": "all"}'
DECLARE @Response NVARCHAR(max)
EXEC [dbo].[DownloadURL] @URL, 'POST', @Headers, @Data, @Response OUT
SELECT @Response

Stahování obsahu URL databázovou procedurou - multipart verze

Pokud je třeba jedním HTTP requestem zároveň poslat data soubory, řeší se to tzv.multipart HTTP requestem. I tento způsob HTTP komunikace lze realizovat z prostředí SQL serveru stejnou CLR knihovnou.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.HTTPPOSTMultipartData (
    @URL             [nvarchar](1000), 
    @Headers         [nvarchar](max), 
    @multipartFormXMLData[nvarchar](max), 
    @fileName        [nvarchar](255), 
    @fileBuffer      [varbinary](max), 
    @fileParamName   [nvarchar](50), 
    @fileContentType [nvarchar](255), 
    @Response        [nvarchar](max) OUTPUT)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.HTTPPOSTMultipartData
go

• @fileName - jméno přenášeného/uploadovaného souboru.
• @fileBuffer - binární obsah přenášeného souboru jako pole bajtů.
• @fileParamName - jméno multipart části se souborem.
• @fileContentType - Content-Type části se souborem. Není-li zadáno, bude application/octet-stream.
• @multipartFormXMLData - multipart data ve formátu XML. Formát:

<formData><name>value</name><name>value</name></formData>

• name = jméno jedné z multipart částí, value = její textový obsah

Příklad použití

DECLARE @credentials NVARCHAR(4000) = 'Basic dGVzdDp0ZXN0'  --Base64 encoded name/password = test/test
DECLARE @headers NVARCHAR(4000) = '<Headers><Accept></Accept><Authorization>' + @credentials + '</Authorization></Headers>'
DECLARE @Data [nvarchar](max) = '{"invoice_id": "ASD123","type": 1,"currency": "CZK","value": "1","customer_id": "TEST","customer_name": "TEST"}'
DECLARE @multipartFormXMLData [nvarchar](max) = '<formData><data>' + @Data + '</data></formData>'
DECLARE @fileContent [varbinary](max)
SELECT @fileContent = FILEIMAGE from [ESO9start_DOC].dbo.Eso9Doc
DECLARE @Response [nvarchar](max)
EXEC [dbo].[HTTPPOSTMultipartData] 'https://api.my-eshop.cz?module=Invoices&action=Insert', @headers, @multipartFormXMLData, 
'faktura.pdf', @fileContent, 'invoice', 'application/octet-stream', @Response OUT
SELECT @Response

Download/upload binárního obsahu URL databázovou procedurou

Předchozí text se týkal případů, kdy se HTTP protokolem přenáší textový obsah.
Pokud potřebujeme přenášet obecně binární obsah (např. obrázek zboží z e-shopu, sken účtenky od dodavatele apod.), lze to udělat Startkovou procedurou DownloadBinURL.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.DownloadBinURL (
    @URL         [nvarchar](1000),
    @RequestMethod [nvarchar](10),      
    @Headers     [nvarchar](max), 
    @Data        [nvarchar](max), 
    @RespHeaders [nvarchar](max) OUTPUT, 
    @Response    [varbinary](max) OUTPUT,
    @ErrDesc     [nvarchar](max) OUTPUT)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.DownloadBinURL

• @URL - volané URL včetně případných URL parametrů, jehož obsah se stahuje.
• @RequestMethod - použitá metoda HTTP požadavku (requestu). Podporovány jsou metody GET (download) a POST (upload). Není-li zadáno, použije se metoda GET.
• @Headers - HTTP hlavičky ve formátu XML, viz procedura DownloadURL
• @RespHeaders - výstupní parametr s HTTP hlavičkami odpovědi ve formátu JSON. Hlavičky mohou obsahovat typ souboru (např. formát obrázku), popř. jeho název a další parametry, které pro další práci se souborem můžeme využít.
• @Response - výstupní parametr s binárním obsahem staženého souboru. V případě chyby bude mít hodnotu null.
• @ErrDesc - výstupní parametr, nepodaří-li se stáhnout soubor, obsahuje popis chyby, k níž při stahování došlo.

Příklad použití

Zkusme si z webu stáhnout obrázek a uložit si jej do DMS ESO9.
Proceduře předáme URL adresu požadovaného obrázku, který po volání dostaneme v proměnné @fileContent. Výstupní parametry si po stažení obrázku pro kontrolu vypíšeme. Obrázek následně pomocí Startkové procedury spDokument_Zalozeni uložíme do DMS v ESO9 a vypíšeme si ID nově založeného dokumentu. Pokud by se jednalo např. o obrázek importovaného zboží, mohu si jej v proceduře rovnou napojit na tabulku ZBOZI pomocí parametrů @TableName a @idTable.

DECLARE @headers NVARCHAR(4000) = '<Headers><CONTENT-TYPE>image/png</CONTENT-TYPE></Headers>'
DECLARE @respHeaders NVARCHAR(max)
DECLARE @idDokument_new INT
DECLARE @fileContent [varbinary](max)
Declare @errDesc [nvarchar](max)
Exec [dbo].[DownloadBinURL] 'https://scripts.eso9.cz/rtm/img/home_bg.jpg', 'GET', @headers, null, @respHeaders OUT, @fileContent OUT, @errDesc OUT
Select DATALENGTH(@fileContent) as length, @errDesc as chyba, @respHeaders as headers

exec dbo.spDokument_Zalozeni
  @TableName='', 
  @idTable=null,
  @FileName='obrazek.jpg',
  @FileImage=@fileContent,
  @idDokument = @idDokument_new OUT
select @idDokument_new as IDDokument

Komunikace s HTTP API s ověřením klientským certifikátem

Pokud je HTTP volání ověřováno klientskými certifikáty, lze k tomu použít proceduru DownloadURLcertAuth. Klientský certifikát, použitý pro ověření, může být uložen např. v DMS.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.DownloadURLcertAuth (
    @URL           [nvarchar](1000), 
    @RequestMethod [nvarchar](10), 
    @Headers       [nvarchar](max), 
    @Data          [nvarchar](max), 
    @Cert          [varbinary](max),
    @CertPwd       [nvarchar](100), 
    @Response      [nvarchar](max) OUTPUT)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.DownloadURLcertAuth

• @Cert - klientský certifikát předávaný jako pole bajtů.
• @CertPwd - heslo ke klientskému certifikátu.

Příklad použití

--stažení URL ověřovaného klientským certifikátem
declare @Response nvarchar(max) 
declare @headers NVARCHAR(4000) = '<Headers><Accept>application/json</Accept></Headers>'
declare @CertGUID varchar(40) = 'abcd1234-4567-5678-abcd-ef1234ef1234'
declare @Cert [varbinary](max)
select @Cert = FILEIMAGE from ESO9start_DOC.dbo.Eso9Doc where FILEGUID = @CertGUID
Exec [dbo].[DownloadURLcertAuth] 'https://testapi.sukl.cz/hsz/v1/pracoviste', 'GET', @headers, '', @Cert, 'SecretPassword', @Response OUT
Select @Response

Komunikace s HTTP API s ověřením OAuth 2.0

Ověřování protokolem OAuth 2.0 je jen zjednodušením obecnější JWT autentikace z první kapitoly. Ověřování OAuth 2.0 spočívá v získání autentikačního tokenu (tzv.Bearer Tokenu) od poskytovatele ověření a následně použití ověřovacího tokenu pro vlastní datovou komunikaci s (obecně) vlastníkem prostředků. Tato dvě HTTP volání umožňuje procedura DownloadURLbearerAuth sloučit do jediného a tedy odstranit parsování autentikační odpovědi a získávání a udržování tokenu.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.DownloadURLbearerAuth (
    @URL           [nvarchar](1000), 
    @AuthURL       [nvarchar](1000), 
    @RequestMethod [nvarchar](10), 
    @Headers       [nvarchar](max) OUTPUT, 
    @AuthHeaders   [nvarchar](1000), 
    @Data          [nvarchar](max), 
    @AuthData      [nvarchar](max), 
    @Response      [nvarchar](max) OUTPUT)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.DownloadURLbearerAuth

• //@AuthURL // - URL pro autentikaci / pro získání Bearer Tokenu.
• @AuthHeaders - HTTP hlavičky použité pro autentikaci (mohou se lišit od HTTP hlaviček pro vlastní datovou komunikaci). Nejsou-li zadány, použijí se hlavičky z parametru @Headers.
• @AuthData - tělo HTTP requestu použité pro získání Bearer Tokenu. Může obsahovat další údaje potřebné pro autentikaci.

Příklad použití

DECLARE @APIVersion NVARCHAR(255) = '9.2'
DECLARE @authURL   NVARCHAR(255) = 'https://login.microsoftonline.com/{{tenantId}}/oauth2/v2.0/authorize'
-- URL použité pro autentikaci = pro získání Bearer tokenu
DECLARE @tokenURL  NVARCHAR(255) = 'https://login.microsoftonline.com/{{tenantId}}/oauth2/v2.0/token'
-- URL použité pro volání funkcí API rozhraní
DECLARE @webAPIURL NVARCHAR(255) = 'https://xxxtest.crm4.dynamics.com/api/data/v{{version}}/'
-- identifikace aplikace a uživatele vůči API
DECLARE @tenantID NVARCHAR(255) = '9251d13e-a086-49a0-8c07-123456789012'
DECLARE @clientID NVARCHAR(255) = '946251d7-04a0-4122-9a46-123456789012'
DECLARE @clientSecret NVARCHAR(255) = 'BzcOyNjYr63S3ZTPoyfuIJ0Fs/xxxYYYzzzYYY='
-- HTTP hlavičky použité pro volání funkcí API rozhraní
DECLARE @Headers  NVARCHAR(4000) 
-- HTTP hlavičky použité pro autentikaci. Pokud nejsou zadány, použijí se pro autentikaci hlavičky z volání API (@Headers)
DECLARE @AuthHeaders NVARCHAR(4000) 
-- HTTP request body použité pro volání funkcí API rozhraní
DECLARE @Data     NVARCHAR(max)
-- HTTP request body použité pro autentikaci
DECLARE @AuthData NVARCHAR(max)
DECLARE @Response NVARCHAR(max)
-- pomocná porměnná pro sestavení finálního URL
DECLARE @APIURL   NVARCHAR(255)

BEGIN TRY

-- URL pro získání autentikačního Bearer tokenu
SET @tokenURL = REPLACE(@tokenURL, '{{tenantId}}', @tenantID)
-- hlavičky HTTP requestu pro komunikace
SET @Headers = '<Headers><Accept>*/*</Accept><Content-Type>application/json</Content-Type></Headers>'
-- hlavičky HTTP requestu pro autentikaci jsou jiné, než pro volání API funkcí
SET @AuthHeaders = '<Headers><Content-Type>application/x-www-form-urlencoded</Content-Type></Headers>'
-- datový obsah Body s autentikačními údaji pro získání Bearer tokenu
SET @AuthData = 'grant_type=client_credentials&client_id=' + @clientID + '&client_secret=' + @clientSecret + '&scope=https%3A%2F%2Ftest.crm4.dynamics.com%2F.default'
-- URL pro volání fce erpnotifications z API
SET @APIURL = REPLACE(@webAPIURL, '{{version}}', @APIVersion) + 'erpnotifications'

-- HTTP request Body pro volání fce erpnotifications z API
SET @Data = '{
    "name": "testovací notifikace",
    "tablename": "PROJEKT",
    "idrecord": "123456",
    "operationtype": 1,
    "textdata": "notifikace změny projektu s ID=123456"
}'

Exec [dbo].[DownloadURLbearerAuth] @APIURL, @tokenURL, 'POST', @Headers, @AuthHeaders, @Data, @AuthData, @Response OUT
Select @Response, @headers

Komunikace s ESO9 API

Pro potřeby volání ESO9 API je možno využít proceduru DownloadURLEso9Api, která na základě x-api-key zaslaného v parametru @Headers s ostatními hlavičkami. A na základě zaslané hodnoty apiHash v parametru @apiHash vypočítá x-eso9-signature používané k ověřování při volání ESO9 API.

Rozhraní

CREATE OR ALTER PROCEDURE dbo.DownloadURLEso9Api (
    @URL              [nvarchar](1000), 
    @RequestMethod    [nvarchar](10), 
    @Headers          [nvarchar](max) OUTPUT, 
    @Data             [nvarchar](max), 
    @ApiHash          [nvarchar](600),
    @Response         [nvarchar](max) OUTPUT,
    @ResponseStatus   [int] = NULL OUTPUT ,
    @ResponseEncoding [nvarchar](10) = NULL)
AS 
EXTERNAL NAME DownloadURL.CDownloadURL.DownloadURLEso9Api
GO

• //@Headers // - HTTP hlavičky, musí obsahovat přidělenou hodnotu x-api-key

<Headers><x-api-key>a62160ba-f04d-47b8-8c3a-718ab6bf00b2</x-api-key></Headers>

• @ApiHash - přidělená hodnota apiHash

Příklad použití

declare @Response       nvarchar(max),
	@ResponseStatus int

exec DownloadURLEso9Api @URL ='http://ws003/webapieso9/getdata', 
			@RequestMethod ='POST', 
			@Headers ='<Headers><Accept>*/*</Accept><Content-Type>application/json</Content-Type><x-api-key>a62160ba-f04d-47b8-8c3a-718ab6bf00b2</x-api-key></Headers>', 
			@Data= '{"vltyp": 1001,"params": [{"ico": "27624609"}]}', 
			@apiHash = 'Ana7Ez7MqFnenlUIQdX94zVml6wz0y7IgjJbmZRaiwQ=',
			@Response = @Response OUT,
			@ResponseStatus = @ResponseStatus out ,
			@ResponseEncoding = NULL

select @Response, @ResponseStatus