Documentation for
Jayan's Query Tools Components :
- Query Tools consists of 2 components
-
TQueryByFilter
-
TQueryBySQL ( Work in Progress )
- Demo Included. Database Alias DBDEMOS.
The documentation for TQueryByFilter has been
given. TQueryBySQL component has not been completed. Please read this documentation fully.
Created & Developed
By : Jayan Chandrasekhar, Riyadh, Saudi
Arabia.
Web - Site
: www.crosswinds.net/~delphitoday
Email
: jayanck@email.com
Installation :
Please install the component TKbmMemTable before
you install QueryTools. This is an excellent Memory Table component
written by Kim Bo Madsen. I have included the Zip file. To get the
latest version of KbmMemTable you can go to http://www.onelist.com/community/memtable.
To install QueryTools you have to install the
package QueryTools.dpk. This is made for Delphi 5. For Delphi 3 and
4 you can make your own package by adding JQryTool.pas.
Disclaimer :
This component is a freeware and you can use it at your own Risk.
Foreword : This
component is a result of hours of hard work. I would like to share and
dedicate it to the entire Delphi Community which consists of Delphi
programmers who have created many innovative freeware softwares. This
component is limited to my knowledge of Delphi and experiences. All sort
of suggestions are welcome. I would appreciate if you can email me and
tell as to how you have found this component useful. Soon I will also be
releasing TQueryBySQL.
TQueryByFilter
Introduction
The aim of this Component is to provide
the Delphi Programmers an easy way to implement Filtering in their
programs. That is, as the user works with a form designed by you a Delphi
programmer, the user can on the fly during run-time see the data you present
as per his requirements. For instance your form may provide employee-details
. The user who is browsing through the data may want to see all the Male
Employees from one particular Country. Usually you will have to provide
for such Queries by providing some button and writing code. That too if
the user has informed you while the program was being designed. If there
are lets say 20 fields on the form the user should be able to Query any
way he wants. The components TQueryByFilter
can help you do so without much effort just that by providing you 3 object
methods. Now the user can query the employee data provided by you on the
form in any way he pleases. Here are some examples. He may want to see
the employee data in 1 of the following ways :
-
All the male employees.
-
All the male employees having Salary Greater
than 50,000.
-
All the male employees who are from a particular
country.
-
All the male employees whose Salary are Greater
than 50,000 and are Americans.
You the programmer need not know in advance
how the user is going to query. The user at run-time would do so by entering
the criteria in the fields on your form - the same form you have provided.
No other Form is used for this purpose. He can also use relational
operators provided by pressing the Control and Alt Key together
and selecting from the relational operator PopUpMenu while in the
field. On executing , the Table or Query will reflect the users selection
in the result set. TQueryByFilter provides this
feature by using the Filter property of TTable / TQuery.
How
To Use TQueryByFilter Component ? ( Please check the Demo )
- As usual a Form with a TTable / TQuery
connected to a TDataSource is needed. Put some DataAware controls
like TDBEdit, TDBCheckBox etc. and connect them to the fields in
the TTable / TQuery. Also if needed put a DBNavigator. All
the above mentioned things are done usually.
- Now to give on-the-fly filtering
factility put a TQueryByFilter component on the form. Its
default name as usual will be QueryByFilter1. Connect the
DataSource property of TQueryByFilter to the DataSource of the TTable
/ TQuery.
- Now for the filtering feature to work there
are 3 methods to be called as and when necessary. Add three Buttons
on to the form.
- The first Button to call the method
QueryByFilter1.GetCriteria.
- The Second Button to Call
QueryByFilter1.SetFilter
- And the Third Button
to call QueryByFilter1.ClearFilter.
- That's all. You have added a Super Filter
facility to your Form.
- You can now see the Data in the DataAware
controls which can be navigated with the DBNavigator. No you want
to see some filtered data. Click on the first Button. You will
see that all the DataAware controls are now cleared of data.
- You can put some value in one of the fields.
For instance if you have an EmployeeNumber field, Put
an employee number in that field. Now click on the second button.
You will see that only data relating to that Employee is now available.
That means the Table/Query has now been filtered to match the employee
number you have put. By pressing the 3rd button, the filter is cleared
and your back to your original data. Easy , isn't it.
- Now lets try again. Click on the 1st
button again. This time you want to see all the EmployeeNumbers
greater than 2000. Put 2000 in the EmployeeNumber field. To
specify GreaterThan you have to press Control + Alt. A PopUpMenu
containing a long list of relational-operators will Pop-Up beside your
field. Click GreaterThan. Now click the 2nd Button. You
will see that only data matching the EmployeeNumbers GreaterThan 2000.
- You can extend the same logic to multiple-fields.
You can see Employees with Numbers GreaterThan 2000 AND Whose Nationality
is American. Please note that the AND is a result of
Match-All property of TQueryByFilter. You can all see all the employees
with Numbers greater-than 2000 OR they are Americans,
by selecting Match-Any Property.
Thus you can have the Relational Operators Equal-To
(default), Not-Equal-To, Greater-Than, Less-Than, Between, Not-Between,
In list, Not-In List, Is Null, Is-Not Null.
Also you can have Match-Any or Match-All,
any one, at one particular time.
All the above things can be done with DBGrid
also. Only that you have to select the Grid property.
Properties
:
-
DataSource ( Published )
Here you have
to select the DataSource that binds the TTable / TQuery Component
and the DataAware controls on which the QueryByFilter feature will work
by changing Fitler Property to meet the users Criteria.
-
Grid ( Published)
Use Grid instead
of DataSource if you want QueryByFilter to work on DBGrid.
-
QTState - (Run time, Read Only)
There are 3 states
-
qsInActive - Is when
GetCriteria
method is not called or after the ClearFilter method is called.
-
qsGetCriteria
- Is When
GetCriteria
method is called and before SetFilter is called. It is in this state
the user enters his criteria. He can also press the Control + Alt to select
the relational operators from the Pop-Up Menu while in a Data-Aware Control.
-
qsExecuted
- Is after the
SetFilter
method is called. ClearFilter method will change the state to
qsInActive
.
-
QTInEffect - ( Run Time , Read Only
)
returns True if the Filter was successfully set after the SetFilter
method is called. Some time
it so happens that the user criteria does not effect any change on the
Filter. For example when the user calls the GetCriteria
method
and then calls the SetFilter method without filling in any
criteria. After the method is called the filter is not changed. By checking QTInEffect
we will be able to
know and inform the user that no change has been made to the Filter of
the DataSet. If QTInEffect does not return True you
do not have to call ClearFilter as it will be done automatically
by the Component.
-
CaseSensitive
( Default is False )
Self Explanatory.
-
Citeria ( Run-Time,
ReadOnly, TStringList) The
Criteria selected by the user is stored in a StringList Which you can use for
various purposes. The Criteria will be in the pattern
Field-Name <|>Relational-Operator<|>Value. See the Demo.
-
MatchMode ( Published, default is mmMatchAll )
MatchMode
decides whether to use logical-operator AND or OR.
- if mmMatchAll is
used AND will be used between conditional expressions in the
filter.
- if mmMatchAny is
used OR will be used
Methods
:
-
GetCriteria ( Returns True if Successful
) This is the first method to be called so that all the DataAware controls
on the form will be cleared. The QBFState becomes qsGetCriteria.
The user will type or select his Criteria in this state. He can also use
the Control + Alt keys to select the relational operators and other functions
from the PopUpMenu. Returns true if GetCriteria
method was successful.
-
SetFilter ( Returns True if Successful
) Once the user enters his Criteria the SetFilter method
should be called so that the user Criteria is translated into a statement to
filter data. Please note that the property QBFInEffect
will tell you whether the attempt to change the Filter was successful or not.
SetFilter also returns true if successful.
-
ClearFilter This method has to
be called if the effect of GetCriteria or SetFilter has to be removed and the DataSet is
back to its original state. Please note that it is very important to call this
method as this method de-allocates the memory allocated by the GetCriteria
method. Also note that if you call GetCriteria method
it will Call the ClearFilter method ( Implicitly).
If you don’t intend to call the GetCriteria method again ClearFilter has to be called
explicitly. Please remember this while destroying
your form. You can put it in the Form Destroy Event.
Relational
Operators available on clicking the Control + Alt Keys while in qsGetCriteria
state :
Relational
Operators |
Explanation |
Equal To, Not Equal
To, Less Than, Greater Than
|
No explanation
required |
Between
& Not Between |
The From
part of the Between Range to be entered in the Field itself and when Between
or Not Between is selected from the Relational Operator PopUpMenu the To
part to be entered in the InputBox which pops up on selection. |
In & Not In |
This is nothing
but the SQL IN operator in the WHERE clause. You do
not have to enter anything in the Field. When you select IN
from the PopUpMenu an InputBox pops up where you can enter all the values
seperated by commas. If string values are not entered in quotes a SQL error
will be generated .For Example :
In case of strings : "Jan","Feb","Mar"
etc.
In case of numbers : 10,20,30
For more details refer to SQL IN documentation. |
Null & Is Not
Null |
No explanation
is required |
Note : In all the above
cases NOT is the negative of the same operator