A developer asked me today for advice on migrating an ISAM-type application to SQL. Reporting is straightforward; SQL query and result sets make that much easier than ISAM would. But how to handle an interactive program where the user looks up a record by some criteria, and then expects to be able to page forwards and backwards by clicking on
Prev and
Next buttons?
I'm not really sure what the best answer is, but thought I would write down some possibilities in case this question comes up again.
The first idea is to do a separate query limited to 1 result record for each operation. That's not the most efficient approach, particularly if the database server is across a network, but for interactive use it might be ok. (As long as the user doesn't hold down the
Prev button to scroll rapidly through hundreds of records.) One big complication here is how to handle duplicate keys. If for example the user looks up customer
Smith, what query do you use to simulate get-next or get-prev? The only answer I can think of is to not limit the query to just one record. If you use, for example
SELECT TOP 100 FROM customers WHERE last_name >= 'Smith' then you can at least advance forward through them (provided there are less than 100 Smiths). But if you hit the end of the result set on a name that has duplicates, it's tricky to select the next batch without overlapping the current batch. We might be able to ignore that problem for a typical program of the type being considered, by just forcing the user to start over with a new starting customer lookup when you hit the end of the result set. (This kind of interactive program isn't intended to allow unlimited scrolling through records; you'd use a more general query front-end to allow that kind of thing.)
So let's say that we want to allow the user to look up a customer and if it's not quite the right one, be able to do, say, a couple of dozen
Prev or
Next operations. The
Next operations we just described, relying on the TOP or LIMIT clause in the query to return a specified number of records. But how do you select a group of records
around the target record, i.e. both above and below it, so that you can use a
Next and
Prev operations (like you could with ISAM)? That's a tougher nut to crack, and actually introduces two separate hurdles:
1) Selecting N records on both sides of a particular target can be accomplished with a UNION clause in the query, but it makes the query a lot more complicated. Instead of something like:
SELECT TOP 20 first_name, last_name FROM sales.customers WHERE last_name = 'Smith' ORDER BY last_name
you have to do something along the lines of:
WITH C1 AS
(
SELECT TOP 20 first_name, last_name FROM customers WHERE last_name < 'Smith' ORDER BY last_name DESC),
), C2 AS
(
SELECT TOP 20 first_name, last_name FROM customers WHERE last_name >= 'Smith' ORDER BY last_name ASC),
)
SELECT * FROM C1
UNION
SELECT * FROM C2
ORDER BY last_name ASC
!!!
2) Then, instead of just advancing through the result set using the normal forward cursor, you need to explicitly specify the row # you want, both to start in the middle of the set on 'Smith', and to be able to go forwards and backwards. .Fortunately that's quite easy; first set the cursor type to scrollable (rather than the default forward only), and then set the cmdhdr.cmdarg2 field in the
SQLOP_FETCH_ROW call to the desired row #. So in this case, we'd start with row 21 (i.e. the first one >= 'Smith'), and then just add or subtract 1 to move forwards and backwards.
Another approach would be to use
SQLOP_FETCH_GRID to fetch the entire result set (maybe even the entire table) in one step into a gridmap and then use the same navigation technique just described. But note that if you want to start at a particular record which is somewhere in the middle of the result set (as in our Smith example), unless you something like the UNION trick above, there's way to go directly to that record, other than scanning the entire result set or gridmap until you locate it.
Either approach (and perhaps every approach) will probably involve considerably more code than the original ISAM version. So maybe before starting out down that path, it might be worth rethinking the user interface to be more naturally compatible with result sets rather than individual record get/next/prev operations. In this customer file maintenance example, allowing
Next and
Prev operations made sense when the only way to look up a customer was by a single key like name. But in the SQL model, you could easily create a customer lookup front-end using query features such as LIKE, CONTAINS, wildcards, etc. to display a batch of possible matches in a pick list that the user can select from. Of course that also will involve more user interface code, but probably allows you to eliminate a bunch of file selection, traversal, and maintenance code. And the result might actually be better for the user, rather than merely just-as-good as the ISAM version.
