MSSQL 2000 확장저장프로시저 만들기

  • Document Number: 1.0.0
  • Author: CHOI, Jae-Hoon
  • May 2006

  • Updated May 2006

Table of Contents

  1. 관련 자료 (Related Links)
  • 개요 (Summary)

  • 적용 사례 (Case Study)

  • 개발 도구 (Development Tools)

  • 샘플 소스 코드

  • 샘플 xp_hello

  • 설치

  • 피드백과 지원 (Feedback and Support)

    • TutorialonExtended Stored Procedures for MS SQL Server v7.0
  • SQL Server 온라인 설명서 (색인 키워드: extended stored procedure)

  • 개요 (Summary)

    확장 저장 프로시저는 C/C++ DLL이고, Transact-SQL을 호출하듯이 사용된다. 확장 저장 프로시저는 Transact-SQL의 기능을 확장한다.

    VC++, VB, 또는 Delphi와 같은 언어로 확장 저장 프로시저를 개발할 수 있다. 이때 Open Data Services (ODS)라는 API를 사용한다.

    적용 사례 (Case Study)

    스스로를 온라인 쇼핑몰 A사의 개발자라고 생각해 보자. 며칠 전에 모든 온라인 거래 내역을 암호화해야 한다는 법안이 통과됐다. 최소한 256 비트 이상의 비대칭 암호화 기법을 사용해야 한다.

    MSSQL 2000는 이러한 암호화 기능을 제공하지 않는다. 데이터베이스가 아닌 외부 어플리케이션에서 암호화DLL을 호출하는 방법도 생각해봤다. 그러나 문제가 있다. 첫째, 모든 어플리케이션이 암호화 DLL을 갖고 있어야 한다. 암호화 DLL이 새로 배포될 때마다 모든 어플리케이션을 손봐야 한다.

    두번째 문제는 보다 결정적이다. 거래량이 줄어드는 오전 3~4시에 전날 거래 내역의 통계를 잡는다. 통계 저장 프로시저를 MSSQL 2000의 작업(Jobs)에 등록해놨다. 그런데 데이터가 암호화되면 통계 저장 프로시저가 데이터를 읽을 방법이 없다. 암호화 DLL의 기능을 Transact-SQL로 구현하는 것은 사실상 불가능하다.

    이런 경우에 기존의 암호화 DLL을 확장 저장 프로시저로 바꾸거나, 새로운 확장 저장 프로시저 DLL에서 기존DLL을 감싸는 방법이 있다. 이 확장 저장 프로시저를 MSSQL 2000 서버에 등록해 놓으면, Transact-SQL을 사용하듯이 암호화 DLL을 호출할 수 있게 된다.

    개발 도구 (Development Tools)

    • 설치하기

      MSSQL 2000 클라이언트 설치 시에 사용자 정의 설치를 선택한다. 설치 목록 중에 개발 도구 또는Dev Tools가 비활성화 되어 있다. 체크 박스를 선택한다. 모든 설치 과정이 끝나면, "{MSSQL 기본 경로}\80\Tools\DevTools"에 개발 도구가 설치되어 있다. 대부분의 경우에 경로는 “C:\Program Files\Microsoft SQL Server\80\Tools\DevTools”이다.

    • 샘플 소스 코드

      확장 저장 프로시저는 Open Data Services (ODS)로 구현한다. ODS 샘플 소스 코드는 "{MSSQL 기본경로}\80\Tools\DevTools\Samples\ods\"에 설치된다.

    샘플 xp_hello

    • 빌드하기

      경로 "{MSSQL 기본 경로}\80\Tools\DevTools\Samples\ods\xp_hello\"에 가장 간단한 확장저장 프로시저가 있다. Microsoft Visual C++ 6.0으로 프로젝트를 열어보자. 빌드 버튼을 클릭하면 오류가 발생한다. 확장 저장 프로시저를 개발하기 위해서는 ODS 헤더 파일을 포함(include>하고, opends60.lib 라이브러리 파일을 추가해야 한다.

      "Prject" – "Settings" 메뉴를 선택한다. 헤더 파일의 경로는 "{MSSQL 기본 경로}\80\Tools\DevTools\Include\"이고, 라이브러리 파일의 경로는 "{MSSQL 기본 경로}\80\Tools\DevTools\Lib\"이므로 다음과 같이 설정하면 된다. 이 예제에서는 상대 경로를 사용했지만, 절대경로를 사용해도 된다.

      MSSQL 2000 확장저장프로시저 만들기 - include ODS header files

      MSSQL 2000 확장저장프로시저 만들기 - include ODS library files

    • 설치

      설치 시에는 System Administrator (sa) 권한이 필요하다.

      1. "xp_hello.dll" 파일을 MSSQL 2000 서버가 설치되어 있는 "{MSSQL 기본 경로}\Binn\"에 복사한다.
  • 다음 SQL 일괄처리 문을 실행시킨다.

        <pre class="codebox"><code>USE master
    

    GO

    — 설치
    IF OBJECT_ID(‘[dbo].[xp_hello]’) IS NOT NULL BEGIN
    EXEC sp_dropextendedproc ‘xp_hello’
    END
    GO

    EXEC sp_addextendedproc ‘xp_hello’, ‘xp_hello.dll’
    GO

    — 테스트
    DECLARE @txt VARCHAR(33)
    EXEC xp_hello @txt OUTPUT
    SELECT @txt AS OUTPUT_Parameter
    GO

    </li>
    </ol>
    

  • 해제 및 재설치
    다음은 확장 저장 프로시저 &quot;xp_hello&quot;를 해제하는 일괄처리 문이다. 설치와 마찬가지로 System Administrator 권한이 필요하다.
    
    <pre class="codebox"><code>USE master
    

    GO

    — 확장 저장 프로시저 해제
    IF OBJECT_ID(‘[dbo].[xp_hello]’) IS NOT NULL BEGIN
    EXEC sp_dropextendedproc ‘xp_hello’
    END
    GO

    — DLL Unload
    dbcc xp_hello (free)

    여기서 주의할 점은 DLL 파일 이름과 확장 저장 프로시저의 등록 이름을 구분해야 한다는 것이다. 이 예제에서는 두 가지 이름을 동일하게 설정했지만, 등록 이름은 DLL 파일 이름과 다를 수도 있다. <strong>sp_dropextendedproc</strong>의 매개변수 'xp_hello'는 등록 이름이다. DLL을 메모리에서 해제할 때는 등록 이름이 아닌 DLL파일 이름을 사용해야 한다.
    
    재설치는 쉽다. 해제하고 다시 설치하면 된다. &quot;{MSSQL 기본 경로}\Binn\xp_hello.dll&quot;을 새 버전의 &quot;xp_hello.dll&quot;로 바꾼다. 만약 DLL을 메모리에서 언로드하지 않으면 &quot;{MSSQL 기본 경로}\Binn\xp_hello.dll&quot;은 지워지지 않는다. 다시 말해 새로운 버전의 &quot;xp_hello.dll&quot;를 복사할수 없다. 
    
  • 최 재훈

    블로그, 페이스북, 트위터 고성능 서버 엔진, 데이터베이스, 지속적인 통합 등 다양한 주제에 관심이 많다.
    Close Menu