埋め込みで動的なSQLというのはなんだか矛盾するような何かをはらんでいるような気がするのは私だけだろうか…とにかくやってみた.
動的SQLって何だ?
サンプル
ecpgのDEALLOCATEにおけるバグ?
雛型を使用しない動的SQL
雛型を使用した動的SQL
動的SQLって何だ?
思うに埋め込みSQLというのは,文字通りプログラムが発行するSQLをソースコードに埋め込んでしまうというものだろう.で,動的SQL?動的?何を埋め込むんだっけ?…動的SQLでは大体雛型を埋め込むということになるという認識でよいだろうか.とにかくコーディングの段階で確定するSQLを静的SQLとして,動的SQLはプログラムの実行段階で確定するSQLのことを言うらしい.
EXEC SQL INSERT INTO hoge VALUES (:i, ‘aaa’);
上の埋め込みSQLは変数:iを使用しており,コーディング次第でその値は実行時に動的に変更することができる.ということはプログラム実行段階でSQLが確定することになり,動的SQLになるような気がする.だがこういうものは動的SQLと呼ばないらしい.上の埋め込みSQLをecpgで処理した結果は以下のようになる.
{ ECPGdo(LINE, 0, 1, NULL, “insert into hoge values( ? , ‘aaa’ )”,
ECPGt_int,&(i),(long)1,(long)1,sizeof(int),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);}
確かにほぼ完成されたSQL文がある.一方,動的SQLと呼ばれるものをecpgで処理したコードには,SQLの雛型(=単なる文字列)以外にSQLらしきものはない(※).話が長くなったが,多少乱暴に言うと,表面的には以下の埋め込みSQLの構文を使用すれば動的SQL,でなければ静的SQLと見れば大体間違いないと思う.
EXEC SQL EXECUTE … ;
ところでSQL文の生成といえば,libpqを使用したDBアクセスでもSQLをプログラムで文字列として生成してDBサーバに発行した.そもそも常に全く同じSQLを発行するというケースの方が稀で,プログラムでDBアクセスするなら普通はSQLは実行時にユーザなどからの入力に応じて変化するのが普通であろう.
※ 人間が見ればどちらも単なる文字列であるSQLが,「意味的に」SQLとして埋め込まれているか,単なる文字列として存在するかという点が「動的SQL」と「静的SQL」の分かれ目という見方ができるだろうか.
サンプル
動的SQLを使用したサンプルを以下に示す.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <getopt.h>
/* デバッグ設定 */
#ifdef DEBUG_ON
#define DEBUG_FLAG 1
#else
#define DEBUG_FLAG 0
#endif
#define DEBUG_STREAM stderr
int mygetopt (int argc, char **argv, char **db, char **user, char **passwd);
int
main (int argc, char **argv)
{
/* DB接続用変数宣言 */
EXEC SQL BEGIN DECLARE SECTION;
char *db;
char *user;
char *passwd;
int id;
char name[9];
int nullFlag;
char *sql1 = "CREATE TABLE hoge (id integer, name char(8));";
char *sql2 = "DROP TABLE hoge;";
char *sql3 = "INSERT INTO hoge VALUES (?, ?);";
char *sql4 = "SELECT id, name FROM hoge;";
EXEC SQL END DECLARE SECTION;
if (mygetopt (argc, argv, &db, &user, &passwd))
{
fprintf (stderr, "Usage: %s -d dbname -u user -p pass\n", argv[0]);
fprintf (stderr,
"Example: %s -d tcp:postgresql://dbserver/firstdb -u matsu -p hogefuga\n",
argv[0]);
exit (EXIT_FAILURE);
}
/* ECPGデバッグ文出力設定 */
ECPGdebug (DEBUG_FLAG, DEBUG_STREAM);
/* 接続 */
memset (sqlca.sqlstate, '0', 5);
EXEC SQL CONNECT TO :db AS dbConnection user :user USING :passwd;
if (memcmp (sqlca.sqlstate, "00", 2) != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
exit (EXIT_FAILURE);
}
/* テーブル作成 */
EXEC SQL EXECUTE IMMEDIATE :sql1;
if (sqlca.sqlcode != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
/* テーブル削除 */
EXEC SQL EXECUTE IMMEDIATE :sql2;
if (sqlca.sqlcode != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
/* テーブル作成 */
EXEC SQL PREPARE dsql1 FROM :sql1;
EXEC SQL EXECUTE dsql1;
EXEC SQL DEALLOCATE PREPARE dsql1;
if (sqlca.sqlcode != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
/* インサート */
id=1;
memcpy(name, "foovar", 7);
EXEC SQL PREPARE dsql3 FROM :sql3;
EXEC SQL EXECUTE dsql3 USING :id, :name;
EXEC SQL DEALLOCATE PREPARE dsql3;
if (memcmp (sqlca.sqlstate, "00", 2) != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
/* セレクト */
EXEC SQL PREPARE dsql4 FROM :sql4;
EXEC SQL EXECUTE dsql4 INTO :id, :name :nullFlag;
EXEC SQL DEALLOCATE PREPARE dsql4;
if (memcmp (sqlca.sqlstate, "00", 2) != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
else
{
if (nullFlag == 0){
fprintf (stderr, "id = %d / name = %s\n", id, name);
}
}
/* テーブル削除 */
EXEC SQL PREPARE dsql2 FROM :sql2;
EXEC SQL EXECUTE dsql2;
EXEC SQL DEALLOCATE PREPARE dsql2;
if (memcmp (sqlca.sqlstate, "00", 2) != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
/* コミット */
EXEC SQL COMMIT;
END:
/* 切断 */
sqlca.sqlcode = 0;
EXEC SQL DISCONNECT dbConnection;
/* 結果チェック */
if (sqlca.sqlcode != 0)
{
fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
exit (EXIT_FAILURE);
}
exit (EXIT_SUCCESS);
}
/*
* オプションの取得.
*/
int
mygetopt (int argc, char **argv, char **db, char **user, char **passwd)
{
char result;
*db = NULL;
*user = NULL;
*passwd = NULL;
while ((result = getopt (argc, argv, "d:u:p:")) != -1)
{
switch (result)
{
case 'd':
*db = optarg;
break;
case 'u':
*user = optarg;
break;
case 'p':
*passwd = optarg;
break;
default:
break;
}
}
if (*db == NULL || *user == NULL || *passwd == NULL)
{
return 1;
}
return 0;
}このサンプルは雛型を使用してSQLを動的に生成するものであるが,動的SQLの機能的な強みは強調されていないが動的SQLを使用する最初のサンプルとしては妥当だろうか.とにかく実行してみる(※).
$> ./dynamic-sql -d tcp:postgresql://dbserver/firstdb -u matsu -p hogefuga
[5224]: ECPGdebug: set to 1
[5224]: ECPGconnect: opening database firstdb on dbserver port <DEFAULT> for user matsu
[5224]: ECPGexecute line 56: QUERY: CREATE TABLE hoge (id integer, name char(8));
on connection dbConnection
[5224]: ECPGexecute line 56 Ok: CREATE
[5224]: ECPGexecute line 65: QUERY: DROP TABLE hoge; on connection dbConnection
[5224]: ECPGexecute line 65 Ok: DROP
[5224]: ECPGexecute line 75: QUERY: CREATE TABLE hoge (id integer, name char(8));
on connection dbConnection
[5224]: ECPGexecute line 75 Ok: CREATE
[5224]: ECPGexecute line 88: QUERY: INSERT INTO hoge VALUES (1, 'foovar');
on connection dbConnection
[5224]: ECPGexecute line 88 Ok: INSERT 964445 1
[5224]: ECPGexecute line 99: QUERY: SELECT id, name FROM hoge;
on connection dbConnection
[5224]: ECPGexecute line 99: Correctly got 1 tuples with 2 fields
[5224]: ECPGget_data line 99: RESULT: 1 offset: 4 array: 3
[5224]: ECPGget_data line 99: RESULT: foovar offset: 9 array: 3
id = 1 / name = foovar
[5224]: ECPGexecute line 116: QUERY: DROP TABLE hoge; on connection dbConnection
[5224]: ECPGexecute line 116 Ok: DROP
[5224]: ECPGtrans line 126 action = commit connection = dbConnection
[5224]: ecpg_finish: Connection dbConnection closed.
※ 次節に記述するecpgの(おそらく)バグによって,ビルドには追加的な作業が必要である.ecpgのDEALLOCATEにおけるバグ?
ecpglib.h(※1)には以下のような記述がある.
bool ECPGdeallocate(int, int, char *);
bool ECPGdeallocate_one(int, char *);
bool ECPGdeallocate_all(int);
しかし,ecpg(3.1.1)によって生成されるCコード(dynamic-sql.c)を調べてみると,
EXEC SQL DEALLOCATE PREPARE ...;
の部分で
{ ECPGdeallocate(__LINE__, ""dsql1"");}
と出力しており,コンパイルエラーになる(引数の数が違う.さらに第二引数が変.).これは
{ ECPGdeallocate_one(__LINE__, "dsql1");}
とすることでとりあえずコンパイルエラーを回避することができる(※2).
※1 私の環境では/usr/include/postgresql/にあった.
※2 関数の仕様の裏をとっていないが,本質的関数名を比較するとたぶんあってる?.変換にはこんな感じか?
awk '/ECPGdeallocate\(__LINE__,[^,]*\);/ {
print "{ ECPGdeallocate_one(__LINE__, " substr($3,2,length($3)-5) ");}"
}
!/ECPGdeallocate\(__LINE__,[^,]*\);/ {
print
}'雛型を使用しない動的SQL
サンプルではまず雛型を使用しない動的SQLを使用してテーブルを作成して削除している.
EXEC SQL EXECUTE IMMEDIATE 文字列;
という埋め込みSQLはプログラム実行時にその文字列をSQLとして発行する.サンプルでは文字列変数を使用している.
EXEC SQL EXECUTE IMMEDIATE :sql1;
SQLの実行結果はsqlcaを使用して行える.サンプルではsql1を編集するということはしていないが,例えばユーザ入力をsql1に格納して発行するということなどももちろんできる.
雛型を使用した動的SQL
雛型を使用しない動的SQLには問題がある.あまりに自由に生成できるので,事前にフィールド数や型などを予測できないため,SELECT結果などを取得する際に問題となる.そこで折衷案として雛型の使用という方法がある.雛型を使用する動的SQLの処理の流れは以下である.
雛型を作成
EXEC SQL PREPARE 雛型名 FROM 雛型文字列;
実行
EXEC SQL EXECUTE 雛型名 USING 変数 INTO 変数;
雛型領域の開放(※)
EXEC SQL DEALLOCATE PREPARE 雛型名;
雛型には?が使用でき,EXECUTE時のUSINGO句の変数が順次?に当てはめられる.さらにSELECT時にはターゲットリストがEXECUTEのINTO句の変数に格納される.サンプルでは雛型を使用した動的SQLでテーブルの作成/削除,インサート,セレクトを行っている.以下はインサートの部分である.
id=1;
memcpy(name, “foovar”, 7);
EXEC SQL PREPARE dsql3 FROM :sql3;
EXEC SQL EXECUTE dsql3 USING :id, :name;
EXEC SQL DEALLOCATE PREPARE dsql3;
if (memcmp (sqlca.sqlstate, “00”, 2) != 0)
{
fprintf (stderr, “%s\n”, sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
sql3は文字列で雛型である.サンプルではDECLARE SECTIONで以下のようにした.
char *sql3 = “INSERT INTO hoge VALUES (?, ?);”;
雛型に?があるが,実行時にUSING句を使用することで,変数の値を順次?の部分に当てはめていく.すなわち上の場合,
EXEC SQL INSERT INTO hoge VALUES (:id, :name);
と同じ結果になる.以下はセレクトの部分である.
EXEC SQL PREPARE dsql4 FROM :sql4;
EXEC SQL EXECUTE dsql4 INTO :id, :name :nullFlag;
EXEC SQL DEALLOCATE PREPARE dsql4;
if (memcmp (sqlca.sqlstate, “00”, 2) != 0)
{
fprintf (stderr, “%s\n”, sqlca.sqlerrm.sqlerrmc);
EXEC SQL ROLLBACK;
goto END;
}
else
{
if (nullFlag == 0)
{
fprintf (stderr, “id = %d / name = %s\n”, id, name);
}
}
sql4は文字列で雛型である.サンプルではDECLARE SECTIONで以下のようにした.
char *sql4 = “SELECT id, name FROM hoge;”;
で,実行時にINTO句を使用して,ターゲットリストをそれぞれ変数id,nameに格納している.セレクトでは上のように指示子が使用でき,NULLかどうかの判定ができるが,インサートでフィールドにNULLを指定する方法がよくわからない.
※ 明示的に開放しない場合は,DB切断時に開放されるらしい.
