The 17th Chapter-sql Programming (Two) (2)

17.5.2 Design a database query device

Example 17.2: In the database query, the user can select the database to query, query the table in the database, query from the field in the database table, and easily specify the query criteria, specifying that the query criteria mainly include the specified logical operators (=, >, <, <=, >=, like, in, not, not, and field values.

Examples of the full list of procedures are as follows:

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 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 a alias

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 "the" tables in the "alias" 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 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 for you selected. Please

Choose another ', Mterror, [Mbok], 0);

ListBox3.Items.Clear;

End

Procedure Tqueryform.listbox2click (Sender:tobject);

Begin

Screen.cursor: = Crhourglass;

Try

{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 the item is

Described in comments, along with the relevant SQL operators. }

Etsqlops = (sonocondition, {not field Conditions:no WHERE clause}

Soequal, {equals: =}

Sonotequal, {isn't 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;

{The 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 blocks in the case of a exception is raised. This

Ensures that's 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.

displaying the form. In this 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.