Question:
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 tobuild Query By Form applications in it.

Answer:
The answer is an unequivocal “Yes!” But I’m not here to giveone-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 isthat is a proprietary feature of a some company’s developmentenvironment. It’s not. Think about QBF as an application designmethodology or paradigm. Anyprogramming environment in which you can build windows (forms) and inturn can access a database platform has the ability tobuild a QBF application. Admittedly, some products provide arelatively easy way to implement QBF over others, but if a productmeets 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 ofquestions 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 canimplement QBF. The concept of QBF can be appliedin numerous ways in Delphi, so I’m not going to talk to much about specificcases of coding. However, I will talk about certain techniques I’veused in Delphi when creating QBF applications.

Delphi is an ideal tool for doing QBF for a number of compellingreasons. 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 anideal tool for creating QBF applications.

Concept Revisited

I mentioned above that QBF implements an intelligent interfacethat has the ability to tranform user input requests into a data setof some sort. What is implied by QBF is that you use queries toperform the transformations, but I’m going to break stride here andsay that you don’t necessarily need to use queries to get your answersets. Why? Think about it for a moment. The whole purpose of QBFis to hide the complexities inherent to data retrieval languages fromthe user. All users care about is the end product: theanswer set. They don’t care about the back-end operations. In thatlight, we open up a bunch of doors to getting data to the user.

Forbrevity’s sake we won’t go into all the different ways to do QBF.What I will concentrate on here are two common, useful waysof doing QBF in Delphi: by Dynamic Querying and TTableSetRange.

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 ordata 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? Usuallythe parameter-ized variety of placing a query variable within a SQLstatement you preprocess with a Prepare, fill with a value, then execute. That’s a perfectly valid methodology to employin many cases. But for a lot of my own applications, I’ve found usingparameter-ized queries limiting in many ways. Youcan’t use a parameter in the FROM clause of a query. This means thatyou can’t apply the query to different tables that have the samestructure. For myself, I want to have ultimate flexibility, so what Ido is address the SQL property directly.

The SQL property of a TQuery is a TStrings type property. Ah! theold TStrings. That’s right folks, this is something many of you haveused time and again in your programs. As you may alreadyknow, a TStrings object is nothing more than an ordered collection ofstrings, each accessed by means of a zero-based index (meaning thefirst string’s index is ‘0’). So what’s so special about this respectto the SQL property of a TQuery? It all has to do with stringsthemselves. The most important thing is that strings can be easilymanipulated. You can pretty much dice and slice them any way youchoose. With respect to dynamic queries, the ability to manipulatethe SQL property is a boon to doing QBF. Let’s look at a sample of areal 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 statementis then tacked onto the end of the SQL TStrings object. Let’s look atthe 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 ifthis is a loophole in the compiler or not. One would assume that tochange something, you would pass it by reference. But this is not sowith TStrings. I’m waiting to hear replies from Borland and the folksa CompuServe. But rest assured, I’ve used this technique in bothversions of Delphi with no problems. My only concern is what willhappen in future versions of the compiler. In any case, the wholepoint to this discussion is that manipulating the SQL propertydirectly is much more flexible that using parameter-izedqueries.

QBF Techniques: TTable SetRange

Remember what I said above, that users don’t care how they get their data, they just want toget it? Especially with simple retrieval functions, you don’tnecessarily need to perform a query. Sometimes a TTableSetRange 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 touse the SetRange function itself, which combines theSetRangeStart, SetRangeEnd and ApplyRange functions in onecall. This is effective for setting ranges on the first index of atable. For other setting ranges on other index fields, you will needto explicitly use the three functions mentioned previously . Thehelp 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 ratherthan a real coding discussion. But you should remember that there’s alot more to progamming than coding. Programming is a really complexprocess that includes a lot of conceptualization and analysis. Over the years that I have been developing applications, I have foundthat I’ve become a much more effective programmer by paying attentionto the concepts that have been put before me, and using them as meansto approaching a code solution from different perspectives.