MSSQL C 확장 저장 프로시저

MSSQL에서 CLR 라이브러리 아닌 C 라이브러리를 사용하는 방법.

MSDN에서는 확장 저장 프로시저 사용하지 말고 CLR 쓰라고 한다. 그래도 쓸일이 있을가봐 남겨둔다. 

 

버전 : Visual Studio 2019 , MSSQL 2019

 

먼저 간단히 C++ 라이브러리를 만들어서 진행한다. 

DLL(동적 연결 라이브러리) 클릭하여 프로젝트 추가. 빈 프로젝트로 설정 후 구성 형식을 DLL로 변경해도 된다. 

프로젝트 이름과 위치를 설정해 준 후 만들기 클릭 

새로운 프로젝트가 생성이 되고 자동으로 기본 소스가 생성된다. 

pch.h 파일은 미리 컴파일된헤더(Precompiled Header) 가 기본으로 설정 되어 있어서 사용 할 수 있게 되어 있다.  프로그램 소스가 커지게 되면 컴파일 양이 많아져 컴파일 하는데 오래 걸리는 시간을 미리 컴파일된 헤더를 사용하여

빠르게 컴파일 해주는 옵션이다. 말 그대로 미리 컴파일 하기 때문에 헤더 파일이 변경 되면 다시 해줘야 한다. 

 

dllmain.cpp 파일의 소스가 기본으로 생성 되어 있다. APIENTRY DllMain 함수가 등록 되어 있는데 이것은 라이브러리가 처음 로딩 되거나 프로세스, 쓰레드 가 접근 및 해제 될 경우에 행위를 설정 할 수 있다. 보통 초기화 함수를 넣을 때 사용한다. 

 

C 소스를 추가 하기 위해 아래 내용을 소스에 추가하거나,

extern "C" { SRVRETCODE __declspec(dllexport) extstr(SRV_PROC* pSrvProc); }

아래와 같이 'C코드로 컴파일/TC'로 변경 후 진행하면 된다.  

MSSQL에서 사용할 함수를 DllMain 함수는 제거 하고 아래 함수를 추가한다. 입력 값을 그대로 리턴하는 함수이다.

#include <stdio.h>
#include <srv.h>

SRVRETCODE __declspec(dllexport) extstr(SRV_PROC* pSrvProc)
{
	BYTE	inval[8000];
	BYTE	cType;
	ULONG	uMaxLen;
	ULONG	uLen;
	BOOL	fNull;
	int ret;
	int nArgs = srv_rpcparams(pSrvProc);
	if (nArgs > 0)
	{
		memset(inval, 0x00, sizeof(inval));
		ret = srv_paraminfo(pSrvProc, 1, &cType, &uMaxLen, &uLen, inval, &fNull);
		if (ret == FAIL)
		{
			return ret;
		}
		srv_paramsetoutput(pSrvProc, 2, inval, uLen, FALSE);
		srv_senddone(pSrvProc, SRV_DONE_FINAL, 0, 0);
	}
	return SUCCEED;
}

 

srv 함수에 대한 설명은 아래 링크 참고하면 된다. 

https://docs.microsoft.com/ko-kr/sql/relational-databases/extended-stored-procedures-reference/srv-paraminfo-extended-stored-procedure-api?view=sql-server-ver15 

srv_paraminfo(확장 저장 프로시저 API) - SQL Server

 

srv_paraminfo 입력된 pSrvProc 파라미터를 읽어서 변수에 넣어주는 함수로 2번째 파라미터에 1은 읽을 pSrvProc 파라미터 위치를 나타내고, 즉 첫번째 파라미터 값, 입력을 1개만 받을거라 1 이외에는 없다.  5번째 uLen 은 입력 값의 길이, 6번째는 pSrvProc 파라미터에서 읽은 값을 넣을 변수를 넣으면 된다. 

srv_paramsetoutput 는 레퍼런스 콜 형태로 inval 을 uLen만큼 들어온 2번째 pSrvProc 파라미터에 넣어 준다.  

srv_senddone 는 결과 완료라고 마지막에 실행해 주면 되는 함수이다. 

 

__declspec(dllexport)  대한 설명은 아래 링크에서 확인한다. 

https://docs.microsoft.com/ko-kr/cpp/build/exporting-from-a-dll-using-declspec-dllexport?view=msvc-160 

__declspec(dllexport)을 사용하여 DLL에서 내보내기

 

간단히 설명하면 함수 명 앞에 __declspec(dllexport) 는 DLL 에서 내보내기 지시문을 붙여줘야 Dll 외부에서 사용 가능하다. 이 지시문을 사용하고 싶지 않을 경우에는 .def 파일을 생성하여 등록해주면 된다.  

 

.def 파일 등록 방법 - 프로젝트 속성 -> 링커 -> 입력 -> 모듈 정의 파일 -> 파일명 추가. 

 

라이브러리도 추가해줘야 srv 관련 함수 사용이 가능하다.

라이브러리는 이전 버전인 SQL Server 2005 에 있으며, 위치는 \Microsoft SQL Server\90\SDK\Lib\x64 에 있다.  

파일명 opends60.lib

 

함수도 추가 했으니 바로 빌드하여 컴파일을 진행한다. 

무난히 컴파일이 진행 된다. 중간에 IPDB를 찾지 못해서 전체 컴파일을 fall back 한다는 내용이 있다. 상관은 없지만 아래와 같이 속성을 변경하면 메시지는 보이지 않는다.

프로젝트 속성 -> 링커 -> 최적화 -> 링커 타임 코드 생성 -> 링크 타임 코드 생성 사용

 

빌드 된 라이브러리를 MSSQL 에 Binn 폴더에 복사한다. 

예시 경로 : C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQL2019\MSSQL\Binn

 

라이브러리에서 함수를 사용하기 위한 SQL을 실행한다. 

exec sp_addextendedproc N'extstr', N'sqlexternal.dll'
go

CREATE FUNCTION [dbo].[proc_extstr]
(@inval varchar(8000) )
RETURNS varchar(8000)
AS
BEGIN
	DECLARE
		@result int,
		@output varbinary(8000)
	EXEC 
		@result = master.dbo.extstr
			@inval,
			@output OUTPUT
	RETURN CONVERT(varchar(8000),@output)
END
GO

 

sp_addextendedproc 저장 프로시저 추가하는 방법으로 DLL을 로드하여 함수를 등록한다. 

위에 언급된 예시 경로에 넣지 않고 절대 경로로 입력하여 사용해도 된다. 

 

프로시저는 바로 사용할 수 없으니 함수 하나 생성하여 사용해야 한다. 

 

아래 쿼리로 간단히 테스트하면 입력값 그대로 결과를 보여준다. 

select master.dbo.proc_extstr('abcdefg')