ファイヤープロジェクト
クエリの発行
2004-05-09T04:45+09:00   matsu
埋め込みSQLによるクエリの発行は簡単である.本頁では特に簡単なものを取り上げる.
プログラムに埋め込むSQLは大まかに分類すると,スキーマが固定のものと固定でないものがある.スキーマが固定であれば,コーディング時に変数を使用するなどして,そのプログラムが発行する全てのSQL文を作成しておくことが可能である.しかし後者の場合はプログラム実行時にプログラムが動的にSQLを生成して発行する必要がある.このようなSQL文を動的SQLというが,これについては別頁にて記述する.本頁では,(動的SQLに対して)静的なSQLを発行してみることを目標とする.
では本頁のサンプルを以下に示す.
このファイルでは
EXEC SQL INCLUDE selectsample.pcg;
としている部分がある.これはecpgに指定したファイルをインクルードするように指示するディレクティブである.#includeでは埋め込みSQLのためのコード生成がなされないが,上記のEXEC SQL文でインクルードしたものは,EXEC SQL文を記述した部分に展開され,その上で通常の埋め込みSQLと同様に処理される.で,そのselectsample.pcgは以下である.
Makefileは以下のようにしてみた.
ECPG=/usr/lib/postgresql/bin/ecpg
LIBS=-lecpg -lpq
INCLUDE=-I/usr/include/postgresql
CFLAGS=-DDEBUG_ON -Wall

query: query.c
	gcc -o query $(INCLUDE) $(LIBS) $(CFLAGS) $<

query.c: query.pcg selectsample.pcg
	$(ECPG) $<

clean:
	rm query query.c
では実行結果.
$> ./query  -d tcp:postgresql://dbserver/firstdb -u matsu -p hogefuga
[12124]: ECPGdebug: set to 1
[12124]: ECPGconnect: opening database firstdb on dbserver port <DEFAULT> for user matsu
[12124]: ECPGexecute line 52: QUERY:
  create  table hoge (id integer   primary key , name char ( 8 ))
  on connection dbConnection
[12124]: CREATE TABLE / PRIMARY KEY will create implicit index 'hoge_pkey'
  for table 'hoge'[12124]: raising sqlcode 0
[12124]: ECPGexecute line 52 Ok: CREATE
!!!!!SQLSTATE!!!!!! YE000
!!!!!SQLCODE!!!!! 0
[12124]: ECPGexecute line 70: QUERY: insert into hoge
  values(  0 , 'aaa' ) on connection dbConnection
[12124]: ECPGexecute line 70 Ok: INSERT 964322 1
[12124]: ECPGexecute line 70: QUERY: insert into hoge
  values(  1 , 'aaa' ) on connection dbConnection
[12124]: ECPGexecute line 70 Ok: INSERT 964323 1

...省略...

[12124]: ECPGexecute line 2: QUERY: select count ( id )
   from hoge on connection dbConnection
[12124]: ECPGexecute line 2: Correctly got 1 tuples with 1 fields
[12124]: ECPGget_data line 2: RESULT: 10 offset: 4 array: 3
OID = 0
LINE NUM = 1
count = 10
[12124]: ECPGexecute line 27: QUERY: declare hogecursor cursor for
  select id ,name from hoge where mod ( id  , 2 ) = 0 on connection dbConnection
[12124]: ECPGexecute line 27 Ok: DECLARE
[12124]: ECPGexecute line 36: QUERY: fetch hogecursor on connection dbConnection
[12124]: ECPGexecute line 36: Correctly got 1 tuples with 2 fields
[12124]: ECPGget_data line 36: RESULT: 0 offset: 4 array: 3
[12124]: ECPGget_data line 36: RESULT: aaa      offset: 9 array: 3
fetched id = 0 name = aaa     
[12124]: ECPGexecute line 36: QUERY: fetch hogecursor on connection dbConnection
[12124]: ECPGexecute line 36: Correctly got 1 tuples with 2 fields
[12124]: ECPGget_data line 36: RESULT: 2 offset: 4 array: 3
[12124]: ECPGget_data line 36: RESULT: aaa      offset: 9 array: 3
fetched id = 2 name = aaa     

...省略...

[12124]: ECPGexecute line 51: QUERY: close hogecursor on connection dbConnection
[12124]: ECPGexecute line 51 Ok: CLOSE
[12124]: ECPGexecute line 83: QUERY: update hoge set name  = 'bbb' 
  where id  = 5 or id  = 9 on connection dbConnection
[12124]: ECPGexecute line 83 Ok: UPDATE 2
OID = 0
LINE NUM = 2
[12124]: ECPGexecute line 98: QUERY: delete from hoge where id  = 7 on connection dbConnection
[12124]: ECPGexecute line 98 Ok: DELETE 1
OID = 0
LINE NUM = 1
[12124]: ECPGexecute line 113: QUERY: drop table hoge  on connection dbConnection
[12124]: ECPGexecute line 113 Ok: DROP
[12124]: ECPGtrans line 122 action = commit connection = dbConnection
[12124]: ecpg_finish: Connection dbConnection closed.
表示の都合上,改行等手を加えた.このサンプルでは出力のほぼ全てはECPGによるもので少しうるさいが,デバッグの時には大変便利そうである.
ECPGでは,デフォルトではオートコミットではない.すなわち
EXEC SQL COMMIT;
などをしないとコミットされない.なお,ロールバックは以下である.
EXEC SQL ROLLBACK;
もちろんオートコミットにすることもできる.これには以下の二つの方法がある.
ecpgのオプションによる指定
ecpg実行時に-tオプションをつける.
EXEC SQL文による指定
コードに以下を記述する.
EXEC SQL SET AUTOCOMMIT TO ON;
もちろん
EXEC SQL SET AUTOCOMMIT TO OFF;
でオートコミットは無効になる.
念のためにデフォルトではオートコミットではないことを確認しておく.サンプルには
// 実験用
//EXEC SQL ROLLBACK;
と記述されている部分がある.これを有効にしてテーブル作成をROLLBACKするとサンプルは続くインサートの最初でこけて(テーブルがない)そのまま終了するはずである.実行してみる.
$> ./query  -d tcp:postgresql://dbserver/firstdb -u matsu -p hogefuga
[12163]: ECPGdebug: set to 1
[12163]: ECPGconnect: opening database firstdb on dbserver port <DEFAULT> for user matsu
[12163]: ECPGexecute line 52: QUERY: 
  create table hoge ( id integer   primary key , name char ( 8 ) )
  on connection dbConnection
[12163]: CREATE TABLE / PRIMARY KEY will create implicit index 'hoge_pkey'
  for table 'hoge'[12163]: raising sqlcode 0
[12163]: ECPGexecute line 52 Ok: CREATE
!!!!!SQLSTATE!!!!!! YE000
!!!!!SQLCODE!!!!! 0
[12163]: ECPGtrans line 65 action = rollback connection = dbConnection
[12163]: ECPGexecute line 70: QUERY: insert into hoge values(  0 , 'aaa' )
  on connection dbConnection
[12163]: ECPGexecute line 70: Error: ERROR:  Relation "hoge" does not exist
[12163]: raising sqlstate YE000 in line 70, ''Relation "hoge" does not exist' in line 70.'.
'Relation "hoge" does not exist' in line 70.
[12163]: ECPGtrans line 74 action = rollback connection = dbConnection
[12163]: ecpg_finish: Connection dbConnection closed.
確かにROLLBACKが実行され,テーブル作成がなかったことになっている.
テーブル作成と削除を行う埋めこみSQLは通常のSQLとほとんど同じである.サンプルでのテーブル作成は以下の部分.
EXEC SQL CREATE TABLE hoge (id integer PRIMARY KEY, name char (8));
サンプルでのテーブル削除は以下の部分.
EXEC SQL DROP TABLE hoge;
通常のSQLの頭に"EXEC SQL"とつけるだけである.さて,エラーコードチェックについてであるが,SQLSTATUSは仕様では正常終了の場合"00000"となるはずである.サンプルではテーブル作成以外はそのようになっているが,テーブル作成だけはなぜか"YE000"となっていたので(※)やむを得ずSQLCODEを使用した.
fprintf (stderr, "!!!!!SQLSTATE!!!!!! %c%c%c%c%c\n", sqlca.sqlstate[0],
         sqlca.sqlstate[1], sqlca.sqlstate[2], sqlca.sqlstate[3],
         sqlca.sqlstate[4]);
fprintf (stderr, "!!!!!SQLCODE!!!!! %ld\n", sqlca.sqlcode);
//  if (memcmp(sqlca.sqlstate, "00", 2) != 0)
if (sqlca.sqlcode != 0)
  {
※ クラス"YE"は内部エラーらしい.
挿入,更新,削除も通常のSQL文の始めにEXEC SQLを付けるのが基本であるが,多くの場合宣言セクションで宣言した変数を使用する.サンプルでは挿入は以下の部分で行っている.
for (i = 0; i < 10; i++)
  {
    EXEC SQL INSERT INTO hoge VALUES (:i, 'aaa');
    if (memcmp (sqlca.sqlstate, "00", 2) != 0)
      {
ループ変数iをフィールドidの値として使用した.サンプルでは更新は以下の部分である.
/* 更新 */
EXEC SQL UPDATE hoge SET name = 'bbb' WHERE id = 5 OR id = 9;
if (memcmp (sqlca.sqlstate, "00", 2) != 0)
  {
    fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK;
    goto END;
  }
/* 更新された行数とそのOIDの出力 */
else
  {
    fprintf (stderr, "OID = %ld\n", sqlca.sqlerrd[1]);
    fprintf (stderr, "LINE NUM = %ld\n", sqlca.sqlerrd[2]);
  }
返り値チェックが正常の場合,sqlca.sqlerrd[1]とsqlca.sqlerrd[2]を出力している.前者は行のOID(何?)らしい.後者は更新された行数である.サンプルでは削除の処理は以下の部分でおこなっている.
/* 削除 */
EXEC SQL DELETE FROM hoge WHERE id = 7;
このあとの処理は更新と同じである.sqlca.sqlerrd[2]には削除した行数が格納されている.
サンプルでは選択処理は
/* 選択コードをインクルード */
EXEC SQL INCLUDE selectsample.pcg;
とし,selectsample.pcgに記述している.単一行の選択(選択される行数が高々1の選択)は以下で行っている.
EXEC SQL SELECT
count (id)
INTO :count FROM hoge;
INTO句があることに注意する.選択された結果の値はINTO句で指定した変数(※)に格納される.サンプルではエラーコードが正常ならばこれを出力している.
※ 当然宣言セクションで宣言しておく必要がある.さらに当然ながらターゲットリストのターゲット数とINTO句の変数の数は一致しなければならない.
単一行が返るとわかっている選択には前節の方法が使えたが,複数行が返る場合にはカーソルを使用する必要がある.念のためカーソル処理の流れを整理しておく.
  1. カーソル宣言
  2. カーソルオープン
  3. フェッチ
  4. カーソルクローズ
サンプルではカーソルの宣言は以下で行っている.
EXEC SQL DECLARE hogecursor CURSOR FOR
  SELECT id, name FROM hoge WHERE mod (id, 2) = 0;
頭にEXEC SQLをつける.sqlstateのチェックは不要(※).以下はカーソルオープン.
/* カーソルオープン */
EXEC SQL OPEN hogecursor;
そしてカーソルクローズ.
/* カーソルクローズ */
EXEC SQL CLOSE hogecursor;
オープン,クローズともに頭にEXEC SQLをつけるだけである.最後にフェッチについて記述する.
/* フェッチ */
do
  {
    EXEC SQL FETCH hogecursor INTO:id,:name;
    if ((memcmp (sqlca.sqlstate, "00", 2) != 0)
	&& (memcmp (sqlca.sqlstate, "02", 2) != 0))
      {
	fprintf (stderr, "%s\n", sqlca.sqlerrm.sqlerrmc);
	goto END;
      }
    else if (memcmp (sqlca.sqlstate, "02", 2) != 0)
      {
	name[8] = '\0';
	fprintf (stderr, "fetched id = %d name = %s\n", id, name);
      }
  }
while (memcmp (sqlca.sqlstate, "02", 2) != 0);
前節のSELECTと基本的に同じである.do-while文で回してSQLSTATEのクラスが"02"(行が選択されなかった)になるまでループしている.おそらくこのパターンは多用する.
※ ecpgはいくつかの埋め込みSQLをECPGdo呼び出しに変換する.sqlstateを含むsqlcaはECPGdo呼び出しによって設定される.で,ecpgの変換では埋め込みSQLのカーソル宣言ではECPGdo呼び出しは行われない(カーソル宣言の情報はカーソルオープン時のECPGdo呼び出しに使用される).
matsu(C)
Since 2002
Mail to matsu