9.ZeosDBO與FireBird綜合示例

9.1關(guān)于 FireBird

Firebird是一個開源的關(guān)系數(shù)據(jù)庫，提供了大部分SQL-99標準的功能，可以運行在Windows、linux和多種Unix操作系統(tǒng)上。Firebird提供良好的并發(fā)性，高效的執(zhí)行，強大的語言支持存儲過程和觸發(fā)器。從1981年起，它已經(jīng)被許多商業(yè)公司運用于許多的產(chǎn)品系統(tǒng)中。

Firebird 和原 Borland 的 InterBase 有著相同的血脈，其作為一個開源項目遵循 InterBase Public License V.1.0 on 25 July, 2000 協(xié)議。

FireBird 數(shù)據(jù)庫的特點：

FireBird 數(shù)據(jù)庫軟件有三種套件，支持不同作業(yè)平臺（windows系列、linux、freebsd 等…）

另外，新版的 FireBird 數(shù)據(jù)庫軟件還支持 Android ARM32、 Android ARM64、 Mac OS X。

9.2 FireBird 開發(fā)示例

本節(jié)示例：實現(xiàn)對一張數(shù)據(jù)表的 CRUD 操作，通過兩個窗體，其中主窗體展現(xiàn)數(shù)據(jù)，表單窗體用于錄入和修改數(shù)據(jù)。數(shù)據(jù)準備如下：

create table c_dep ( dep_id varchar(64) primary key, dep_name varchar(16));insert into c_dep (dep_id, dep_name)values (‘yfb’, ‘研發(fā)部’);insert into c_dep (dep_id, dep_name)values (‘xsb’, ‘銷售部’);insert into c_dep (dep_id, dep_name)values (‘jszcb’, ‘技術(shù)支持部’);insert into c_dep (dep_id, dep_name)values (‘cwb’, ‘財務(wù)部’);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’, ‘本科’, ‘內(nèi)蒙古大學(xué)’, ‘經(jīng)濟管理’, ‘內(nèi)蒙古烏蘭察布市集寧區(qū)’);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’, ‘本科’, ‘內(nèi)蒙古大學(xué)’, ‘計算機科學(xué)與技術(shù)’, ‘內(nèi)蒙古鄂爾多斯市東勝區(qū)’);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’, ‘本科’, ‘內(nèi)蒙古科技大學(xué)’, ‘計算機科學(xué)與技術(shù)’, ‘內(nèi)蒙古呼和浩特市托克托縣’);

在數(shù)據(jù)表設(shè)計上，我比較習(xí)慣于主鍵使用 UUID，這個習(xí)慣也是受近幾年的分布式數(shù)據(jù)庫的影響而形成的。在 FireBird 中，對于 UUID 在支持包括如下函數(shù)：

• 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 應(yīng)用程序示例

9.3.1 界面設(shè)計

主窗體界面如下圖：

表單窗體界面如下圖：

9.3.2 數(shù)據(jù)模塊

數(shù)據(jù)模塊組件聲明代碼：

TDataModule1 = class(TDataModule) // 數(shù)據(jù)庫組件 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;

數(shù)據(jù)庫組件屬性設(shè)置：

9.3.3 主窗體組件

主窗體組件聲明代碼如下：

TForm1 = class(TForm) // CRUD 按鈕 Button1: TButton; Button2: TButton; Button3: TButton; Button4: TButton; // 數(shù)據(jù)庫組件 DataSource1: TDataSource; DBGrid1: TDBGrid; // 數(shù)據(jù)導(dǎo)航組件 DBNavigator1: TDBNavigator; // 按鈕及數(shù)據(jù)導(dǎo)航組件容器 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;

主窗體數(shù)據(jù)庫組件的屬性：

9.3.4 表單窗體組件

表單窗體組件聲明代碼：

TForm2 = class(TForm) // 確定、取消按鈕 Button1: TButton; Button2: TButton; // 數(shù)據(jù)編輯組件 SpecialityEdit: TEdit; SchoolEdit: TEdit; EduLevelComboBox: TComboBox; BirthdayDateTimePicker: TDateTimePicker; DepComboBox: TComboBox; NameEdit: TEdit; NativePlaceEdit: TEdit; SexRadioGroup: TRadioGroup; // 數(shù)據(jù)標簽組件 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; // 標識是否為新建數(shù)據(jù)記錄 end;

9.3.5 主窗體功能實現(xiàn)代碼

代碼如下：

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 表單窗體功能實現(xiàn)代碼

代碼如下：

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; // 初始化數(shù)據(jù)項 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;