SQL記述子領域はSELECT、FETCH、DESCRIBE文の結果を処理する、より洗練された手法です。
SQL記述子領域は1行のデータをメタデータ項目と一緒に1つのデータ構造体としてグループ化します。
特に動的SQL文を実行する場合は結果列の性質が前もってわかりませんので、メタデータが有用です。
PostgreSQLは記述子領域を使用するための2つの方法、名前付きSQL記述子領域とC構造化SQLDA、を提供します。
名前付きSQL記述子領域は、記述子全体に関する情報を持つヘッダと、基本的に結果行内の1つの列を記述する、1つ以上の項目記述子領域から構成されます。
SQL記述子領域を使用可能にするためには、それを以下のように割り当てなければなりません。
EXEC SQL ALLOCATE DESCRIPTOR identifier;
この識別子は記述子領域の「変数名」として使用されます。 記述子が不要になったら、以下のように解放してください。
EXEC SQL DEALLOCATE DESCRIPTOR identifier;
記述子領域を使用するには、INTO句内の格納対象として、ホスト変数を列挙するのではなく、記述子領域を指定してください。
EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;
結果セットが空の場合であっても、記述子領域には問い合わせのメタデータ、つまりフィールド名、が含まれます。
まだ実行されていないプリペアド問い合わせでは、結果セットのメタデータを入手するためにDESCRIBEを使用することができます。
EXEC SQL BEGIN DECLARE SECTION; char *sql_stmt = "SELECT * FROM table1"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :sql_stmt; EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
PostgreSQL 9.0より前では、SQLキーワードは省略可能でした。
このためDESCRIPTORおよびSQL DESCRIPTORは名前付きSQL記述子領域を生成しました。
これは強制事項になり、SQLキーワードを省略すると、SQLDA記述子領域を生成します。
36.7.2を参照してください。
DESCRIBEおよびFETCH文では、INTOおよびUSINGキーワードを同じように使用することができます。
これらは結果セットと記述子領域内のメタデータを生成します。
さて、どうやって記述子領域からデータを取り出すのでしょうか。 この記述子領域を名前付きフィールドを持つ構造体とみなすことができます。 ヘッダからフィールド値を取り出し、それをホスト変数に格納するには、以下のコマンドを使用します。
EXEC SQL GET DESCRIPTORname:hostvar=field;
今のところ、COUNTというヘッダフィールドが1つだけ定義されています。
これは、記述子領域に存在する項目数を表すものです
（つまり、結果内に含まれる列数です）。
このホスト変数は整数型でなければなりません。
項目記述子領域からフィールドを取り出すには、以下のコマンドを使用します。
EXEC SQL GET DESCRIPTORnameVALUEnum:hostvar=field;
numはリテラル整数、もしくは整数を持つホスト変数を取ることができます。
取り得るフィールドは以下の通りです。
CARDINALITY (整数)結果セット内の行数です。
DATA実際のデータ項目です （したがってこのフィールドのデータ型は問い合わせに依存します）。
DATETIME_INTERVAL_CODE (整数)
TYPEが9の場合、DATETIME_INTERVAL_CODEは、DATEでは1、TIMEでは2、TIMESTAMPでは3、TIME WITH TIME ZONEでは4、TIMESTAMP WITH TIME ZONEでは5という値を取ります。
DATETIME_INTERVAL_PRECISION (整数)未実装です。
INDICATOR (整数)（NULL値や値の切り詰めを示す）指示子です。
KEY_MEMBER (整数)実装されていません。
LENGTH (整数)データの文字列の長さです。
NAME (文字列)列名です。
NULLABLE (整数)実装されていません。
OCTET_LENGTH (整数)データの文字表現のバイト長です。
PRECISION (整数)
（numeric型用の）精度です。
RETURNED_LENGTH (整数)データの文字数です。
RETURNED_OCTET_LENGTH (整数)データの文字表現のバイト長です。
SCALE (整数)
（numeric型用の）桁です。
TYPE (整数)列のデータ型の数値コードです。
EXECUTE、DECLAREおよびOPEN文では、INTOおよびUSINGの効果は異なります。
また、問い合わせやカーソル用の入力パラメータを提供するために記述子領域は手作業で構築することができます。
USING SQL DESCRIPTOR は入力パラメータとパラメータ付きの問い合わせに渡す方法です。
名前付きSQL記述子領域を構築するSQL文は以下の通りです。
name
EXEC SQL SET DESCRIPTORnameVALUEnumfield= :hostvar;
PostgreSQLは、1つのFETCH文内の1レコードを複数取り出し、ホスト変数に格納することをサポートします。
この場合ホスト変数は配列であると仮定されます。
EXEC SQL BEGIN DECLARE SECTION; int id[5]; EXEC SQL END DECLARE SECTION; EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;
SQLDA記述子領域は、問い合わせの結果セットとメタデータを取り出すために使用可能なC言語の構造体です。 1つの構造体には結果セットの1レコードが格納されます。
EXEC SQL include sqlda.h; sqlda_t *mysqlda; EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;
SQLキーワードが省略されていることに注意してください。
36.7.1のINTOおよびUSINGの使用状況に関する段落はここで多少追加して適用します。
DESCRIBE文では、INTOが使用されている場合DESCRIPTORキーワードは完全に省略可能です。
EXEC SQL DESCRIBE prepared_statement INTO mysqlda;
SQLDAを使用するプログラムの一般的な流れは以下の通りです。
問い合わせをプリペアし、そのカーソルを宣言します。
結果セット用のSQLDAを宣言します。
入力パラメータ用のSQLDAを宣言し、初期化（メモリ割り当て、パラメータの設定）します。
入力用SQLDAでカーソルを開きます。
カーソルから行を取り出し、出力用SQLDAに格納します。
出力用SQLDAから値をホスト変数に（必要に応じて変換を行い）読み取ります。
カーソルを閉じます。
入力用SQLDAに割り当てられたメモリ領域を解放します。
SQLDAはsqlda_t、sqlvar_t、struct sqlnameという３つの種類のデータ構造を使用します。
PostgreSQLのSQLDAはIBM DB2ユニバーサルデータベースのものと似たデータ構造を持ちます。 このため、DB2のSQLDAに関する技術情報の一部はPostgreSQLのSQLDAの理解のより良い助けになるでしょう。
sqlda_t構造体は実際のSQLDAの型です。
これは１つのレコードを保持します。
そして２つ以上のsqlda_t構造体をdesc_nextフィールド内においてポインタを使ってリンクリスト内でつなげることができます。
こうして行の順序付き集合を表現します。
このため、２つ以上の行を取り出す時、アプリケーションは各sqlda_tノードのdesc_nextポインタを追うことでそれらを読み取ることができます。
sqlda_tの定義は以下の通りです。
struct sqlda_struct
{
char sqldaid[8];
long sqldabc;
short sqln;
short sqld;
struct sqlda_struct *desc_next;
struct sqlvar_struct sqlvar[1];
};
typedef struct sqlda_struct sqlda_t;
フィールドの意味は以下の通りです。
sqldaid
ここには"SQLDA "文字列リテラルが含まれます。
sqldabcここにはバイト単位の割り当てられた領域のサイズが含まれます。
sqln
USINGキーワードを使用してOPEN、DECLARE、EXECUTE文に渡される場合、ここにはパラメータ付き問い合わせの入力パラメータ数が含まれます。
SELECT、EXECUTE、FETCH文の出力として使用される場合、この値はsqld文と同じです。
sqldここには結果セットのフィールド数が含まれます。
desc_next
問い合わせが複数のレコードを返す場合、複数結び付いたSQLDA構造体が返されます。
desc_nextにリスト内の次の項目を指し示すポインタが保持されます。
sqlvarこれは結果セット内の列の配列です。
sqlvar_t構造体は列の値と型や長さなどのメタデータを保持します。
この型の定義は以下の通りです。
struct sqlvar_struct
{
short sqltype;
short sqllen;
char *sqldata;
short *sqlind;
struct sqlname sqlname;
};
typedef struct sqlvar_struct sqlvar_t;
フィールドの意味は以下の通りです。
sqltype
ここにはフィールドの型識別子が含まれます。
値についてはecpgtype.hのenum ECPGttypeを参照してください。
sqllen
フィールドのバイナリ長が含まれます。
例えばECPGt_intでは4バイトです。
sqldataデータそのものを指し示します。 データ書式は36.4.4で説明します。
sqlindデータのNULL指示子を指し示します。 0は非NULLを、-1はNULLを意味します。
sqlnameフィールドの名前です。
struct sqlname構造体は列名を保持します。
sqlvar_t構造体のメンバとして使用されます。
構造体の定義は以下の通りです。
#define NAMEDATALEN 64
struct sqlname
{
short length;
char data[NAMEDATALEN];
};
フィールドの意味は以下の通りです。
lengthフィールド名の長さが含まれます。
data実際のフィールド名が含まれます。
SQLDAを通して問い合わせの結果を取り出す一般的な手順は以下に示します。
結果セットを受けとるためのsqlda_t構造体を宣言します。
宣言したSQLDAを指定した問い合わせを処理するためにFETCH/EXECUTE/DESCRIBEを実行します。
sqlda_t構造体のメンバsqlnを検索することにより結果セット内のレコード数を検査します。
sqlda_t構造体のメンバsqlvar[0]、sqlvar[1]などから各列の値を入手します。
sqlda_t構造体のメンバdesc_nextポインタを追い、次の行（sqlda_t構造体）に進みます。
必要なだけ上を繰り返します。
以下にSQLDAを通して結果セットを取り出す例を示します。
まず、結果セットを受け取るsqlda_t構造体を宣言します。
sqlda_t *sqlda1;
次にコマンド内にSQLDAを指定します。
以下はFETCHコマンドの例です。
EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
行を取り出すためにリンクリストを追うループを実行します。
sqlda_t *cur_sqlda;
for (cur_sqlda = sqlda1;
cur_sqlda != NULL;
cur_sqlda = cur_sqlda->desc_next)
{
...
}
ループの内側では、行の列データ（sqlvar_t構造体）それぞれを取り出す別のループを実行します。
for (i = 0; i < cur_sqlda->sqld; i++)
{
sqlvar_t v = cur_sqlda->sqlvar[i];
char *sqldata = v.sqldata;
short sqllen = v.sqllen;
...
}
列の値を入手するために、sqlvar_t構造体のメンバsqltypeの値を検査します。
そして、列の型に応じて、sqlvarフィールドからホスト変数にデータをコピーするための適切な方法に切り替えます。
char var_buf[1024];
switch (v.sqltype)
{
case ECPGt_char:
memset(&var_buf, 0, sizeof(var_buf));
memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));
break;
case ECPGt_int: /* 整数 */
memcpy(&intval, sqldata, sqllen);
snprintf(var_buf, sizeof(var_buf), "%d", intval);
break;
...
}
プリペアド問い合わせに入力パラメータを渡すためにSQLDAを使用する、一般的な手順は以下の通りです。
プリペアド問い合わせ（プリペアド文）を作成します。
入力用SQLDAとしてsqlda_t構造体を宣言します。
入力用SQLDA用にメモリ領域を（sqlda_t構造体として）割り当てます。
割り当てたメモリに入力値を設定（コピー）します。
入力用SQLDAを指定してカーソルを開きます。
以下に例を示します。
まずプリペアド文を作成します。
EXEC SQL BEGIN DECLARE SECTION; char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)"; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE stmt1 FROM :query;
次にSQLDA用にメモリを割り当て、sqlda_t構造体のメンバ変数sqlnに入力パラメータ数を設定します。
プリペアド問い合わせで２つ以上の入力パラメータが必要な場合、アプリケーションは(パラメータ数 - 1) * sizeof(sqlvar_t)で計算される追加のメモリ空間を割り当てなければなりません。
ここで示す例では２つの入力パラメータ用にメモリ空間を割り当てます。
sqlda_t *sqlda2; sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); sqlda2->sqln = 2; /* 入力変数の個数 */
メモリを割り当てた後、sqlvar[]配列にパラメータ値を格納します。
（これは、SQLDAが結果セットを受け取る時に列値を取り出すために使用した配列と同じです。）
この例では、入力パラメータは文字列型を持つ"postgres"と整数型を持つ1です。
sqlda2->sqlvar[0].sqltype = ECPGt_char; sqlda2->sqlvar[0].sqldata = "postgres"; sqlda2->sqlvar[0].sqllen = 8; int intval = 1; sqlda2->sqlvar[1].sqltype = ECPGt_int; sqlda2->sqlvar[1].sqldata = (char *) &intval; sqlda2->sqlvar[1].sqllen = sizeof(intval);
ここまでで設定したSQLDAを指定するカーソルを開くことで、入力パラメータはプリペアド文に渡されます。
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
最後に、問い合わせ結果を受け取るために使用するSQLDAとは異なり、入力用SQLDAの使用後、割り当てたメモリ空間を明示的に解放しなければなりません。
free(sqlda2);
以下に、システムカタログから入力パラメータにより指定されたデータベースの統計情報にアクセスし取り出す方法を示す、プログラム例を示します。
このアプリケーションは、pg_databaseとpg_stat_databaseシステムテーブルをデータベースOIDで結合し、２つの入力パラメータ（データベースpostgresとOID1）により取り出されるデータベース統計情報を読み取り、表示します。
まず、入力用のSQLDAと出力用のSQLDAを宣言します。
EXEC SQL include sqlda.h; sqlda_t *sqlda1; /* 出力記述子 */ sqlda_t *sqlda2; /* 入力記述子 */
次に、データベースに接続し、プリペアド文を作成し、プリペアド文用のカーソルを宣言します。
int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO testdb AS con1 USER testuser;
EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
EXEC SQL PREPARE stmt1 FROM :query;
EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
次に、入力パラメータのために入力用SQLDA内にいくつかの値を格納します。
入力用SQLDAのためのメモリを割り当て、入力パラメータの個数をsqlnに設定します。
型、値、値の長さをsqlvar構造体内のsqltype、sqldata、sqllenに格納します。
/* 入力パラメータ用のSQLDA構造体を作成する。 */
sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
sqlda2->sqln = 2; /* 入力変数の数 */
sqlda2->sqlvar[0].sqltype = ECPGt_char;
sqlda2->sqlvar[0].sqldata = "postgres";
sqlda2->sqlvar[0].sqllen = 8;
intval = 1;
sqlda2->sqlvar[1].sqltype = ECPGt_int;
sqlda2->sqlvar[1].sqldata = (char *)&intval;
sqlda2->sqlvar[1].sqllen = sizeof(intval);
入力用SQLDAを設定し終えた後、入力用SQLDAを付けたカーソルを開きます。
/* 入力パラメータ付きでカーソルを開く。 */
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
開いたカーソルから出力用SQLDA内に行を取り込みます。
（一般的に結果セット内の行をすべて取り込むためには、ループ内でFETCHを繰り返し呼び出さなければなりません。）
while (1)
{
sqlda_t *cur_sqlda;
/* 記述子をカーソルに割り当てる */
EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
次に、sqlda_t構造体のリンクリストを追うことで、SQLDAから取り込んだレコードを取り出します。
for (cur_sqlda = sqlda1 ;
cur_sqlda != NULL ;
cur_sqlda = cur_sqlda->desc_next)
{
...
最初のレコードから各列を読み取ります。
列数はsqldに、最初の列の実データはsqlvar[0]に格納されています。
どちらもsqlda_t構造体のメンバです。
/* 1行の列をすべて表示する。 */
for (i = 0; i < sqlda1->sqld; i++)
{
sqlvar_t v = sqlda1->sqlvar[i];
char *sqldata = v.sqldata;
short sqllen = v.sqllen;
strncpy(name_buf, v.sqlname.data, v.sqlname.length);
name_buf[v.sqlname.length] = '\0';
ここで、列データがv変数内に格納されました。
列の型についてv.sqltypeを検索しながら、すべてのデータをホスト変数にコピーします。
switch (v.sqltype) {
int intval;
double doubleval;
unsigned long long int longlongval;
case ECPGt_char:
memset(&var_buf, 0, sizeof(var_buf));
memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen));
break;
case ECPGt_int: /* 整数 */
memcpy(&intval, sqldata, sqllen);
snprintf(var_buf, sizeof(var_buf), "%d", intval);
break;
...
default:
...
}
printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
}
すべてのレコードを処理した後カーソルを閉じ、データベースとの接続を切断します。
EXEC SQL CLOSE cur1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL;
プログラム全体を例 36.1に示します。
例36.1 SQLDAプログラムの例
#include <stdlib.h>
#include <string.h>
#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
EXEC SQL include sqlda.h;
sqlda_t *sqlda1; /* 出力記述子 */
sqlda_t *sqlda2; /* 入力記述子 */
EXEC SQL WHENEVER NOT FOUND DO BREAK;
EXEC SQL WHENEVER SQLERROR STOP;
int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";
int intval;
unsigned long long int longlongval;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO uptimedb AS con1 USER uptime;
EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
EXEC SQL PREPARE stmt1 FROM :query;
EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
/* 入力パラメータ用のSQLDA構造体を作成する */
sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));
memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));
sqlda2->sqln = 2; /* 入力変数の数 */
sqlda2->sqlvar[0].sqltype = ECPGt_char;
sqlda2->sqlvar[0].sqldata = "postgres";
sqlda2->sqlvar[0].sqllen = 8;
intval = 1;
sqlda2->sqlvar[1].sqltype = ECPGt_int;
sqlda2->sqlvar[1].sqldata = (char *) &intval;
sqlda2->sqlvar[1].sqllen = sizeof(intval);
/* 入力パラメータ付きでカーソルを開く。 */
EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;
while (1)
{
sqlda_t *cur_sqlda;
/* 記述子をカーソルに割り当てる */
EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;
for (cur_sqlda = sqlda1 ;
cur_sqlda != NULL ;
cur_sqlda = cur_sqlda->desc_next)
{
int i;
char name_buf[1024];
char var_buf[1024];
/* 1行の列をすべて表示する。 */
for (i=0 ; i<cur_sqlda->sqld ; i++)
{
sqlvar_t v = cur_sqlda->sqlvar[i];
char *sqldata = v.sqldata;
short sqllen = v.sqllen;
strncpy(name_buf, v.sqlname.data, v.sqlname.length);
name_buf[v.sqlname.length] = '\0';
switch (v.sqltype)
{
case ECPGt_char:
memset(&var_buf, 0, sizeof(var_buf));
memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) );
break;
case ECPGt_int: /* 整数 */
memcpy(&intval, sqldata, sqllen);
snprintf(var_buf, sizeof(var_buf), "%d", intval);
break;
case ECPGt_long_long: /* bigint */
memcpy(&longlongval, sqldata, sqllen);
snprintf(var_buf, sizeof(var_buf), "%lld", longlongval);
break;
default:
{
int i;
memset(var_buf, 0, sizeof(var_buf));
for (i = 0; i < sqllen; i++)
{
char tmpbuf[16];
snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]);
strncat(var_buf, tmpbuf, sizeof(var_buf));
}
}
break;
}
printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);
}
printf("\n");
}
}
EXEC SQL CLOSE cur1;
EXEC SQL COMMIT;
EXEC SQL DISCONNECT ALL;
return 0;
}
この例の出力は以下のようなものになるはずです（一部の数値は変動します）。
oid = 1 (type: 1)
datname = template1 (type: 1)
datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = t (type: 1)
datallowconn = t (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
datconfig = (type: 1)
datacl = {=c/uptime,uptime=CTc/uptime} (type: 1)
datid = 1 (type: 1)
datname = template1 (type: 1)
numbackends = 0 (type: 5)
xact_commit = 113606 (type: 9)
xact_rollback = 0 (type: 9)
blks_read = 130 (type: 9)
blks_hit = 7341714 (type: 9)
tup_returned = 38262679 (type: 9)
tup_fetched = 1836281 (type: 9)
tup_inserted = 0 (type: 9)
tup_updated = 0 (type: 9)
tup_deleted = 0 (type: 9)
oid = 11511 (type: 1)
datname = postgres (type: 1)
datdba = 10 (type: 1)
encoding = 0 (type: 5)
datistemplate = f (type: 1)
datallowconn = t (type: 1)
datconnlimit = -1 (type: 5)
datfrozenxid = 379 (type: 1)
dattablespace = 1663 (type: 1)
datconfig = (type: 1)
datacl = (type: 1)
datid = 11511 (type: 1)
datname = postgres (type: 1)
numbackends = 0 (type: 5)
xact_commit = 221069 (type: 9)
xact_rollback = 18 (type: 9)
blks_read = 1176 (type: 9)
blks_hit = 13943750 (type: 9)
tup_returned = 77410091 (type: 9)
tup_fetched = 3253694 (type: 9)
tup_inserted = 0 (type: 9)
tup_updated = 0 (type: 9)
tup_deleted = 0 (type: 9)