ESO9 wiki

Tato stránka je pouze pro čtení. Můžete si pouze prohlédnout zdrojový kód, ale ne ho měnit. Zeptejte se správce, pokud si myslíte, že něco není v pořádku.
==== 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í ===
<code sql>
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
</code>
  * //@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:
<code xml><Headers><Jmeno>hodnota</Jmeno></Headers></code>
Základní ověření (//Basic authentication//) se předává v hlavičce ve formátu:\\
<code xml><Headers><Authorization>Basic xxx</Authorization></Headers></code>
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:<nowiki>//</nowiki>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ů.
<code sql>
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
</code>
=== 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í ===
<code sql>
--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
</code>


==== 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í ===
<code sql>
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
</code>
  * //@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: 
<code xml>
<formData><name>value</name><name>value</name></formData>
</code>
  * //name// = jméno jedné z multipart částí, //value// = její textový obsah
=== Příklad použití ===
<code sql>
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
</code>

==== 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í ===
<code sql>
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
</code>
  * //@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//.
<code sql>
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
</code>

==== 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í ===
<code sql>
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
</code>
  * //@Cert// - klientský certifikát předávaný jako pole bajtů.
  * //@CertPwd// - heslo ke klientskému certifikátu.
=== Příklad použití ===
<code sql>
--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
</code>

==== 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í ===
<code sql>
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
</code>
  * //@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í ===
<code sql>
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
</code>

==== 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í ===
<code sql>
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
</code>
 * //@Headers // - HTTP hlavičky, musí obsahovat přidělenou hodnotu x-api-key
<code xml><Headers><x-api-key>a62160ba-f04d-47b8-8c3a-718ab6bf00b2</x-api-key></Headers></code>
 * //@ApiHash// - přidělená hodnota apiHash
=== Příklad použití ===
<code sql>
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
</code>