10.ZeosDBO与FireBird embedded综合示例

对于 FireBird embedded 开发时，因其需要一些 FireBird embedded 版本的 dll 及相关文件，我们需要在项目创建后做一些工作，具体可参照下面本节中 10.1 的步骤来完成。

10.1 项目准备

1.首先创建一个应用程序项目

2.将 FireBird embedded 版本的文件复制到应用程序项目所在目录，具体文件包括：

fbembed.dll
firebird.msg
ib_util.dll
icudt30.dll
icuin30.dll
icuuc30.dll
IDPLicense.txt
IPLicense.txt
Microsoft.VC80.CRT.manifest
msvcp80.dll
msvcr80.dll

3.将同版本号的 FireBird C/S 版本的 isql.exe 文件复制到Lazarus 应用所在目录

4.复制 fbembed.dll 文件并重命名为 fbclient.dll

5.在命令提示符下执行如下命令：

> cd <Lazarus 应用所在目录>
> isql
Use CONNECT or CREATE DATABASE to specify a database
SQL>

6.创建数据库：

SQL> create database 'testdb.fdb' user 'SYSDBA' password 'admin' page_size 16384 default character set UTF8;
SQL>

7.退出 isql

SQL> exit;

10.2 数据准备

进入 isql，并连接数据库

> isql
Use CONNECT or CREATE DATABASE to specify a database
SQL> connect testdb.fdb user 'SYSDBA' password 'admin';
SQL>

在 isql 下执行创建表的脚本：

create table c_dep (
    dep_id varchar(64) primary key,
    dep_name varchar(16)
);

create table d_dep_staff (
    staff_id varchar(64) primary key,
    dep_id varchar(64),
    name varchar(32),
    sex varchar(8),
    birthday date,
    edu_level varchar(16),
    school varchar(64),
    speciality varchar(32),
    native_place varchar(256)
);

-- 由于演示应用程序不涉及部门表的数据操作，所以事先在数据表上插入几条数据
insert into c_dep (dep_id, dep_name)
values ('yfb', 'YanFaBU');
insert into c_dep (dep_id, dep_name)
values ('xsb', 'XiaoShouBu');
insert into c_dep (dep_id, dep_name)
values ('jszcb', 'JiShuZhiChiBu');
insert into c_dep (dep_id, dep_name)
values ('cwb', 'CaiWuBu');
insert into c_dep (dep_id, dep_name)
values ('xzb', 'XingZhengBu');
-- 之所以使用英文字母而不使用汉字是因为 isql 在命令提示符下使用汉字不识别

10.3 数据模块

在 Lazarus 环境下新建一个数据模块，并拖放 TZConnection、TZReadOnlyQuery 组件到数据模块中，设置组件属性如下：

• ZConnection1 的属性设置：

【备注】必须设置 LibraryLocation 属性为 fbembed.dll 文件。

• ZReadOnlyQuery 1 的属性设置：

数据模块声明代码：

TDataModule1 = class(TDataModule)
    // 数据库组件
    ZConnection1: TZConnection;
    ZReadOnlyQuery1: TZReadOnlyQuery;
    // 字段声明
    ZReadOnlyQuery1_BIRTHDAY: TDateField;
    ZReadOnlyQuery1_DEP_ID: TStringField;
    ZReadOnlyQuery1_DEP_NAME: TStringField;
    ZReadOnlyQuery1_EDU_LEVEL: TStringField;
    ZReadOnlyQuery1_NAME: TStringField;
    ZReadOnlyQuery1_NATIVE_PLACE: TStringField;
    ZReadOnlyQuery1_SCHOOL: TStringField;
    ZReadOnlyQuery1_SEX: TStringField;
    ZReadOnlyQuery1_SPECIALITY: TStringField;
    ZReadOnlyQuery1_STAFF_ID: TStringField;
  private

  public

  end;             

10.4 主窗体组件

主窗体组件设计如下图：

数据组件及其属性设置：

• DataSource1

• DBGrid1

• DBNavigator1

主窗体组件声明代码：

TForm1 = class(TForm)
    // 按钮
    Button1: TButton;
    Button2: TButton;
    Button3: TButton;
    Button4: TButton;
    // 数据组件
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    // 按钮及数据导航组件的容器
    Panel1: TPanel;
    
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure Button4Click(Sender: TObject);
    procedure DBGrid1DblClick(Sender: TObject);
  private

  public

  end;        

10.5 表单窗体组件

表单窗体设计如下图所示：

表单窗体声明代码：

TForm2 = class(TForm)
    // 确定和取消按钮
    Button1: TButton;
    Button2: TButton;
    // 数据控件
    BirthdayDateTimePicker: TDateTimePicker;
    NativePlaceEdit: TEdit;
    SpecialityEdit: TEdit;
    SchoolEdit: TEdit;
    EduLevelComboBox: TComboBox;
    DepComboBox: TComboBox;
    NameEdit: TEdit;
    SexRadioGroup: TRadioGroup;
    // 标签
    GroupBox1: TGroupBox;
    GroupBox2: TGroupBox;
    GroupBox3: TGroupBox;
    GroupBox4: TGroupBox;
    GroupBox5: TGroupBox;
    GroupBox6: TGroupBox;
    GroupBox7: TGroupBox;
    
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormActivate(Sender: TObject);
  private

  public
    isNew: Boolean;  // 标识是否为新增数据记录

  end;    

10.6 功能实现

• 主窗体代码：

uses unit2, unit3;

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  // 新建
  Form2.isNew := True;
  if Form2.ShowModal = mrOk then
  begin
    DataModule1.ZReadOnlyQuery1.Refresh;
  end;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  // 编辑
  if DataModule1.ZReadOnlyQuery1.BOF then
    if DataModule1.ZReadOnlyQuery1.RecordCount > 0 then
      DataModule1.ZReadOnlyQuery1.First
    else
      Exit;

  if DataModule1.ZReadOnlyQuery1.EOF then
    if DataModule1.ZReadOnlyQuery1.RecordCount > 0 then
      DataModule1.ZReadOnlyQuery1.Last
    else
      Exit;

  Form2.isNew := False;
  if Form2.ShowModal = mrOk then
  begin
    DataModule1.ZReadOnlyQuery1.Refresh;
  end;
end;

procedure TForm1.Button3Click(Sender: TObject);
var
  Query: TZQuery;
begin
  // 删除
  if DataModule1.ZReadOnlyQuery1.BOF then
    if DataModule1.ZReadOnlyQuery1.RecordCount > 0 then
      DataModule1.ZReadOnlyQuery1.First
    else
      Exit;

  if DataModule1.ZReadOnlyQuery1.EOF then
    if DataModule1.ZReadOnlyQuery1.RecordCount > 0 then
      DataModule1.ZReadOnlyQuery1.Last
    else
      Exit;

  Query := TZQuery.Create(Self);
  try
    Query.Connection := DataModule1.ZConnection1;

    Query.Close;

    Query.SQL.Text := 'DELETE FROM d_dep_staff where staff_id = :staffId';
    Query.Params.ParamByName('staffId').AsString := DataModule1.ZReadOnlyQuery1_STAFF_ID.Value;

    Query.ExecSQL;
  except
      on D: EDatabaseError do
         MessageDlg('Error', 'A database error has occurred. Technical error message: ' + D.Message, mtError, [mbOK], 0);
  end;

  Query.Destroy;

  DataModule1.ZReadOnlyQuery1.Refresh;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  DataModule1.ZReadOnlyQuery1.Refresh;
end;

procedure TForm1.DBGrid1DblClick(Sender: TObject);
begin
  // 双击修改
  Button2Click(Sender);
end;   

• 表单窗体代码：

uses unit2;

procedure TForm2.FormActivate(Sender: TObject);
var
  Query: TZQuery;
  index: Integer;
begin
  // 初始化部门选项
  try
    Query := TZQuery.Create(Self);
    Query.Connection := DataModule1.ZConnection1;

    Query.Close;
    Query.SQL.Text := 'SELECT dep_id, dep_name FROM c_dep';
    Query.Open;

    DepComboBox.Items.Clear;
    while not Query.EOF do
    begin
      DepComboBox.Items.AddObject(
        Query.FieldByName('dep_name').AsString,
        TObject(NewStr(Query.FieldByName('dep_id').AsString))
      );
      Query.Next;
    end;
  except
      on D: EDatabaseError do
         MessageDlg('Error', 'A database error has occurred. Technical error message: ' + D.Message, mtError, [mbOK], 0);
  end;

  // 初始化数据项
  if isNew then
  begin
    NameEdit.Text:='';
    SexRadioGroup.ItemIndex:=0;
    SchoolEdit.Text:='';
    SpecialityEdit.Text:='';
    NativePlaceEdit.Text:='';
  end
  else
  with DataModule1 do
  begin
    NameEdit.Text:=ZReadOnlyQuery1_NAME.Value;
    if ZReadOnlyQuery1_SEX.Value = '男' then
      SexRadioGroup.ItemIndex:=0
    else
      SexRadioGroup.ItemIndex:=1;

    index := DepComboBox.Items.IndexOf(ZReadOnlyQuery1_DEP_NAME.Value);
    DepComboBox.ItemIndex:=index;

    BirthdayDateTimePicker.Date := ZReadOnlyQuery1_BIRTHDAY.Value;
    EduLevelComboBox.Text := ZReadOnlyQuery1_EDU_LEVEL.Value;
    SchoolEdit.Text := ZReadOnlyQuery1_SCHOOL.Value;
    SpecialityEdit.Text:=ZReadOnlyQuery1_SPECIALITY.Value;
    NativePlaceEdit.Text:= ZReadOnlyQuery1_NATIVE_PLACE.Value;
  end;
end;

procedure TForm2.Button1Click(Sender: TObject);
begin
  // 取消
  ModalResult := mrCancel;
end;

procedure TForm2.Button2Click(Sender: TObject);
var
  Query: TZQuery;
  index: Integer;
  depId: String;
begin
  // 确定
  if NameEdit.Text = '' then
  begin
    MessageDlg('提示', '请输入姓名！', mtError, [mbOK], 0);
    Exit;
  end;

  if DepComboBox.ItemIndex < 0 then
  begin
    MessageDlg('提示', '请选择所在部门！', mtError, [mbOK], 0);
    Exit;
  end;

  index := DepComboBox.ItemIndex;
  depId := PAnsiString(DepComboBox.Items.Objects[index])^;

  Query := TZQuery.Create(Self);
  try
    Query.Connection := DataModule1.ZConnection1;
    Query.Close;

    if isNew then
    begin
      Query.SQL.Text := 'INSERT INTO d_dep_staff (staff_id, dep_id, name, sex, birthday, edu_level, school, speciality, native_place) VALUES(uuid_to_char(gen_uuid()), :depId, :name, :sex, :birthday, :eduLevel, :school, :speciality, :nativePlace)';

      Query.Params.ParamByName('depId').AsString := depId;
      Query.Params.ParamByName('name').AsString := NameEdit.Text;
      if SexRadioGroup.ItemIndex = 0 then
        Query.Params.ParamByName('sex').AsString := '男'
      else
        Query.Params.ParamByName('sex').AsString := '女';
      Query.Params.ParamByName('birthday').AsDate := BirthdayDateTimePicker.Date;
      Query.Params.ParamByName('eduLevel').AsString := EduLevelComboBox.Text;
      Query.Params.ParamByName('school').AsString := SchoolEdit.Text;
      Query.Params.ParamByName('speciality').AsString := SpecialityEdit.Text;
      Query.Params.ParamByName('nativePlace').AsString := NativePlaceEdit.Text;

      Query.ExecSQL;
    end
    else
    begin
      Query.SQL.Text := 'UPDATE d_dep_staff SET dep_id=:depId, name=:name, sex=:sex, birthday=:birthday, edu_level=:eduLevel, school=:school, speciality=:speciality, native_place=:nativePlace WHERE staff_id=:staffId';

      Query.Params.ParamByName('depId').AsString := depId;
      Query.Params.ParamByName('name').AsString := NameEdit.Text;
      if SexRadioGroup.ItemIndex = 0 then
        Query.Params.ParamByName('sex').AsString := '男'
      else
        Query.Params.ParamByName('sex').AsString := '女';
      Query.Params.ParamByName('birthday').AsDate := BirthdayDateTimePicker.Date;
      Query.Params.ParamByName('eduLevel').AsString := EduLevelComboBox.Text;
      Query.Params.ParamByName('school').AsString := SchoolEdit.Text;
      Query.Params.ParamByName('speciality').AsString := SpecialityEdit.Text;
      Query.Params.ParamByName('nativePlace').AsString := NativePlaceEdit.Text;

      Query.Params.ParamByName('staffId').AsString := DataModule1.ZReadOnlyQuery1_STAFF_ID.Value;

      Query.ExecSQL;
    end;
  except
      on D: EDatabaseError do
         MessageDlg('Error', 'A database error has occurred. Technical error message: ' + D.Message, mtError, [mbOK], 0);
  end;

  Query.Destroy;
  ModalResult := mrOk;
end;     

F9 运行程序，执行效果如下：