Embedded SQL 개발 시 Database 별 Error Handling

<Database 별 에러코드 체계 정리>

Embedded SQL 을 이용하여 Database Application 개발 시 항상 Error Code 가 DB 별로 달라서 혼동되는 경우가 있다. 시스템의 Database 가 교체되었을 때 Application 의 소스 코드에서 가장 먼저 점검해야하는 부분이 Error Handling 부분이다.
오늘은 Oracle, Altibase, Goldilocks 라는 3 가지 Relational Database 의 Error Code 체계를 비교해본다.
먼저 한가지 알아두어야할 것은 위의 3 가지 DBMS 가 모두 Error 처리 관련하여 SQLCA, SQLCODE, SQLSTATE 라는 3 가지 자료구조를 사용한다는 것이다.
사용자는 ESQL Application 개발 시 이러한 3 가지 자료구조의 특징만 잘 이해하고 있다면, 어떤 것을 이용해서 SQL 결과를 검사하더라도 상관없다. 다만, 아래에 언급되었듯이 SQLCODE 는 사실상 표준에서는 deprecate 되었다고 할 수 있고 DB 마다 용도가 약간씩 다른 부분이 있다. 이 때문에, 표준에 맞는 SQLSTATE 를 이용하는 것이 현재로서는 가장 좋은 방법이다. SQLSTATE 하나만으로도 SQL 결과에 대한 모든 Case(Error, Warning)를 검사할 수 있기 때문이다.
하지만, 현장에서는 아직 관행 상 SQLCA 의 sqlcode 와 SQLCODE, SQLSTATE 등을 혼란스럽게 병행 사용하고 있는 실정이다.

• SQLCA
  DBMS Vendor 가 자체적으로 Error Handling 을 위해 정의한 자료 구조이고, 에러코드(sqlcode) 및 메시지(sqlerrm)를 담기 위한 변수를 비롯한 다양한 변수를 포함한다. Oracle 에서 처음 사용하였고(아마도), Altibase 와 Goldilocks 의 경우는 시장지배자인 Oracle Application 에 대한 호환성을 최대한 맞추기 위해 유사한 자료 구조를 사용한다.
• SQLCODE
  Error Code 를 담는 integer 형 변수이다. ISO/IEC-9075 에서 초기에 제안되었으나 SQL-92 에서 deprecate 되었다. 그러나, 많은 Application 에서 사용하고 있기 때문에 하위 호환성을 위해 아직 사용할 수 있도록 지원하고 있다.
  DB 마다 용도가 조금씩 달라서 잘 이해하고 사용해야 한다. (내 생각에는 사용하지 않는 편이 더 낫다.)
• SQLSTATE
  SQLCODE 가 deprecate 되고 대신 제안되었다.
  5 개의 문자로 구성된다. 앞의 2 자리는 Class, 뒤의 3 자리는 SubClass 라고 한다. 각 Database 는 상황 별로 각기 다른 Error Code 를 가지고 있을 것이지만, 최소한 아래와 같이 앞 2 자리 Class Category 의 의미를 만족하는 범위에서 Error Code 를 적절히 분류해 놓았다.
  Application 개발 시에는 "성공/실패/Warning/결과 없음"과 같이 앞의 2 자리(Class)만으로 결과를 검사하고 싶은 경우도 있지만, 특정 상황에 대한 상세한 Error Code 를 검사하고 싶은 경우도 있을 것이다. 이 때는 SQLSTATE 의 5 자리 모두를 검사해야 한다. 당연히 이는 Database 마다 다른 값일 수 있다.
  - 00000 : 성공
  - 01xxx : Warning 발생
  - 02000 : 결과가 없음
  - 그 밖의 모든 State : Error 발생

각 Database 별로 SQLCA, SQLCODE, SQLSTATE 자료 구조의 특성을 알아보자.

Oracle

Oracle 에서도 Error Handling 관련하여 SQLCA, SQLCODE, SQLSTATE 를 사용한다.
다만, Altibase 나 Goldilocks 와 다른 점이 있는데, SQLCODE 와 SQLSTATE 는 SQLCA 자료 구조와는 완전히 별도로 선언되어야 하는 변수라는 점이다. (Altibase 와 Goldilocks 는 SQLCA 자료 구조의 내부 변수를 가리키는 형태로 구현되어 있다.)
또 한가지 다른 점은, 위에서 언급했듯이 SQLCODE 의 용도이다.
Goldilocks 에서는 SQLCODE 와 SQLSTATE 가 둘 다 Error Code 와 Warning Code 까지 저장되는 반면에, Altibase 와 Oracle 에서는 SQLCODE 는 Error Code 를 저장할 때만 사용된다.
SQLSTATE 는 3 개 Database 모두 동일하게 Error Code 뿐 아니라 Warning Code 까지 저장된다.

그렇다면, Oracle 에서는 사용자가 SQLCA 와 SQLCODE, SQLSTATE 변수를 별도로 선언해주어야 할까?
이는 precompiler 를 통한 전처리를 실행할 때 사용자가 부여하는 Command Line Option 에 따라 다른데, MODE 라는 옵션이 그것이다.

• MODE=ANSI 로 전처리할 경우
  SQLSTATE 선언 : 사용자가 Declare Section 내에 선언 필요 (Declare Section 외부에 선언하면 무시됨)
  SQLCODE 선언 : 사용자가 SQLSTATE 를 이미 선언했을 경우 Optional, SQLSTATE 를 선언하지 않았을 경우 필수
  SQLCA : 사용자가 SQLSTATE 를 선언했건 안했건 Optional.
• MODE=ORACLE
  SQLSTATE : 사용자가 선언하더라도 무시됨
  SQLCODE : 사용자가 선언하더라도 무시됨
  SQLCA : 사용자가 선언 필요. 선언하지 않으면 Compile Error

위의 Case 를 간단하게 정리하면 다음과 같다.

• MODE=ANSI 일 경우 SQLCODE 또는 SQLSTATE 중 하나를 반드시 선언 필요, SQLCA 는 Optional
• MODE=ORACLE 일 경우 SQLCA 는 선언 필요, SQLCODE 와 SQLSTATE 는 선언해도 무시됨

다소 복잡한데 이를 간단하게 이해하기 위해서는 SQLCODE 와 SQLSTATE 가 표준으로부터 생긴 것이라는걸 생각하면 된다. 즉, MODE=ANSI 라는 옵션은 표준을 사용하겠다는 의미이므로, SQLCODE 와 SQLSTATE 중 하나 이상을 반드시 선언해 주어야할 것이다.
반대로, MODE=ORACLE 로 사용한다는 것은 이전부터 Oracle 이 자체적으로 정의해서 사용해온 SQLCA 를 결과 코드 저장용으로 이용하겠다는 것이다. 표준으로부터 생긴 SQLCODE 와 SQLSTATE 는 선언해도 무시된다. 오라클은 표준과 공유를 싫어한다.

SQLCA

SQLCA 의 사용을 위해서는 소스 파일의 상단에 아래와 같이 선언해 주어야 한다. (이는 Goldilocks 와 같다)
SQLCA 자료 구조는 sqlca.h 파일에 정의되어 있다.

EXEC SQL INCLUDE SQLCA; // 또는 #include <sqlca.h>

(참고로, SQLCA 에서 제공하는 Runtime Error 정보보다 더 상세한 정보를 보고 싶을 경우에는 ORACA 자료 구조를 참조하면 되는데 현장에서는 잘 사용하지는 않는 것 같다.)
Precompile 시 MODE=ANSI 로 수행할 경우 SQLCA 선언은 Option 이다.
만약 SQLCA 를 선언해둘 경우 Oracle 은 SQL 수행 결과를 SQLCA 와 SQLSTATE 에 모두 저장해준다.
SQLCA 은 Global 과 Local 에 중복으로 선언하여 사용할 수 있다. 단, 변수의 Scope 에 대한 고려는 사용자의 몫이다.

sqlca.sqlcode 에 저장될 수 있는 값은 다음과 같다.

• 0
  에러 없이 SQL 정상 수행
• > 0
  SQL 은 정상적으로 수행되었으나 Exception 발생.
  SELECT...INTO 나 Fetch 에서 Where 조건에 맞는 레코드를 찾지 못했을 경우에 발생. (1403 등)
  MODE=ANSI,+100 으로 사용할 경우에는 INSERT...SELECT 등을 수행할 때 Insert 가 한 건도 되지 않았을 경우 100 을 리턴. (ANSI 가 아닐 경우 1095 를 리턴)
• < 0
  Database, System, Network 등의 이유로 SQL 이 정상적으로 실행되지 못한 경우.
  이 때는 대부분 트랜잭션을 Rollback 으로 처리하는 것이 좋다.

SQLCODE

MODE=ANSI 로 전처리를 수행할 경우에 사용자는 SQLSTATE 를 선언해야 하는데, 만약 SQLSTATE 를 선언하지 않았다면 SQLCODE 를 반드시 선언해주어야 한다.
만약 사용자가 SQLCODE 를 선언했다면, 선언한 위치에 따라 동작이 다르다.
만약 SQLSTATE 와 동일하게 Declare Section 내에 선언했다면, Oracle 은 SQL 수행 후 결과 코드를 SQLCODE 와 SQLSTATE 에 모두 저장해준다. 하지만, Declare Section 바깥에서 선언했다면 SQLSTATE 에만 결과를 저장해주고 SQLCODE 는 무시된다.
물론 SQLSTATE 를 선언하지 않은 상태라면 SQLCODE 가 어디에 선언되었건 간에 결과를 SQLCODE 에 저장해준다.
SQLCODE 는 Global 이나 Local 에 중복으로 다양하게 선언하여 사용할 수 있다. 단, 변수의 유효 Scope 에 대한 고려는 사용자의 몫이다.
SQLCODE 에는 Warning Code 는 저장되지 않고 Error Code 만 저장된다.
MODE=ORACLE 로 사용할 경우에는 SQLCODE 를 선언해도 무시된다.

long SQLCODE;

SQLSTATE

Precompiler 의 MODE 옵션을 ANSI 로 설정할 경우 SQLSTATE 를 아래와 같이 선언해 주어야 한다. (반드시 Declare Section 내에 선언해야 한다. 그렇지 않으면 무시된다.)
MODE=ORACLE 일 경우에는 선언해도 무시된다.

char SQLSTATE[6]; /* Upper case is required. */

Goldilocks

SQLCA

SQLCA 의 사용을 위해서는 소스파일의 상단에 아래와 같이 선언해주어야 한다.
(sqlca 자료 구조가 정의된 sqlca.h 파일을 include 하기 위함. 선언하지 않으면 빌드 시 sqlca 구조체를 알지 못해 에러 발생)

EXEC SQL INCLUDE SQLCA; // 또는 #include <sqlca.h>

기본적으로 Goldilocks 에서는 sqlca 변수를 전역으로 가지고 있기 때문에 별도의 선언없이 사용할 수 있지만, 전역 변수는 Multi-Threaded 환경에서 thread 간 동시성에 문제가 될 수 있다. 이 때는 thread 별로 별도 선언하여 사용하거나, stack 변수로 사용 가능하도록 함수 내에서 별도로 선언해 주어야 한다.

int func() {
    EXEC SQL BEGIN DECLARE SECTION;
    ...
    EXEC SQL END DECLARE SECTION;
    struct sqlca sqlca;
    ...
}

SQL 이 수행되면 그 결과는 sqlca.sqlcode 에 아래의 값 중 하나로 저장된다.

• SQL_SUCCESS (0)
  SQL 수행 성공
• > 0
  Warning 발생 (다양한 Warning Code)
• SQL_NO_DATA (100)
  조회 결과가 없음
• < 0
  Error 발생 (다양한 Error Code)

Altibase 나 Goldilocks 의 경우는 Embedded SQL 내부적으로 DB 접근을 위해 ODBC Driver 를 사용하기 때문에 ODBC 를 위해 define 된 것들(SQL_SUCCESS, SQL_NO_DATA 등)을 0, 100 과 같은 실제 값 대신 사용할 수 있다.
사용자는 이렇게 DB 에서 제공하는 definition 을 사용할 수도 있지만, 값만 같게 한다면 사용자가 직접 define 하여 다른 이름으로 사용하는 것도 상관없다.

SQLCODE

다음을 보면 Goldilocks 에서 SQLCODE 는 내부적으로 sqlca.sqlcode 와 같다는 것을 알 수 있다. 따라서, 사용자가 SQLCODE 변수 공간을 별도로 선언해줄 필요가 없다. (오라클은 별도의 integer 변수 선언하여 사용)

// $GOLDILOCKS_HOME/include/sql.h
#define SQLCODE  sqlca.sqlcode

SQLCODE 와 sqlca.sqlcode 가 같다는 것은 Altibase 와 다른 점이다.
즉, Altibase 에서는 sqlca.sqlcode 값과 SQLCODE 값이 다를 수 있다. Error 발생 시 sqlca.sqlcode 에는 -1 을 저장한 후 실제 상세한 Code 값은 SQLCODE 와 SQLSTATE 에 저장해주기 때문이다.
하지만, Goldilocks 에서는 Warning 은 0 보다 큰 값으로, Error 시에는 0 보다 작은 값으로 직접 sqlca.sqlcode(=SQLCODE)에 저장해주고, 사용자가 SQLCODE 값을 조회하면 이는 sqlca.sqlcode 값을 리턴해줄 뿐이다.

SQLSTATE

SQLSTATE 는 sqlca.sqlstate 와 같다. 따라서, 사용자가 별도로 SQLSTATE 변수 공간을 선언해줄 필요가 없다.

Altibase

SQLCA, SQLCODE, SQLSTATE 모두 이미 전역으로 선언되어 있어서 사용자는 별도의 선언없이 사용할 수 있다.
그리고, sqlca 자료 구조를 알려주기 위해 sqlca.h 파일을 별도로 include 해주는 코드도 불필요하다. 이는 Altibase 의 경우 sqlca 관련 자료 구조(ulpSqlCa -> sqlca 는 ulpSqlCa 의 인스턴스)가 ulpLibInterface.h 파일에 정의되어 있는데, apre(sesc)를 이용한 전처리 시 해당 header 파일을 자동으로 include 해주기 때문이다.
다만 Multi-Threaded 환경으로 개발할 경우 각 thread 간 동시성에 문제가 없도록 thread 별로 변수가 선언되어야 하는데, 이를 위해 precompiler(apre) 의 -mt 옵션을 사용하거나 파일(sc 파일)에서 다음과 같이 선언해야 한다.

EXEC SQL OPTION (THREADS = TRUE);

SQLCA

다음의 4 가지 값 만을 가질 수 있다.
위에 언급했듯이 Altibase 나 Goldilocks 의 경우는 Embedded SQL 내부적으로 DB 접근을 위해 ODBC Driver 를 사용하기 때문에 ODBC 를 위해 define 된 것들을 0,100 과 같은 실제 값 대신 사용할 수 있다.
sqlca.sqlcode 값이 -1 일 경우 상세한 Error Code 를 확인하고 싶다면 SQLCODE 및 SQLSTATE 값을 확인해야 한다. 하지만, 혼동하지 말아야할 것은 sqlca.sqlcode 값이 SQL_SUCCESS_WITH_INFO(1)인 경우에는 상세한 Warning Code 를 SQLCODE 가 아닌 반드시 SQLSTATE 로 확인해야 한다. (v6 기준. 왜 이렇게 해두었는지는 모르겠지만, 개인적으로는 SQL_ERROR 의 경우와 비교할 때 일관성이 없어서 좀 마음에 안들긴 하다.)
사실, SQLCODE 는 이미 deprecate 된 것이므로 SQLSTATE 로 상세 코드를 확인하는 것이 정석이라 할 수 있겠다.

• SQL_SUCCESS (0)
• SQL_SUCCESS_WITH_INFO (1)
• SQL_NO_DATA (100)
• SQL_ERROR (-1)

SQLCODE

바로 위에서 언급했듯이 sqlca.sqlcode 값이 1 일 경우 정확한 Warning Code 를 확인하기 위해서는 SQLCODE 가 아닌 SQLSTATE 를 확인해야 한다는 것을 주의해야 한다.
(이는 아마도 Oracle 의 SQLCODE 가 Warning Code 를 담지 않는다는 것을 염두에 두고 유사하게 처리한 듯 하다.)
Altibase 는 SQLCODE 가 sqlca.sqlcode 로 정의되어 있는 것이 아니라, sqlca 자료 구조 내의 다른 변수로 정의되어 있다.

• 0
  내장 SQL 문을 성공적으로 수행한 경우. 즉, sqlca.sqlcode 값이 SQL_SUCCESS 인 경우
• 1
  내장 SQL 문을 수행하였으나 warning 이 발견된 경우. 즉, sqlca.sqlcode 값이 SQL_SUCCESS_WITH_INFO 인 경우.
• 100
  SELECT 문이나 FETCH 문 수행 후 반환되는 레코드가 없는 경우.
  즉, sqlca.sqlcode 값이 SQL_NO_DATA 인 경우
• -1
  내장 SQL 문 수행 시 에러가 발생하였지만 해당 에러코드가 없는 경우.
  이 때의 sqlca.sqlcode 값은 SQL_ERROR 이다.
• 그외 음수 값
  내장 SQL 문 수행 시 에러가 발생한 경우. 이 경우, 이 값은 실제 에러 코드이다.

SQLSTATE

SQLSTATE 에는 상태코드가 저장된다.
Altibase 에서도 SQLSTATE 는 Goldilocks 와 마찬가지로 sqlca.sqlstate 값을 참조한다.
이 상태코드를 통해 어떤 에러가 발생했는지, 또는 어떤 warning이 발견되었는지 알 수 있다. SQLSTATE는 내장 SQL문의 결과(sqlca.sqlcode)가 SQL_ERROR 또는 SQL_SUCCESS_WITH_INFO 인 경우 참조할 수 있다. 

References
Altibase 6.5.1 Manual (Precompiler User's Manual)
Goldilocks 3.2 Manual (Handling Runtime Errors)
Oracle Precompilers Guide (Handling Runtime Error)