15 Oracle動的SQL: 方法4
この章ではOracle動的SQL方法4を実装する方法を説明します。この方法では、ホスト変数を含む動的SQL文を受け取ったり、作成できます。含まれるホスト変数の個数は様々です。既存のアプリケーションは、この方法を使用してサポートしてください。新しいアプリケーションでは、すべてANSI動的SQL方法4を使用してください。
Oracle動的SQL方法4は、オブジェクト型、カーソル変数、構造体の配列、DML RETURNING句、Unicode変数およびLOBをサポートしていません。かわりにANSI動的SQL方法4を使用してください。この章のトピックは、次のとおりです:
-
サンプル・プログラム: スクロール可能カーソルを使用する動的SQL方法4
関連項目:
-
動的SQL方法1、2、3の詳細および方法4の概要は、Oracle動的SQLを参照してください。
-
15.1 方法4の特殊要件
方法4の必要条件を学習する前に、選択リスト項目とプレースホルダという用語を理解する必要があります。選択リスト項目とは、問合せ内でキーワードSELECTの後に続く列または式のことです。たとえば、次の動的問合せは3つの選択リスト項目を含んでいます。
SELECT ename, job, sal + comm FROM emp WHERE deptno = 20
プレースホルダとは、SQL文の中で実際のバインド変数用に場所を確保する、ダミーのバインド変数のことです。宣言する必要はなく、任意の名前を付けられます。
バインド変数のプレースホルダはSET句、VALUES句およびWHERE句で最もよく使用されます。たとえば、次の動的SQL文はそれぞれ2つのプレースホルダを含んでいます。
INSERT INTO emp (empno, deptno) VALUES (:e, :d) DELETE FROM dept WHERE deptno = :num OR loc = :loc
15.1.2 Oracleに必要な情報
Pro*C/C++プリコンパイラでは、すべての実行可能な動的SQL文についてOracleコールが生成されます。動的SQL文に選択リスト項目またはプレースホルダが指定されていない場合、Oracleにはその文を実行するための補足情報は必要ありません。次のDELETE文がこのカテゴリに該当します。
DELETE FROM emp WHERE deptno = 30
ただし、ほとんどの動的SQL文には、次のUPDATE文のように、選択リスト項目、またはバインド変数のプレースホルダが含まれています。
UPDATE文:
UPDATE emp SET comm = :c WHERE empno = :e
バインド変数のためのプレースホルダまたは選択リスト項目を含む動的SQL文を実行するには、入力(バインド)値および問合せを実行するときにFETCHされた値を保持するプログラム変数についての情報が必要です。Oracleは次の情報を必要とします。
-
バインド変数の数と選択リスト項目の数
-
各バインド変数と選択リスト項目の長さ
-
各バインド変数と選択リスト項目のデータ型
-
各バインド変数のアドレスと、各選択リスト項目の値が設定される出力変数のアドレス
15.1.3 情報の格納位置
選択リスト項目またはバインド変数のプレースホルダについてOracleで必要な情報は、その値以外はすべて、SQL記述子領域(SQLDA)というプログラム・データ構造体に格納されます。SQLDA構造体はsqlda.hヘッダー・ファイルに定義されています。
選択リスト項目の記述は選択記述子に格納されます。また、バインド変数に対するプレースホルダの記述はバインド記述子に格納されます。
選択リスト項目の値は出力変数に格納され、バインド変数の値は入力変数に格納されます。これらの変数のアドレスを選択SQLDAまたはバインドSQLDAに格納すると、出力値を書き込む位置および入力値を読み込む位置がOracleに認識されます。
値はどのようにしてこれらのデータ変数に格納されるのでしょうか。出力値は、カーソルを使用してFETCHされます。入力値は、通常はユーザーが対話形式で入力した情報をもとに、プログラムによって代入されます。
15.1.4 SQLDAの参照方法
バインド記述子および選択記述子は、通常はポインタによって参照されます。動的SQLプログラムでは、少なくとも1つのバインド記述子と1つの選択記述子のポインタを次のように宣言する必要があります。
#include <sqlda.h> ... SQLDA *bind_dp; SQLDA *select_dp;
この後にSQLSQLDAAlloc()関数を使用すると、次のように記述子を割り当てることができます。
bind_dp = SQLSQLDAAlloc(runtime_context, size, name_length, ind_name_length);
Oracle8以前のバージョンでは、SQLSQLDAAlloc()はsqlaldt()に相当します。
定数SQL_SINGLE_RCTX は、(dvoid*)0として定義されます。これは、アプリケーションがシングル・スレッドの場合に、runtime_contextに使用します。
15.1.5 情報の取得方法
DESCRIBE文を使用すると、Oracleに必要な情報が得られます。
DESCRIBE SELECT LIST文は、各選択リスト項目を検査して名前とその長さを判断します。次にこの情報を使用できるように選択SQLDAに格納します。たとえば、格納された情報は、後で印刷出力の列見出しとして選択リスト名を使用するときなどに使用できます。選択リスト項目の合計数も、DESCRIBE文によりSQLDAに格納されます。
DESCRIBE BIND VARIABLES文は、各プレースホルダを調べて、その名前および長さを確認した後、それらの情報を入力バッファおよびバインドSQLDAに格納します。格納された情報は、後でプレースホルダ名を使用してバインド変数の値の入力をユーザーに求めるときなどに使用できます。
15.2 SQLDAの説明
この項ではSQLDAのデータ構造を詳しく説明します。SQLDAの宣言方法、格納されている変数、初期化の方法、プログラム内での使用方法を理解できます。
15.2.1 SQLDAの目的
選択リスト項目の数またはバインド変数のプレースホルダの数が不明の動的SQL文には、方法4を使用する必要があります。このような動的SQL文を処理するには、プログラムでSQLDA (記述子とも呼ばれます)を明示的に宣言する必要があります。記述子はそれぞれ構造体になっています。記述子はプログラムにコピーまたはハードコードする必要があります。
選択記述子には、選択リスト項目の記述、選択リスト項目の名前および値が格納されている出力バッファのアドレスが保持されます。
注意:
選択リスト項目の名前には、列名、列の別名あるいはsal + comm などの式のテキストを指定できます。
バインド記述子には、バインド変数と標識変数の記述、およびバインド変数と標識変数の名前と値が格納されている入力バッファのアドレスが保持されます。
15.2.2 複数のSQLDA
プログラムにアクティブな動的SQL文が2つ以上ある場合は、それぞれの文が専用のSQLDAを持つ必要があります。別の名前で任意の数のSQLDAを宣言できます。たとえば、sel_desc1、sel_desc2およびsel_desc3という名前を付けて3つの選択SQLDAを宣言すると、同時にOPENされている3つのカーソルからFETCHできます。ただし、非並行のカーソルではSQLDAを再利用できます。
15.2.3 SQLDAの宣言
SQLDAを宣言するには、sqlda.hヘッダー・ファイルをインクルードします。SQLDAの内容は、次のとおりです。
struct SQLDA
{
long N; /* Descriptor size in number of entries */
char **V; Ptr to Arr of addresses of main variables */
int *L; /* Ptr to Arr of lengths of buffers */
short *T; /* Ptr to Arr of types of buffers */
short **I; * Ptr to Arr of addresses of indicator vars */
long F; /* Number of variables found by DESCRIBE */
char **S; /* Ptr to Arr of variable name pointers */
short *M; /* Ptr to Arr of max lengths of var. names */
short *C; * Ptr to Arr of current lengths of var. names */
char **X; /* Ptr to Arr of ind. var. name pointers */
short *Y; /* Ptr to Arr of max lengths of ind. var. names */
short *Z; /* Ptr to Arr of cur lengths of ind. var. names */
};
15.2.4 SQLDAの割当て
SQLDAの宣言後に、次の構文のライブラリ関数SQLSQLDAAlloc()(Oracle8以前のバージョンではsqlaldt()に相当)を使用して、記憶域を割り当てます。
descriptor_name = SQLSQLDAAlloc (runtime_context, max_vars, max_name, max_ind_name);
各パラメータの意味は次のとおりです。
| 構文 | 説明 |
|---|---|
|
runtime_context |
ランタイム・コンテキストへのポインタ |
|
max_vars |
記述子が記述できる選択リスト項目またはプレースホルダの最大数。 |
|
max_name |
選択リスト名またはプレースホルダ名の最大長。 |
|
max_ind_name |
オプション指定でプレースホルダ名に付加される標識変数名の最大長。このパラメータはバインド記述子専用です。したがって、選択記述子を割り当てるときは、このパラメータを0(ゼロ)に設定します。 |
記述子の他に、SQLSQLDAAlloc()は記述子変数が指すデータ・バッファも割り当てます。
図15-1に、変数がSQLSQLDAAlloc()コール、DESCRIBEコマンド、FETCHコマンドまたはプログラム割当てのうち、どの方法で設定されるかを示します。
15.3 SQLDA変数の使用について
15.3.2 V変数
Vは、選択リストまたはバインド変数の値を格納するデータ・バッファのアドレスからなる配列のポインタです。
記述子を割り当てると、SQLSQLDAAlloc()によってアドレスの配列にあるV[0]からV[N - 1]の要素が0(ゼロ)に設定されます。
選択記述子の場合は、FETCHコマンドを発行する前にデータ・バッファを割り当てて、この配列を設定する必要があります。文
EXEC SQL FETCH ... USING DESCRIPTOR ...
この文は、FETCHされた選択リストの値を、V[0]からV[N - 1]が指しているデータ・バッファに格納するようにOracleに指示します。Oracleにより、i番目の選択リストの値が、V[i]が指しているデータ・バッファに格納されます。
バインド記述子の場合は、OPENコマンドを発行する前に、この配列を設定する必要があります。文
EXEC SQL OPEN ... USING DESCRIPTOR ...
この文では、V[0]からV[N - 1]が指しているバインド変数の値を使用して動的SQL文を実行するようにOracleに指示します。Oracleでは、V[i]が指しているデータ・バッファでi番目のバインド変数の値が参照されます。
15.3.3 L変数
Lは、データ・バッファに格納されている選択リストまたはバインド変数の値の長さからなる配列のポインタです。
選択記述子の場合、この配列は、DESCRIBE SELECT LISTによって、各選択リスト項目に予想される最大値に設定されます。しかし、FETCHコマンドを発行する前に長さを再設定する場合も考えられます。FETCHでは最大でn文字が戻されます(nは、FETCHを実行する前のL[i]の値です)。
長さの形式はOracleデータ型によって異なります。CHARまたはVARCHAR2の選択リスト項目については、DESCRIBE SELECT LISTはL[i]を選択リスト項目の最大長に設定します。NUMBER型の選択リスト項目については、位取りおよび精度が変数の下位バイトおよびその次の上位側バイトにそれぞれ戻されます。精度と位取りの値をL[i]から抽出するには、ライブラリ関数SQLNumberPrecV6()を使用できます。
FETCHする前に、L[i]を必要なデータ・バッファの長さに再設定する必要があります。たとえば、NUMBERをC言語のchar文字列に強制変換する場合は、L[i]を精度の数値に符号と小数点のための2を加えたものに設定します。NUMBER型をC言語のfloat型に強制変換する場合は、L[i]をシステム上のfloat型の長さに設定します。
バインド記述子の場合は、OPENコマンドを発行する前に、配列の長さを設定する必要があります。たとえば、strlen()を使用してユーザーが入力したバインド変数文字列の長さを取得してから、適切な配列要素を設定します。
Oracleは、V[i]に格納されているアドレスを使用して間接的にデータ・バッファにアクセスするため、データ・バッファ内の値の長さは認識しません。i番目の選択リスト値またはバインド変数値に対してOracleが使用する長さを変更する場合は、L[i]を必要な長さに再設定してください。入力バッファまたは出力バッファにはそれぞれ異なる長さを指定できます。
関連項目
15.3.4 T変数
Tは、選択リストまたはバインド変数の値のデータ型コードからなる配列のポインタです。これらのデータ型コードは、V配列の要素が指示するデータ・バッファにOracleデータが格納されるときのデータの変換方法を決定します。
選択記述子の場合、DESCRIBE SELECT LISTはデータ型コードの配列を選択リスト内の項目の内部データ型(CHAR、NUMBER、DATEなど)に設定します。
Oracleデータ型の内部形式は処理が複雑なため、FETCHする前にデータ型をいくつか再設定する必要がある場合があります。表示用データのときは、一般には選択リストの値のデータ型をVARCHAR2またはSTRINGに強制変換することをお薦めします。計算用データのときは、Oracleの数値をC言語の形式に強制変換する必要がある場合があります。
T[i]の上位ビットの設定は、i番目の選択リスト項目のNULL/NOT NULLステータスを示します。OPENコマンドまたはFETCHコマンドを発行する前に、常にこのビットを消去する必要があります。データ型コードを取り出してNULL/NOT NULLビットを消去するには、ライブラリ関数SQLColumnNullCheck()を使用します。
OracleのNUMBER内部データ型は、V[i]が指すC言語のデータ・バッファと互換性のある外部データ型に変更する必要があります。
バインド記述子の場合、データ型コードの配列はDESCRIBE BIND VARIABLESによって0に設定されます。OPENコマンドを発行する前に、各要素に格納されたデータ型を設定する必要があります。コードは、V[i]が指すデータ・バッファの外部(C)データ型を表します。バインド変数の値が文字列に格納され、データ型配列の要素が1(VARCHAR2データ型コード)に設定されることがよくあります。データ型コード5(STRING)を使用することもできます。
i番目の選択リストまたはバインド変数の値のデータ型を変更するには、T[i]を変更するデータ型に再設定してください。
15.3.5 I変数
Iは、標識変数値を格納するデータ・バッファのアドレスの配列へのポインタです。
アドレスの配列のI[0]からI[N - 1]の要素を設定する必要があります。
選択記述子の場合は、FETCHコマンドを発行する前に、アドレスの配列を設定する必要があります。次の文を実行するとします。
EXEC SQL FETCH ... USING DESCRIPTOR ...
この場合、戻されたi番目の選択リスト値がNULLの場合は、I[i]が指す標識変数値が-1に設定されます。それ以外の場合は、(値がNULLでない) 0または(値が切り捨てられている)正の整数に設定されます。
バインド記述子の場合は、OPENコマンドを発行する前に、アドレスの配列とそれに対応付けられた標識変数を設定する必要があります。次の文を実行するとします。
EXEC SQL OPEN ... USING DESCRIPTOR ...
この場合、I[i]が指しているデータ・バッファはi番目のバインド変数の値がNULLであるかどうかを決定します。標識変数の値が-1のとき、関連するバインド変数の値はNULLです。
15.4 予備知識
15.4.1 データの変換
この項では、T(データ型)の記述子配列について詳しく説明します。データ型の同値化と動的SQL方法4のどちらも使用しないホスト・プログラムでは、Oracleの内部データ型と外部データ型との変換方法はプリコンパイル時に決定されます。デフォルトでは、プリコンパイラは宣言部内のそれぞれのホスト変数に特定の外部データ型を割り当てます。たとえば、プリコンパイラは int型のホスト変数にINTEGER外部データ型を割り当てます。
しかし方法4を使用すると、データの変換および形式を制御できます。データの変換方法を指定するには、T記述子配列にデータ型コードを設定します。
15.4.1.1 内部データ型
内部データ型は、Oracleがデータベース表に列値を格納するための形式と、疑似列値を表すための形式を指定します。
DESCRIBE SELECT LISTコマンドを発行すると、Oracleはそれぞれの選択リスト項目に対する内部データ型コードをT記述子配列に戻します。たとえば、i番目の選択リスト項目に対するデータ型コードはT[i]に戻されます。
表15-1に、Oracleの内部データ型とそのコードを示します。
表15-1 Oracle内部データ型
| Oracle内部データ型 | コード |
|---|---|
|
VARCHAR2 |
1 |
|
NUMBER |
2 |
|
LONG |
8 |
|
BINARY_FLOAT |
100 |
|
BINARY_DOUBLE |
101 |
|
ROWID |
11 |
|
DATE |
12 |
|
RAW |
23 |
|
LONG RAW |
24 |
|
CHARACTER (またはCHAR) |
96 |
|
ユニバーサルROWID |
104 |
15.4.1.2 外部データ型
外部データ型は、入力ホスト変数と出力ホスト変数に値を格納するための形式を指定します。
DESCRIBE BIND VARIABLESコマンドはデータ型コードのT配列を0(ゼロ)に設定します。このため、OPENコマンドを発行する前に、それらのコードを再設定する必要があります。データ型コードは、様々なバインド変数にどの外部データ型が使用されるかをOracleに知らせます。i番目のバインド変数については、必要な外部データ型をT[i]に再設定してください。
表15-2に、Oracleの外部データ型とそのコード、および各外部データ型で通常使用するC言語のデータ型を示します。
表15-2 Oracle外部データ型とデータ型コード
| 外部データ型 | コード | C言語のデータ型 |
|---|---|---|
|
VARCHAR2 |
1 |
char[n] |
|
NUMBER |
2 |
char[n] (n <= 22) |
|
INTEGER |
3 |
int |
|
FLOAT |
4 |
float |
|
STRING |
5 |
char[n+1] |
|
VARNUM |
6 |
char[n] (n <= 22) |
|
DECIMAL |
7 |
float |
|
LONG |
8 |
char[n] |
|
SQLT_BFLOAT |
21 |
float |
|
SQLT_BDOUBLE |
22 |
double |
|
VARCHAR |
9 |
char[n+2] |
|
ROWID |
11 |
char[n] |
|
DATE |
12 |
char[n] |
|
VARRAW |
15 |
char[n] |
|
RAW |
23 |
unsigned char[n] |
|
LONG RAW |
24 |
unsigned char[n] |
|
UNSIGNED |
68 |
unsigned int |
|
DISPLAY |
91 |
char[n] |
|
LONG VARCHAR |
94 |
char[n+4] |
|
LONG VARRAW |
95 |
unsigned char[n+4] |
|
CHAR |
96 |
char[n] |
|
CHARF |
96 |
char[n] |
|
CHARZ |
97 |
char[n+1] |
15.4.2 データ型の強制変換
選択記述子の場合、DESCRIBE SELECT LISTはOracleの内部データ型をどれでも戻すことができます。文字データの場合など、ほとんどの場合内部データ型は適切な外部データ型と正確に対応しています。ただし、内部データ型には扱いにくい外部データ型にマップするものもあります。そのため、T記述子配列の一部の要素を再設定する必要がある場合があります。たとえば、NUMBER値をC言語のfloat値に対応するFLOAT値に再設定する場合があります。Oracleは、内部データ型と外部データ型の間の必要な変換をFETCH時に行います。このため、データ型の再設定は必ずDESCRIBE SELECT LISTの後、FETCHの前に行ってください。
バインド記述子の場合は、DESCRIBE BIND VARIABLESによってバインド変数のデータ型が戻されることはなく、バインド変数の数および名前のみ戻されます。したがって、データ型コードのT配列を明示的に設定することで、それぞれのバインド変数の外部データ型をOracleに通知する必要があります。Oracleは、内部データ型と外部データ型の間の必要な変換をOPEN時に行います。
T記述子配列でデータ型コードを再設定すると、データ型を強制変換することになります。たとえば、i番目の選択リスト値をSTRINGに強制変換するには、次の文を使用します。
/* Coerce select-list value to STRING. */ select_des->T[i] = 5;
データ表示用にNUMBERの選択リスト値をSTRINGに強制変換するときは、値の精度と位取りのバイトを抽出し、それらを使用して最大表示長を算出する必要もあります。FETCHの前に、L (長さ)記述子配列の該当する要素を再設定し、使用するバッファの長さをOracleに通知する必要があります。
たとえば、DESCRIBE SELECT LISTによってi番目の選択リスト項目のデータ型がNUMBER型であるとわかっているとします。このときfloat型で宣言されているC変数に戻り値を格納する場合は、T[i]には4を、L[i]にはシステムが定めるfloatの長さを設定するのみで済みます。
警告:
DESCRIBE SELECT LISTによって戻される内部データ型が、目的に合わない場合もあります。DATE型およびNUMBER型がその例です。DATE型の選択リスト項目をDESCRIBEすると、Oracleではデータ型コード12がT記述子配列に戻されます。FETCHの前にコードを再設定しないかぎり、日付の値はその7バイト内部形式で戻されます。日付を文字形式(DD-MON-YY)で取得するには、12に設定されているデータ型コードを1(VARCHAR2)または5(STRING)に変更し、7に設定されているL値を9または10に増やします。
NUMBER型の選択リスト項目を同じ要領でDESCRIBEすると、Oracleではデータ型コード2がT配列に戻されます。FETCHの前にコードを再設定しないかぎり、数値はその内部形式で戻されるため、おそらく求めている値とは異なります。そのときは、2に設定されているコードを1(VARCHAR2)、3(INTEGER)、4(FLOAT)、5(STRING)またはその他の適切なデータ型に変更します。
関連項目
15.4.2.1 精度および位取りの抽出
ライブラリ関数SQLNumberPrecV6()(従来のsqlprc())は、精度と位取りを抽出します。この関数は通常、DESCRIBE SELECT LISTの後に使用します。その最初の引数はL[i]です。次の構文で、SQLNumberPrecV6()をコールします。
注意:
プラットフォームの正しいプロトタイプは、プラットフォーム固有のSQLNumberPrecV6ヘッダー・ファイルを参照してください。
SQLNumberPrecV6(dvoid *runtime_context, int *length, int *precision,
int *scale);
各パラメータの意味は次のとおりです。
位取りが負の場合は、その絶対値を長さに追加してください。たとえば、精度に3、位取りに-2を指定すると、99900までの値が有効になります。
次の例に、SQLNumberPrecV6()を使用して、STRINGに強制変換するNUMBER値の最大値表示長を計算する方法を示します。
/* Declare variables for the function call. */ sqlda *select_des; /* pointer to select descriptor */ int prec; /* precision */ int scal; /* scale */ extern void SQLNumberPrecV6(); /* Declare library function. */ /* Extract precision and scale. */ SQLNumberPrecV6(SQL_SINGLE_RCTX, &(select_des->L[i]), &prec, &scal); /* Allow for maximum size of NUMBER. */ if (prec == 0) prec = 38; /* Allow for possible decimal point and sign. */ select_des->L[i] = prec + 2; /* Allow for negative scale. */ if (scal < 0) select_des->L[i] += -scal;
この関数コールの最初の引数は長さの配列のi番目の要素を指します。また、パラメータは3つともすべてアドレスであることに注意してください。
SQLNumberPrecV6()関数では、一部のSQLデータ型の精度と位取りの値に0(ゼロ)が戻されます。SQLNumberPrecV7()関数も同様で、次に示すSQLデータ型の場合を除けば引数リストも戻り値も同じです。
表15-3 SQLデータ型の精度と位取り
| SQLデータ型 | 2進数精度 | 位取り |
|---|---|---|
|
FLOAT |
126 |
-127 |
|
FLOAT(N) |
N(範囲は1から126) |
-127 |
|
REAL |
63 |
-127 |
|
DOUBLE PRECISION |
126 |
-127 |
15.4.3 NULLまたはNOT NULLデータ型の処理
すべての選択リスト列(式は不可)について、DESCRIBE SELECT LISTは選択記述子のデータ型配列TにNULL/NOT NULLインジケータを戻します。i番目の選択リスト列にNOT NULL制約が指定されていると、T[i]の上位ビットはオフにされます。それ以外の場合は上位ビットが設定されます。
OPEN文またはFETCH文でデータ型を使用する前に、すでにNULL/NOT NULLビットが設定されているときは、そのビットをオフにする必要があります。(このビットは絶対にオンにしないでください。)
列にNULLが有効かどうかを調べ、データ型のNULL/NOT NULLビットを消去するには、ライブラリ関数SQLColumnNullCheck()(従来のsqlnul())を使用します。次の構文で、SQLColumnNullCheck()をコールします。
SQLColumnNullCheck(dvoid *context, unsigned short *value_type,
unsigned short *type_code, int *null_status);各パラメータの意味は次のとおりです。
| 構文 | 説明 |
|---|---|
|
context |
ランタイム・コンテキストへのポインタ |
|
value_type |
選択リスト列のデータ型コードを格納する符号なしshort int型変数へのポインタ。データ型はT[i]に格納されます。 |
|
type_code |
選択リスト列のデータ型コードを戻す符号なしshort int型変数へのポインタ。上位ビットはオフにされています。 |
|
null_status |
選択リスト列のNULL状態を戻すint型変数へのポインタ。1は列がNULLを許可し、0は許可しないことを意味します。 |
次の例に、SQLColumnNullCheck()の使用方法を示します。
/* Declare variables for the function call. */
sqlda *select_des; /* pointer to select descriptor */
unsigned short dtype; /* datatype without null bit */
int nullok; /* 1 = null, 0 = not null */
extern void SQLColumnNullCheck(); /* Declare library function. */
/* Find out whether column is not null. */
SQLColumnNUllCheck(SQL_SINGLE_RCTX, (unsigned short *)&(select_des->T[i]), &dtype, &nullok);
if (nullok)
{
/* Nulls are allowed. */
...
/* Clear the null/not null bit. */
SQLColumnNullCheck(SQL_SINGLE_RCTX, &(select_des->T[i]), &(select_des->T[i]), &nullok);
}
SQLColumnNullCheck()関数の2回目のコールで指定されている1番目と2番目の引数は、データ型配列のi番目の要素を指します。また、パラメータは3つともアドレスであることに注意してください。
15.5 基本ステップ
方法4は任意の動的SQL文に使用できます。次の例には問合せの処理が示されているため、入力ホスト変数と出力ホスト変数の両方の処理方法が理解できます。
このサンプル・プログラムでは次の手順に従って動的問合せを処理します。
- 問合せのテキストを保持するためのホスト文字列を宣言部で宣言します。
- 選択SQLDAとバインドSQLDAを宣言します。
- 選択記述子とバインド記述子に対する記憶域を割り当てます。
- DESCRIBEできる選択リスト項目とプレースホルダの最大数を設定します。
- 問合せのテキストをホスト文字列に設定します。
- ホスト文字列から問合せをPREPAREします。
- 問合せ用のカーソルをDECLAREします。
- バインド記述子にバインド変数をDESCRIBEします。
- プレースホルダの最大数をDESCRIBEによって実際に検出された数に再設定します。
- DESCRIBEで検出されたバインド変数の値を取得し、それらの変数に対する記憶域を割り当てます。
- バインド記述子を使用してカーソルをOPENします。
- 選択記述子に選択リストをDESCRIBEします。
- 選択リスト項目の最大数をDESCRIBEにより実際に検出された数に再設定します。
- 表示用にそれぞれの選択リスト項目の長さおよびデータ型を再設定します。
- 選択記述子が指している割当て済のデータ・バッファに(INTO)データベースの行をFETCHします。
- FETCHにより戻された選択リストの値を処理します。
- 選択リスト項目、プレースホルダ、標識変数および記述子に対する記憶域の割当てを解除します。
- カーソルをCLOSEします。
注意:
動的SQL文に含まれる選択リスト項目またはプレースホルダの数が明確である場合、一部の手順は必要ありません。
15.6 各手順の詳細
この項では、それぞれのステップを詳しく説明します。章の終わりには、方法4を使用したコメント付きの完全なプログラム例を示します。
EXEC SQL PREPARE statement_name
FROM { :host_string | string_literal };
EXEC SQL DECLARE cursor_name CURSOR FOR statement_name;
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
INTO bind_descriptor_name;
EXEC SQL OPEN cursor_name
[USING DESCRIPTOR bind_descriptor_name];
EXEC SQL DESCRIBE [SELECT LIST FOR] statement_name
INTO select_descriptor_name;
EXEC SQL FETCH cursor_name
USING DESCRIPTOR select_descriptor_name;
EXEC SQL CLOSE cursor_name;
方法4では、スクロール可能カーソルも使用できます。スクロール可能カーソルには、埋込みSQL文を次の順序で使用する必要があります。
EXEC SQL PREPARE statement_name
FROM { :host_string | string_literal };
EXEC SQL DECLARE cursor_name SCROLL CURSOR FOR statement_name;
EXEC SQL DESCRIBE BIND VARIABLES FOR statement_name
INTO bind_descriptor_name;
EXEC SQL OPEN cusor_name
[ USING DESCRIPTOR bind_descriptor_name];
EXEC SQL DESCRIBE [ SELECT LIST FOR] statement_name
INTO select_descriptor_name;
EXEC SQL FETCH [ FIRST| PRIOR|NEXT|LAST|CURRENT | RELATIVE fetch_offset
|ABSOLUTE fetch_offset ] cursor_name USING DESCRIPTOR
select_descriptor_name;
EXEC SQL CLOSE cursor_name;
動的問合せの選択リスト項目の数がわかっているときは、DESCRIBE SELECT LISTを省略するとともに次の方法3のFETCH文を使用できます。
EXEC SQL FETCH cursor_name INTO host_variable_list;
または、動的SQL文のバインド変数に対するプレースホルダの数が明確な場合は、DESCRIBE BIND VARIABLESを省略するとともに次の方法3のOPEN文を使用できます。
EXEC SQL OPEN cursor_name [USING host_variable_list];
次にこれらの文により、ホスト・プログラムで記述子を使用して動的SQL文を受け入れ、それを処理する方法を説明します。
注意:
以降では、図を使用して説明します。図が複雑になるのを避けるために、次の条件を満たすことが必要です。
-
記述子配列は3要素までに制限します。
-
名前の最大長は5文字以内に制限します。
-
値の最大長は10文字以内に制限します。
15.6.1 ホスト文字列の宣言
プログラムには、動的SQL文のテキストを格納するためのホスト変数が必要です。ホスト変数(ここではselect_stmt)は文字列として宣言する必要があります。
... int emp_number; VARCHAR emp_name[10]; VARCHAR select_stmt[120]; float bonus;
15.6.2 SQLDAの宣言
ここでは、SQLDAのデータ構造体をハードコードするかわりに、次のようにINCLUDEを使用してSQLDAをプログラムにコピーします。
#include <sqlda.h>
問合せに含まれる選択リスト項目の数またはバインド変数のプレースホルダの数が不明なため、次のように選択記述子とバインド記述子のポインタを宣言します。
sqlda *select_des; sqlda *bind_des;
15.6.3 記述子用の記憶域の割当て
記述子用の記憶域を割り当てるには、SQLSQLDAAlloc() ライブラリ関数を使用することを思い出してください。ANSI C表記法による構文は次のとおりです。
SQLDA *SQLSQLDAAlloc(dvoid *context, unsigned int max_vars, unsigned int max_name, unsigned int max_ind_name);
SQLSQLDAAlloc()関数は、記述子構造体およびポインタ変数V、L、T、Iによってアドレス指定される配列を割り当てます。
max_nameが0(ゼロ)以外のときは、ポインタ変数S、M、Cによってアドレス指定される配列が割り当てられます。max_ind_nameが0(ゼロ)以外のときは、ポインタ変数X、Y、Zによってアドレス指定される配列が割り当てられます。max_nameとmax_ind_nameが0(ゼロ)のときは、領域は割り当てられません。
SQLSQLDAAlloc()は、成功すると構造体のポインタを戻します。SQLSQLDAAlloc()は、失敗すると0(ゼロ)を戻します。
この例では、選択記述子とバインド記述子を次のように割り当てます。
select_des = SQLSQLDAAlloc(SQL_SINGLE_RCTX, 3, (size_t) 5, (size_t) 0); bind_des = SQLSQLDAAlloc(SQL_SINGLE_RCTX, 3, (size_t) 5, (size_t) 4);
選択記述子には、Xによってアドレス指定される配列に領域が割り当てられないようにするため、常にmax_ind_nameを0(ゼロ)に設定します。
15.6.4 DESCRIBEへの最大数の設定
15.6.5 ホスト文字列への問合せテキストの設定
次の例では、ユーザーにSQL文の入力を求め、入力された文字列をselect_stmtに格納します。
printf("\n\nEnter SQL statement: ");
gets(select_stmt.arr);
select_stmt.len = strlen(select_stmt.arr);
このときユーザーが次の文字列を入力したと仮定します。
"SELECT ename, empno, comm FROM emp WHERE comm < :bonus"
15.6.7 カーソルの宣言
DECLARE CURSORは名前を指定し、特定のSELECT文に対応付けることにより、カーソルを定義します。
静的問合せ用のカーソルを宣言するには、次の構文を使用します。
EXEC SQL DECLARE cursor_name CURSOR FOR SELECT ...
動的問合せ用にカーソルを宣言するには、PREPAREにより動的問合せに指定された文の名前で静的問合せを置換します。例では、DECLARE CURSORはemp_cursorという名前のカーソルを定義し、このカーソルをsql_stmtに対応付けます。
EXEC SQL DECLARE emp_cursor CURSOR FOR sql_stmt;
注意:
問合せのみでなく、すべての動的SQL文のカーソルを宣言できます。また、問合せ以外の場合も、カーソルのOPENにより動的SQL文を実行します。
15.6.8 バインド変数のDESCRIBE
DESCRIBE BIND VARIABLESは、バインド記述子にプレースホルダの記述を設定します。例では、DESCRIBEは次のようにbind_desを準備します。
EXEC SQL DESCRIBE BIND VARIABLES FOR sql_stmt INTO bind_des;
bind_desはコロンで始めることはできません。
DESCRIBE BIND VARIABLES文はPREPARE文の後で、かつOPEN文の前に指定する必要があります。
図15-4に、DESCRIBE実行後のバインド記述子を示します。SQL文の実行で検出されたプレースホルダの実際の数が、DESCRIBEによってFに設定されています。
15.6.9 プレースホルダの最大数の再設定
次に、プレースホルダの最大数を、DESCRIBEによって実際に検出された数に再設定する必要があります。
bind_des->N = bind_des->F;
15.6.10 バインド変数の値の取得と記憶域の割当て
プログラムでは、SQL文で検出されたバインド変数に対する値を取得し、メモリーを割り当てる必要があります。値はどのように取得してもかまいません。たとえば値をハードコードしたり、ファイルから読み込んだり、または対話形式で入力することもできます。
例では、問合せのWHERE句のプレースホルダbonusに置換されるバインド変数に値を割り当てる必要があります。そこで、ユーザーに値の入力を求め、入力された値を次のように処理します。
for (i = 0; i < bind_des->F; i++)
{
printf("\nEnter value of bind variable %.*s:\n? ",
(int) bind_des->C[i], bind_des->S[i]);
gets(hostval);
/* Set length of value. */
bind_des->L[i] = strlen(hostval);
/* Allocate storage for value and null terminator. */
bind_des->V[i] = malloc(bind_des->L[i] + 1);
/* Allocate storage for indicator value. */
bind_des->I[i] = (unsigned short *) malloc(sizeof(short));
/* Store value in bind descriptor. */
strcpy(bind_des->V[i], hostval);
/* Set value of indicator variable. */
*(bind_des->I[i]) = 0; /* or -1 if "null" is the value */
/* Set datatype to STRING. */
bind_des->T[i] = 5;
}
ここでは、ユーザーがbonusの値として625を入力したと想定します。図15-5に、結果として得られるバインド記述子を示します。値はヌル文字で終了します。
15.6.11 カーソルのOPEN
動的問合せに使用するOPEN文は、カーソルがバインド記述子に対応付けられることを除けば静的問合せに使用するものと同じです。実行時に決定され、バインド記述子表の要素でアドレス指定したバッファに格納された値を使用して、SQL文を評価します。問合せの場合は、アクティブ・セットの識別にも同じ値を使用します。
例では、OPENは次のようにemp_cursorをbind_desに対応付けます。
EXEC SQL OPEN emp_cursor USING DESCRIPTOR bind_des;
bind_desはコロンで始めることはできません。
OPENはSQL文を実行します。問合せのときは、OPENはアクティブ・セットを決定するとともにカーソルを先頭行に位置づけます。
15.6.12 選択リストのDESCRIBE
動的SQL文が問合せのときは、DESCRIBE SELECT LIST文はOPEN文の後で、かつFETCH文の前に指定する必要があります。
DESCRIBE SELECT LISTは、選択記述子に選択リスト項目の記述を設定します。例では、DESCRIBEは次のようにselect_desを準備します。
EXEC SQL DESCRIBE SELECT LIST FOR sql_stmt INTO select_des;
Oracleのデータ・ディクショナリにアクセスすることで、DESCRIBEは各選択リストの値の長さとデータ型を設定します。
図15-6に、DESCRIBE実行後の選択記述子を示します。問合せの選択リストで検出された項目の実際の数がDESCRIBEによってFに設定されています。SQL文が問合せでないときは、Fは0(ゼロ)に設定されます。
また、NUMBER型の長さはまだ使用できないことに注意してください。NUMBERと定義した列には、ライブラリ関数SQLNumberPrecV6() を使用して精度と位取りを抽出する必要があります。
関連項目
15.6.13 選択リスト項目の最大数の再設定
次に選択リスト項目の最大数を、DESCRIBEにより実際に検出された数に再設定する必要があります。
select_des->N = select_des->F;
15.6.14 各選択リスト項目の長さとデータ型の再設定
例では、選択リストの値をFETCHする前に、ライブラリ関数malloc()を使用して、記憶域を割り当てます。また、表示用の長さとデータ型の配列の要素のいくつかを再設定します。
for (i=0; i<select_des->F; i++)
{
/* Clear null bit. */
SQLColumnNullCheck(SQL_SINGLE_RCTX, (unsigned short *)&(select_des->T[i]),
(unsigned short *)&(select_des->T[i]), &nullok);
/* Reset length if necessary. */
switch(select_des->T[i])
{
case 1: break;
case 2: SQLNumberPrecV6(SQL_SINGLE_RCTX, (unsigned long *)
&(select_des->L[i]), &prec, &scal);
if (prec == 0) prec = 40;
select_des->L[i] = prec + 2;
if (scal < 0) select_des->L[i] += -scal;
break;
case 8: select_des->L[i] = 240;
break;
case 11: select_des->L[i] = 18;
break;
case 12: select_des->L[i] = 9;
break;
case 23: break;
case 24: select_des->L[i] = 240;
break;
}
/* Allocate storage for select-list value. */
select_des->V[i] = malloc(select_des->L[i]+1);
/* Allocate storage for indicator value. */
select_des->I[i] = (short *)malloc(sizeof(short *));
/* Coerce all datatypes except LONG RAW to STRING. */
if (select_des->T[i] != 24) select_des->T[i] = 5;
}
図15-7に、結果として得られる選択記述子を示します。NUMBERの長さはこのとき使用可能となります。データ型はすべてSTRINGです。L[1]およびL[2]の長さはそれぞれ6と9になっています。これは、DESCRIBEされた長さ4と7にそれぞれ符号と小数点のための2を加算したためです。
15.6.15 アクティブ・セットからの行のFETCH
FETCHはアクティブ・セットから1行を戻し、データ・バッファに選択リストの値を格納してから、カーソルをアクティブ・セットの次の行に進めます。行がなくなると、FETCHは「データが見つかりません。」のOracleエラー・コードをsqlca.sqlcodeに設定します。例では、次に示すように、FETCHによってENAME、EMPNOおよびCOMMの列の値をselect_desに戻します。
EXEC SQL FETCH emp_cursor USING DESCRIPTOR select_des;
図15-8に、FETCH実行後の選択記述子を示します。Oracleは選択リストの値とインジケータの値を、VとIの要素によってアドレス指定されるデータ・バッファに格納しています。
データ型1の出力バッファについては、OracleはL配列に格納された長さを使用し、CHARまたはVARCHAR2のデータを左揃えにしてから、NUMBERデータを右揃えにします。データ型5(STRING)の出力バッファについては、値を左揃えにし、CHAR、VARCHAR2およびNUMBERのデータにヌル文字を付けます。
値'MARTIN'は、EMP表のVARCHAR2(10)列から取り出されました。L[0]の長さを使用して、Oracleは10バイトのフィールドの値を左揃えにしてバッファを埋めます。
値7654はNUMBER(4)列から取り出され、'7654'に強制変換されています。しかし、符号と小数点を使用可能にするため、L[1]の長さが2のみ増えています。そこでOracleは6バイトのフィールドの値を左揃えにしてから、ヌル文字を付けます。
値482.50はNUMBER(7,2)列から取り出され、'482.50'に強制変換されています。L[2]の長さが2のみ増えています。Oracleは9バイトのフィールドの値を左揃えにしてから、ヌル文字を付けます。
15.6.17 記憶域の割当て解除
malloc()によって割り当てられた記憶域を解除するには、free()ライブラリ関数を使用します。構文は次のとおりです。
free(char *pointer);
例では、選択リスト項目、バインド変数および標識変数の値に対する記憶域の割当てを次のように解除します。
for (i = 0; i < select_des->F; i++) /* for select descriptor */
{
free(select_des->V[i]);
free(select_des->I[i]);
}
for (i = 0; i < bind_des->F; i++) /* for bind descriptor */
{
free(bind_des->V[i]);
free(bind_des->I[i]);
}
記述子の記憶域の割当てを解除するには、次の構文のライブラリ関数SQLSQLDAFree()を使用します。
SQLSQLDAFree(context, descriptor_name);
記述子はSQLSQLDAAlloc()を使用して割り当ててください。そうしないと結果は予測できなくなります。
例では、選択記述子とバインド記述子に対する記憶域の割当てを次のように解除します。
SQLSQLDAFree(SQL_SINGLE_RCTX, select_des); SQLSQLDAFree(SQL_SINGLE_RCTX, bind_des);
15.6.19 ホスト配列の使用について
方法4で入力ホスト配列または出力ホスト配列を使用するには、オプションのFOR句を使用してホスト配列のサイズをOracleに通知する必要があります。
次の構文を使用して、i番目の選択リスト項目またはバインド変数に記述子エントリを設定する必要があります。
V[i] = array_address; L[i] = element_size;
array_addressはホスト配列のアドレスで、element_sizeはある配列要素のサイズです。
EXECUTE文またはFETCH文(どちらか適切な方)にFOR句を指定して、処理対象の配列要素の数をOracleに通知する必要があります。Oracleがホスト配列のサイズを認識する方法は他にないので、この手順は必須です。
次の完全なプログラム例では、3つの入力ホスト配列を使用してEMP表に行をINSERTします。方法4による問合せ以外のDML文にもEXECUTEを使用できます。
#include <stdio.h>
#include <sqlcpr.h>
#include <sqlda.h>
#include <sqlca.h>
#define NAME_SIZE 10
#define INAME_SIZE 10
#define ARRAY_SIZE 5
/* connect string */
char *username = "scott/tiger";
char *sql_stmt =
"INSERT INTO emp (empno, ename, deptno) VALUES (:e, :n, :d)";
int array_size = ARRAY_SIZE; /* must have a host variable too */
SQLDA *binda;
char names[ARRAY_SIZE][NAME_SIZE];
int numbers[ARRAY_SIZE], depts[ARRAY_SIZE];
/* Declare and initialize indicator vars. for empno and deptno columns */
short ind_empno[ARRAY_SIZE] = {0,0,0,0,0};
short ind_dept[ARRAY_SIZE] = {0,0,0,0,0};
main()
{
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
/* Connect */
EXEC SQL CONNECT :username;
printf("Connected.\n");
/* Allocate the descriptors and set the N component.
This must be done before the DESCRIBE. */
binda = SQLSQLDAAlloc(SQL_SINGLE_RCTX, 3, NAME_SIZE, INAME_SIZE);
binda->N = 3;
/* Prepare and describe the SQL statement. */
EXEC SQL PREPARE stmt FROM :sql_stmt;
EXEC SQL DESCRIBE BIND VARIABLES FOR stmt INTO binda;
/* Initialize the descriptors. */
binda->V[0] = (char *) numbers;
binda->L[0] = (long) sizeof (int);
binda->T[0] = 3;
binda->I[0] = ind_empno;
binda->V[1] = (char *) names;
binda->L[1] = (long) NAME_SIZE;
binda->T[1] = 1;
binda->I[1] = (short *)0;
binda->V[2] = (char *) depts;
binda->L[2] = (long) sizeof (int);
binda->T[2] = 3;
binda->I[2] = ind_dept;
/* Initialize the data buffers. */
strcpy(&names[0] [0], "ALLISON");
numbers[0] = 1014;
depts[0] = 30;
strcpy(&names[1] [0], "TRUSDALE");
numbers[1] = 1015;
depts[1] = 30;
strcpy(&names[2] [0], "FRAZIER");
numbers[2] = 1016;
depts[2] = 30;
strcpy(&names[3] [0], "CARUSO");
numbers[3] = 1017;
ind_dept[3] = -1; /* set indicator to -1 to insert NULL */
depts[3] = 30; /* value in depts[3] is ignored */
strcpy(&names[4] [0], "WESTON");
numbers[4] = 1018;
depts[4] = 30;
/* Do the INSERT. */
printf("Adding to the Sales force...\n");
EXEC SQL FOR :array_size
EXECUTE stmt USING DESCRIPTOR binda;
/* Print rows-processed count. */
printf("%d rows inserted.\n\n", sqlca.sqlerrd[2]);
EXEC SQL COMMIT RELEASE;
exit(0);
sql_error:
/* Print Oracle error message. */
printf("\n%.70s", sqlca.sqlerrm.sqlerrmc);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK RELEASE;
exit(1);
} 関連項目
15.7 サンプル・プログラム: 動的SQL方法4
このプログラムでは、動的SQL方法4を使用するために必要な基本ステップを示します。Oracleへの接続後に、プログラムでは次のことが実行されます。
-
SQLSQLDAAlloc()を使用して記述子にメモリーを割り当てます。
-
ユーザーに対してSQL文の入力を求めるプロンプトを表示します。
-
文をPREPAREします。
-
カーソルをDECLAREします。
-
DESCRIBE BINDを使用してバインド変数をチェックします。
-
カーソルをOPENします。
-
選択リスト項目をDESCRIBEします。
入力されたSQL文が問合せのときは、プログラムは各行のデータをFETCHしてからカーソルをCLOSEします。このプログラムはdemoディレクトリのファイルsample10.pcにあり、オンラインで利用できます。
/*******************************************************************
Sample Program 10: Dynamic SQL Method 4
This program connects you to ORACLE using your username and
password, then prompts you for a SQL statement. You can enter
any legal SQL statement. Use regular SQL syntax, not embedded SQL.
Your statement will be processed. If it is a query, the rows
fetched are displayed.
You can enter multiline statements. The limit is 1023 characters.
This sample program only processes up to MAX_ITEMS bind variables and
MAX_ITEMS select-list items. MAX_ITEMS is #defined to be 40.
*******************************************************************/
#include <stdio.h>
#include <string.h>
#include <setjmp.h>
#include <sqlda.h>
#include <stdlib.h>
#include <sqlcpr.h>
/* Maximum number of select-list items or bind variables. */
#define MAX_ITEMS 40
/* Maximum lengths of the _names_ of the
select-list items or indicator variables. */
#define MAX_VNAME_LEN 30
#define MAX_INAME_LEN 30
#ifndef NULL
#define NULL 0
#endif
/* Prototypes */
#if defined(__STDC__)
void sql_error(void);
int oracle_connect(void);
int alloc_descriptors(int, int, int);
int get_dyn_statement(void);
void set_bind_variables(void);
void process_select_list(void);
void help(void);
#else
void sql_error(/*_ void _*/);
int oracle_connect(/*_ void _*/);
int alloc_descriptors(/*_ int, int, int _*/);
int get_dyn_statement(/* void _*/);
void set_bind_variables(/*_ void -*/);
void process_select_list(/*_ void _*/);
void help(/*_ void _*/);
#endif
char *dml_commands[] = {"SELECT", "select", "INSERT", "insert",
"UPDATE", "update", "DELETE", "delete"};
EXEC SQL INCLUDE sqlda;
EXEC SQL INCLUDE sqlca;
EXEC SQL BEGIN DECLARE SECTION;
char dyn_statement[1024];
EXEC SQL VAR dyn_statement IS STRING(1024);
EXEC SQL END DECLARE SECTION;
SQLDA *bind_dp;
SQLDA *select_dp;
/* Define a buffer to hold longjmp state info. */
jmp_buf jmp_continue;
/* A global flag for the error routine. */
int parse_flag = 0;
void main()
{
int i;
/* Connect to the database. */
if (oracle_connect() != 0)
exit(1);
/* Allocate memory for the select and bind descriptors. */
if (alloc_descriptors(MAX_ITEMS, MAX_VNAME_LEN, MAX_INAME_LEN) != 0)
exit(1);
/* Process SQL statements. */
for (;;)
{
(void) setjmp(jmp_continue);
/* Get the statement. Break on "exit". */
if (get_dyn_statement() != 0)
break;
/* Prepare the statement and declare a cursor. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
parse_flag = 1; /* Set a flag for sql_error(). */
EXEC SQL PREPARE S FROM :dyn_statement;
parse_flag = 0; /* Unset the flag. */
EXEC SQL DECLARE C CURSOR FOR S;
/* Set the bind variables for any placeholders in the
SQL statement. */
set_bind_variables();
/* Open the cursor and execute the statement.
* If the statement is not a query (SELECT), the
* statement processing is completed after the
* OPEN.
*/
EXEC SQL OPEN C USING DESCRIPTOR bind_dp;
/* Call the function that processes the select-list.
* If the statement is not a query, this function
* just returns, doing nothing.
*/
process_select_list();
/* Tell user how many rows processed. */
for (i = 0; i < 8; i++)
{
if (strncmp(dyn_statement, dml_commands[i], 6) == 0)
{
printf("\n\n%d row%c processed.\n", sqlca.sqlerrd[2],
sqlca.sqlerrd[2] == 1 ? '\0' : 's');
break;
}
}
} /* end of for(;;) statement-processing loop */
/* When done, free the memory allocated for
pointers in the bind and select descriptors. */
for (i = 0; i < MAX_ITEMS; i++)
{
if (bind_dp->V[i] != (char *) 0)
free(bind_dp->V[i]);
free(bind_dp->I[i]); /* MAX_ITEMS were allocated. */
if (select_dp->V[i] != (char *) 0)
free(select_dp->V[i]);
free(select_dp->I[i]); /* MAX_ITEMS were allocated. */
}
/* Free space used by the descriptors themselves. */
SQLSQLDAFree( SQL_SINGLE_RCTX, bind_dp);
SQLSQLDAFree( SQL_SINGLE_RCTX, select_dp);
EXEC SQL WHENEVER SQLERROR CONTINUE;
/* Close the cursor. */
EXEC SQL CLOSE C;
EXEC SQL COMMIT WORK RELEASE;
puts("\nHave a good day!\n");
EXEC SQL WHENEVER SQLERROR DO sql_error();
return;
}
int oracle_connect()
{
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR username[128];
VARCHAR password[32];
EXEC SQL END DECLARE SECTION;
printf("\nusername: ");
fgets((char *) username.arr, sizeof username.arr, stdin);
username.arr[strlen((char *) username.arr)-1] = '\0';
username.len = (unsigned short)strlen((char *) username.arr);
printf("password: ");
fgets((char *) password.arr, sizeof password.arr, stdin);
password.arr[strlen((char *) password.arr) - 1] = '\0';
password.len = (unsigned short)strlen((char *) password.arr);
EXEC SQL WHENEVER SQLERROR GOTO connect_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("\nConnected to ORACLE as user %s.\n", username.arr);
return 0;
connect_error:
fprintf(stderr, "Cannot connect to ORACLE as user %s\n", username.arr);
return -1;
}
/*
* Allocate the BIND and SELECT descriptors using SQLSQLDAAlloc().
* Also allocate the pointers to indicator variables
* in each descriptor. The pointers to the actual bind
* variables and the select-list items are realloc'ed in
* the set_bind_variables() or process_select_list()
* routines. This routine allocates 1 byte for select_dp->V[i]
* and bind_dp->V[i], so the realloc will work correctly.
*/
alloc_descriptors(size, max_vname_len, max_iname_len)
int size;
int max_vname_len;
int max_iname_len;
{
int i;
/*
* The first SQLSQLDAAlloc parameter is the runtime context.
* The second parameter determines the maximum number of
* array elements in each variable in the descriptor. In
* other words, it determines the maximum number of bind
* variables or select-list items in the SQL statement.
*
* The third parameter determines the maximum length of
* strings used to hold the names of select-list items
* or placeholders. The maximum length of column
* names in ORACLE is 30, but you can allocate more or less
* as needed.
*
* The fourth parameter determines the maximum length of
* strings used to hold the names of any indicator
* variables. To follow ORACLE standards, the maximum
* length of these should be 30. But, you can allocate
* more or less as needed.
*/
if ((bind_dp =
SQLSQLDAAlloc(SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for bind descriptor.");
return -1; /* Have to exit in this case. */
}
if ((select_dp =
SQLSQLDAAlloc (SQL_SINGLE_RCTX, size, max_vname_len, max_iname_len)) ==
(SQLDA *) 0)
{
fprintf(stderr,
"Cannot allocate memory for select descriptor.");
return -1;
}
select_dp->N = MAX_ITEMS;
/* Allocate the pointers to the indicator variables, and the
actual data. */
for (i = 0; i < MAX_ITEMS; i++) {
bind_dp->I[i] = (short *) malloc(sizeof (short));
select_dp->I[i] = (short *) malloc(sizeof(short));
bind_dp->V[i] = (char *) malloc(1);
select_dp->V[i] = (char *) malloc(1);
}
return 0;
}
int get_dyn_statement()
{
char *cp, linebuf[256];
int iter, plsql;
for (plsql = 0, iter = 1; ;)
{
if (iter == 1)
{
printf("\nSQL> ");
dyn_statement[0] = '\0';
}
fgets(linebuf, sizeof linebuf, stdin);
cp = strrchr(linebuf, '\n');
if (cp && cp != linebuf)
*cp = ' ';
else if (cp == linebuf)
continue;
if ((strncmp(linebuf, "EXIT", 4) == 0) ||
(strncmp(linebuf, "exit", 4) == 0))
{
return -1;
}
else if (linebuf[0] == '?' ||
(strncmp(linebuf, "HELP", 4) == 0) ||
(strncmp(linebuf, "help", 4) == 0))
{
help();
iter = 1;
continue;
}
if (strstr(linebuf, "BEGIN") ||
(strstr(linebuf, "begin")))
{
plsql = 1;
}
strcat(dyn_statement, linebuf);
if ((plsql && (cp = strrchr(dyn_statement, '/'))) ||
(!plsql && (cp = strrchr(dyn_statement, ';'))))
{
*cp = '\0';
break;
}
else
{
iter++;
printf("%3d ", iter);
}
}
return 0;
}
void set_bind_variables()
{
int i, n;
char bind_var[64];
/* Describe any bind variables (input host variables) */
EXEC SQL WHENEVER SQLERROR DO sql_error();
bind_dp->N = MAX_ITEMS; /* Initialize count of array elements. */
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_dp;
/* If F is negative, there were more bind variables
than originally allocated by SQLSQLDAAlloc(). */
if (bind_dp->F < 0)
{
printf ("\nToo many bind variables (%d), maximum is %d\n.",
-bind_dp->F, MAX_ITEMS);
return;
}
/* Set the maximum number of array elements in the
descriptor to the number found. */
bind_dp->N = bind_dp->F;
/* Get the value of each bind variable as a
* character string.
*
* C[i] contains the length of the bind variable
* name used in the SQL statement.
* S[i] contains the actual name of the bind variable
* used in the SQL statement.
*
* L[i] will contain the length of the data value
* entered.
*
* V[i] will contain the address of the data value
* entered.
*
* T[i] is always set to 1 because in this sample program
* data values for all bind variables are entered
* as character strings.
* ORACLE converts to the table value from CHAR.
*
* I[i] will point to the indicator value, which is
* set to -1 when the bind variable value is "null".
*/
for (i = 0; i < bind_dp->F; i++)
{
printf ("\nEnter value for bind variable %.*s: ",
(int)bind_dp->C[i], bind_dp->S[i]);
fgets(bind_var, sizeof bind_var, stdin);
/* Get length and remove the new line character. */
n = strlen(bind_var) - 1;
/* Set it in the descriptor. */
bind_dp->L[i] = n;
/* (re-)allocate the buffer for the value.
SQLSQLDAAlloc() reserves a pointer location for
V[i] but does not allocate the full space for
the pointer. */
bind_dp->V[i] = (char *) realloc(bind_dp->V[i],
(bind_dp->L[i] + 1));
/* And copy it in. */
strncpy(bind_dp->V[i], bind_var, n);
/* Set the indicator variable's value. */
if ((strncmp(bind_dp->V[i], "NULL", 4) == 0) ||
(strncmp(bind_dp->V[i], "null", 4) == 0))
*bind_dp->I[i] = -1;
else
*bind_dp->I[i] = 0;
/* Set the bind datatype to 1 for CHAR. */
bind_dp->T[i] = 1;
}
return;
}
void process_select_list()
{
int i, null_ok, precision, scale;
if ((strncmp(dyn_statement, "SELECT", 6) != 0) &&
(strncmp(dyn_statement, "select", 6) != 0))
{
select_dp->F = 0;
return;
}
/* If the SQL statement is a SELECT, describe the
select-list items. The DESCRIBE function returns
their names, datatypes, lengths (including precision
and scale), and NULL/NOT NULL statuses. */
select_dp->N = MAX_ITEMS;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;
/* If F is negative, there were more select-list
items than originally allocated by SQLSQLDAAlloc(). */
if (select_dp->F < 0)
{
printf ("\nToo many select-list items (%d), maximum is %d\n",
-(select_dp->F), MAX_ITEMS);
return;
}
/* Set the maximum number of array elements in the
descriptor to the number found. */
select_dp->N = select_dp->F;
/* Allocate storage for each select-list item.
SQLNumberPrecV6() is used to extract precision and scale
from the length (select_dp->L[i]).
sqlcolumnNullCheck() is used to reset the high-order bit of
the datatype and to check whether the column
is NOT NULL.
CHAR datatypes have length, but zero precision and
scale. The length is defined at CREATE time.
NUMBER datatypes have precision and scale only if
defined at CREATE time. If the column
definition was just NUMBER, the precision
and scale are zero, and you must allocate
the required maximum length.
DATE datatypes return a length of 7 if the default
format is used. This should be increased to
9 to store the actual date character string.
If you use the TO_CHAR function, the maximum
length could be 75, but will probably be less
(you can see the effects of this in SQL*Plus).
ROWID datatype always returns a fixed length of 18 if
coerced to CHAR.
LONG and
LONG RAW datatypes return a length of 0 (zero),
so you need to set a maximum. In this example,
it is 240 characters.
*/
printf ("\n");
for (i = 0; i < select_dp->F; i++)
{
char title[MAX_VNAME_LEN];
/* Turn off high-order bit of datatype (in this example,
it does not matter if the column is NOT NULL). */
SQLColumnNullCheck ((unsigned short *)&(select_dp->T[i]),
(unsigned short *)&(select_dp->T[i]), &null_ok);
switch (select_dp->T[i])
{
case 1 : /* CHAR datatype: no change in length
needed, except possibly for TO_CHAR
conversions (not handled here). */
break;
case 2 : /* NUMBER datatype: use SQLNumberPrecV6() to
extract precision and scale. */
SQLNumberPrecV6( SQL_SINGLE_RCTX,
(unsigned long *)&(select_dp->L[i]), &precision, &scale);
/* Allow for maximum size of NUMBER. */
if (precision == 0) precision = 40;
/* Also allow for decimal point and
possible sign. */
/* convert NUMBER datatype to FLOAT if scale > 0,
INT otherwise. */
if (scale > 0)
select_dp->L[i] = sizeof(float);
else
select_dp->L[i] = sizeof(int);
break;
case 8 : /* LONG datatype */
select_dp->L[i] = 240;
break;
case 11 : /* ROWID datatype */
select_dp->L[i] = 18;
break;
case 12 : /* DATE datatype */
select_dp->L[i] = 9;
break;
case 23 : /* RAW datatype */
break;
case 24 : /* LONG RAW datatype */
select_dp->L[i] = 240;
break;
}
/* Allocate space for the select-list data values.
SQLSQLDAAlloc() reserves a pointer location for
V[i] but does not allocate the full space for
the pointer. */
if (select_dp->T[i] != 2)
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i] + 1);
else
select_dp->V[i] = (char *) realloc(select_dp->V[i],
select_dp->L[i]);
/* Print column headings, right-justifying number
column headings. */
/* Copy to temporary buffer in case name is null-terminated */
memset(title, ' ', MAX_VNAME_LEN);
strncpy(title, select_dp->S[i], select_dp->C[i]);
if (select_dp->T[i] == 2)
if (scale > 0)
printf ("%.*s ", select_dp->L[i]+3, title);
else
printf ("%.*s ", select_dp->L[i], title);
else
printf("%-.*s ", select_dp->L[i], title);
/* Coerce ALL datatypes except for LONG RAW and NUMBER to
character. */
if (select_dp->T[i] != 24 && select_dp->T[i] != 2)
select_dp->T[i] = 1;
/* Coerce the datatypes of NUMBERs to float or int depending on
the scale. */
if (select_dp->T[i] == 2)
if (scale > 0)
select_dp->T[i] = 4; /* float */
else
select_dp->T[i] = 3; /* int */
}
printf ("\n\n");
/* FETCH each row selected and print the column values. */
EXEC SQL WHENEVER NOT FOUND GOTO end_select_loop;
for (;;)
{
EXEC SQL FETCH C USING DESCRIPTOR select_dp;
/* Since each variable returned has been coerced to a
character string, int, or float very little processing
is required here. This routine just prints out the
values on the terminal. */
for (i = 0; i < select_dp->F; i++)
{
if (*select_dp->I[i] < 0)
if (select_dp->T[i] == 4)
printf ("%-*c ",(int)select_dp->L[i]+3, ' ');
else
printf ("%-*c ",(int)select_dp->L[i], ' ');
else
if (select_dp->T[i] == 3) /* int datatype */
printf ("%*d ", (int)select_dp->L[i],
*(int *)select_dp->V[i]);
else if (select_dp->T[i] == 4) /* float datatype */
printf ("%*.2f ", (int)select_dp->L[i],
*(float *)select_dp->V[i]);
else /* character string */
printf ("%-*.*s ", (int)select_dp->L[i],
(int)select_dp->L[i], select_dp->V[i]);
}
printf ("\n");
}
end_select_loop:
return;
}
void help()
{
puts("\n\nEnter a SQL statement or a PL/SQL block at the SQL> prompt.");
puts("Statements can be continued over several lines, except");
puts("within string literals.");
puts("Terminate a SQL statement with a semicolon.");
puts("Terminate a PL/SQL block (which can contain embedded semicolons)");
puts("with a slash (/).");
puts("Typing \"exit\" (no semicolon needed) exits the program.");
puts("You typed \"?\" or \"help\" to get this message.\n\n");
}
void sql_error()
{
/* ORACLE error handler */
printf ("\n\n%.70s\n",sqlca.sqlerrm.sqlerrmc);
if (parse_flag)
printf
("Parse error at character offset %d in SQL statement.\n",
sqlca.sqlerrd[4]);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK;
longjmp(jmp_continue, 1);
}
15.8 サンプル・プログラム: スクロール可能カーソルを使用する動的SQL方法4
次のデモ・プログラムには、Oracle動的SQL方法4を使用したスクロール可能カーソル機能が記述されています。このプログラムは、demoディレクトリのファイルscdemo1.pcとして、オンラインで使用可能です。
scdemo1.pc
/*
* This demo program exhibits the scrollable cursor feature
* used with oracle dynamic method 4. The scrollable cursor
* feature can also be used with ANSI dynamic method 4.
*
* This program takes as argument the username/passwd. Once
* logged in, it prompts for a select query. It then prompts
* for the orientation and prints the results of the query.
*
* Before executing this example, make sure that the HR
* schema exists.
*/
#include <oci.h>
#include <stdio.h>
#include <sqlca.h>
#include <sqlda.h>
#include <string.h>
#include <ctype.h>
#include <sqlcpr.h>
#include <stdlib.h>
#include <setjmp.h>
#define MAX_SELECT_ITEMS 200
#define MAX_CHARS 20
/* Maximum size of a select-list item name */
#define MAX_NAME_SIZE 50
SQLDA *selda;
SQLDA *bind_des;
jmp_buf beginEnv;
jmp_buf loopEnv;
/* Data buffer */
char c_data[MAX_SELECT_ITEMS][MAX_CHARS];
char username[60];
char stmt[500];
char stmt2[500];
/* Print the generic error message & exit */
void sql_error()
{
char msgbuf[512];
size_t msgbuf_len, msg_len;
msgbuf_len = sizeof(msgbuf);
sqlglm(msgbuf, &msgbuf_len, &msg_len);
printf ("\n\n%.*s\n", msg_len, msgbuf);
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
exit(EXIT_FAILURE);
}
/* Print the error message and continue to query
the user */
void sql_loop_error()
{
char msgbuf[512];
size_t msgbuf_len, msg_len;
int code = sqlca.sqlcode;
msgbuf_len = sizeof(msgbuf);
sqlglm(msgbuf, &msgbuf_len, &msg_len);
printf ("\n%.*s\n", msg_len, msgbuf);
printf("The error code is %d\n", sqlca.sqlcode);
if(code!=0)
longjmp(beginEnv, 1);
longjmp(loopEnv, 1);
}
/* FETCH has returned the "no data found" error code.
This means that either we have reached the end of
the active set or the offset refers to a row beyond the
active set */
void no_data_found()
{
printf("\nNo Data available at the specified offset\n");
longjmp(loopEnv, 1);
}
void main(int argc, char *argv[])
{
int i, n;
int sli; /* select-list item */
int offset;
int contFlag;
char bindVar[20];
char *u, temp[3];
char choice;
/* Error Handler */
EXEC SQL WHENEVER SQLERROR DO sql_error();
if (argc == 1)
{
printf("Logging in as default user hr\n");
strcpy(username, "hr/hr");
}
else
strcpy(username, argv[1]);
/* Establish a connection to the data base */
EXEC SQL CONNECT :username;
u = username;
while(*++u != '/');
*u = '\0';
/* Error Handler */
EXEC SQL WHENEVER SQLERROR DO sql_loop_error();
for (;;)
{
setjmp(beginEnv);
printf("[%s] SQL > ", username);
gets(stmt);
if (!strlen(stmt))
continue;
for (i=0 ; i < strlen(stmt) ; i++)
stmt2[i] = tolower(stmt[i]) ;
stmt2[i]=0 ;
if(!strcmp(stmt2, "exit"))
break;
selda = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_SELECT_ITEMS, MAX_NAME_SIZE, 0);
bind_des = SQLSQLDAAlloc(SQL_SINGLE_RCTX, MAX_SELECT_ITEMS,
MAX_NAME_SIZE, 30);
/* prepare a sql statement for the query*/
EXEC SQL PREPARE S FROM :stmt;
/* Declare a cursor as scrollable */
EXEC SQL DECLARE C SCROLL CURSOR FOR S;
for (i=0; i<MAX_SELECT_ITEMS; i++)
{
bind_des->I[i] = (short *) malloc(sizeof (short));
bind_des->V[i] = (char *) malloc(1);
}
bind_des->N = MAX_SELECT_ITEMS;
EXEC SQL DESCRIBE BIND VARIABLES FOR S INTO bind_des;
/* set up the bind variables */
if (bind_des->F < 0)
{
printf("Bind descriptor, value exceeds the limit\n");
exit(-1);
}
bind_des->N = bind_des->F;
for (i=0; i<bind_des->F; i++)
{
printf("Enter the value for bind variable %.*s: ",
(int)bind_des->C[i], bind_des->S[i]);
fgets(bindVar, sizeof(bindVar), stdin);
n = strlen(bindVar) - 1;
bind_des->L[i] = n;
bind_des->V[i] = (char *) realloc(bind_des->V[i],
(bind_des->L[i] +1));
strncpy(bind_des->V[i], bindVar, n);
if ((strncmp(bind_des->V[i], "NULL", 4) == 0) ||
(strncmp(bind_des->V[i], "null", 4) == 0))
*bind_des ->I[i] = -1;
else
*bind_des ->I[i] = 0;
bind_des->T[i] = 1;
}
/* open the cursor */
EXEC SQL OPEN C USING DESCRIPTOR bind_des;
EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda;
if (selda->F < 0)
{
printf("Select descriptor, value exceeds the limit\n");
exit(-1);
}
selda->N = selda->F;
for (sli = 0; sli < selda->N; sli++)
{
/* Set addresses of heads of the arrays
in the V element. */
selda->V[sli] = c_data[sli];
/* Convert everything to varchar on output. */
selda->T[sli] = 1;
/* Set the maximum lengths. */
selda->L[sli] = MAX_CHARS;
}
contFlag = 'Y';
setjmp(loopEnv);
while(1)
{
while(contFlag != 'Y' && contFlag != 'N')
{
printf("\nContinue with the current fetch? [y/n] :");
contFlag = toupper(getchar());
/* To flush the input buffer */
getchar();
}
if(contFlag != 'Y')
break;
contFlag = 'x';
printf("\n\nEnter the row number to be fetched \n");
printf("1.ABSOLUTE\n");
printf("2.RELATIVE\n");
printf("3.FIRST \n");
printf("4.NEXT \n");
printf("5.PREVIOUS \n");
printf("6.LAST \n");
printf("7.CURRENT \n");
printf("Enter your choice --> ");
scanf("%c",&choice);
/* To flush the input buffer */ getchar(); EXEC SQL WHENEVER NOT FOUND DO no_data_found();
switch(choice)
{
case '1': printf("\nEnter Offset :");
scanf("%d",&offset);
getchar();
EXEC SQL FETCH ABSOLUTE :offset C USING DESCRIPTOR selda;
break;
case '2': printf("\nEnter Offset :");
scanf("%d",&offset);
getchar();
EXEC SQL FETCH RELATIVE :offset C USING DESCRIPTOR selda;
break;
case '3': EXEC SQL FETCH FIRST C USING DESCRIPTOR selda;
break;
case '4': EXEC SQL FETCH NEXT C USING DESCRIPTOR selda;
break;
case '5': EXEC SQL FETCH PRIOR C USING DESCRIPTOR selda;
break;
case '6': EXEC SQL FETCH LAST C USING DESCRIPTOR selda;
break;
case '7': EXEC SQL FETCH CURRENT C USING DESCRIPTOR selda;
break;
default : printf("Invalid choice\n");
contFlag = 'Y';
continue;
}
/* print the row */
for(sli=0; sli<selda->N; sli++)
printf("%.20s ", c_data[sli]);
puts("");
}
EXEC SQL CLOSE C;
}
EXEC SQL ROLLBACK RELEASE;
exit(EXIT_SUCCESS);
}