땅을 떠나지 않으면 하늘을 날수 없다

오늘의 주제는 제목과 같이 "MS-SQL Stored procedures 에서 Select 와 Output Parameter 동시 사용시 주의점" 이다. 이 문제 때문에 하루 반나절을 삽질을 하고야 말았다. ㅡㅡ^
이미 대부분의 사람들이 알고 있는 내용이겠지만 나 같이 MS-SQL 을 많이 사용해 보지 못한 사람에게는 나름 유익한 정보가 될거 같아서 남긴다.

결론 부터 말하자면
"MS-SQL 서버는 Select(RecordSet) 와 OUTPUT Parameter 동시에 사용할 때 해당 Select(RecordSet) 를 모두 읽어 들여야만 OUTPUT Parameter 에 값이 채워진다."
 
무슨 말인지 잘 이해가 안되면 다음을 보도록 하자.
 참고 DBMS 마다 특성이 달라서 모두 같은 방식으로 동작하지는 않고  MS-SQL 의 경우는 그렇다는 것이다.

이번에 개발중인 것이 하나 있는데 MS-SQL 를 데이타 베이스로 사용한다. 그래서 DB쪽을 개발해 주신 분이 Stored procedures 를 개발해 주셨고 거기에 대한 명세를 주셨다.
그 SP 가 USP_TestProc 이 라고 하자. 근데 USP_TestProc 는 Output Parameter  도 사용하고 결과를 RecordSet 으로도 반환한다. 간단히 예를 들면 다음과 같다.

SQL 에서 쿼리로 보면 다음과 같이 사용한다.

declare @OUT SMALL INT

exec USP_TestProc @OUT OUTPUT

select @OUT

이와 같이 쿼리를 만들고 수행하면 USP_TestProc 는 @OUT 변수에 값을 채워주고 Select 한 것과 같이 RecodeSet 으로도 반환 한다.
예들들어 @OUT 값이 1 이면 "output1" 이라는 필드에 값이 나타나고 @OUT 값이 2 이면 "output2" 필드에 값이 표시 되게 되어있다.

그러면 우리는 @OUT 값에 따라 필요한 필드값을 가져와서 처리하면된다. 여기까지는 아무런 문제가 없었다. 이 부분을 구현할때 문제가 발생했다. ㅠㅠ

이번에 데이타 베이스에 접속 하기 위해 SQLAPI 를 사용했다. (유료 라이브러리다) 그래서 샘플소스도 SQLAPI 로 작성한다. (ADO 를 많이 사용하기는 하지만 소스는 그게 그거니까 머 ㅡㅡ)

먼저 소스를 보면서 얘기해 보자

원래 의도대로 OUTPUT Parameter 인  OUT 의 값을 가지고 어떤 필드를 사용할지 결정하려고 하였다.
그러나 ret 변수의 값은 항상 0 이 리턴되었다.
왜 그럴까 ? 그것은 OUT 변수에 값이 할당되기 전에 값을 가지고 왔기 때문이다.
그럼 어떻게 해야 OUT 변수에 값이 할당이 될까 ? 그것은 처음에 말했듯이 모든 RecordSet 을 읽어온 후에야 제대로 값이 할당이 된다. 
그러면 제대로 값을 가지고 오려면 어떻게 해야 하나 ?  다음 소스를 보자


빨간 박스안의 소스를 추가 해보자. 빨간 박스 안의 소스는 Roop 를 돌면서 모든 RecordSet 을 순회 하며 읽어온다. 그리고  OUT 변수의 값을 받으면 정상적으로 값이 받아진다.

그리고 보너스로 아래 파란박스의 소스도 수정해야 한다. 그냥 쓴다면 Except 가 발생할것이다. ㅡㅡ

% 참고로 ADO 의 Execute 메소드에서는 ADODB::adExecuteNoRecords 옵션을 인자로 설정할 수가 있다.
이 옵션을 사용하면 바로  Output Parameter 에 값을 받아올수 있지만 RecordSet 을 받을수는 없다 ㅡㅡ

예전 학습용으로 사용하던 터보씨뿔뿔 3.0 이다.
지금도 가끔 먼가 간단한 코드를 만들어서 컴파일 해보고 싶을때 간혹 생각난다.
그래서 보관용으로 ㅎㅎ

% 이제는 Free 라이센스라 걱정없이 사용해도 되겠다.

먼저 위의 링크를 클릭하여 파일을 다운로드 해보자.

그리고 압축을 푼다.

압축을 풀고 "INSTALL.EXE" 파일을 실행한다.

Enter 키를 눌러서 설치를 시작한다.

현재 압축푼 드라이브를 지정하는 것이다. (바탕화면에 압축을 풀었다면 C 입력하면되겠다.)

Install.exe 파일이 있는 경로 지정이다. (기본으로 설정되므로 Enter...)

터보씨를 설치할 경로이다. (기본은 "C:\TC" 에 설치된다.)
Start Install 로 커서를 이동하여 Enter...

이제 ReadMe 화면이 나타나고 설치가 종료된다.

% 이 상태에서 "C:\TC\BIN\TC" 를 실행하면 화면이 조금 깨져서 나온다.
    그것은 한글을 지원하지 않아 좀 깨지는 것이다. 그래서 배치 파일을 만들어놨다.
    아까 압축을 푼 폴더에 가면 "exe.bat","TC_EN" 파일이 있을것이다. 이 두개의 파일을 "C:\TC\BIN" 로
    복사한다. 그리고 실행을 "TC_EN" 파일로 하면 깨지는 화면없이 깔끔하게 나올것이다.

Visual Studio 에서 Debug 모드로 실행파일을 실행하고 종료할때 Output 창에 메모리릭이 표시된다.
Win32 콘솔 프로젝트를 만들어서 테스트를 해보자.

다음과 같은 코드를 만들어서 F5 키를 눌러 디버그 해본다.
이게 어쩐일인가 ? Output 창에 메모리릭에 대한 정보가 없다.  ㅡㅡ

다음 코드를 추가해야 나타난다.













% 참고로 콘솔 프로젝트가 아닌 MFC 로 프로젝트를 만들면 이와 같은 추가 작업이 필요없다.

이제 다시 F5 키를 눌러서 Debug 해보자.

다음과 같이 출력될 것이다.

'TestLeak.exe': 'C:\Documents and Settings\Administrator\바탕 화면\TestLeak\Debug\TestLeak.exe' 로드, 기호가 로드되었습니다.
'TestLeak.exe': 'C:\WINDOWS\system32\ntdll.dll' 로드, 기호가 로드되지 않았습니다.
'TestLeak.exe': 'C:\WINDOWS\system32\kernel32.dll' 로드, 기호가 로드되지 않았습니다.
Detected memory leaks!
Dumping objects ->
{41} normal block at 0x00370FF0, 4 bytes long.
 Data: <    > CD CD CD CD
Object dump complete.
'[2536] TestLeak.exe: 네이티브' 프로그램이 0 (0x0) 코드에서 끝났습니다.

이 부분이 메모리 릭 발생 부분이다.

CRT 라이브러리는 프로그램이 실행되고 부터 메모리 생성을 순차적으로 카운트 한다. 그것을 표시한 부분이 {41} 이다. 이것은 41번째로 생성된 메모리가 해제가 안되었다는 것이다. 41 번째 메모리 생성의 의미는 내가 직접 생성한 메모리 이외에도 내가 사용하는 라이브러(MFC 라이브러리, CRT 라이브러리, 등 모든 라이브러리를 포함)에서 생성한 메모리를 모두 카운트 한 수치이다.

이제 메모리가 해제되지 않는 부분을 찾아보자

먼저 OUTPUT 창에서 메모리 릭이 발생한 부분을 더블클릭했을때, 소스로 이동하는 경우가 있다.
이럴때는 감사하게 소스로 이동해서 수정하면된다. ^^; 그리고 밑의 내용을 보지 않아도 되겠다.

먼저 코드를 보자


















_CrtSetBreakAlloc(41);  함수가 추가 되었다.
이것의 역할은 메모리가 41 번재 생성될때 자동으로 브레이크 포인트가 걸리도록 해준다.
 브레이크 포인트가 걸리면 CallStack 창에서 메모리 생성 부분을 찾아 가는 것은 어려운 일이 아닐것이다.

마무리 해보자.
꽤 유용한 방법이지만 이 방법을 쓸수 있는 경우는 그리 많지 않다. 왜냐 하면 프로그램이 항상 같은 루틴으로 동작하는 일이 거의 없기 때문이다. 이 말은 같은 소스 부분이라도 프로그램을 사용하는 방법에 따라 메모리 생성 순서가 변경될수 있다는 말이다. (같은 패턴을 찾으면 가능하겠지만 ...)

% Visual Studio 6 만 한 6~7년간 사용했었다. ㅡㅡ; VS60 기준으로 보면 메모리릭 탐지 기능이 너무 안좋았었다. 생성 해제 부분을 좀 꼬아 버리면 해제 해도 릭이라고 표시되는 부분이 많았다. 지금은 VS2008 까지 나왔으니 이젠 좀 괜찮아 졌겠지 ???

1, 바이트 정렬(Byte Alignment) 왜 해야 하는가 ?

현재 일반적으로 많이 사용하는 32bit 체계를 예를들면, CPU 가 메모리에서 데이타를 가지고 올때
32bit 즉 4byte 의 데이타를 가지고 와서 처리를 한다. 이 얘기는 CPU 가 메모리의 주소를 4byte 단위로
쪼개서 접근을 한다는 말과 같겠다. 

말로 하면 머리에 잘 안들어온다. 예들 들어보도록 하자.

int n; 이라는 변수가 있다.
이변수의 주소는 0x1000 ~ 0x1004 이다. 왜냐 4byte 변수니까 4byte 를 차지하고 있겠지.
이러면 cpu 에서 메모리 0x1000 ~ 0x1004 의 조소를 한번에 받아 처리 할수가 있다.
CPU 는 32bit 씩 처리 되니까.
이러면 아무런 문제가 없다. 그런데 만일 int n;. 의 변수가 0x1001 ~ 0x1005 의 주소에
존재 한다고 생각해 보라.
그러면 CPU 는 메모리의 0x1000 ~ 0x1004, 0x1005 ~ 0x1008 의 주소를 받아서 처리해야
int n; 을 처리할 수 있는것이다.

그러면 쓰잘데기 없이 두번의 일을 하는 결과가 됐다. 그래서 바이트를 정렬해서 효율을 높이자는 것이다.

2, 그럼 실제 바이트 정렬(Byte Alignment) 을 어떻게 설정하나 ?

VC++ 설정에 보면 기본적으로 바이트 정렬해야 하는 byte 가 설정이 되어있다.
다른 컴파일러는 잘 모르것다. 있긴 있겠지 ㅡㅡ

[VC++ 71 설정화면]

이렇게 말로만 해서는 감이 안온다. 직접 코드를 보고 비교해 보장.















이와 같은 코드를 높고 설정값을 변경 해보자. 결과는 다음과 같다.

설정값 ==> nsize 값
1byte ==> 5 (ch, n, n, n, n)
2byte ==> 6 (ch0, nn, nn)
4byte ==> 8 (ch000, nnnn)
8byte ==> 8 (chnnnn000)

% ch: char 변수, n: int 변수, 0: byte padding

3, 컴파일러 바이트 패딩(Compiler Byte Padding) 은 무엇인가 ?

위의 예에서 STTEST 구조체가 실제 5byte 이라도 구조체 멤버 설정이 2byte 이면 6byte 로 나온다.
어떻게 처리되는 것일까 ?

2byte 단위로 정렬하기 때문에 하나의 단위를 2byte 라고 생각해 보자. 그리고 구조체 내의 변수를 넣어보자
처음 char 을 2byte 중 첫번째 바이트에 넣고 다음 int 를 넣으려고 했는데, 4byte 중 1byte 밖에 넣을수가
없다. 그래서 컴파일러는 2byte 정렬제한을 위반하지 않도록 바이트 패팅을 추가하여 char 를 2byte처럼
처리하고 다음 공간부터 int 를 저장한다.


4, 바이트 정렬을 코드에서 처리하기

바이트 정렬 구간을 소스에서 정의 하려면 "#pragma pack" 을 사용한다.

#pragma pack(1)
typedef struct Test
{
 char ch;
 int n;
}STTEST;
#pragma pack()

이렇게 사용하면 1byte 로 정렬하게 된다.
%참고로 "pragma" 키워드는 컴파일러에게 어떻게 처리하라고 명려하는 컴파일러 지시자 이다.

추가로 정렬을 Stack 를 이용할수도 있다.

#pragma pack(push, 1)
typedef struct Test
{
 char ch;
 int n;
}STTEST;
#pragma pack(pop)

요런식이 되겠다.

% 1,2,4,8,16 이외의 값을 설정하면 무시되고 컴파일러 옵션에 설정된 값으로 사용된다.