Thursday, April 18, 2013

Dynamic parametric queries

As I wrote the other day, there is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 rows in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. Although I have sorted out the problem of transferring that data to Excel, there remains the thorny question do I need to display all 4,000 rows every time I display the report? What if I only want receipts which have been issued since 01/03/2013 or cash receipts over 1,000 NIS?

What is needed is some form of preliminary screen with edit boxes which correspond to fields in the report. If the user places a value in an edit box, then the report (or more accurately, the query) has to take this value into account, whereas if the edit box is empty, then it should be ignored.

After some cogitation, I decided to use place a page control component on my form, with two different tab sheets. The first sheet would hold my preliminary, 'parameters', components whereas the second would hold the 'results' grid. I worked up a simple example with two edit boxes whose titles are 'from date' and 'to date'. The parameters sheet also has a 'show' button which builds the query, opens it and displays the results sheet.

My original query was in the form 'select <nine fields> from <some tables>'. The query has no parameters which is why 4,000 rows were being returned. I very naively wrote something like the following pseduocode which is executed when the 'show' button is pressed.
check whether 'fromdate' is empty or holds a valid date if not, exit check whether 'tilldate' is empty or holds a valid date if not exit whereflag:= false; copy the original query to string 'newquery' if 'fromdate' is not empty then begin if whereflag is false then begin whereflag:= true append ' where' to newquery end else append ' and' to newquery append ' receipts.curdate ' to newquery if 'tilldate' is not empty then begin append ' between :p1 and :p2' to newquery parambyname ('p1').asdate:= strtodate (fromdate) parambyname ('p2').asdate:= strtodate (tilldate) end else begin append ' >= :p1' to newquery parambyname ('p1').asdate:= strtodate (fromdate) end end else if 'tilldate' is not empty then begin if whereflag is false then begin whereflag:= true append ' where' to newquery end else append ' and' to newquery append ' receipts.curdate >= :p2' to newquery parambyname ('p2').asdate:= strtodate (tilldate) end
This mess did actually work but it was clear that it was far too wordy. As I wanted two paired values (dates and amounts) and two non-paired values (receipt number and payment type), it was clear that the above was not amenable to expansion. My first optimisation was to remove the 'whereflag' variable: it was needed so that I could append 'where' to the standard query for the first new statement, whereas every following statement would use 'and'. I realised that if I added the meaningless statement 'where 1 = 1' to the standard query, then every dynamic statement could start with 'and'.

The second optimisation came when I realised that being too clever and that using 'between' was counter productive: it would be easier to build the dynamic query if I had a separate clause for each edit box. In other words, instead of writing
and receipts.curdate between :p1 and :p2
I could write
and receipts.curdate >= :p1 and receipts.curdate <= :p2
This lead to the third optimisation which was checking all the edit boxes in one loop to see which were empty and which had values. This required setting the 'tag' value of each edit box. Once all these changes had been incorporated, the final code is as follows
procedure TReceiptsList.ShowBtnClick(Sender: TObject); const maxflags = 6; var fdt, tdt: tdatetime; i, tag: integer; flags: array [1..maxflags] of boolean; s: string; begin if (edFromDate.text <> '' ) and not TryStrToDate (edFromDate.text, fdt) then begin errflag:= true; EdFromDate.setfocus end else if (edTillDate.text <> '' ) and not TryStrToDate (edTillDate.text, tdt) then begin errflag:= true; EdTillDate.setfocus end; if errflag then begin laErrText.caption:= date_errtext; laErrText.Color:= clWhite; end else begin for i:= 0 to componentcount - 1 do if components[i] is TLabeledEdit then begin tag:= TLabeledEdit (components[i]).Tag; flags[tag]:= TLabeledEdit (components[i]).text <> '' end; flags[6]:= rg.ItemIndex > 0; s:= ''; for i:= 0 to querytext.Count - 1 do s:= s + querytext[i]; for i:= 1 to maxflags do if flags[i] then case i of 1: s:= s + ' and receipts.kabnum = :p1'; 2: s:= s + ' and receipts.curdate >= :p2'; 3: s:= s + ' and receipts.curdate <= :p3'; 4: s:= s + ' and receipts.price >= :p4'; 5: s:= s + ' and receipts.price <= :p5'; 6: s:= s + ' and = :p6'; end; with qReceiptsList do try disablecontrols; close; sdsReceiptsList.commandtext:= s; for i:= 1 to maxflags do if flags[i] then case i of 1: sdsReceiptsList.parambyname ('p1').asstring:= edKabnum.Text; 2: sdsReceiptsList.parambyname ('p2').asdate:= fdt; 3: sdsReceiptsList.parambyname ('p3').asdate:= tdt; 4: sdsReceiptsList.parambyname ('p4').asinteger:= strtoint (edFromRCP.text); 5: sdsReceiptsList.parambyname ('p5').asinteger:= strtoint (edTillRCP.text); 6: sdsReceiptsList.parambyname ('p6').asinteger:= rg.itemindex; end; open; finally enablecontrols; dbgrid1.columns[prevcol].title.font.color:= clRed; end; pc.tabindex:= 1; end; end;
I think that the loop which adds the conditions to the query should be merged with the loop which set the query parameters. It also occurs to me whilst editing this entry that instead of converting string values to integers (for the receipt amounts), I could pass them as strings and write parambyname ('p5').asstring:= edFromRCP.text, thus saving an unnecessary conversion. Improvements never end.

I should mention that this arrangement of preliminary parameters screen and final results screen works very well. The user can switch back and forth between the screens and issue new queries regarding receipts which return results at the speed of lightning (unless the user ignores all the parameters and requests everything!). There is at least one other screen in the program which will greatly benefit from this technique, but first I have to show it to the OP.

Blogger note: I am now using the <pre> HTML command to preserve code formatting. This saves me a great deal of time and means that I can use angle brackets without fear of them being replaced.

No comments: