9.ZeosDBO与FireBird综合示例

9.1关于 FireBird

Firebird是一个开源的关系数据库，提供了大部分SQL-99标准的功能，可以运行在Windows、linux和多种Unix操作系统上。Firebird提供良好的并发性，高效的执行，强大的语言支持存储过程和触发器。从1981年起，它已经被许多商业公司运用于许多的产品系统中。

Firebird 和原 Borland 的 InterBase 有着相同的血脉，其作为一个开源项目遵循 InterBase Public License V.1.0 on 25 July, 2000 协议。

FireBird 数据库的特点：

FireBird 数据库软件有三种套件，支持不同作业平台（windows系列、linux、freebsd 等…）

另外，新版的 FireBird 数据库软件还支持 Android ARM32、 Android ARM64、 Mac OS X。

9.2 FireBird 开发示例

本节示例：实现对一张数据表的 CRUD 操作，通过两个窗体，其中主窗体展现数据，表单窗体用于录入和修改数据。数据准备如下：

create table c_dep ( dep_id varchar(64) primary key, dep_name varchar(16));insert into c_dep (dep_id, dep_name)values (‘yfb’, ‘研发部’);insert into c_dep (dep_id, dep_name)values (‘xsb’, ‘销售部’);insert into c_dep (dep_id, dep_name)values (‘jszcb’, ‘技术支持部’);insert into c_dep (dep_id, dep_name)values (‘cwb’, ‘财务部’);insert into c_dep (dep_id, dep_name)values (‘xzb’, ‘行政部’);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 d_dep_staff(staff_id, dep_id, name, sex, birthday, edu_level, school, speciality, native_place)VALUES(uuid_to_char(gen_uuid()), ‘yfb’, ‘张三’, ‘女’, ‘1981-9-10’, ‘本科’, ‘内蒙古大学’, ‘经济管理’, ‘内蒙古乌兰察布市集宁区’);INSERT INTO d_dep_staff(staff_id, dep_id, name, sex, birthday, edu_level, school, speciality, native_place)VALUES(uuid_to_char(gen_uuid()), ‘xsb’, ‘刘五’, ‘男’, ‘1978-7-11’, ‘本科’, ‘内蒙古大学’, ‘计算机科学与技术’, ‘内蒙古鄂尔多斯市东胜区’);INSERT INTO d_dep_staff(staff_id, dep_id, name, sex, birthday, edu_level, school, speciality, native_place)VALUES(uuid_to_char(gen_uuid()), ‘xzb’, ‘弓九’, ‘女’, ‘1983-3-10’, ‘本科’, ‘内蒙古科技大学’, ‘计算机科学与技术’, ‘内蒙古呼和浩特市托克托县’);

在数据表设计上，我比较习惯于主键使用 UUID，这个习惯也是受近几年的分布式数据库的影响而形成的。在 FireBird 中，对于 UUID 在支持包括如下函数：

• CHAR_TO_UUID (ascii_uuid)
• GEN_UUID ()
• UUID_TO_CHAR (uuid)

如：

select char_to_uuid(‘A0bF4E45-3029-2a44-D493-4998c9b439A3’) from rdb$database– returns A0BF4E4530292A44D4934998C9B439A3 (16-byte string)select char_to_uuid(‘A0bF4E45-3029-2A44-X493-4998c9b439A3′) from rdb$database– error: -Human readable UUID argument for CHAR_TO_UUID must– have hex digit at position 20 instead of “X (ASCII 88)”select gen_uuid() from rdb$database– returns e.g. 017347BFE212B2479C00FA4323B36320 (16-byte string)select uuid_to_char(x’876C45F4569B320DBCB4735AC3509E5F’) from rdb$database– returns ‘876C45F4-569B-320D-BCB4-735AC3509E5F’select uuid_to_char(gen_uuid()) from rdb$database– returns e.g. ‘680D946B-45FF-DB4E-B103-BB5711529B86’select uuid_to_char(‘Firebird swings!’) from rdb$database– returns ‘46697265-6269-7264-2073-77696E677321’

9.3 Lazarus 应用程序示例

9.3.1 界面设计

主窗体界面如下图：

表单窗体界面如下图：

9.3.2 数据模块

数据模块组件声明代码：

TDataModule1 = class(TDataModule) // 数据库组件 ZConnection1: TZConnection; ZQuery1: TZQuery; // 字段定义 ZQuery1_BIRTHDAY: TDateField; ZQuery1_DEP_ID: TStringField; ZQuery1_DEP_NAME: TStringField; ZQuery1_EDU_LEVEL: TStringField; ZQuery1_NAME: TStringField; ZQuery1_NATIVE_PLACE: TStringField; ZQuery1_SCHOOL: TStringField; ZQuery1_SEX: TStringField; ZQuery1_SPECIALITY: TStringField; ZQuery1_STAFF_ID: TStringField; private public end;

数据库组件属性设置：

9.3.3 主窗体组件

主窗体组件声明代码如下：

TForm1 = class(TForm) // CRUD 按钮 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;

主窗体数据库组件的属性：

9.3.4 表单窗体组件

表单窗体组件声明代码：

TForm2 = class(TForm) // 确定、取消按钮 Button1: TButton; Button2: TButton; // 数据编辑组件 SpecialityEdit: TEdit; SchoolEdit: TEdit; EduLevelComboBox: TComboBox; BirthdayDateTimePicker: TDateTimePicker; DepComboBox: TComboBox; NameEdit: TEdit; NativePlaceEdit: 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;

9.3.5 主窗体功能实现代码

代码如下：

uses unit2, unit3;{ TForm1 }procedure TForm1.Button1Click(Sender: TObject);begin // 新建 Form2.isNew := True; if Form2.ShowModal = mrOk then begin DataModule1.ZQuery1.Refresh; end;end;procedure TForm1.Button2Click(Sender: TObject);begin // 编辑 if DataModule1.ZQuery1.BOF then if DataModule1.ZQuery1.RecordCount > 0 then DataModule1.ZQuery1.First else Exit; if DataModule1.ZQuery1.EOF then if DataModule1.ZQuery1.RecordCount > 0 then DataModule1.ZQuery1.Last else Exit; Form2.isNew := False; if Form2.ShowModal = mrOk then begin DataModule1.ZQuery1.Refresh; end;end;procedure TForm1.Button3Click(Sender: TObject);var Query: TZQuery;begin // 删除 if DataModule1.ZQuery1.BOF then if DataModule1.ZQuery1.RecordCount > 0 then DataModule1.ZQuery1.First else Exit; if DataModule1.ZQuery1.EOF then if DataModule1.ZQuery1.RecordCount > 0 then DataModule1.ZQuery1.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.ZQuery1_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.ZQuery1.Refresh;end;procedure TForm1.Button4Click(Sender: TObject);begin // 刷新 DataModule1.ZQuery1.Refresh;end;procedure TForm1.DBGrid1DblClick(Sender: TObject);begin // 双击修改 Button2Click(Sender);end;

9.3.6 表单窗体功能实现代码

代码如下：

uses unit2;{ TForm2 }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.ZQuery1_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;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:=ZQuery1_NAME.Value; if ZQuery1_SEX.Value = '男' then SexRadioGroup.ItemIndex:=0 else SexRadioGroup.ItemIndex:=1; index := DepComboBox.Items.IndexOf(ZQuery1_DEP_NAME.Value); DepComboBox.ItemIndex:=index; BirthdayDateTimePicker.Date := ZQuery1_BIRTHDAY.Value; EduLevelComboBox.Text := ZQuery1_EDU_LEVEL.Value; SchoolEdit.Text := ZQuery1_SCHOOL.Value; SpecialityEdit.Text:=ZQuery1_SPECIALITY.Value; NativePlaceEdit.Text:= ZQuery1_NATIVE_PLACE.Value; end;end;