SQLdb Tutorial2/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

概要

If you have followed SQLdb Tutorial1, you have a basic grid showing database information. While this application works, we can add some refinements on this.

動的データベース接続

これまでは、わかりやすくするために、固定のデータベース サーバー名、データベースの場所、ユーザー名、パスワードを使用してきた。 前述したように、「実際の」アプリケーションでは通常、ユーザーが独自のユーザー名とパスワードを指定できる。

それらを指定できるようにフォームを変更しよう。標準メニューから 2 つの TEdit を追加します。 名前のプロパティを「ユーザー名」と「パスワード」に設定する。 肩越しに覗き見されることに対するセキュリティを確保するには、パスワードの PasswordChar プロパティを * (アスタリスク) に設定する。

接続を簡単にしたい場合 (もちろん安全性は低くなるが)、UserName Text プロパティを SYSDBA などの有効なデータベース ユーザーに設定できる。 パスワード テキスト プロパティをマスターキーのようなデフォルト値に設定することもでき、セキュリティが重要でない場合は開発者のマシンで簡単にテストできるが...

見た目上、ラベルを追加して何を入力すればよいのかわかるようにすると便利だ。

また、Firebird/Interbase サーバー上の従業員サンプル データベースに簡単に接続できるように、サーバー名とデータベース パス用の 2 つのテキスト ボックスを追加する。 さらに 2 つの TEdit を追加し、ServerName と DatabaseName という名前を付ける。

必要に応じて、「Text」プロパティを状況に応じたデフォルトの適切な値に設定できる。 localhost と C:\Program Files\Firebird\Firebird_2_5\examples\empbuild\EMPLOYEE.FDB

ユーザーが入力する必要がある内容を説明するラベルもここで役に立つ。

明確にするために、設計時コンポーネントから接続情報を削除する。TSQLConnector コンポーネントで、UserName、Password、DatabaseName、および HostName プロパティからすべてのテキストを削除する。

さて、最後に、データベース接続コンポーネントに接続方法を指示する必要がある。 これは通常、アプリケーションの実行の開始時にのみ必要になる。 この例では、既存の「Button1」コードが接続をセットアップする良い方法である。

以下が得られるまでコードを追加。

procedure TForm1.Button1Click(Sender: TObject);
begin
   SQLQuery1.Close;
   //Firebird/Interbaseデータベースの接続設定
   // まだ接続していない場合にのみ必要:
   if not DBConnection.Connected then
   begin
     DBConnection.HostName := ServerName.Text;
     DBConnection.データベース名 := データベース名.テキスト;
     DBConnection.DatabaseName := DatabaseName.Text;
     DBConnection.Password := Password.Text;
     // これで接続が設定された。それを視覚的に示す。
     // 変更はもう行われない可能性がある
    ServerName.ReadOnly:=true;
    DatabaseName.ReadOnly:=true;
    UserName.ReadOnly:=true;
    Password.ReadOnly:=true;
   end;
   SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
   DBConnection.Connected:= True;
   SQLTransaction1.Active:= True;
   SQLQuery1.Open;
end;

次に、実行して接続できるかどうかをテストする。

SQLite、その他のデータベース

必要に応じて、例えば SQLite の employee.sqlite、DatabaseName TEdit の Text、 プロパティを調整する。 。

sqlite の場合、HostName、Username、および Password の指定は意味がないため、これらの TEdit を省略できる。 明らかに、上記のコードでは、対応する値を DBConnection に割り当てるのを省略またはコメントアウトする。 Firebird が埋め込まれている場合は、ユーザー名を SYSDBA にハードコーディングする。 sqlite を使用するときにはこれを指定しても問題ない。

コードは次のようになる:

procedure TForm1.Button1Click(Sender: TObject);
begin
   SQLQuery1.Close;
   //組み込みデータベースの接続設定
   // まだ接続していない場合にのみ必要:
   if not DBConnection.Connected then
   begin
      DBConnection.DatabaseName := DatabaseName.Text;
      DBConnection.UserName := 'SYSDBA';  //Firebird 埋め込みにはこれが必要。 SQLiteを使用する場合は問題ない
     // これで接続が設定された。それを視覚的に示す。
     // 変更はもう行われない可能性がある
      DatabaseName.ReadOnly:=true;
   end;
   SQLQuery1.SQL.Text:= 'select * from CUSTOMER';
   DBConnection.Connected:= True;
   SQLTransaction1.Active:= True;
   SQLQuery1.Open;
end;

データのフィルタリング

多くの場合、テーブルにはユーザーが見たくない大量のデータが含まれている (データベースからクエリを実行し、ネットワーク上を移動するには時間がかかる可能性がある)。米国からの顧客のみを表示する必要があると仮定する。 したがって、「SQLQuery1」の SQL 命令は次のようになる。

select * from CUSTOMER where COUNTRY = 'USA'

...これは、コードでは次のように変換される。

SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = 'USA'';

このサンプル アプリケーションでこの命令を使用しない理由は 2 つある。

まず、単一引用符の使用法に問題がある。 コンパイラーは USA の前の引用符を終了引用符 (最初の引用符は select from... の前にある) として解釈するため、SQL 命令は無効になる。 解決策: 内側の引用符を 2 倍にする。

SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = ''USA''';

2 番目の、より重要な理由は、ユーザーがどのような制約でフィルターをかけたいのかがおそらくわからないという事実だ。 ユーザーの柔軟性を制限したくはない。

この柔軟性を実現するには、まず SQL クエリ ステートメントを変更し、「USA」をプレースホルダー (SQL のパラメータ) に置き換える。Button1click プロシージャを変更して次のように置き換える。

SQLQuery1.SQL.Text := 'select * from CUSTOMER';

と：

SQLQuery1.SQL.Text:= 'select * from CUSTOMER where COUNTRY = :COUNTRY';

FPC SQLDB では、SQL パラメーターは先頭のコロンでマークされる (他の言語/環境では ? などの他の規則が使用されます)。 ユーザーがフィルターの値を入力できるようにするために、フォームに TEdit コンポーネントを配置する。 「Text」プロパティの値を削除する。

これで、TEdit に入力されたテキストを取得し、TSQLQuery の 'Params' プロパティを使用して SQL COUNTRY パラメータを入力できるようになった。 これを前のステートメントの下に追加する。

SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;

パラメータは位置または名前で指定できます。 名前を使用すると、ソース コードの読みやすさが向上し、既存のパラメーターの途中にさらにパラメーターを挿入する場合に明らかに役立つ。

.AsString を使用して文字列値をパラメータに割り当てる。 整数パラメータ、ブール値パラメータなどに対して同等のプロパティ割り当てがある。

これまでのコードでは、フィルターの使用を強制されている。 ユーザーが編集ボックスに空の値を指定した場合、レコードは表示されない。 おそらくこれは私たちが望んでいることではない。 空の値をテストし、それに応じてクエリを作成しよう。 最終的には次のような手順になるはずだ。

procedure TForm1.Button1Click(Sender: TObject);
begin
   SQLQuery1.Close;
   // Firebird/Interbaseデータベースの接続設定
   // まだ接続していない場合にのみ必要:
  if not DBConnection.Connected then
   begin
    DBConnection.HostName := ServerName.Text;
    DBConnection.DatabaseName := DatabaseName.Text;
    DBConnection.Username := UserName.Text;
    DBConnection.Password := Password.Text;
     // これで接続が設定されました。それを視覚的に示します。
     // 変更はもう行われない可能性があります
    ServerName.ReadOnly:=true;
    DatabaseName.ReadOnly:=true;
    UserName.ReadOnly:=true;
    Password.ReadOnly:=true;
   end;
     // すべてのレコードを表示するか、ユーザーがフィルター基準を指定した場合はフィルターする
    if Edit1.Text='' then
     SQLQuery1.SQL.Text :=  'select * from CUSTOMER where COUNTRY = :COUNTRY';
    else
     begin
      SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
      SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
    end;
   DBConnection.Connected:= True;
   SQLTransaction1.Active:= True;
   SQLQuery1.Open;
end;

ここで、Edit1 を使用してフィルタリングを少し試す。 データベースに存在しない国を入力すると、空のグリッドが表示される。

Error handling

The application should run, but sometimes problems can occur. Databases, even embedded databases can crash (e.g. when the database server crashes, the disk is full, or just due to a bug), leaving the application hanging.

Access to a database (any external process, really) should therefore always be integrated in a try ... except and/or try ... finally construct. This ensures that database errors are handled and the user isn't left out in the cold. A rudimentary routine for our example application could look like this:

begin
  try
    SQLQuery1.Close;
    ...
    SQLQuery1.Open;
  except
    //We could use EDatabaseError which is a general database error, but we're dealing with Firebird/Interbase, so:
    on E: EDatabaseError do
    begin
      MessageDlg('Error','A database error has occurred. Technical error message: ' + E.Message,mtError,[mbOK],0);
      Edit1.Text:='';
    end;
  end;
end;

SQLite, PostgreSQL, other databases

You can either use the more generic EDatabaseError, or - if available - your own specialized databaseerror, if you need more details. E.g. SQLite and the PostgreSQL driver in FPC 2.6.1 and lower doesn't have a specialized E*DatabaseError; you'd have to use EDatabaseError. PostgreSQL on FPC trunk (development version) has EPQDatabaseError.

Editing data using the grid

Editing

Up to now, if you tried to edit data in the grid, the changes would not be saved. This is because the SQLQuery1 is not instructed to send the changes to the database transaction at the right moment. We need to fix this, and then commit the transaction in the database, so all changes get written. For this, you would use code like this:

SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
SQLTransaction1.Commit; //... and commit them using the transaction.
//SQLTransaction1.Active now is false

We want to make sure any edits (inserts, updates, deletes) are written to the database:

• when the users changes the filtering criteria and presses the button to query the database
• when the form is closed

It makes sense to make a separate procedure for this that is called in those two instances. Go to the code, and add an empty line here:

  TForm1 = class(TForm)
    Button1: TButton;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    Edit1: TEdit;
    DBConnection: TIBConnection;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
*****insert the empty line here****
    procedure Button1click(Sender: TObject);
    procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
  private

then type:

    procedure SaveChanges;

press shift-ctrl-c (default combination) to let code completion automatically create the corresponding procedure body.

We need to add error handling and check that the transaction is active - remember, this code also gets called when pressing the button the first time, when the transaction is not active yet. We get:

procedure Tform1.SaveChanges;
// Saves edits done by user, if any.
begin
  try
    if SQLTransaction1.Active then
    // Only if we are within a started transaction;
    // otherwise you get "Operation cannot be performed on an inactive dataset"
    begin
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
      SQLTransaction1.Commit; //... and commit them using the transaction.
      //SQLTransaction1.Active now is false
    end;
  except
  on E: EDatabaseError do
    begin
      SQLTransaction1.Rollback;
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
        E.Message, mtError, [mbOK], 0);
      Edit1.Text := '';
    end;
  end;
end;

Now we need to call this procedure at the appropriate moments:

procedure Tform1.Button1click(Sender: TObject);
begin
  SaveChanges; //Saves changes and commits transaction
  try
    SQLQuery1.Close;
....

and:

procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
begin
  SaveChanges; //Saves changes and commits transaction
  SQLQuery1.Close;
....

Now test and see if edits made in the dbgrid are saved to the database.

Hiding primary key column

Often, you don't want your users to see autonumber/generated primary keys as they are only meant to maintain referential integrity. If users do see them, they might want to try the edit the numbers, get upset that the numbers change, that there are gaps in the numbers, etc.

In our example, CUST_NO is the primary key, with content auto-generated by Firebird using triggers and a sequence/generator. This means that you can insert a new record without specifying the CUST_NO; Firebird will create one automatically.

We could simply change our SQLQuery1.SQL.Text property to not include CUST_NO, but this would lead to problems when editing data - a primary key is needed in those circumstances for uniquely identifying the row/record in question.

Therefore, let's use a trick to query for all columns/fields in the table, but keep the grid from showing the first field, CUST_NO: in the Button1Click procedure, add code so it looks like:

procedure Tform1.Button1click(Sender: TObject);
begin
...
    SQLQuery1.Open;
    // Hide the primary key column which is the first column in our queries.
    // We can only do this once the DBGrid has created the columns
    DBGrid1.Columns[0].Visible:=false;

Recompile, and check to see if the primary key column is really hidden.

SQLite, other databases

• Other databases: a lot of other databases use an 'autonumber' or 'autoinc' type of field to provide auto-generated field content. Try changing your table definition and see if it works.
• Sqlite: the example above works for SQLite as is because we're using an integer primary key. See the documentation for details.

Inserting new data

If you insert new rows/records without any CUST_NO information you may have noticed that you get an error message: Field CUST_NO is required, but not supplied. This also happens if you hid the CUST_NO column, as in the previous section.

The reason: Lazarus thinks that CUST_NO is required. That's not so strange, because it is a primary key and the underlying table definition in the database does say it is required.

If we can instruct Lazarus that this field is not actually required, we can pass empty values (=NULL values) to the database. Fortunately, a query's field object has a Required property that does exactly that.

Change the code to something like:

    SQLQuery1.Open;
    {
    Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, e.g.:
    Field CUST_NO is required, but not supplied
    We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
    when inserting new records.
    }
    SQLQuery1.FieldByName('CUST_NO').Required:=false;
    // Hide the primary key column which is the first column in our queries.
    // We can only do this once the DBGrid has created the columns
    DBGrid1.Columns[0].Visible:=false;

Deleting data

You can let your users use the mouse to do this. You don't even need to code a single line for this functionality...

On the 'Data Controls' tab, select a TDBNavigator component and drop it on the form, above the grid.

To indicate what the navigator should be linked to, set its DataSource property to your existing datasource ('DataSource1') using the Object Inspector. Now you can use the button on the DBNavigator to delete records, but also insert them, and move around the records. Also, when editing cells/fields, you can use the Cancel button to cancel your edits.

To allow users to delete the row they're in on the grid using the Delete key, add LCLType (this contains definitions for key codes) to your uses clause:

uses
  Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
  Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;

... then handle the KeyUp event for the grid, which occurs when a key is released if in the grid. However, we do need to check that the user is not editing a field - as he'll probably use the Delete key to delete letters rather than the record he's working on.

Select the grid, then go to events and create an OnKeyUp event like this:

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState
  );
begin
  // Check for del key being hit and delete the current record in response
  // as long as we're not editing data
  if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
  begin
    //... delete current record and apply updates to db:
    SQLQuery1.Delete;
    SQLQuery1.ApplyUpdates;
  end;
end;

  Note: By default TDBGrid property Options / dgDisableDelete is set to false, this means a user can delete any record with the ctrl-delete key combo. You may not want this behaviour.

Summary

If you followed along up to now, you can retrieve data from the database, filter it, and edit and delete data in the grid. Your code should look something like this:

unit sqldbtutorial1unit;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, sqldb, pqconnection, DB, FileUtil, Forms,
  Controls, Graphics, Dialogs, DBGrids, StdCtrls, DbCtrls, LCLType;
type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    DatabaseName: TEdit;
    Datasource1: TDatasource;
    DBGrid1: TDBGrid;
    Dbnavigator1: Tdbnavigator;
    Edit1: TEdit;
    Label2: Tlabel;
    Label3: Tlabel;
    Label4: Tlabel;
    Label5: Tlabel;
    Password: TEdit;
    UserName: TEdit;
    ServerName: TEdit;
    DBConnection: TIBConnection;
    Label1: TLabel;
    SQLQuery1: TSQLQuery;
    SQLTransaction1: TSQLTransaction;
    procedure SaveChanges;
    procedure Button1click(Sender: TObject);
    procedure Formclose(Sender: TObject; var Closeaction: Tcloseaction);
  private
    { private declarations }
  public
    { public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.lfm}

{ TForm1 }

procedure Tform1.Savechanges;
// Saves edits done by user, if any.
begin
  try
    if SQLTransaction1.Active then
    // Only if we are within a started transaction
    // otherwise you get "Operation cannot be performed on an inactive dataset"
    begin
      SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
      SQLTransaction1.Commit; //... and commit them using the transaction.
      //SQLTransaction1.Active now is false
    end;
  except
  on E: EDatabaseError do
    begin
      SQLTransaction1.Rollback;
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
        E.Message, mtError, [mbOK], 0);
      Edit1.Text := '';
    end;
  end;
end;

procedure TForm1.DBGrid1KeyUp(Sender: TObject; var Key: Word; Shift: TShiftState
  );
begin
  // Check for del key being hit and delete the current record in response
  // as long as we're not editing data
  if (key=VK_DELETE) and (not(DBGrid1.EditorMode)) then
  begin
    //... delete current record and apply updates to db:
    SQLQuery1.Delete;
    SQLQuery1.ApplyUpdates;
  end;
end; 

procedure Tform1.Button1click(Sender: TObject);
begin
  SaveChanges; //Saves changes and commits transaction
  try
    SQLQuery1.Close;
    //Connection settings for Firebird/Interbase database
    //only needed when we have not yet connected:
    if not DBConnection.Connected then
    begin
      DBConnection.HostName := ServerName.Text;
      DBConnection.DatabaseName := DatabaseName.Text;
      DBConnection.Username := UserName.Text;
      DBConnection.Password := Password.Text;
      // Now we've set up our connection, visually show that
      // changes are not possibly any more
      ServerName.ReadOnly:=true;
      DatabaseName.ReadOnly:=true;
      UserName.ReadOnly:=true;
      Password.ReadOnly:=true;
    end;
    // Show all records, or filter if user specified a filter criterium
    if Edit1.Text='' then
      SQLQuery1.SQL.Text := 'select * from CUSTOMER'
    else
    begin
      SQLQuery1.SQL.Text := 'select * from CUSTOMER where COUNTRY = :COUNTRY';
      SQLQuery1.Params.ParamByName('COUNTRY').AsString := Edit1.Text;
    end;
    DBConnection.Connected := True;
    SQLTransaction1.Active := True; //Starts a new transaction
    SQLQuery1.Open;
    {
    Make sure we don't get problems with inserting blank (=NULL) CUST_NO values, i.e. error message:
    "Field CUST_NO is required, but not supplied"
    We need to tell Lazarus that, while CUST_NO is a primary key, it is not required
    when inserting new records.
    }
    SQLQuery1.FieldByName('CUST_NO').Required:=false;
    {
    Hide the primary key column which is the first column in our queries.
    We can only do this once the DBGrid has created the columns
    }
    DBGrid1.Columns[0].Visible:=false;
  except
    // EDatabaseError is a general error; 
    // you could also use one for your specific db, e.g.
    // use EIBDatabaseError for Firebird/Interbase
    on E: EDatabaseError do
    begin
      MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
        E.Message, mtError, [mbOK], 0);
      Edit1.Text := '';
    end;
  end;
end;

procedure Tform1.Formclose(Sender: TObject; var Closeaction: Tcloseaction);
begin
  SaveChanges; //Saves changes and commits transaction
  SQLQuery1.Close;
  SQLTransaction1.Active := False;
  DBConnection.Connected := False;
end;

end.

Embedded database without code changes

Firebird on Windows

A bonus for Firebird users on Windows: if you have been following this tutorial (even if you only did the basic example), you renamed the fbembed.dll embedded Firebird library to fbclient.dll. With this, Lazarus could connect to regular Firebird servers (either on another machine or on your local machine). However, you can also copy the employee.fdb database to your application directory, run the application, clear the Server name TEdit and use Firebird embedded to directly connect to the database file, without any servers set up.

This is great if you want to deploy database applications to end users, but don't want the hassle of installing servers (checking if a server is already installed, if it's the right version, having users check firewalls, etc).

See Firebird embedded for more details.

September 2011: in recent development (SVN) versions of Free Pascal, FPC tries to first load fbembed.dll, so you need not rename fbclient.dll anymore for this to work.

Firebird on Linux/macOS/Unix

There must be a way to get this to work on Linux/macOS. See Firebird for hints and links. Updates to the wiki are welcome.

SQLite

SQLite certainly offers embedded functionality - it does not allow a client/server setup on the other hand. By following the tutorial above, you can see that switching between databases (e.g. SQLite and Firebird) is not so much work at all.

Other databases

Your database might offer similar functionality. Updates of this wiki for other database systems are welcome.

See also

• SQLdb Tutorial0: Instructions for setting up sample tables/sample data for the tutorial series.
• SQLdb Tutorial1: First part of the DB tutorial series, showing how to set up a grid that shows database data
• SQLdb Tutorial3: Third part of the DB tutorial series, showing how to program for multiple databases and use a login form
• SQLdb Tutorial4: Fourth part of the DB tutorial series, showing how to use data modules
• Lazarus Database Overview: 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
• SqlDBHowto: information about using the SQLdb package
• Working With TSQLQuery: information about TSQLQuery