I used Oracle Forms for a long time to build Query By Form
applications. I have recently switched to Delphi, and am wondering if it's possible to
build Query By Form applications in it.
The answer is an unequivocal "Yes!" But I'm not here to give one-word
answers, so let's discuss Query By Form in more detail...
What is Query By Form (QBF)?
First of all, one of the mistakes many people make about QBF is that is a proprietary
feature of a some company's development environment. It's not. Think about QBF as an
application design methodology or paradigm. Any programming environment in which you can
build windows (forms) and in turn can access a database platform has the ability to build
a QBF application. Admittedly, some products provide a relatively easy way to implement
QBF over others, but if a product meets the two requirements mentioned above, it can do
QBF.
For those of you not familiar with the concept, Query By Form is the act of wrapping an
intelligent user interface around a query or group of queries that they might
normally have to create by hand. By intelligence I don't mean a program that has
cognitive abilities; rather, it's one that can translate and process user input by way of
a form and provide result data in a reasonable format such as a printed report or a data
grid.
In a nutshell, QBF is a way to hide the complexities of data extraction from the user,
thus allowing him/her to focus on a specific business problem rather than being distracted
by cryptic commands and keystrokes usually associated with query languages such as SQL.
And because QBF is by nature business-problem-centric, QBF applications have the added
advantage of restricting the user to a specific problem domain. In other words, it is very
unlikely that while using a QBF application, the user could ask the wrong questions. This
is because the program has only a limited set of questions which are bound by a specific
problem domain.
There are a few people out there that disagree with this concept, saying it's
inflexible and contending that users want to perform more ad hoc queries of their data to
get their answers. In some cases I will agree with this. But I will counter that
almost all business problems are defined by very specific sets of protocols and so have
clearly defined and expected results. These protocols can in turn be modeled, then
transformed into a seamless automation of the protocols.
Ad hoc querying is not only error-prone, but suffers from the danger of introducing
unnecessary, extraneous data that could be perceived as meaningful but in actuality is far
from it. Not only that, but most analyses require more than one query to achieve an
intelligible answer set, usually starting with some initial extraction, then going through
various levels of refinement until the appropriate data set is achieved. Users performing
queries by ad hoc means may run their refinement queries out of sequence, or even miss
some intermediate steps altogether.
Enter Delphi
Now let's look at how we can implement QBF. The concept of QBF can be applied in
numerous ways in Delphi, so I'm not going to talk to much about specific cases of coding.
However, I will talk about certain techniques I've used in Delphi when creating QBF
applications.
Delphi is an ideal tool for doing QBF for a number of compelling reasons. Among them
are:
- Delphi applications are built with a form or window design paradigm. Every new project
you start has a form and an associated unit that's created along with the project. This
puts the developer in the interface design state right away. That's what QBF is all about:
building a form to be the interface to your data extraction.
- Delphi data-aware VCL components such as TTable and TQuery can make the process of
creating QBF applications as easy as dragging and dropping and setting properties. This
especially applies to really simple QBF apps that have only one query. Of course, for
several sequential queries you'll have to do a bit of coding, but it's still pretty easy.
- On top of all that, the Borland Database Engine (BDE) provides connectivity to a variety
of database platforms, which means you can create generic QBFs that can go after data on
heterogenous platforms.
The above are just a few examples of why I feel Delphi is an ideal tool for creating
QBF applications.
Concept Revisited
I mentioned above that QBF implements an intelligent interface that has the ability to
tranform user input requests into a data set of some sort. What is implied by QBF is that
you use queries to perform the transformations, but I'm going to break stride here and say
that you don't necessarily need to use queries to get your answer sets. Why? Think about
it for a moment. The whole purpose of QBF is to hide the complexities inherent to data
retrieval languages from the user. All users care about is the end product: the answer
set. They don't care about the back-end operations. In that light, we open up a bunch of
doors to getting data to the user.
For brevity's sake we won't go into all the different ways to do QBF. What I will
concentrate on here are two common, useful ways of doing QBF in Delphi: by Dynamic
Querying and TTable SetRange.
If there's something bugging you about the whole concept of QBF, it's probably this: You
probably already know how to do this! That's right. Anytime you put a front-end form
in front of a query or data retrieval operation, you're essentially doing Query By Form.
QBF Techniques: Another Flavor of Dynamic Queries
When you think of dynamic queries, what comes to mind? Usually the parameter-ized
variety of placing a query variable within a SQL statement you preprocess with a Prepare,
fill with a value, then execute. That's a perfectly valid methodology to employ in many
cases. But for a lot of my own applications, I've found using parameter-ized queries
limiting in many ways. You can't use a parameter in the FROM clause of a query. This means
that you can't apply the query to different tables that have the same structure. For
myself, I want to have ultimate flexibility, so what I do is address the SQL property
directly.
The SQL property of a TQuery is a TStrings type property. Ah! the old TStrings. That's
right folks, this is something many of you have used time and again in your programs. As
you may already know, a TStrings object is nothing more than an ordered collection of
strings, each accessed by means of a zero-based index (meaning the first string's index is
'0'). So what's so special about this respect to the SQL property of a TQuery? It all has
to do with strings themselves. The most important thing is that strings can be easily
manipulated. You can pretty much dice and slice them any way you choose. With respect to
dynamic queries, the ability to manipulate the SQL property is a boon to doing QBF. Let's
look at a sample of a real code snippet from one of my larger QBF applications.
InitQuery := TQuery.Create(Application);
with InitQuery do
begin
DatabaseName := 'PRIVATE';
Close;
SQL.Clear;
SQL.Add('SELECT D.BATCH, D.RECORD, D.ACCOUNT, D.FACILITY, D."INGREDIENT COST",');
SQL.Add('D."PHARMACY ID", D.DAW, D."DAYS SUPPLY", D."DISPENSING FEE", D."MEMBER ID",');
SQL.Add('D."DOCTOR ID", D.NDC, D.FORMULARY, D."Apr Amt Due",');
SQL.Add('D1."DEA CODE", D1."GPI CODE", D1."DRUG NAME", D1."GENERIC CODE", 0 AS D."DAW COUNT"');
SQL.Add('FROM "' + EncPath + '" D, ":DRUGS:' + DrugTable + '" D1');
SQL.Add('WHERE (D.' + DateFld + ' = ' + BStart + ' AND D.' + DateFld + ' <= ' + BEnd + ') AND');
SQL.Add('((D."RECORD STATUS" = ''P'') OR (D."RECORD STATUS" = ''R'')) AND ');
//Get Account List and Medical Group entries. Have to do this conditionally to
//handle both lists at the same time. A bit of a short-circuit
if (MainForm.DBRadioGroup1.ItemIndex = 1) then
if (MainForm.DBRadioGroup2.ItemIndex = 1) then
begin
AddSQLList(MainForm.AccountList, SQL, 'Account', True);
AddSQLList(MainForm.MedGrpList, SQL, 'Facility', True);
end
else
AddSQLList(MainForm.AccountList, SQL, 'Account', True)
else
if (MainForm.DBRadioGroup2.ItemIndex = 1) then
AddSQLList(MainForm.MedGrpList, SQL, 'Facility', True);
SQL.Add('(D.FORMULARY <> ''Q'') AND (D.NDC = D1.NDC)');
SQL.SaveToFile('mgrInit.sql');
try
Open;
except
Free;
raise;
Abort;
end;
end;
In the code above, I've marked in bold the places I've inserted string variables to be
filled in at runtime. Due to the changing nature of user requests, I found this technique
far more flexible and it allows me to change the SQL in any number of places in the
SQL statement. One thing you should note in the code above is that not only did I just
provide fill-in areas with string vars, I also used a remote procedure to load in SQL
items using AddSQLList.
This takes advantage of an interesting feature of a TStrings item. While you cannot
pass a TStrings item by reference (ie. procedure procName(var _tString :
TStrings);), you can pass a TStrings object by constant value to add or delete from the
list depending upon what you want to do. That is what the procedure AddSQLList
performs. Essentially, it takes what users have entered in a TDBMemo criteria field
on the QBF form, turns the list values into a string of comma-separated values, then turns
the string into a SQL IN statement. The IN statement is then tacked onto the end of
the SQL TStrings object. Let's look at the code:
{======================================================================================
This procedure will add an IN query statement from a list of values passed from a
TDBMemo into the SQL of a TQuery. Using an IN is far more elegant than several
Field = 'value1' OR Field = 'value2' statements.
======================================================================================}
procedure AddSQLList(lst : TDBMemo; //List you want to read from
const encSQL: TStrings; //SQL to add to
fldName : String; //The field to query on
AddAND : Boolean); //Add an AND to tail end?
var
I : Word;
valStr : String;
begin
//initialize vars;
valStr := '';
//Parse the list and make a CSV string out of the values
for I := 0 to (lst.Lines.Count - 1) do begin
valStr := valStr + '''' + lst.Lines[I] + ''',';
end;
//Remove the trailing comma
valStr := Copy(valStr, 1, Length(valStr) - 1);
//Append the SQL IN clause with field name. If there is another
//SQL statement to follow, append an AND to the end.
if addAND then
encSQL.Add(' D.' + fldName + ' IN (' + valStr + ') AND ')
else
encSQL.Add(' D.' + fldName + ' IN (' + valStr + ')');
end;
The only danger to the procedure above is that I don't know if this is a loophole in
the compiler or not. One would assume that to change something, you would pass it by
reference. But this is not so with TStrings. I'm waiting to hear replies from Borland and
the folks a CompuServe. But rest assured, I've used this technique in both versions of
Delphi with no problems. My only concern is what will happen in future versions of the
compiler. In any case, the whole point to this discussion is that manipulating the SQL
property directly is much more flexible that using parameter-ized queries.
QBF Techniques: TTable SetRange
Remember what I said above, that users don't care how they get their data, they just
want to get it? Especially with simple retrieval functions, you don't necessarily need to
perform a query. Sometimes a TTable SetRange will do the job for you, and not only
that -- but faster.
There are a couple of ways to perform a SetRange. The first is to use the SetRange
function itself, which combines the SetRangeStart, SetRangeEnd and ApplyRange
functions in one call. This is effective for setting ranges on the first index of a table.
For other setting ranges on other index fields, you will need to explicitly use the three
functions mentioned previously . The help file explains the usage of these functions in
detail, so I won't go into specific coding examples.
Wrapping It Up
I realize that this has been more of a concept discussion rather than a real coding
discussion. But you should remember that there's a lot more to progamming than coding.
Programming is a really complex process that includes a lot of conceptualization and
analysis. Over the years that I have been developing applications, I have found that I've
become a much more effective programmer by paying attention to the concepts that have been
put before me, and using them as means to approaching a code solution from different
perspectives.
Copyright © 1995, 1996, 1997 Brendan V. Delumpa All Rights Reserved
|