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

MSSQL서버상에서 웹서비스를 호출하는 방법에 대하여 설명드리겠습니다. 
우연한 기회에 MSSQL서버자체에서 웹서비스를 제공하는부분에 대해서 알게되었고 해당부분을 언제사용할일이 있을까 생각하던 도중에 외부업체와 웹서비스로 연동을 해야되는 경우가 생겼는데,  별도 프로그램을 구축하지 않고 간단하게 스케쥴러를 통하여 웹서비스를 알림을 하게되었습니다.  
특정테이블에 데이터저장시 트리거에 웹서비스를호출하여 알림처리. 스케쥴러를 통한 일괄데이터 전송시에도 사용하면 별도 클라이언트프로그램을 구축하지 않고 간단히 연동할수 있을것으로 보입니다. 
 
1. 웹서버 구축 
먼저 웹서비스관련 서버가 구축이 되어야 테스트가 가능합니다. 이전강좌에 Rest Api 서버를 만드는 부분을 참고해서 진행하도록 하겠습니다.  Rest Api서버구축 참고 :  sosopro.tistory.com/15프로젝트를 빌드후 콜솔로 실행해서 테스트 해보겠습니다.  http://localhost:5000 으로 서비스를 실행하겠습니다.

[C#] ASP.Net Core를 이용하여 Rest Api 서버만들기

2. MSSQL서버 웹서비스 호출 
먼저 TodoItem테이블생성 합니다. 

USE [TEST]
GO
 
/****** Object:  Table [dbo].[TodoItem]    Script Date: 2020-09-07 오후 11:17:47 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
CREATE TABLE [dbo].[TodoItem](
    [Id] [int] NOT NULL primary key ,
    [Name] [varchar](50) NULL,
    [IsComplete] [bit] NULL,
) 
GO

Ole Automation Procedures 활성화. 
sp_configure명령을 이용하여 OLE자동화 저장 프로시저를 활성화합니다. 

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 방식 호출방법. GetTodoItem 프로시저를 만들어보겠습니다.

USE [TEST]
GO
 
/****** Object:  StoredProcedure [dbo].[GetTodoitem]    Script Date: 2020-09-08 오후 11:16:04 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
 
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetTodoitem]
AS
BEGIN
    DECLARE @contentType NVARCHAR(64);    --요청할 HTTP 서버에 보낼 데이터의 콘텐츠 타입을 정의하는 변수
    DECLARE @responseText NVARCHAR(2000); --HTTP 서버의 처리 결과를 응답받을 변수
    DECLARE @responseXML NVARCHAR(2000);  --사용안함(응답받을 데이터 XML형식일 경우를 가정해 만든것으로 보임)
    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 @sComplete    NVARCHAR(10);
 
    SET NOCOUNT ON;
 
    BEGIN TRY --예외 실행문 시작
        SET @contentType = 'application/json';                                                --컨텐츠타입
        SET @url = 'http://localhost:5000/api/TodoItems'                --웹URL
 
        --커넥션 생성
        EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT; --MSXML2.ServerXMLHTTP 웹페이지를 읽는 xml파서 개체 형식의 OLE 인스턴스 개체 생성(출력된 토큰값으로 사용)
        IF @ret = 0 BEGIN --OLE 인스턴스 개체 생성 결과가 정상일 경우 시작
            --HTTP 서버 요청
            EXEC @ret = sp_OAMethod @token, 'open', NULL, 'GET', @url, 'false';                    --서버와의 통신 방식 정의
            EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType; --데이터 요청 헤더 정의
            EXEC @ret = sp_OAMethod @token, 'setTimeouts', NULL, 1000, 1000, 1000, 1000                --연결 유지 시간 정의
            EXEC @ret = sp_OAMethod @token, 'send';                                                        --데이터 전송
 
            --응답 결과 처리
            EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;                    --서버 상태
            EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;            --서버 상태 내용
            EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;        --응답 내용
 
            --응답 결과 확인
            PRINT 'Status: ' + @status + ' (' + @statusText + ')';  --OLE 개체의 속성 상태와 내용을 출력
            PRINT 'Response text: ' + @responseText;  --응답 결과 출력
 
            --해당 토큰값의 OLE 인스턴스 개체 소멸(소켓 클로즈라고 보면됨.)
            EXEC @ret = sp_OADestroy @token;
        END
    END TRY        
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH  
END
 
 
/* 실행 
EXEC [GetTodoitem]
*/
GO
 
 
 

GetTodoItem프로시저를 호출하게되면 아래와 같이 Status값이 200으로 등록된 TodoItem리스트정보를 서버로 부터 받아 올수 있습니다. EF를 사용하여 메모리디비로 테스트하게 구축해놓은 상태라 최초 결과값이 []이 나옵니다. 아래Post방식으로 데이터를 등록하 다시 GetTodoItem프로시저를 호출하게되면 등록된 정보가 조회되는것을 확인할수 있습니다. 

Post방식 호출방식. AddTodoItem 프로시저를 만들어보겠습니다. 

USE [TEST]
GO
 
/****** Object:  StoredProcedure [dbo].[AddTodoitem]    Script Date: 2020-09-08 오후 11:22:11 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddTodoitem]
    @name        nvarchar(20),
    @isComplete bit
AS
BEGIN
    DECLARE @contentType NVARCHAR(64);    --요청할 HTTP 서버에 보낼 데이터의 콘텐츠 타입을 정의하는 변수
    DECLARE @postData NVARCHAR(2000);     --실제 보내는 데이터 담을 변수
    DECLARE @responseText NVARCHAR(2000); --HTTP 서버의 처리 결과를 응답받을 변수
    DECLARE @responseXML NVARCHAR(2000);  --사용안함(응답받을 데이터 XML형식일 경우를 가정해 만든것으로 보임)
    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 @sComplete    NVARCHAR(10);
 
    --쿼리문 또는 프로시저의 영향을 받은 행 수를 나타내는 메시지가 결과 집합의 일부로 반환되지 않도록 하는 것
    --프로시저 속도 향상을 위해...((0개 행인 영향을 받음) <<< 영향받은 행이 0일 경우 이 메시지가 나오지 않도록 조치됨.)
    SET NOCOUNT ON;
 
    IF (@isComplete = 0)
        SET @sComplete = 'false'
    ElSE
        SET @sComplete = 'true'
 
 
    BEGIN TRY --예외 실행문 시작
        SET @contentType = 'application/json';                                                --컨텐츠타입
        SET @postData = '{  "name":"' + @name + '",  "isComplete":' + @sComplete + '}';    --전송데이터
        SET @url = 'http://localhost:5000/api/TodoItems'                --웹URL
 
        --커넥션 생성
        EXEC @ret = sp_OACreate 'MSXML2.ServerXMLHTTP', @token OUT; --MSXML2.ServerXMLHTTP 웹페이지를 읽는 xml파서 개체 형식의 OLE 인스턴스 개체 생성(출력된 토큰값으로 사용)
        IF @ret = 0 BEGIN                                            --OLE 인스턴스 개체 생성 결과가 정상일 경우 시작
            --HTTP 서버 요청
            EXEC @ret = sp_OAMethod @token, 'open', NULL, 'POST', @url, 'false';                    --서버와의 통신 방식 정의
            EXEC @ret = sp_OAMethod @token, 'setRequestHeader', NULL, 'Content-type', @contentType; --데이터 요청 헤더 정의
            EXEC @ret = sp_OAMethod @token, 'setTimeouts', NULL, 1000, 1000, 1000, 1000                --연결 유지 시간 정의
            EXEC @ret = sp_OAMethod @token, 'send', NULL, @postData; --데이터 전송
 
            --응답 결과 처리
            EXEC @ret = sp_OAGetProperty @token, 'status', @status OUT;                    --서버 상태
            EXEC @ret = sp_OAGetProperty @token, 'statusText', @statusText OUT;            --서버 상태 내용
            EXEC @ret = sp_OAGetProperty @token, 'responseText', @responseText OUT;        --응답 내용
 
            --응답 결과 확인
            PRINT 'Status: ' + @status + ' (' + @statusText + ')';  --OLE 개체의 속성 상태와 내용을 출력
            PRINT 'Response text: ' + @responseText;  --응답 결과 출력
 
            --해당 토큰값의 OLE 인스턴스 개체 소멸(소켓 클로즈라고 보면됨.)
            EXEC @ret = sp_OADestroy @token;
        END
    END TRY        
    BEGIN CATCH
        PRINT ERROR_MESSAGE()
    END CATCH  
END
 
 /* 테스트 프로시저 
    exec [AddTodoitem] 'test1234', 0
*/
GO
 
 

AddTodoItem 프로시저 호출결과입니다. 아래와 같이 Status 상태값 201로 test1234관련 고객정보가 등록된부분을 확인 할수 있습니다.

MSSQL서버상에서 웹서비스 호출하는 방법을 알아보았습니다. 이제 해당부분을 이용해서 알람테이블에 트리거를 걸어서 웹서비스를 통해 알람관련 자료를 전송할수도 있으며,  SQL Agent를 통하여 스케쥴러를 걸어 간단하게 배치를 걸어 사용할수도 있습니다.