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 ライブラリを使用)

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

Getting database data into normal controls

  Note: Before starting this section, please make sure you have set up the sample employee database as specified in SQLdb Tutorial0#Requirements

In previous tutorials, data-bound controls were covered: special controls such as the TDBGrid that can bind its contents to a TDataSource, get updates from that source and send user edits back.

It is also possible to programmatically retrieve database content and fill any kind of control (or variable) with that content. As an example, we will look at filling a stringgrid with some salary details for the sample employee database table.

On the main form, let's add a TStringGrid and retrieve the data (e.g. via a procedure LoadSalaryGrid called in the OnCreate event):

    // Load from DB
    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;

      // Lowest salary
      // Note: we would like to only retrieve 1 row, but unfortunately the SQL
      // used differs for various dbs. As we'll deal with db dependent SQL later
      // in the tutorial, we leave this for now.
      // 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: add
        //'rows 1 '; here and below... won't work on e.g. PostgreSQL though
      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;
      // Always commit(retain) an opened transaction, even if only reading
      // this will allow updates by others to be seen when reading again
      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;

Things to note: we catch database errors using try..except. You'll notice we forgot to roll back the transaction in case of errors - which is left as an exercise to the reader.

We Open the query object, thereby asking FQuery to query the database via its SQL statement. Once this is done, we're on the first row of data. We simply assume there is data now; this is actually a programming error: it would be tidier to check for FQuery.EOF being true (or FQuery.RecordCount being >0).

Next, we retrieve the data from the first row of results. If we wanted to move to the next row, we'd use FQuery.Next, but that is not necessary here. We put the results in the stringgrid, giving the lowest salary in the list. A similar approach can be taken for the highest salary.

Adapting SQL for various databases

As we noticed above, various databases support various versions of SQL (either in addition to or in contradiction to the official ISO SQL standards). Fortunately, you can customize your application based on which DB it ends up using, which will be demonstrated by getting the standard deviation of the employees' salaries - built into e.g. PostgreSQL SQL but not available by default in e.g. Firebird.

In our LoadSalaryGrid procedure, we'll use the SQL for PostgreSQL and build a code solution for all other databases. First detect which database is loaded, below the other lines add:

  ...
  SalaryGrid.Cells[3,2]:=FQuery.Fields[2].AsString;
  FQuery.Close;
  // Always commit(retain) an opened transaction, even if only reading
  FTran.Commit;
//end of existing code

  if FConn.ConnectorType = 'PostGreSQL' then
  begin
    // For PostgreSQL, use a native SQL solution:
    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;
    // Always commit(retain) an opened transaction, even if only reading
    FTran.Commit;
  end
  else
  begin
    // For other database, use the code approach:
    ....see below...
  end;

Notice the use of ConnectorType; the string used must match exactly. We also properly check for empty results from the query (which might happen if the employee table is empty).

... now let's implement a code-based solution for other databases that do not support standard deviation:

  // For other databases, use the code approach:
  // 1. Get average of values
  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. For each value, calculate the square of (value-average), and add it up
    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. Now calculate the average "squared difference" and take the square root
    if Count>0 then //avoid division by 0
      SalaryGrid.Cells[3,3]:=FloatToStr(Sqrt(DifferencesSquared/Count))
    else
      SalaryGrid.Cells[3,3]:='No data'; 
  end;
  FQuery.Close;

Note that we use FQuery.EOF to check for empty data (and avoid division by zero errors etc). The loop shows how to:

• retrieve a database value into a variable
• use FQuery.Next to move to the next record
• properly check if the query dataset has hit the last record, then stop retrieving data.

The resulting screen should show something like this - note the use of a decimal comma - while your computer may show a decimal point depending on your locale:

Revisiting our lowest/highest salary

This section gives some more useful details on SQL but is not required to work through for the rest of the tutorial

Now we know how to deal with detecting various database connections, we can adjust the SQL that gets the lowest and highest salary as well to make use of db specific functionality.

An example: this would work for MS SQL Server by limiting the number of returned rows to just the first:

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

to get the lowest salary.

This efficiently returns one record. Other databases use other syntax, such as the ISO ROWS 1. The diligent SQL student will soon learn not to miss out that important part and request entire large recordsets just for one required record!

Let's briefly examine other ways to achieve the same thing, that are worth knowing.

Another way to retrieve the record(s) with the minimum salary would be :

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

SQL students would greatly benefit from researching Common Table Expressions.

A CTE allows a virtual temporary table to be used in a following expression, allowing you to clearly code some very complex queries that otherwise may not be possible. Knowing about CTEs will catapult you ahead of colleagues who have never heard of them! For example the above may be rewritten (example in Microsoft SQL Server syntax) as :

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)

Several such temporary tables may be chained together, each using the results from the previous tables. You can treat these virtual tables as though they were real tables in the database, using JOINs to link recordsets together. And it can be very useful for quick tests using hardcoded data - this can be run without any database connection :

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)

You can end up with quite long strings for the code of such SQL queries, but it is only one query and may be called from anywhere where you are limited to a simple single expression - it can be useful to answer complex queries without resorting to functions or stored procedures.

Getting data out of normal controls into the database

Previously, we have seen:

• how to let SQLDB update the database with data-bound controls (earlier tutorials)
• how to get data out of the database using queries (the section above)

You can also execute SQL to get arbitrary data back into the database via code. This allows you to use variables or controls that have no db aware equivalent such as sliders or custom controls to enter data into the database, at the expense of a bit more coding.

As an example, we are going to allow the user to change the lowest and highest salary in the stringgrid.

For ease of editing, set the grid's Options/goEditing to true; then assign the procedure below to the OnValidate event for the grid, which will be called every time a user has finished updating the grid.

Parameterized queries

The following code also demonstrates how to use parameterized queries to avoid SQL injection, fiddling with quoting for string values, date formatting, etc.

As you can see in the code, you can name your parameters whatever you wish and prefix them with : in the SQL. In code, you can set/get their values by <somequery>.Params.ParamByName('<thename>').As'<variabletype>'; the code demonstrates .AsFloat and .AsString.

Parameterized queries are especially useful (and can be much faster) if you run the same query, only with different parameters, in a loop (think e.g. bulk loading of data).

Continuing with our example: after having set up the query SQL and parameters, the transaction is started (and later on committed) as usual, then the query is run by calling ExecSQL (which does not return a result set; if the SQL statement were e.g. a SELECT or INSERT...RETURNING that does return data, you would use Open as in the examples above):

procedure TForm1.SalaryGridValidateEntry(sender: TObject; aCol, aRow: Integer;
  const OldValue: string; var NewValue: String);
begin
  // Only these cells have min and max salary:
  if (aCol=3) and ((aRow=1) or (aRow=2)) then
  begin
    // Allow updates to min and max salary if positive numerical data is entered
    if StrToFloatDef(NewValue,-1)>0 then
    begin
      // Storing the primary key in e.g. a hidden cell in the grid and using that in our
      // update query would be cleaner, but we can do it the hard way as well:
      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; //reload standard deviation
    end
    else
    begin
      // Notify user that his input was wrong... he'll be wondering otherwise:
      Showmessage('Invalid salary entered.');
      NewValue:=OldValue;
    end;
  end
  else
  begin
    // Silently discard edits to any other cells
    NewValue:=OldValue;
  end;
end;

Note how we forgot to add a try..except block to this code to nicely catch database errors and display a sensible error message. If you are running the Firebird sample EMPLOYEE database for this tutorial, try to change the salary to a very low value (say 1) and see what happens.

Finally, while this example showed an UPDATE SQL query, you could just as well run INSERT queries to insert new data programmatically. Also, you can use parameters in any kind of SQL query (SELECT, UPDATE, etc) as long as you use them for fields, not for table/view/procedure names.

Summary

This tutorial explained:

• how to code for multiple database types
• how to use a login form to decouple db access configuration from your program
• how to retrieve and update data programmatically

Code

Since November 2012, the code can be found in $(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: information about using the SQLdb package
• Working With TSQLQuery/ja: information about TSQLQuery