sql.txt

sQL语言作为关系数据库管理系统中的一种通用的结构查询语言已经被众多的数据库管理系统所采用

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-downlist. 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.