第十七章 SQL编程(二).doc

17.5 SQL编程实例我们在学习了SQL程序的编写方法之后，我们便可以着手创建自己的应用程序了，通过创建应用程序我们对Delphi的强大功能就会有更深刻的印象，同时会进一步全面掌握有关SQL编程的知识，在本节中我们主要介绍两个例子，前一个例子主要是用静态的SQL语句编程，后一个例子是用动态SQL语句编程。17.5.1 设计简单的SQL程序编辑器例17.1：在这个例子中，我们设计一个交互式的SQL程序编辑器，在这个编辑器中，我们可以根据SQL语言的语法规则，编写常用的SQL命令，并通过单击编辑器中的有关的按钮，直接执行编写好的SQL命令，SQL命令的执行结果也会及时地通过一个TDBGrid 部件显示出来。表17.3 SQL编辑器中个主要部件的属性部 件 属 性 值Form1 Caption=SQL程序编辑器DBGrid1 DataSource=DataSource1Button1 Caption=执行(&E)Button2 Caption=清除(&C)Button3 Caption=退出(&X)Button3 kind=bkCloseMemo1 DataSource1 DataSet=Query1Query1 DatabaseName=DEMOS因为我们在设置Query1的DatabaseName属性时将其设置为DEMOS，所以我们设计的这个SQL程序编辑器只能对DEOMS中的数据库表进行操作。单击按钮Button1的事件处理过程代码为：程序清单17.1procedure TForm1.Button1Click(Sender:TObject);beginQuery1.close;Query1.SQL.clear;Query1.SQL.Add(Memo1.text);Query1.Open;end;单击按钮Button2的事件处理过程为：程序清单17.2procedure TForm1.Button2Click(Sender:TObject);beginQuery1.close;Query1.SQL.clear;Query1.ExceSQL;end;下面我们对程序清单17.1和程序清单17.2中的程序代码进行简要的分析：程序清单17.1中的程序代码是用来执行查询的。Query1.close;这一行程序是用来关闭Query1的，我们在前面的章节中介绍过，只有在调用close 方法将TQuery部件关闭之后，才能修改其SQL属性值，执行close命令关闭查询是很安全的，如果查询已经被关闭了，调用该方法不会产生任何影响。Query1.SQL.clear;因为TQuery部件的SQL属性只能包含一条SQL语句，调用Clear 方法的目的是为了清除SQL属性原来的属性值即原来的SQL命令语句，如果不调用clear方法清除原来的SQL命令语句，当在后面的程序中调用Add方法为SQL属性设置新的SQL命令语句时，Delphi 会将新的SQL命令语句加在原来的SQL命令语句，这样使得SQL属性中包含两条独立的SQL语句，这是不允许的。Query1.SQL.Add(Memo.text);该条命令是将SQL编辑器的编辑区内的内容(TMemo部件Memo1)设置成Query1的SQL属性值。Query1.open;该语句用来执行Query1中的SQL命令语句，如果执行查询从数据库中获得查询结果，查询结果会在数据网格DBGrid1中显示出来。程序清单是用来清除查询的，其前两行语句跟程序清单中的代码是一样的。Query1.ExecSQL有一些特别，调用ExecSQL方法也是打开Query1，ExecSQL方法与open方法不一样的，请参看前面的章节，当Query1中SQL属性值为空时，即没有SQL语句时，只能调用ExecSQL方法来打开Query1，如果调用 open 方法会返回一个错误。 在执行完 Query1.ExecSQL语句之后，应用程序将会清除数据网格DBGrid1中的所有内容。17.5.2 设计一个数据库查询器例17.2：在数据库查询器中，用户可以选择要查询的数据库，查询数据库中的那一个表、根据数据库表中那一个字段进行查询，并且可以方便地指定查询条件，指定查询条件主要包括指定逻辑运算符(=、=、like、in、NOT like、NOT in)和字段值。例子全部的程序清单如下：unit main;interfaceusesSysUtils, Windows, Messages, Classes, Graphics, Controls,Forms, Dialogs, StdCtrls, DB, DBTables, Buttons, ComCtrls, Tabnotbk;typeTQueryForm = class(TForm)BitBtn1: TBitBtn;DataSource1: TDataSource;Table1: TTable;GroupBox1: TGroupBox;CheckBox1: TCheckBox;CheckBox2: TCheckBox;PageControl1: TPageControl;TabSheet1: TTabSheet;Label5: TLabel;Label1: TLabel;Label2: TLabel;Label3: TLabel;Label4: TLabel;ListBox1: TListBox;ListBox2: TListBox;ListBox3: TListBox;Edit1: TEdit;ComboBox1: TComboBox;BitBtn2: TBitBtn;TabSheet2: TTabSheet;Memo1: TMemo;procedure FormCreate(Sender: TObject);procedure ListBox1Click(Sender: TObject);procedure ListBox2Click(Sender: TObject);procedure BitBtn2Click(Sender: TObject);end;varQueryForm: TQueryForm;implementation$R *.DFMuses RSLTFORM;procedure TQueryForm.FormCreate(Sender: TObject);beginScreen.Cursor := crHourglass; Populate the alias list with ListBox1 dobeginItems.Clear;Session.GetAliasNames(Items);end; Make sure there are aliases defined Screen.Cursor := crDefault;if ListBox1.Items.Count 1 thenMessageDlg( There are no database aliases currently defined. Youneed at least one alias to use this demonstration.,mtError, mbOK, 0 ); Default the drop-down list to the first value in the list ComboBox1.ItemIndex := 0;end;procedure TQueryForm.ListBox1Click(Sender: TObject);varstrValue: string; Holds the alias selected by the user bIsLocal: Boolean; Indicates whether or not an alias is local slParams: TStringList; Holds the parameters of the selected alias iCounter: Integer; An integer counter variable for loopsbegin Determine the alias name selected by the user with ListBox1 dostrValue := Items.StringsItemIndex; Get the names of the tables in the alias and put them in theappropriate list box, making sure the users choices are reflectedin the list. ListBox2.Items.Clear;Session.GetTableNames(strValue, alias to enumerate , pattern to match CheckBox1.Checked, show extensions flag CheckBox2.Checked, show system tables flag ListBox2.Items); target for table list Make sure there are tables defined in the alias. If not, show anerror; otherwise, clear the list box. Screen.Cursor := crDefault;if ListBox2.Items.Count 1 thenMessageDlg(There are no tables in the alias you selected. Pleasechoose another, mtError, mbOK, 0 );ListBox3.Items.Clear;end;procedure TQueryForm.ListBox2Click(Sender: TObject);beginScreen.Cursor := crHourglass;try First, disable the TTable object. if Table1.Active thenTable1.Close; Open the selected table with ListBox1 doTable1.DatabaseName := Items.StringsItemIndex;with ListBox2 doTable1.TableName := Items.StringsItemIndex; Open the table and put a list of the field names in the Fieldslist box. Table1.Open;if Table1.Active thenTable1.GetFieldNames(ListBox3.Items);finallyScreen.Cursor := crDefault;end;end;procedure TQueryForm.BitBtn2Click(Sender: TObject);varstrAlias, Alias name selected by the user strTable, Table name selected by the user strField, Field name selected by the user strValue, Field Value entered by the user strWhere, WHERE clause for the users query strQuote, Holds quotes is the query field is text strQuery: string; String used to construct the query frmQuery: TResultForm; The Results form type The following type is used with the Type drop-downlist. The text values corresponding with each item isdescribed in comments, along with the relevant SQL operators. etSQLOps = (soNoCondition, not field conditions: no WHERE clause soEqual, equals: = soNotEqual, is not equal to: soLessThan, is less than: soLessEqual, is less than or equal to: soMoreEqual, is greater than or equal to: = soStartsWith, starts with: LIKE xx% soNoStartsWith, doesnt start with: NOT LIKE xx% soEndsWith, ends with: LIKE %xx soNoEndsWith, doesnt end with: NOT LIKE %xx soContains, contains: LIKE %xx% soNoContains, doesnt contain: NOT LIKE %xx% soBlank, is blank: soNotBlank, is not blank: soInside, contains only: IN ( xx, yy, zz ) soOutside); doesnt contain: NOT IN (xx, yy, zz) begin Initialize the variables needed to run the query with ListBox1 doif ItemIndex = -1 thenraise Exception.Create(Cant Run Query: No Alias Selected)elsestrAlias := Items.StringsItemIndex;with ListBox2 doif ItemIndex = -1 thenraise Exception.Create(Cant Run Query: No Table Selected)elsestrTable := Items.StringsItemIndex;with ListBox3 doif ItemIndex = -1 thenbeginif ComboBox1.ItemIndex Ord(soNocondition) thenraise Exception.Create(Cant Run Query: No Field Selected)elsestrField := ;endelsestrField := Items.StringsItemIndex;if (Edit1.Text = ) and(ComboBox1.ItemIndex Ord(soNoCondition) and(ComboBox1.ItemIndex Ord(soBlank) thenraise Exception.create(Cant Run Query: No Search Value Entered)elsestrValue := Edit1.Text; See if the field being search is a string field. If so, then pad thequote string with quotation marks; otherwise, set it to a null value. if strField thenwith Table1.FieldByName(strField) doif (DataType = ftString) or (DataType = ftMemo) thenstrQuote := elsestrQuote := ; Construct the WHERE clause of the query based on the users choicein Type. case etSQLOps(ComboBox1.ItemIndex) ofsoNoCondition: strWhere := ;soEqual: strWhere := strField + = + strQuote + strValue+ strQuote;soNotEqual: strWhere := strField + + strQuote + strValue +strQuote;soLessThan: strWhere := strField + + strQuote + strValue +strQuote;soLessEqual: strWhere := strField + + strQuote + strValue +strQuote;soMoreEqual: strWhere := strField + = + strQuote + strValue +strQuote;soStartsWith: strWhere := strField + LIKE + strQuote +strValue + % + strQuote;soNoStartsWith: strWhere := strField + NOT LIKE + strQuote +strValue + % + strQuote;soEndsWith: strWhere := strField + LIKE + strQuote +% + strValue + strQuote;soNoEndsWith: strWhere := strField + NOT LIKE +strQuote + % + strValue + strQuote;soContains: strWhere := strField + LIKE + strQuote+%+ strValue+ % + strQuote;soNoContains: strWhere := strField + NOT LIKE + strQuote + %+ strValue + % + strQuote;soBlank: strWhere := strField + IS NULL;soNotBlank: strWhere := strField + IS NOT NULL;end;if ComboBox1.ItemIndex = Ord(soNoCondition) thenstrQuery := SELECT * FROM + strTable + else if Table1.FieldByName(strField).DataType = ftString thenstrQuery := SELECT * FROM + strTable + t WHERE t. + strWhereelsestrQuery := SELECT * FROM + strTable + t WHERE t. + strWhere; Create an instance of the browser form. frmQuery := TResultForm.Create(Application); Use a resource protection block in case an exception is raised. Thisensures that the memory allocated for the Results form is released. trywith frmQuery dobeginScreen.Cursor := crHourglass;if Query1.Active then Query1.Close;Query1.DatabaseName := strAlias; set the alias the query poitns toQuery1.SQL.clear; empty existing SQL in the query Query1.SQL.Add(strQuery); add query string to query object Query1.Active := True; try to run the query Screen.Cursor := crDefault;if Query1.Active thenbegin If the query didnt return any records, theres no point indisplaying the form. In that event, raise an exception. if Query1.RecordCount 1 thenraise Exception.create(No records matched your criteria. Please try again. ); write a message to the browse forms status line if strField = thenPanel3.Caption := Now showing all records from + strTable + .elsePanel3.Caption := Now showing + strTable + where + strField+ contains values equal to + strValue + .; show the form ShowModal;end;end;finallyfrmQuery.Free;e