Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
Can I build "Query By Form" applications using Delphi? Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
08-Jan-03
Category
DB-General
Language
Delphi 2.x
Views
147
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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.

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC