[MSSQL] REST API 호출하기

업무 중에 매일 한 번씩 API를 호출하고 결과(JSON)를 받아 사용해야 하는 일이 생겼다.

처음에는 다른 서버에서 직접 호출하고 MSSQL로 INSERT 하려고 했으나 찾다 보니 MSSQL에서 Job Scheduler로 REST API를 호출이 가능하다는 걸 찾았고 이어 JSON Parsing도 가능해서 기왕 찾아본 걸 기록 삼아 정리해본다.

---

MSSQL에서 REST API 호출하기

OLE 자동화 기능 활성 - SQL

sp_configure 'show advanced options', 1;  --SQL 서버의 모든 환경 구성 옵션을 사용하겠다고 정의
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;  --OLE 자동화 저장 프로시저 엑세스 활성화
RECONFIGURE;
GO

위 쿼리 없이 아래 내용을 실행하게 되면

활성화하지 않을 경우 웹서비스 호출 시 아래와 같은 에러 메시지가 발생하여 동작이 되지 않습니다. 구성 요소 'Ole Automation Procedures'이(가) SQL Server 보안 구성의 일부로 해제되었으므로 이 구성 요소의 프로시저 'sys.sp_OACreate'에 대한 액세스가 차단되었습니다. 
시스템 관리자는 sp_configure를 통해 'Ole Automation Procedures'을(를) 활성화할 수 있습니다. 
'Ole Automation Procedures' 활성화에 대한 자세한 내용을 보려면 SQL Server 온라인 설명서에서 'Ole Automation Procedures'을(를) 검색하십시오.

의 에러를 확인할 수 있다.

---

GET 방식의 호출 - SQL

DECLARE @responseText NVARCHAR(MAX);  --HTTP 서버의 처리 결과를 응답 받을 변수 
DECLARE @ret INT;                     --OLE 자동화 프로시저 호출 결과 리턴 값을 담을 변수(0:성공, 0 아닌 숫자는 실패) 
DECLARE @status NVARCHAR(32);         --요청 서버의 상태 값을 담는 변수 
'DECLARE @statusText NVARCHAR(32);    --요청 서버의 상태 값의 상세 내용을 담는 변수 
DECLARE @token INT;                   --OLE 자동화 프로시저 개채 생성 토큰 값을 담을 변수(여기서는 1회용 개체 사용권)  
DECLARE @url NVARCHAR(256);           --HTTP 서버 URL 변수 

SET NOCOUNT ON; 

SET @url = 'http://127.0.0.1/api/testapi' -- API 주소 

-- 커넥션 생성 
-- MSXML2.ServerXMLHTTP 웹페이지를 읽는 xml파서 개체 형식의 OLE 인스턴스 개체 생성(출력된 토큰값으로 사용) 
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT; 
IF @ret <> 0 GOTO CLEANUP 

-- HTTP 서버 요청 
-- 서버와의 통신 방식 정의 
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false'; -- GET 방식
IF @ret <> 0 GOTO CLEANUP 

-- 데이터 요청 헤더 정의 
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'application/json'; 
IF @ret <> 0 GOTO CLEANUP 

--연결 유지 시간 정의 
EXEC @ret = sp_OAMethod @token, 'setTimeouts', NULL, 1000, 1000, 1000, 1000 
IF @ret <> 0 GOTO CLEANUP 

-- 데이터 전송 
EXEC @ret = sp_OAMethod @token, 'send'; 
IF @ret <> 0 GOTO CLEANUP 
-- 응답 결과 처리 
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT; 
IF @ret <> 0 GOTO CLEANUP 

'EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT; 
IF @ret <> 0 GOTO CLEANUP 

EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT; 
IF @ret <> 0 GOTO CLEANUP 

-- OLE 인스턴스 개체 소멸(소켓 클로즈라고 보면됨.) 
EXEC @ret = sp_OADestroy @token; 

-- 반환 결과가 비정상
IF @status <> 200
	RETURN

-- 응답 결과 출력 
SELECT @responseText

CLEANUP:
BEGIN
    sp_OADestroy @token;
END

---

POST 방식의 호출 - SQL

DECLARE @responseText NVARCHAR(MAX);  --HTTP 서버의 처리 결과를 응답 받을 변수 
DECLARE @ret INT;                     --OLE 자동화 프로시저 호출 결과 리턴 값을 담을 변수(0:성공, 0 아닌 숫자는 실패) 
DECLARE @status NVARCHAR(32);         --요청 서버의 상태 값을 담는 변수 
'DECLARE @statusText NVARCHAR(32);    --요청 서버의 상태 값의 상세 내용을 담는 변수 
DECLARE @token INT;                   --OLE 자동화 프로시저 개채 생성 토큰 값을 담을 변수(여기서는 1회용 개체 사용권)  
DECLARE @url NVARCHAR(256);           --HTTP 서버 URL 변수
DECLARE @postData NVARCHAR(MAX);      --POST로 보낼 데이터

SET NOCOUNT ON; 

SET @url = 'http://127.0.0.1/api/testapi' -- API 주소 
SET @postData = 'test';

-- 커넥션 생성 
-- MSXML2.ServerXMLHTTP 웹페이지를 읽는 xml파서 개체 형식의 OLE 인스턴스 개체 생성(출력된 토큰값으로 사용) 
EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT; 
IF @ret <> 0 GOTO CLEANUP 

-- HTTP 서버 요청 
-- 서버와의 통신 방식 정의 
EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false'; -- POST 방식
IF @ret <> 0 GOTO CLEANUP 

-- 데이터 요청 헤더 정의 
EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', 'application/json'; 
IF @ret <> 0 GOTO CLEANUP 

--연결 유지 시간 정의 
EXEC @ret = sp_OAMethod @token, 'setTimeouts', NULL, 1000, 1000, 1000, 1000 
IF @ret <> 0 GOTO CLEANUP 

-- 데이터 전송 
EXEC @ret = sp_OAMethod @token, 'send' NULL @postData; 
IF @ret <> 0 GOTO CLEANUP 
-- 응답 결과 처리 
EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT; 
IF @ret <> 0 GOTO CLEANUP 

'EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT; 
IF @ret <> 0 GOTO CLEANUP 

EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT; 
IF @ret <> 0 GOTO CLEANUP 

-- OLE 인스턴스 개체 소멸(소켓 클로즈라고 보면됨.) 
EXEC @ret = sp_OADestroy @token; 

-- 반환 결과가 비정상
IF @status <> 200
	RETURN

-- 응답 결과 출력 
SELECT @responseText

CLEANUP:
BEGIN
    sp_OADestroy @token;
END

---

@status가 200(OK)이 아니면 에러 처리로 작성했는데, 그 외의 응답 코드는 아래 링크에서 확인하면 된다.

https://developer.mozilla.org/ko/docs/Web/HTTP/Status/200

200 OK - HTTP | MDN

---

@responseText에 실질적인 결과물이 들어있다.

 

내가 받을 데이터는 JSON 형식이었고 JSON의 return이 'data1,data2' 이런 형식의 데이터였으므로 ','로 데이터를 잘라서 사용하면 되겠다.

---

2021.06.11 - [DB/MSSQL] - [MSSQL] JSON 파싱하기

2021.06.11 - [DB/MSSQL] - [MSSQL] 구분자로 구분된 문자열 분리하기

---

API 호출 쿼리 출처 : https://sosopro.tistory.com/14

[DB] MSSQL 웹서비스(GET/POST)호출방법