Author: Lou Adler
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.
Answer:
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.
1 InitQuery := TQuery.Create(Application);
2 with InitQuery do
3 begin
4 DatabaseName := 'PRIVATE';
5 Close;
6 SQL.Clear;
7 SQL.Add('SELECT D.BATCH, D.RECORD, D.ACCOUNT, D.FACILITY, D."INGREDIENT COST",');
8 SQL.Add('D."PHARMACY ID", D.DAW, D."DAYS SUPPLY", D."DISPENSING FEE",
9 D."MEMBER ID",'
10 SQL.Add('D."DOCTOR ID", D.NDC, D.FORMULARY, D."Apr Amt Due",');
11 SQL.Add('D1."DEA CODE", D1."GPI CODE", D1."DRUG NAME", D1."GENERIC CODE",
12 0 AS D."DAW COUNT"'
13 SQL.Add('FROM "' + EncPath + '" D, ":DRUGS:' + DrugTable + '" D1');
14 SQL.Add('WHERE (D.' + DateFld + ' = ' + BStart + ' AND D.' + DateFld + ' <= ' +
15 BEnd
16 + ') AND');
17 SQL.Add('((D."RECORD STATUS" = ''P'') OR (D."RECORD STATUS" = ''R'')) AND ');
18
19 //Get Account List and Medical Group entries. Have to do this conditionally to
20 //handle both lists at the same time. A bit of a short-circuit
21 if (MainForm.DBRadioGroup1.ItemIndex = 1) then
22 if (MainForm.DBRadioGroup2.ItemIndex = 1) then
23 begin
24 AddSQLList(MainForm.AccountList, SQL, 'Account', True);
25 AddSQLList(MainForm.MedGrpList, SQL, 'Facility', True);
26 end
27 else
28 AddSQLList(MainForm.AccountList, SQL, 'Account', True)
29 else if (MainForm.DBRadioGroup2.ItemIndex = 1) then
30 AddSQLList(MainForm.MedGrpList, SQL, 'Facility', True);
31
32 SQL.Add('(D.FORMULARY <> ''Q'') AND (D.NDC = D1.NDC)');
33
34 SQL.SaveToFile('mgrInit.sql');
35 try
36 Open;
37 except
38 Free;
39 raise;
40 Abort;
41 end;
42 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:
43
44 {===================================================================================
45 ==
46 This procedure will add an IN query statement from a list of values passed from a
47 TDBMemo into the SQL of a TQuery. Using an IN is far more elegant than several
48 Field = 'value1' OR Field = 'value2' statements.
49
50 ====================================================================================
51 }
52
53 procedure AddSQLList(lst: TDBMemo; //List you want to read from
54 const encSQL: TStrings; //SQL to add to
55 fldName: string; //The field to query on
56 AddAND: Boolean); //Add an AND to tail end?
57 var
58 I: Word;
59 valStr: string;
60
61 begin
62
63 //initialize vars;
64
65 valStr := '';
66
67 //Parse the list and make a CSV string out of the values
68 for I := 0 to (lst.Lines.Count - 1) do
69 begin
70 valStr := valStr + '''' + lst.Lines[I] + ''',';
71 end;
72
73 //Remove the trailing comma
74 valStr := Copy(valStr, 1, Length(valStr) - 1);
75
76 //Append the SQL IN clause with field name. If there is another
77 //SQL statement to follow, append an AND to the end.
78 if addAND then
79 encSQL.Add(' D.' + fldName + ' IN (' + valStr + ') AND ')
80 else
81 encSQL.Add(' D.' + fldName + ' IN (' + valStr + ')');
82
83 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.
|
