InteBase/FireBird + dbExpress によるデータベースの低レベルアクセス実験 - SQLconnection だけでデータベースを操作してみよう -

－データベース接続まで－

　データベースの接続までは、一般の dbExpress を使ったデータベースアプリケーションとの違いはありません。ご存知の方は読み飛ばしてください。

コンポーネントの配置
　まず最初は、コンポーネントの配置です。
　C++ Builder のコンポーネントパレットの"dbExpress"タグ内にある、"SQLConnection" コンポーネント（通常は一番左端）をフォームの好きな場所に貼り付けます。 SQLConnection コンポーネントは非ビジュアルコンポーネントですので、フォームの何処に貼り付けても実行時には表示されません。
　そのため、Webサーバアプリケーションやコマンドラインベースのプログラムでも使用できます。
　ちなみに、コマンドラインプログラム作成時は、通常は貼り付けるフォームがありません。この場合は、手動でヘッダをインクルードしオブジェクトの作成をソース上で行うことにより、使用できるようになります。

接続パラメータの設定
　２番目のステップとして、接続パラメータの設定を行います。　これには、大雑把に分けて次の３つの方法があります。

1	接続設定ダイアログによる設定	C++ Builder のIDE上で接続の設定を行う方法です。
2	iniファイルから設定を読み込む	設定を ini ファイルに記述しておき、LoadParamFromIniFile メソッドで読み込みます。
3	プロパティを直接設定	コンポーネントのプロパティを、IDE及びプログラムソース上で設定する方法です。

(1) 接続設定ダイアログによる設定

　C++ Builder のIDE上で接続の設定を行う方法です。
　フォームに貼り付けたコンポーネントをダブルクリックすると、接続設定のダイアログが開きますので、各項目を設定します。例では、キャラクタセットはシフトJISとしています。

データベース接続のダイアログ

　また、その場でデータベースに接続を試みることが可能です。具体的には、オブジェクトインスペクタの "Connected"プロパティを true に変更すると、その場でデータベースに接続します。接続できない場合はエラーが表示されるので、パラメータの確認も可能です。

(2) iniファイルから設定を読み込む

　.ini ファイルに情報を記述しておき、実行時にそれを読み込む方法です。テキストファイルでパラメータを設定するので、運用時の変更が容易です。
　クーロンから起動されるプログラム、デーモンなどのバックグラウンドで動作するプログラムや、環境変更が多いシステムに最適だと思われます。
　ただし、重要な情報が単純なテキストファイルに記述されることになりますので、ファイル自体のアクセス権限や運用に注意しないと、セキュリティ上の弱点となってしまいますので注意が必要です。　パスワードなどは空白や無意味なものを設定し、LoginPrompt プロパティを true にしておくことにより、ある程度セキュリティを高めることが出来ます。

　また、すべてのプロパティが本設定を読み込むだけでは設定されないと思われるので、設定が行われないと思われるプロパティに対しては、前述の項(1)、後述の項(3)に示す方法で設定しています。

　ここでは、ネットワーク経由でアクセスする場合の設定を示します。ネットワーク経由ですので、ファイルパスの前に IP アドレスが必要です。　ちなみに、データベース本体は Linux + FireBird となっています。


[TABLE_MASTER]
BlobSize=-1
CommitRetain=False
Database=192.168.1.16:/db/master.gdb
DriverName=Interbase
ErrorResourceFile=
LocaleCode=0000
Password=ijioqwd3z
RoleName=RoleName
ServerCharSet=SJIS_0208
SQLDialect=1
InterbaseTransIsolation=ReadCommited
User_Name=SYSDBA
WaitOnLocks=True

接続
　SQLConnection コンポーネントの Open メソッドを呼び出すか、Connected プロパティを true にすることによりデータベースに接続します。
　しかし実際に使用してみると、SQL を発行するときに非接続の場合、自動的に接続処理を行うようです。つまり、切断、接続を明示的に行いたい場合（一時的に接続を切りたい場合など）以外は、明示的に呼び出す必要はほとんどない模様です。

　LoginPrompt プロパティを true にして接続を行うと、ダイアログを開いてデータベースアカウントの問合せが自動で行われます。接続パラメータに設定したアカウントとパスワードを使用し、問い合わせなしで接続を行いたい場合は、忘れずに false に変更しましょう。ちなみに、デフォルトでは true に設定されているようです。

切断
　SQLConnection コンポーネントの Close メソッドを呼び出すか、Connected プロパティを false にすることにより、データベースとの接続を切断します。　これにより、データベースサーバのリソースの開放が行われる場合がありますので、しばらくアクセスを行わない場合は、切断したほうが良いようです。ただし、接続や切断の回数が増えるとオーバーヘッドが問題となりますので、場合によりアルゴリズム上の工夫も必要です。
　また、あくまで原因不明ですが、同セッションで SQL を連続(数万回オーダー)して発行していると、クライアント側の動作が不安定になり操作に失敗する場合がありましたが、定期的に切断、再接続の処理を入れると安定して動作するようになりました。

－ SQLの発行と行の取得－

　SQL発行によるデータの更新では、失敗時のロールバックの処理を行うために、C++ の例外処理を使用しています。これにより、SQL 実行失敗時にもデータの整合性を保つことが出来ます。

パラメータが不要な場合
　ExecuteDirect メソッドが簡単、かつ軽量です。
　結果のレコードを受取らない場合は、2番目の引数は省略することが出来ます。
　SQL内に直接すべてのデータが書ける場合は本メソッドで問題ないはずですが、今のところは、デフォルトトランザクションのコミット等に使用し、問合せには Execute メソッドを使用しています。
　取得したレコードは、TCustomSQLDataSet クラスに格納されます。

　試してみた限りでは、SQL によるパラメータ付きのトランザクションの明示的開始は上手くいかないようです。このような場合は、素直に StartTransaction メソッドを使用しましょう。場合によっては接続パラメータの項目も見直さないと、期待した動作とならない場合がありますので、注意が必要です。(InterBaseの場合は、Interbase TransIsolation等)

パラメータが必要な場合
　Execute メソッドを使用します。
　BLOBはバイナリやサイズの大きいデータがおおいため、SQL 上では上手く扱えない場合が多くなります。このような場合にはパラメータを使用して受け渡しを行います。

　パラメータに関しては、続編で記述予定です。速く読みたいという人はメールで要望を頂ければスピードアップするかもしれません。

簡単な例
　ユニークなシリアル番号をデータベースの表を使用して生成するプログラムです。UPDATE で更新後に SELECT で読んでいます。始めの UPDATE による更新で実質的な行ロックが行われるため不要なのですが、一応トランザクションも使用しています。

(1) 表の構造

　標識に使用している mark と、カウント値を保持する counter の２列の整数型の項目が必要です。スキームの定義と初期化を以下のような SQL 文で行います。


/* テーブル作成 */
CREATE TABLE COUNTER (
/* 1 行識別子  */  mark    INTEGER NOT NULL PRIMARY KEY,
/* 2 カウンタ  */  counter INTEGER NOT NULL,
);

/* 初期化 */
INSERT INTO COUNTER(mark, counter) VALUES(0,0);
COMMIT;

(2) ソースリスト

　関数のソースリストを示します。初期化処理等は行っていませんので、接続設定などは関数を呼び出す前に行う必要があります。
　詳しい解説は項(3)をお読みください。


// ********************************************************************
//              ユニークなシリアル発行
//                  SQLcnt  : TSQLConnection のエンティティ
//                  tblname : 操作するテーブルの名前
//                  retry   : ロックされていた場合のリトライ回数
//                  slp     : リトライ時のスリープ時間(msec)
// ********************************************************************
int     GetNewSerial(TSQLConnection *SQLcnt, char* tblname, int retry = 500, int slp = 20)
{
    int     ret = -1;       // error
    TTransactionDesc td;
    TCustomSQLDataSet       *ds = NULL;

    // SQL コマンド生成
    AnsiString  sql1,sql2;
    sql1.sprintf("UPDATE %s SET counter = counter+1 WHERE mark=0", tblname);
    sql2.sprintf("SELECT counter FROM %s WHERE mark=0", tblname);

    // リトライループ
    for ( ; retry >= 0 ; retry--) {
        try {
            if (!SQLcnt->InTransaction) {
                td.TransactionID = 1;
                td.IsolationLevel = xilREPEATABLEREAD;
                SQLcnt->StartTransaction(td);
                try {
                    // 値更新（実質的ロック兼用）
                    SQLcnt->Execute(sql1, NULL, NULL);
                    // 値取得
                    SQLcnt->Execute(sql2, NULL, &ds);
                    // カウンタ
                    int     counter = ds->FieldValues["counter"];
                    ret = counter-1;    // +1 の値を返すので -1 する
                    // 正常に終了
                    SQLcnt->Commit(td);     // 成功した場合，変更をコミットする
                }
                catch (...)
                {
                    SQLcnt->Rollback(td);   // 失敗した場合，変更を取り消す
                }
            }
        }
        __finally {
            // メモリリーク防止のため、異常時も含めて必ず開放処理を行う
            if (ds != NULL) delete ds;
            ds = NULL;
        }
        if (ret < 0) {
            // 成功していない場合、少し休んでリトライ
            Sleep(slp);
        } else {
            break;
        }
    }
    return  ret;
}

(3) プログラムの解説
－　SQL 文の生成　－
　SQL文を、AnsiString の sprintf メソッドを使用して生成しています。


    sql1.sprintf("UPDATE %s SET counter = counter+1 WHERE mark=0", tblname);
    sql2.sprintf("SELECT counter FROM %s WHERE mark=0", tblname);

　sql1 は値の更新用、sql2 は更新後の値の取得です。
　取得後にプログラムで加算、その後に行更新でも良いのですが、確実なレコードロックも兼ねて加算処理は SQL 内で行う方法としています。更新後に値を取得しますので、結果は後で -1 を施こしています。

－　リトライループ　－
　トランザクションの排他処理による例外発生や、接続の一時的な異常に対応するために、SQL 発行処理全体をリトライループで囲っています。ロック時などに連続して処理を行うとCPUの時間を無駄に消費してしまうため、リトライ時は若干の時間だけタスクがスリープするようになっています。

－　メモリ開放保証のための例外処理　－
　問合せ(SELECT)の結果を受取るために、TCustomSQLDataSet 型のクラスを使用しています。クラスのエンティティは Execute メソッド内で自動で確保されますが、開放は、使用する側で責任を持って行う必要があるようです。
　そのため、SQL 発行処理全体を try {} __finally{} 例外処理を使用することにより、確実にエンティティ削除の処理を行うようにしています。　具体的には以下のような形になっています。


        TCustomSQLDataSet       *ds = NULL;  // フラグ兼用のために初期値を NULL に
        try {
   ・・・・・ SQL発行処理がここに入る ・・・・・
        }
        __finally {
            // メモリリーク防止のため、異常時も含めて必ず開放処理を行う
            if (ds != NULL) delete ds;
            ds = NULL;
        }

－　明示的トランザクションの使用　－
　排他処理のために、明示的なトランザクションを使用しています。
　トランザクションを開始する前に、まずトランザクション中か判定し、トランザクションが開始されてない場合にのみ、トランザクション開始と問合せなどの SQL 発行を行うようにしています。　トランザクションで排他中の場合は、リトライループなどの処理でフォローするという前提となっています。
　各パラメータに関しては、C++ Builder のヘルプを参照してください。


            if (!SQLcnt->InTransaction) {
                td.TransactionID = 1;
                td.IsolationLevel = xilREPEATABLEREAD;
                SQLcnt-<StartTransaction(td);
                try {
        ・・・・・ SQL発行処理がここに入る ・・・・・
                    // 正常に終了した場合
                    SQLcnt->Commit(td);     // 成功した場合，変更をコミットする
                }
                catch (...)
                {
                    SQLcnt->Rollback(td);   // 失敗した場合，変更を取り消す
                }
            }

　ちなみに、デフォルトのトランザクションのみを使用する場合は、ExecuteDirectメソッドを使用し、成功時の COMMIT や失敗時の ROLLBACK 発行処理を行うようにします。

－　SQL の発行と行取得　－
　Execute メソッドにより、SQL を発行しています。今回の例の場合、ExucuteDirect の方がよりふさわしいと考えられますが、現状では Execute メソッドでのみ実験しています。
　Execute メソッドには、TCustomSQLDataSet のポインタを受取るためのポインタを渡していますので、取得した行のデータを格納しクラスのポインタが、指定したポインタに設定されてきます。
　最後に、設定されたポインタを使用して FieldValues プロパティに列名を渡し、格納されたデータを取得しています。本例の場合は、取得できる行は１つしか有り得ないという前提でプログラムが組まれていますので、実使用時には注意が必要です。　テーブル作成時に、mark 列を主キー（プライマリーキー）に指定しているのに注目してください。


            // 値取得
            SQLcnt->Execute(sql2, NULL, &ds);
            // カウンタ
            int     counter = ds->FieldValues["counter"];

　取得したデータの開放は、前述した例外処理(__finally {})として行っています。

－行取得に関しての参考情報－

　取得した行データからの情報取得に関して役に立つ事項をまとめてみました。　個々の詳しい情報は、C++ Builder のヘルプを参照してください。

取得した行数の取得	TCustomSQLDataSet::RecordCount プロパティを使用して取得できます。
複数行アクセス	TDataSet::Firstメソッド、Nextメソッドを使用します。ループを組むことにより、複数行（レコード）へのアクセスを実現出来ます。 C++ Builder のヘルプに例がありますので参照してください。
取得項目数(列数)の取得	TDataSet::FieldCountプロパティで取得できます。

	－はじめに－
	C++ Builder には、データベースをアクセスするためのコンポーネントが色々用意されていますが、 2003年末現在、最もボーランドが推しているのは dbExpress だと勝手に思っています。　C++ Builder 6 Professional 付属の dbExpress では、データベースとしては InterBase と MySQL をサポートしていたので、今回は InterBase6 / FireBird (1.0.3) を選択しました。 FireBird ならば、ネットワークを介したアクセスが必要な環境でもライセンスフリーで使用できますし、通常の用途には十分な機能があるのが魅力です。　dbExpress にも色々便利なコンポーネントがありますが、最大限に薄いレイヤでデータベースにアクセスしたい.....とい個人的な欲求の下に、TSQLConnection コンポーネントのみを使用してデータベースを自在にアクセスする実験をやってみました。　オールドDBプログラマでしたら、SQL 埋め込み用のプリプロセッサを使う方法に似ていると言えばわかりやすいでしょう。ORACLE の Pro-C や InterBase / FireBird の gpre.exe を使用した方式です。　このような単純な使用法に関して、ヘルプの情報も少なめですし、適当なサンプルも発見できなかったので、手探りで実験してみました。　そのため、必ずしも正しい方法で使用しているとは限りませんので、ご承知おき下さい。　とりあえず現状では、BLOBの取り扱いまで一通りの操作に成功していますが、今回は「何はともあれ、ともかくアクセスしてみよう！」というコンセプトでまとめてみました。　気が向いたら随時追加していきます。 BLOBとパラメータに関してはここに追加しました。

データベース操作実験