[펌] MySQL C API로 MySQL 5 Stored Procedure 사용하기

---

[출처]C 로 MySQL 5 Stored Procedure 사용하기|작성자레인보우

(http://blog.naver.com/gaeean?Redirect=Log&logNo=60040076581)

---

 

초록(Abstract)

MySQL 5.0 에서 가장 주요하게 추가된 기능인 Stored Procedure는 IN 파라미터만 포함하는 경우, 별 문제없이 처리가 가능하지만 OUT 파라미터가 하나라도 있으면 C API로 다루기가 어렵다. 이런 경우 OUT 파라미터의 값을 얻기 위해서는 약간의 workaround가 필요하다. 이 테크노트에서는 그 workaround를 어떻게 구현하면 되는지 살펴보고자 한다.

 

서론 #

• MySQL 5.0이 출시되면서 개발자들에게 가장 환영을 받았던 추가기능이 바로 Stored Procedure였다. 하지만, OUT 파라미터가 포함된 Stored Procedure는 해당 OUT 파라미터의 값을 얻어오는 방법이 API 레벨에서 지원되지 않는 문제가 있다. 이 문제는 현재의 5.0 버전 (5.0.22)에서도 개선되지 않고 있다. 따라서, 이 OUT 파라미터의 값을 얻어오기 위해서는 약간의 workaround가 필요하게 된다.
  
  

 

뭐가 문제인가? MySQL vs. Oracle #

• mysql 클라이언트 프로그램으로 Stored Procedure의 OUT 파라미터 값을 얻는 방법은 아래와 같다.

               mysql> SET @out = null;
               Query OK, 0 rows affected (0.00 sec)
               mysql> CALL proc_test(1, @out);
               Query OK, 0 rows affected (0.00 sec)
               mysql> SELECT @out;
               +------+
               | @out |
               +------+
               | 1234 |
               +------+

• 오라클 sqlplus로 Stored Procedure의 OUT 파라미터 값을 얻는 방법은 아래와 같다.

              SQL> VAR out VARCHAR2(20);
              SQL> CALL proc_getrid(1, :out);
          호출이 완료되었습니다.
              SQL> PRINT out;
              OUT
              --------------------------------
              AAAEhCAAGAAABktAAA

얼핏 보기에는 거의 유사한 것 같지만, API 레벨에서 보기에 가장 커다란 차이점은 out 이라는 OUT 파라미터가 오라클의 경우에는 Bind variable 그 차제(:out)이지만, MySQL의 경우에는 API에서 직접 참조할 수 없는 Connection 내부의 user variable (@out) 이라는 점이다. (MySQL C API에서 Stored Procedure의 OUT 파라미터에 일반 bind variable을 bind하면 에러가 발생한다.)

 

그럼 어떻게 해결하면 되는가? #

• MySQL C API에는 @out과 같은 user variable을 참조하는 API가 제공되지 않기 때문에, SELECT @out;과 같은 추가적인 SQL 실행이 필요하다. 따라서, MySQL C API에서 Stored Procedure를 수행하기 위해서는 총 3번의 SQL을 실행해야 한다.
  1. SET @in = ?, @out = ?;
  2. CALL proc_test(@in, @out);
  3. SELECT @in, @out;
• 위의 경우, 1번 "SET ...;" Statement에 bind한 MYSQL_BIND 구조체 배열을, 마지막 3번 "SELECT @...;" Statement의 result 컬럼으로 bind해도 된다.
• 대략의 코드는 아래와 같다.(편의상 에러 체크 루틴은 생략한다.)
  
  

MYSQL *mysql;    // 미리 유효한 커넥션이 맺어져있다고 가정한다.
//
// Statement 3개 미리 준비..
//
MYSQL_STMT *stmt_set = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt_set, "SET @in = ?, @out = ?", ...);
MYSQL_STMT *stmt_call = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt_call, "CALL proc_test(@in, @out)", ...);
MYSQL_STMT *stmt_select = mysql_stmt_init(mysql);
mysql_stmt_prepare(stmt_select, "SELECT @in, @out", ...);
//
// Bind variable 설정
//int val_in = 1;               // IN 파라미터 value
char buf_out[30];         // OUT 파라미터 buffer

unsigned long len_in, len_out;
my_bool null_in, null_out;
MYSQL_BIND binds[2];
binds[0].buffer_type = MYSQL_TYPE_LONG;
binds[0].buffer = &val_in;
binds[0].length = &len_in;
binds[0].is_null = &null_in;
binds[1].buffer_type = MYSQL_TYPE_VAR_STRING;
binds[1].buffer = buf_out;
binds[1].buffer_length = sizeof(buf_out);
binds[1].length = &len_out;
binds[1].is_null = &null_out;
//
// 1번 SET statement 파라미터 바인딩 & 3번 SELECT statement result 컬럼 바인딩
// (동일한 구조체 배열을 그대로 사용했다.)
//mysql_stmt_bind_param(stmt_set, binds);
mysql_stmt_bind_result(stmt_select, binds);
//
// execute statements
//
mysql_stmt_execute(stmt_set);
mysql_stmt_execute(stmt_call);
mysql_stmt_execute(stmt_select);
//
// fetch
//
mysql_stmt_fetch(stmt_select);
// 여기까지 도달하면 buf_out에 proc_test를 수행한 이후의 OUT 파라미터 값이 저장되어 있다.
...

• Multi-statements 기능을 활용하여 한번의 execution으로 OUT 파라미터의 값을 얻을 수도 있다.
  1. MySQL 서버에 접속할때 호출하는 mysql_real_connect() 함수의 마지막 파라미터에 CLIENT_MULTI_STATEMENTS 옵션을 주고,
  2. mysql_query("SET @in = 1, @out = null; CALL proc_test(@in, @out); SELECT @in, @out;");
     • <<주의>> Multi-statements 기능은 Prepared Statement 기능과 함께 사용할 수 없다.
  3. mysql_more_results() 로 다음 result-set이 있는지 체크하면서, 모든 statement의 result-set을 소비할 때까지 mysql_next_result()를 적절히 호출해준다.
     • 그렇지 않으면, 다음번 Statement 실행시에 "Command out of sync" 오류가 발생한다.
       
       

결론 #

• MySQL에서는 Oracle과 같이 bind variable 자체를 Stored Procedure의 OUT 파라미터로 설정할 수가 없는 한계가 있기 때문에, 위에서 나열한대로 3번의 SQL을 실행시켜야만 OUT 파라미터의 값을 얻을 수 있다.
• "무슨소리? JDBC를 사용해서 MySQL Stored Procedure를 사용할 때 이런 workaround를 쓸 필요없이 그냥 잘 되던데?"
  • MySQL JDBC Driver 에서 이런 작업을 내부적으로 수행한다.
    

     

===================================================================================================