SQLdb Tutorial3/ja

│ English (en) │ français (fr) │ 日本語 (ja) │

データベースのポータル

参照: Databases 概要 ライブラリ フィールドの型 コントロール FAQ SqlDBHowto/ja TSQLQuery の利用 メモリ上で動作するデータベースの作り方 チュートリアル/練習となる記事: 概要 0 - データベースのセットアップ 1 - 開始 2 - 編集 3 - クエリ 4 - データモジュール SQLdb Programming Reference/ja 各種データベース Advantage - MySQL - MSSQL - Postgres - Interbase - Firebird - Oracle - ODBC - Paradox - SQLite - dBASE - MS Access - Zeos

概要

このチュートリアルでは、次の方法を学ぶ

• ログインフォームの使用など、アプリケーションを複数のデータベースに適したものにする
• データベース データを (データベース コントロールではなく) 通常のコントロールに取得する
• コントロールからデータを取得してデータベースに戻す
• パラメータ化されたクエリを実行する。

マルチデータベースのサポート

任意のデータベースとログイン フォームを使用して、SQLDB がサポートする複数の異なるデータベース サーバー/組み込みライブラリをサポートできる。

利点:

• ユーザー/プログラマは、任意の sqldb t*connection を動的に使用できるため、データベースを選択できる。

短所:

• より複雑な SQL をさらに調整する必要がある可能性がある。 各データベースには独自の方言がある。 もちろん、データベース固有の SQL を呼び出すことも可能だが、これはメンテナンスの問題に発展する可能性がある。
• T*connection 固有のプロパティ (Firebird 方言を設定するための TIBConnection.Dialect など) は使用できない。

マルチデータベース サポートを使用するには、TIBConnection などの特定の T*Connection の代わりに、TSQLConnector (TSQLConnection ではない) を使用する。これにより、(プログラムの実行中に) 特定の T*Connection が動的に選択される。 ConnectorTypeプロパティに基づいてT*Connectionを使用する。:

uses
...
var
  Conn: TSQLConnector;
begin
  Conn:=TSQLConnector.Create(nil);
  try
     // ...実際のコネクタのタイプはこのプロパティによって決まる。
     // ChosenConfig.DBType 文字列が一致することを確認する
     // コネクタのタイプ (例:
     // そのコネクタの T*ConnectionDef.TypeName 。
    Conn.ConnectorType:=ChosenConfig.DBType;
    // 残りは通常通り:
    Conn.HostName:='DBSERVER';
    Conn.DatabaseName:='bigdatabase.fdb';
    Conn.UserName:='SYSDBA';
    Conn.Password:='masterkey';
    try
      Conn.Open;

ログインフォーム

SQLdb Tutorial1/ja で説明したように、ユーザーはアプリケーションにハードコードされた資格情報を使用するのではなく、フォーム (または安全に保存されている構成ファイル) を使用してデータベースにログインする必要がある。 セキュリティ上の考慮事項に加えて、データベース サーバーの情報が変更されるたびにアプリケーションを再コンパイルしなければならないのは、あまり良い考えではない。

dbconfiggui.pas では、ini ファイルが存在する場合はそこからデフォルト値を取得するログイン フォームをセットアップする。 これにより、エンタープライズ展開、いくつかの詳細 (データベース サーバ、データベース名) を入力してデフォルトの接続を設定できる。 。 このフォームでは、ユーザーは自分のユーザー名/パスワードを追加/編集し、次に進む前に接続をテストできる。

TDBConnectionConfig クラスを持つ別の dbconfig.pas ユニットを使用して、選択した接続の詳細を保存します。 このクラスは、ini ファイルからのデフォルト設定の読み取りをサポートしています。

これにより、GUI ログイン フォームなしでの使用 (例: 無人/バッチ操作の実行時) が可能になり、たとえば、Web サイトでの再利用が可能になる。

このTDBConnectionConfigクラスはログイン フォームにConfigプロパティとして表示されるため、メイン プログラムは設定フォームをモーダルに表示し、ユーザーによる [OK] クリックを検出して、フォーム設定を閉じる前に選択した設定を取得できる。

接続テストコールバック関数

ログイン フォームを柔軟に保つために (Zeos などの他のデータベース層で使用できる)、テスト セクションをコールバック関数として実装し、メイン プログラムで処理させる。

dbconfiggui.pas のログイン フォームの定義:

type
   TConnectionTestFunction = function(ChosenConfig: TDBConnectionConfig): オブジェクトのブール値。

メイン フォームは、構成フォームからのテスト リクエストを処理するために、この定義に一致する関数を実装する必要がある。

コールバック関数は、構成フォームによって渡された構成オブジェクトを受け取り、それを使用して、選択されたデータベース タイプとの接続を構築する。 その後、単純にサーバーへの接続を試行する。 成功した場合は関数の結果を true に設定し、そうでない場合は結果は false のままとなる。

存在しないサーバーへのデータベース接続の試行は長いタイムアウトになる可能性があるため、カーソルを砂時計アイコンに設定して待機する必要があることをユーザーに示す。

uses
...
dbconfig, dbconfiggui
...
procedure TForm1.FormCreate(Sender: TObject);
  LoginForm:=TDBConfigForm.Create(self);
  try
    // dbconfigguiのテストボタンはこのプロシージャにリンクされるだろう
    //ここで...これが''dbconfiggui.pas''の コールバックをConnectionTest functionにリンクさせる...
    LoginForm.ConnectionTestCallback:=@ConnectionTest;
...
function TForm1.ConnectionTest(ChosenConfig: TDBConnectionConfig): boolean;
// コネクションを確かめるためにdbconfigguiの情報を使うコールバック関数
// 確かめるために結果をdbconfigguiへ返す
var
  // 通常のデータベース接続...
  Conn: TSQLConnector;
begin
  result:=false;
  Conn:=TSQLConnector.Create(nil);
  Screen.Cursor:=crHourglass;
  try
    // ...このプロパティで決定される実際の接続タイプ。
    // ChosenConfig.DBType文字列がマッチすることを確かめること
    // コネクタタイプ(例えば、このコネクタにたいする
    // T*ConnectionDef.TypeNameをみること
    Conn.ConnectorType:=ChosenConfig.DBType;
    Conn.HostName:=ChosenConfig.DBHost;
    Conn.DatabaseName:=ChosenConfig.DBPath;
    Conn.UserName:=ChosenConfig.DBUser;
    Conn.Password:=ChosenConfig.DBPassword;
    try
      Conn.Open;
      result:=Conn.Connected;
    except
      // 結果はすでにfalse
    end;
    Conn.Close;
  finally
    Screen.Cursor:=crDefault;
    Conn.Free;
  end;
end;

最後に、実際にコールバックを呼び出す dbconfiggui.pas 内のコードが [テスト] ボタンにリンクされます。 (クラッシュを避けるために) コールバック関数が割り当てられているかどうかをテストする。完全を期すために、有効な構成オブジェクトがあるかどうかもチェックし、単純にコールバック関数を呼び出す。

...
TDBConfigForm = class(TForm)
...
  private
    FConnectionTestFunction: TConnectionTestFunction;
  public
    property ConnectionTestCallback: TConnectionTestFunction write FConnectionTestFunction;
...
procedure TDBConfigForm.TestButtonClick(Sender: TObject);
begin
  // 設定を持つコールバックを呼び出す、接続が
  // 成功したことを明らかにし、テスト結果を返す
  if assigned(FConnectionTestFunction) and assigned(FConnectionConfig) then
    if FConnectionTestFunction(FConnectionConfig) then
      showmessage('Connection test succeeded.')
    else
      showmessage('Connection test failed.')
  else
    showmessage('Error: connection test code has not been implemented.');
end;

追加・修正

ログインフォームの可能な追加/変更:

• dbconfig のコマンド ライン引数処理を追加して適切なデフォルトをプリロードし、プログラムをバッチ スクリプトやショートカットなどで使用できるようにする。
• ログインフォームに「プロファイルの選択」コンボボックスを追加する。 ini ファイル内でデータベースの種類と接続の詳細を指定する複数のプロファイルを使用する。
• 1 つのデータベース タイプのみがサポートされている場合は、データベース タイプ コンボボックスを非表示にする。
• 組み込みデータベースが選択されていることが確実な場合は、ユーザー名とパスワードを非表示にする。
• MS SQL Server コネクタを使用したポート番号またはインスタンス名の指定のサポートを追加。
• 信頼できる認証をサポートするデータベース (Firebird、MS SQL) にサポートを追加: ユーザー名/パスワードの制御を無効にする
• 組み込みデータベースを選択したがファイルが存在しない場合: 確認要求を表示し、データベースを作成する
• コマンドライン アプリケーション用にログイン フォームのコマンドライン/TUI バージョンを作成する(たとえば、curses ライブラリを使用)

この記事やコードの更新は大歓迎する。

データベース データを通常のコントロールに取り込む

  Note: このセクションを開始する前に、SQLdb Tutorial0#Requirements で指定されているようにサンプル従業員データベースが設定されていることを確認されたい。

前のチュートリアルでは、データ バインド コントロールについて説明した。TDBGrid などの特別なコントロールは、そのコンテンツを TDataSource にバインドし、そのソースから更新を取得し、ユーザーの編集内容を送り返すことができる。

プログラム的にデータベースの内容を取得し、その内容を任意の種類のコントロール (または変数) に入力することも可能である。 例として、サンプル従業員データベース テーブルの給与明細を stringgrid に入力する方法を見ていく。

メイン フォームで TStringGrid を追加し、(例えば、OnCreate イベントで呼び出されるプロシージャ LoadSalaryGrid 経由で)データを取得する 。

    // データベースから取得
    try
      if not FConn.Connected then
        FConn.Open;
      if not FConn.Connected then
      begin
        ShowMessage('Error connecting to the database. Aborting data loading.');
        exit;
      end;

      // 最低賃金
      // 注意: ここで１行だけ取得したいが、SQLは様々なデータベースシステムで異なる
      // 後にチュートリアルで、データベースに依存したSQLを扱うが、ここではMS SQL:
      // のMS SQL: 'select top 1 '...を用いておく
      FQuery.SQL.Text:='select ' +
        '    e.first_name, ' +
        '    e.last_name, ' +
        '    e.salary ' +
        'from employee e ' +
        'order by e.salary asc ';
        // ISO SQL+Firebird SQL: では
        //'rows 1 '; をここと以下に加えること。PostgreSQLでは動作しないが
      FTran.StartTransaction;
      FQuery.Open;
      SalaryGrid.Cells[1,1]:=FQuery.Fields[0].AsString;  // i.e. Cells[Col,Row]
      SalaryGrid.Cells[2,1]:=FQuery.Fields[1].AsString;
      SalaryGrid.Cells[3,1]:=FQuery.Fields[2].AsString;
      FQuery.Close;
      // 読み込むときでも、常にコミット(retain)すること
      // これは読み直したときに他者によって更新することを可能にする
      FTran.Commit;
...
      end;
    except
      on D: EDatabaseError do
      begin
        FTran.Rollback;
        MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
          D.Message, mtError, [mbOK], 0);
      end;
    end;

注意すべき点: try..except を使用してデータベース エラーを検出する。 エラーが発生した場合にトランザクションをロールバックするのを忘れていることがわかるが、これは読者の演習として残されている。

クエリ オブジェクトを Open し、それによって FQuery に SQL ステートメントを介してデータベースにクエリを実行するよう命ずる。 これが完了すると、データの最初の行が表示される。 私たちは単にデータが存在すると仮定する。 これは実際にはプログラミング エラーである。FQuery.EOF が true (または FQuery.RecordCount が >0) であるかどうかを確認する方がよりまともだ。

次に、結果の最初の行からデータを取得する。 次の行に移動したい場合は、FQuery.Next を使用するが、ここではその必要はない。 結果を stringgrid に入力し、リスト内の最低給与を示す。 最高の給与についても同様のアプローチがとれる。

上で述べたように、さまざまなデータベースがさまざまなバージョンの SQL をサポートしている(公式 ISO SQL 標準に加えて、またはこれに反して)。 幸いなことに、最終的に使用する DB に基づいてアプリケーションをカスタマイズできる。これは、従業員の給与の標準偏差を取得することで示される、例えば、PostgreSQL SQL では組み込みだが、例えば、Firebirdではデフォルトで利用できない。

この、LoadSalaryGrid プロシージャではPostgreSQLのSQLを用い、コードを他の全てのデータベースに対して構築する。初めに、どのデータベースが読み込まれたか検知する。他の行に以下を加える:

  ...
  SalaryGrid.Cells[3,2]:=FQuery.Fields[2].AsString;
  FQuery.Close;
  // たとえ読み取り時でも、常にトランザクションをコミット(retain)すること
  FTran.Commit;
//存在するコードの最後

  if FConn.ConnectorType = 'PostGreSQL' then
  begin
    // PostgreSQLに対しては、それ固有の SQL 解決策:
    FQuery.SQL.Text:='select stddev_pop(salary) from employee ';
    FTran.StartTransaction;
    FQuery.Open;
    if not FQuery.EOF then
      SalaryGrid.Cells[3,3]:=FQuery.Fields[0].AsString;
    FQuery.Close;
    //  たとえ読み取り時でも、常にトランザクションをコミット(retain)すること
    FTran.Commit;
  end
  else
  begin
    // 他のデータベースでは、このコードアプローチで:
    ....以下参照...
  end;

ConnectorType の使用に注目すること。 使用される文字列は正確に一致する必要がある。 また、クエリからの空の結果も適切にチェックする (従業員テーブルが空の場合に発生する可能性がある)。

... ここで標準偏差をサポートしない他のデータベースのための、コードベースの解決策を実装する:

  // 他のデータベースでは、コードによるアプローチを行う:
  // 1. 値の平均を求める
  FQuery.SQL.Text:='select avg(salary) from employee ';
  FQuery.Open;
  if FQuery.EOF then
    SalaryGrid.Cells[3,3]:='No data'
  else
  begin
    Average:=FQuery.Fields[0].AsFloat;
    FQuery.Close;
    // 2. それぞれの値に対し、平均値の二乗をとり、加え合わせると
    FQuery.SQL.Text:='select salary from employee where salary is not null ';
    FQuery.Open;
    while not FQuery.EOF do
    begin
      DifferencesSquared:=DifferencesSquared+Sqr(FQuery.Fields[0].AsFloat-Average);
      Count:=Count+1;
      FQuery.Next;
    end;
    // 3. 次に、平均「二乗差」を計算し、平方根を取得する。
    if Count>0 then // 0での除算を防ぐ
      SalaryGrid.Cells[3,3]:=FloatToStr(Sqrt(DifferencesSquared/Count))
    else
      SalaryGrid.Cells[3,3]:='No data'; 
  end;
  FQuery.Close;

空のデータ(および0での除算など)を避けるためにFQuery.EOFを用いていることに注意すること。このループがそのやり方を示している: Note that we use FQuery.EOF to check for empty data (and avoid division by zero errors etc). The loop shows how to:

• データベースの値を変数に代入する
• 次のレコードに移動するためFQuery.Next を実行
• クエリデータセットが最後のレコードに達したことを適切にチェックし、データ取得を停止する

結果の画面は以下のようになるだろう - 十進のコンマが用いられていることに気をつけられたい - コンピューターのロケールによってはこれが現れるかもしれない:

最低/最高給与を取得する

このセクションでは、SQL についてさらに役立つ詳細を説明するが、チュートリアルの残りの部分まで作業する必要はない

これで、さまざまなデータベース接続の検出に対処する方法がわかったので、最低給与と最高給与を取得する SQL を調整して、データベース固有の機能を利用できるようになる。

一例として: これは、返される行の数を最初の行のみに制限することで、MS SQL Server で機能するだろう:

select top 1 
e.first_name, e.last_name, e.salary 
from employee e
order by e.salary asc

最低給与を求めるために。

これにより効率的に 1 つのレコードが返される。 他のデータベースでは、ISO ROWS 1 などの他の構文が使用される。勤勉な SQL の学習者は、その重要な部分を見逃して、必要な 1 つのレコードだけのために大きなレコードセット全体を要求しないことをすぐに気づくだろう!。

同じことを達成するための、知っておく価値のある他の方法を簡単に検討してみよう。

最低給与のレコードを取得する別の方法は次ようになるだろう:

SELECT e.first_name, e.last_name, e.salary FROM employee e WHERE e.salary=(SELECT min(salary) FROM employee)

SQL を学ぶものは、Common Table Expressionを研究することで大きな利益を得られるだろう。

CTE を使用すると、次の式で仮想一時テーブルを使用できるようになり、他の方法では不可能な非常に複雑なクエリを明確にコーディングできるようになる。 CTE について知っていれば、それを聞いたことのない同僚よりも先を行くことができる。 たとえば、上記は次のように書き換えることができる (Microsoft SQL Server 構文の例):

WITH TheMinimum as
(
  SELECT min(salary) as MinimumPay FROM Employee
)
SELECT e.first_name, e.last_name, e.salary FROM Employee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)

このような一時テーブルをいくつか連結させて、それぞれが前のテーブルの結果を使用することができる。 JOIN を使用してレコードセットをリンクすると、これらの仮想テーブルをデータベース内の実際のテーブルであるかのように扱うことができる。 また、ハードコードされたデータを使用した簡単なテストに非常に役立つ。これはデータベース接続なしで実行できる:

WITH TestEmployee as
(
  SELECT 'Fred' as first_name, 'Bloggs' as last_name, 10500 as salary
   UNION 
  SELECT 'Joe' as first_name, 'Public' as last_name, 10000 as salary
   UNION 
  SELECT 'Mike' as first_name, 'Mouse' as last_name, 11000 as salary
),
 TheMinimum as
(
  SELECT min(salary) as MinimumPay FROM TestEmployee
)
SELECT e.first_name, e.last_name, e.salary FROM TestEmployee e WHERE e.salary=(SELECT MinimumPay FROM TheMinimum)

このような SQL クエリのコードは非常に長い文字列になる可能性があるが、それは 1つのクエリにすぎず、単純な1 つの式に限定されているどこからでも呼び出すことができます。関数やストアドプロシージャに頼ることなく、複雑なクエリに答えるのに役立つ。

通常のコントロールからデータベースへデータを取得する

これまでに以下を見てきた:

• データ バインド コントロールを使用して SQLDB にデータベースを更新させる方法 (以前のチュートリアル)
• クエリを使用してデータベースからデータを取得する方法 (上記のセクション)

SQL を実行して、コード経由で任意のデータをデータベースに戻すこともできる。 これにより、多少のコーディングを犠牲にして、データベースに対応するスライダーやカスタム コントロールなどのデータベース対応のない変数やコントロールを使用してデータベースにデータを入力できるようになる。

例として、ユーザーが stringgrid 内の最低給与と最高給与を変更できるようにする。

編集を容易にするために、グリッドの Options/goEditing を true に設定する。 次に、以下のプロシージャをグリッドの OnValidate イベントに割り当てる。このイベントは、ユーザーがグリッドの更新を完了するたびに呼び出される。

パラメータクエリ

次のコードは、パラメーター化されたクエリを使用して SQL インジェクション、文字列値の引用符の調整、日付の書式設定などを回避する方法も示している。

コードにあるように、パラメーターに任意の名前を付けて、SQL 内で先頭に : を付けることができる。その値を<somequery>.Params.ParamByName('<thename>').As'<variabletype>';で設定、取得できる。コードでは.AsFloat and .AsStringを示す。

パラメーター化されたクエリは、同じクエリを異なるパラメーターのみを使用してループ内で実行する場合に特に便利である (データの一括読み込みなどを考えてみてほしい)。

例を続ける:クエリ SQL とパラメータを設定した後、通常どおりトランザクションが開始され (その後コミットされ)、その後、ExecSQL を呼び出してクエリが実行される (結果セットは返されない。SQL ステートメントがデータを返す SELECT または INSERT...RETURNING である場合は、上記の例のように Open を使用する):

procedure TForm1.SalaryGridValidateEntry(sender: TObject; aCol, aRow: Integer;
  const OldValue: string; var NewValue: String);
begin
  // 最低、最高給与を持つセルのみ:
  if (aCol=3) and ((aRow=1) or (aRow=2)) then
  begin
    // もし正の数値データが入力された場合、最低、最高給与を更新することを可能にする
    if StrToFloatDef(NewValue,-1)>0 then
    begin
       // 主キーを次のように保存する。 グリッド内の非表示のセルとそれを使用します。
       // 更新クエリの方がクリーンだが、難しい方法でも実行できます:
      FQuery.SQL.Text:='update employee set salary=:newsalary '+
        ' where first_name=:firstname and last_name=:lastname and salary=:salary ';
      FQuery.Params.ParamByName('newsalary').AsFloat:=StrToFloatDef(NewValue,0);
      FQuery.Params.ParamByName('firstname').AsString:=SalaryGrid.Cells[1,aRow];
      FQuery.Params.ParamByName('lastname').AsString:=SalaryGrid.Cells[2,aRow];
      FQuery.Params.ParamByName('salary').AsFloat:=StrToFloatDef(OldValue,0);
      FTran.StartTransaction;
      FQuery.ExecSQL;
      FTran.Commit;
      LoadSalaryGrid; //標準偏差を再び読み込み
    end
    else
    begin
      // その入力が誤っていることを伝える... さもないと迷わせることになる:
      Showmessage('Invalid salary entered.');
      NewValue:=OldValue;
    end;
  end
  else
  begin
    // 他のセルの編集を隠蔽して廃棄する
    NewValue:=OldValue;
  end;
end;

データベース エラーを適切に捕捉して適切なエラー メッセージを表示するために、このコードに try..except ブロックを追加するのを忘れていることに注意すること。 このチュートリアルで Firebird サンプル EMPLOYEE データベースを実行している場合は、給与を非常に低い値 (たとえば 1) に変更して、何が起こるかを確認されたい。

最後に、この例では UPDATE SQL クエリを示したが、INSERT クエリを実行してプログラムで新しいデータを挿入することもできる。 また、テーブル/ビュー/プロシージャ名ではなく、フィールドにパラメータを使用する限り、あらゆる種類の SQL クエリ (SELECT、UPDATE など) でパラメータを使用できる。

まとめ

このチュートリアルでは次のように説明しました。

• 複数のデータベースタイプをコード化する方法
• ログインフォームを使用してプログラムからデータベースアクセス構成を分離する方法
• プログラムでデータを取得および更新する方法

コード

2012 年 11 月以降、コードは $(lazarusdir)examples/database/sqldbtutorial3 にあります。

If you have an older version (e.g. Lazarus 1.0.2), you can also download the code via the Lazarus SVN website

See also

• SQLdb Tutorial0/ja: Instructions for setting up sample tables/sample data for the tutorial series.
• SQLdb Tutorial1/ja: First part of the DB tutorial series, showing how to set up a grid that shows database data
• SQLdb Tutorial2/ja: Second part of the DB tutorial series, showing editing, inserting etc.
• SQLdb Tutorial4: Fourth part of the DB tutorial series, showing how to use data modules
• Lazarus Database Overview/ja: Information about the databases that Lazarus supports. Links to database-specific notes.
• SQLdb Package: information about the SQLdb package
• SQLdb Programming Reference: an overview of the interaction of the SQLdb database components
• using parameters
• SqlDBHowto/ja: information about using the SQLdb package
• Working With TSQLQuery/ja: information about TSQLQuery