第十七章 SQL编程（二）
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=DataSource1
Button1 Caption=执行(&E)
Button2 Caption=清除(&C)
Button3 Caption=退出(&X)
Button3 kind=bkClose
Memo1
DataSource1 DataSet=Query1
Query1 DatabaseName=DEMOS
━━━━━━━━━━━━━━━━━━━━
因为我们在设置Query1的DatabaseName属性时将其设置为DEMOS， 所以我们设计的这个SQL程序编辑器只能对DEOMS中的数据库表进行操作。
单击按钮Button1的事件处理过程代码为：
程序清单17.1
procedure TForm1.Button1Click(Sender:TObject);
begin
Query1.close;
Query1.SQL.clear;
Query1.SQL.Add(Memo1.text);
Query1.Open;
end;
单击按钮Button2的事件处理过程为：
程序清单17.2
procedure TForm1.Button2Click(Sender:TObject);
begin
Query1.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;
interface
uses
SysUtils, Windows, Messages, Classes, Graphics, Controls,
Forms, Dialogs, StdCtrls, DB, DBTables, Buttons, ComCtrls, Tabnotbk;
type
TQueryForm = 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;
var
QueryForm: TQueryForm;
implementation
{$R *.DFM}
uses RSLTFORM;
procedure TQueryForm.FormCreate(Sender: TObject);
begin
Screen.Cursor := crHourglass;
{ Populate the alias list }
with ListBox1 do
begin
Items.Clear;
Session.GetAliasNames(Items);
end;
{ Make sure there are aliases defined }
Screen.Cursor := crDefault;
if ListBox1.Items.Count < 1 then
MessageDlg( 'There are no database aliases currently defined. You
need 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);
var
strValue: 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 loops}
begin
{ Determine the alias name selected by the user }
with ListBox1 do
strValue := Items.Strings[ItemIndex];
{ Get the names of the tables in the alias and put them in the
appropriate list box, making sure the user's choices are reflected
in 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 an
error; otherwise, clear the list box. }
Screen.Cursor := crDefault;
if ListBox2.Items.Count < 1 then
MessageDlg('There are no tables in the alias you selected. Please
choose another', mtError, [mbOK], 0 );
ListBox3.Items.Clear;
end;
procedure TQueryForm.ListBox2Click(Sender: TObject);
begin
Screen.Cursor := crHourglass;
try
{ First, disable the TTable object. }
if Table1.Active then
Table1.Close;
{ Open the selected table }
with ListBox1 do
Table1.DatabaseName := Items.Strings[ItemIndex];
with ListBox2 do
Table1.TableName := Items.Strings[ItemIndex];
{ Open the table and put a list of the field names in the Fields
list box. }
Table1.Open;
if Table1.Active then
Table1.GetFieldNames(ListBox3.Items);
finally
Screen.Cursor := crDefault;
end;
end;
procedure TQueryForm.BitBtn2Click(Sender: TObject);
var
strAlias, { 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 user's 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-down
list. The text values corresponding with each item is
described 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: <= }
soMoreThan, { is greater than: > }
soMoreEqual, { is greater than or equal to: >= }
soStartsWith, { starts with: LIKE xx% }
soNoStartsWith, { doesn't start with: NOT LIKE xx% }
soEndsWith, { ends with: LIKE %xx }
soNoEndsWith, { doesn't end with: NOT LIKE %xx }
soContains, { contains: LIKE %xx% }
soNoContains, { doesn't contain: NOT LIKE %xx% }
soBlank, { is blank: }
soNotBlank, { is not blank: }
soInside, { contains only: IN ( xx, yy, zz ) }
soOutside); { doesn't contain: NOT IN (xx, yy, zz) }
begin
{ Initialize the variables needed to run the query }
with ListBox1 do
if ItemIndex = -1 then
raise Exception.Create('Can''t Run Query: No Alias Selected')
else
strAlias := Items.Strings[ItemIndex];
with ListBox2 do
if ItemIndex = -1 then
raise Exception.Create('Can''t Run Query: No Table Selected')
else
strTable := Items.Strings[ItemIndex];
with ListBox3 do
if ItemIndex = -1 then
begin
if ComboBox1.ItemIndex > Ord(soNocondition) then
raise Exception.Create('Can''t Run Query: No Field Selected')
else
strField := '';
end
else
strField := Items.Strings[ItemIndex];
if (Edit1.Text = '') and
(ComboBox1.ItemIndex > Ord(soNoCondition)) and
(ComboBox1.ItemIndex < Ord(soBlank)) then
raise Exception.create('Can''t Run Query: No Search Value Entered')
else
strValue := Edit1.Text;
{ See if the field being search is a string field. If so, then pad the
quote string with quotation marks; otherwise, set it to a null value. }
if strField <> '' then
with Table1.FieldByName(strField) do
if (DataType = ftString) or (DataType = ftMemo) then
strQuote := '"' else
strQuote := '';
{ Construct the WHERE clause of the query based on the user's choice
in Type. }
case etSQLOps(ComboBox1.ItemIndex) of
soNoCondition: 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;
soMoreThan: 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) then
strQuery := 'SELECT * FROM "' + strTable + '"'
else if Table1.FieldByName(strField).DataType = ftString then
strQuery := 'SELECT * FROM "' + strTable + '" t WHERE t.' + strWhere
else
strQuery := '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. This
ensures that the memory allocated for the Results form is released. }
try
with frmQuery do
begin
Screen.Cursor := crHourglass;
if Query1.Active then Query1.Close;
Query1.DatabaseName := strAlias; {set the alias the query poitns to}
Query1.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 then
begin
{ If the query didn't return any records, there's no point in
displaying the form. In that event, raise an exception. }
if Query1.RecordCount < 1 then
raise Exception.create('No records matched your criteria.
Please try again.' );
{ write a message to the browse form's status line }
if strField = '' then
Panel3.Caption := 'Now showing all records from ' + strTable
+ '...'
else
Panel3.Caption := 'Now showing '+ strTable +' where '+ strField
+' contains values equal to '+ strValue + '...';
{ show the form }
ShowModal;
end;
end;
finally
frmQuery.Free;
end;
end;
end.
unit RSLTFORM;
interface
uses
SysUtils, Windows, Messages, Classes, Graphics, Controls, StdCtrls, DB,
Forms, DBCtrls, DBGrids, DBTables, Buttons, Grids, ExtCtrls, Dialogs;
type
TResultForm = class(TForm)
DBGrid1: TDBGrid;
DBNavigator: TDBNavigator;
Panel1: TPanel;
DataSource1: TDataSource;
Panel2: TPanel;
Panel3: TPanel;
Query1: TQuery;
SpeedButton2: TSpeedButton;
Panel4: TPanel;
SpeedButton1: TSpeedButton;
procedure SpeedButton1Click(Sender: TObject);
procedure SpeedButton2Click(Sender: TObject);
end;
var
ResultForm: TResultForm;
implementation
{$R *.DFM}
procedure TResultForm.SpeedButton1Click(Sender: TObject);
begin
Close;
end;
procedure TResultForm.SpeedButton2Click(Sender: TObject);
var
strText: string; { Variable to hold display text }
iCounter: Integer; { Loop counter variable }
begin
{ Build a string containing the query }
strText := '';
for iCounter := 0 to Query1.SQL.Count - 1 do
strText := strText + Query1.SQL[iCounter];
{ Display the query text }
MessageDlg('The underlying query is: ' + #10 + #10 + strText,
mtInformation, [mbOK], 0 );
end;
end.