MSSQL 웹(Get/Post)통신 : SQL_APIConsumer

관리자

공지사항

내일은 오지 않는다 또 다른 오늘이 있을 뿐이다

글 보관함

달력

« 2024/05 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

티스토리 뷰

IT Story/TIP

MSSQL 웹(Get/Post)통신 : SQL_APIConsumer

행복한소식까치

공공Data 포탈이나 다른 시스템과 연동시 XML 또는 Json으로 Data를 Web을 통해 받아 오는 경우가 종종 있습니다.

웹방식의 프로그램을 작성 할 경우 바로 받아서 사용하면 되지만 , 웹방식으로 받은 Data를 C/S방식의 프로그램에 적용

시 불편함이 있었습니다. 그래서 구글링중 SP(Stored Proc)를 사용해서 바로 가공하여 사용하는 방법이 있어서 공유 해

볼 까 합니다.(사실 제가 안까먹기 위한 기록용 성격이 더 강함)

환경 설정 ( SQL Server Management Studio 에서 실행)

1. 아래와 같이 clr 사용을 활성화 합니다.

USE DBNAME
GO
sp_configure 'clr enabled',1
RECONFIGURE

2. 사용할 DataBase의 모드를 TRUSTWORTHY 로 설정

ALTER DATABASE DBNAME SET TRUSTWORTHY ON

3. .Net Framework에서 어셈브리[System.Runtim.Serialization]를 만듭니다. 설치된 .Net 버전이 다를경우 설치된 경로로 FROM 이하 경로를 수정 합니다.

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

4. Json 사용을 위해 Newtonsoft.Json 어셈블리를 생성합니다

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

5. C 드라이버에 CLR 이란 폴더를 만들고 아래 3개의 DLL를 복사하여 붙여넣기 합니다.

(dll 파일은 아래 출처에서 다운 받아 사용하세요)

설치방법

1. API_Consumer 어셈블리 생성

CREATE ASSEMBLY [API_Consumer]
AUTHORIZATION dbo
FROM  N'C:\CLR\API_Consumer.dll'
WITH PERMISSION_SET = UNSAFE

2. CLR을 사용 할 저장 프로시저 생성

PRINT N'Creating [dbo].[Create_HMACSHA256]...';

GO
CREATE FUNCTION [dbo].[Create_HMACSHA256]
(@message NVARCHAR (MAX) NULL, @SecretKey NVARCHAR (MAX) NULL)
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [API_Consumer].[UserDefinedFunctions].[Create_HMACSHA256]

 GO
PRINT N'Creating [dbo].[GetTimestamp]...';

GO
CREATE FUNCTION [dbo].[GetTimestamp]
( )
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [API_Consumer].[UserDefinedFunctions].[GetTimestamp]

GO
	PRINT N'Creating [dbo].[fn_GetBytes]...';
GO
CREATE FUNCTION [dbo].fn_GetBytes
(@value NVARCHAR (MAX) NULL )
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [API_Consumer].[UserDefinedFunctions].fn_GetBytes
GO

Get 방식 호출시 사용 할 프로시저 생성

PRINT N'Creating [dbo].[APICaller_GET]...';
GO
CREATE PROCEDURE [dbo].[APICaller_GET]
@URL NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_GET]

Post 방식 호출시 사용 할 프로시저 생성

PRINT N'Creating [dbo].[APICaller_POST]...';
GO
CREATE PROCEDURE [dbo].[APICaller_POST]
@URL NVARCHAR (MAX) NULL
,@JsonBody	NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_POST]

Post방식 Auth 인증시 사용

PRINT N'Creating [dbo].[APICaller_POSTAuth]...';
GO
CREATE PROCEDURE [dbo].[APICaller_POSTAuth]
@URL NVARCHAR (MAX) NULL
,@Token NVARCHAR (MAX) NULL
,@JsonBody	NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_POST_Auth]

Get방식 Auth 인증 및 그외 사용 프로시저 생성

PRINT N'Creating [dbo].[APICaller_GETAuth]...';
GO
CREATE PROCEDURE [dbo].[APICaller_GETAuth]
@URL NVARCHAR (MAX) NULL
,@Token NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_GET_Auth]

GO

PRINT N'Creating [dbo].[APICaller_GET_Headers]...';

GO
CREATE PROCEDURE [dbo].[APICaller_GET_Headers]
@URL NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_GET_Headers]

GO

PRINT N'Creating [dbo].[APICaller_GET_Headers_BODY]...';

GO

CREATE PROCEDURE [dbo].[APICaller_GET_Headers_BODY]
@URL NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].APICaller_GET_JsonBody_Header
GO

PRINT N'Creating [dbo].[APICaller_POST_Headers]...';
GO
CREATE PROCEDURE [dbo].[APICaller_POST_Headers]
@URL NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].APICaller_POST_Headers

GO
PRINT N'Creating [dbo].[APICaller_POST_JsonBody_Header]...';
GO
CREATE PROCEDURE [dbo].[APICaller_POST_JsonBody_Header]
	@URL NVARCHAR (MAX)  
  , @Headers NVARCHAR (MAX)  
  , @jSON NVARCHAR (MAX)  
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].APICaller_POST_JsonBody_Headers

GO
PRINT N'Creating [dbo].[APICaller_GET_Extended]...';
GO
CREATE PROCEDURE [dbo].[APICaller_GET_Extended]
@URL NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_GET_Extended]

GO
PRINT N'Creating [dbo].[APICaller_POST_Extended]...';
GO
CREATE PROCEDURE [dbo].[APICaller_POST_Extended]
@URL NVARCHAR (MAX) NULL, @Headers NVARCHAR (MAX) NULL, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].[APICaller_POST_Extended]

GO
PRINT N'Creating [dbo].[APICaller_POST_Encoded]...';
GO

CREATE PROCEDURE [dbo].APICaller_POST_Encoded
  @URL		NVARCHAR (MAX) NULL
, @Headers	NVARCHAR (MAX) NULL
, @JsonBody NVARCHAR (MAX) NULL
AS EXTERNAL NAME [API_Consumer].[StoredProcedures].APICaller_POST_Encoded

사용방법

1. Get 방식 호출 방법 (결과 값이 Json) 으로 넘어올 경우

DECLARE @RoutingNumber AS VARCHAR(50) = '122242597'

--Public API: routingnumbers.info
DECLARE @Url  VARCHAR(200) = CONCAT('https://www.routingnumbers.info/api/name.json?','rn=',@RoutingNumber) 

DECLARE @Results AS TABLE
(
	Context varchar(max)
)

DECLARE @Result AS VARCHAR(MAX)

INSERT INTO @Results
EXEC  [dbo].[APICaller_GET] @Url

--Result: Row per value 

 SELECT  B.*
  FROM (
			SELECT Context 
			  from @Results
		)tb
	OUTER APPLY OPENJSON  (context) B

--Result: column per value.
SELECT 
		[name]	
		,[rn]		
		,[message]	
		,[code]	
 FROM (
			SELECT Context 
			  from @Results
		)tb
	OUTER APPLY OPENJSON  (context)  
  WITH
    ( [name]		VARCHAR(20) '$.name'
	, [rn]			VARCHAR(20) '$.rn'
	, [message]		VARCHAR(20) '$.message'
	, [code]		INT			'$.code'
    );

2. 다중 헤더를 호출하는 Get 메서드 샘플

  use DBNAME   -- 환경설정한 DBNAME
go 
--Set Header
Declare @header nvarchar(max) = '[{
								  "Name": "X-RapidAPI-Host",
								  "Value" :"restcountries-v1.p.rapidapi.com"
								},{
								  "Name": "X-RapidAPI-Key",
								  "Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"
								}]';
--Set URL
Declare @wurl varchar(max) = 'https://restcountries-v1.p.rapidapi.com/all' 

Declare @ts as table(Json_Table nvarchar(max))

 insert into @ts
 --Get Account Data
 exec [dbo].APICaller_GET_headers
							@wurl
							,@header

SELECT  * 
 FROM OPENJSON((select * from @ts))  
		WITH (   
				 name				nvarchar(max) '$."name"'      
				,alpha2Code			nvarchar(max) '$."alpha2Code"'      
				,alpha3Code			nvarchar(max) '$."alpha3Code"'      
				,callingCodes		nvarchar(max) '$."callingCodes"'  as JSON       
				,capital			nvarchar(max) '$."capital"'      
				,region				nvarchar(max) '$."region"'      
				,subregion			nvarchar(max) '$."subregion"'  
				,timezones			nvarchar(max) '$."timezones"'	  as JSON     
				,population			nvarchar(max) '$."population"'      
				,"currencies"		nvarchar(max) '$."currencies"'	  as JSON 
				,languages			nvarchar(max) '$."languages"'	  as JSON         
				) a

3. Get Header Method Extended를 호출하는 샘플

--Script sample execution Calling Rapid API.

--Set Header
Declare @header nvarchar(max) = 
  '[{
		"Name": "Content-Type",
		"Value" :"application/json; charset=utf-8"
	},
	{
		"Name": "X-RapidAPI-Host",
		"Value" :"restcountries-v1.p.rapidapi.com"
	},{
		"Name": "X-RapidAPI-Key",
		"Value" :"c56b333d25mshdbfec15f02f096ep19fa94jsne5189032cf7d"
	}]';
--Set URL
Declare @wurl varchar(max) = 'https://restcountries-v1.p.rapidapi.com/all' 

Declare @ts as table
(
	Json_Result nvarchar(max),
	ContentType varchar(100),
	ServerName varchar(100),
	Statuscode varchar(100),
	Descripcion varchar(100),
	Json_Headers nvarchar(max)
)
declare @i as int 
 
 insert into @ts
 --Get Account Data
 exec @i = [dbo].[APICaller_GET_Extended]
							@wurl
							,''
							,@header

select * from @ts

SELECT  * 
 FROM OPENJSON((select Json_Result from @ts))  
		WITH (   
				 name				nvarchar(max) '$."name"'      
				,alpha2Code			nvarchar(max) '$."alpha2Code"'      
				,alpha3Code			nvarchar(max) '$."alpha3Code"'      
				,callingCodes		nvarchar(max) '$."callingCodes"'  as JSON       
				,capital			nvarchar(max) '$."capital"'      
				,region				nvarchar(max) '$."region"'      
				,subregion			nvarchar(max) '$."subregion"'  
				,timezones			nvarchar(max) '$."timezones"'	  as JSON     
				,population			nvarchar(max) '$."population"'      
				,"currencies"		nvarchar(max) '$."currencies"'	  as JSON 
				,languages			nvarchar(max) '$."languages"'	  as JSON         
				) a

SELECT  * 
 FROM OPENJSON((select Json_Headers from @ts))  
		WITH (   
				 Header				nvarchar(max) '$."Name"'      
				,Value		nvarchar(max) '$."Value"'      
				) a

4. 인증 Get / POST 메서드 호출 샘플

   DECLARE @Result AS TABLE
    (
        Token VARCHAR(MAX)
    )
    
    INSERT INTO @Result
    
     exec  [dbo].[APICaller_POST]
    	 @URL = 'http://localhost:5000/api/auth/login'
    	,@BodyJson = '{"Username":"gdiaz","Password":"password"}'
    
    DECLARE @Token AS VARCHAR(MAX)
    
    SELECT TOP 1 @Token = CONCAT('Bearer ',Json.Token)
     FROM @Result
      CROSS APPLY ( SELECT value AS Token FROM OPENJSON(Result)) AS [Json]
    
    EXEC [dbo].[APICaller_GETAuth] 
         @URL	  = 'http://localhost:5000/api/values'
       , @Token = @Token

다음 포스팅 예고

- 다음 포스팅은 SQL_APIConsumer API를 사용하여 공공Data의 우편번호 검색 Data를 받아와 파싱하는 예제를 만들어 보겠습니다.

출처 : https://github.com/geral2/SQL-APIConsumer/blob/master/README.md

저작자표시 비영리 동일조건

'IT Story > TIP' 카테고리의 다른 글

PowerBuilder + 키입력시 곱하기 1000 기능 구현 팁 (0)	2021.04.12
MSSQL 웹(Get/Post)통신 : SQL_APIConsumer 활용 (0)	2020.08.21
네이버 SmartEditor 기본글꼴 변경하기 (0)	2020.04.09
PowerBuilder 2019 저장프로시저(SP) 사용시 오류 해결방법 (0)	2020.04.07
파워빌더 2019 Demo DB설치 방법 (0)	2020.01.08