Symfoware Server アプリケーション開発ガイド(埋込みSQL編) - FUJITSU -

B.4 状態変数およびメッセージ変数の使用例

状態変数とメッセージ変数を使用し、SQL文の実行結果を確認するサンプルプログラムを以下に示します。サンプルプログラムには、SQL文実行時にエラーが発生した場合の処理を記述しています。

例1

状態変数およびメッセージ変数の内容をチェックし、エラーが発生している場合はSQLSTATE値およびメッセージを出力する例

000010 IDENTIFICATION DIVISION.
000020 PROGRAM-ID. COBTHD1.
000030 DATA DIVISION.
000040 WORKING-STORAGE SECTION.
000050      EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000060 01 SQLSTATE  PIC X(5).
000070 01 SQLMSG    PIC X(255).
000080 01 DATA1.
000090   02 NUM     PIC S9(9) BINARY.
000100   02 NAME    PIC X(20).
000110   02 ATTA    PIC X(40).
000120   02 YEAR    PIC S9(4) BINARY.
000130      EXEC SQL END   DECLARE SECTION END-EXEC.
000140
000150 PROCEDURE DIVISION.
000160      INITIALIZE SQLSTATE.
000170      INITIALIZE SQLMSG.
000180      INITIALIZE DATA1.
000190*
000200*
000210* CONNECT
000220      DISPLAY "CONNECT TO DEFAULT"
000230      EXEC SQL
000240             CONNECT TO 'DB1'
000250      END-EXEC.
000260      IF SQLSTATE IS NOT = "00000" THEN
000270          DISPLAY "SQLSTATE " SQLSTATE
000280          DISPLAY "SQLMSG   " SQLMSG
000290      END-IF.
000300*
000310      EXEC SQL
000320          SELECT COL01, COL02, COL03, COL04
000330              INTO :DATA1
000340              FROM SCH.TBL
000350              WHERE COL = 100
000360      END-EXEC.
000370      IF SQLSTATE IS NOT = "00000" THEN
000380          DISPLAY "SQLSTATE " SQLSTATE
000390          DISPLAY "SQLMSG   " SQLMSG
000400      END-IF.
000410*
000420      DISPLAY "COL1=" NUM.
000430      DISPLAY "COL2=" NAME.

000440      DISPLAY "COL3=" ATTA.
000450      DISPLAY "COL4=" YEAR.
000460*
000470* COMMIT
000480      EXEC SQL
000490              COMMIT WORK
000500      END-EXEC.
000510*
000520* DISCONNECT
000530      EXEC SQL
000540              DISCONNECT DEFAULT
000550      END-EXEC.
000560*
000570      EXIT PROGRAM.

例2

WHENEVER文を使用し、SQL文の実行時にエラーが発生した場合、SQLSTATE値およびメッセージを出力する例

000010 IDENTIFICATION DIVISION.
000020 PROGRAM-ID. COBTHD1.
000030 DATA DIVISION.
000040 WORKING-STORAGE SECTION.
000050      EXEC SQL BEGIN DECLARE SECTION END-EXEC.
000060 01 SQLSTATE PIC X(5).
000070 01 SQLMSG   PIC X(255).
000080 01 DATA1.
000090   02 NUM     PIC S9(9) BINARY.
000100   02 NAME    PIC X(20).
000110   02 ATTA    PIC X(40).
000120   02 YEAR    PIC S9(4) BINARY.
000130      EXEC SQL END   DECLARE SECTION END-EXEC.
000140
000150 PROCEDURE DIVISION.
000160      INITIALIZE SQLSTATE.
000170      INITIALIZE SQLMSG.
000180      INITIALIZE DATA1.
000190*
000200      EXEC SQL WHENEVER SQLERROR GOTO :CONNECT_ERROR END-EXEC.
000210*
000220* CONNECT
000230      DISPLAY "CONNECT TO DEFAULT"
000240      EXEC SQL
000250              CONNECT TO 'DB1'
000260      END-EXEC.
000270      EXEC SQL WHENEVER SQLERROR GOTO :STM_ERROR END-EXEC.
000280      EXEC SQL WHENEVER NOT FOUND GOTO :NOT_FOUND_ERROR END-EXEC.
000290*
000300      EXEC SQL
000310          SELECT COL01, COL02, COL03, COL04
000320              INTO :DATA1
000330              FROM SCH.TBL
000340              WHERE COL = 100
000350      END-EXEC.
000360*
000370      DISPLAY "COL1=" NUM.
000380      DISPLAY "COL2=" NAME.
000390      DISPLAY "COL3=" ATTA.
000400      DISPLAY "COL4=" YEAR.
000410*
000420      EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
000430* COMMIT
000440      EXEC SQL COMMIT WORK END-EXEC.
000450* DISCONNECT
000460      EXEC SQL DISCONNECT DEFAULT END-EXEC.
000470      EXIT PROGRAM.
000480*
000490 STM_ERROR.
000500      DISPLAY "SQLSTATE " SQLSTATE
000510      DISPLAY "SQLMSG   " SQLMSG
000520      EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
000530*
000540* COMMIT
000550      EXEC SQL COMMIT WORK END-EXEC.
000560* DISCONNECT
000570      EXEC SQL DISCONNECT DEFAULT END-EXEC.
000580      EXIT PROGRAM.
000590*
000600 NOT_FOUND_ERROR.
000610      DISPLAY "SQLSTATE " SQLSTATE
000620      DISPLAY "SQLMSG   " SQLMSG
000630      EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
000640*
000650* COMMIT
000660      EXEC SQL COMMIT WORK END-EXEC.
000670* DISCONNECT
000680      EXEC SQL DISCONNECT DEFAULT END-EXEC.
000690      EXIT PROGRAM.
000700
000710 CONNECT_ERROR.
000720      DISPLAY "SQLSTATE " SQLSTATE
000730      DISPLAY "SQLMSG   " SQLMSG
000740      EXIT PROGRAM.