Can I build "Query By Form" applications using Delphi?

Problem/Question/Abstract:

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.

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.