RE: SETLL in SQL ?

From: David Foxwell

From Birgitta :

Executing the SQL statement CLOSE will only perform a hard close if the
SQL
statement is not reuseable.

What does not reuseable mean ?.

Yeah, that's a good question. From my testing, I know that new records will
appear in a cursor as they are added, so that's not an issue.

My problem was this:

All clients, companies and people in one client file. 2 LFs : one on the
person's name, one on the company name.

The subfile is cleared and recharged at every pageup or down operation, as
a
lot of users are adding and deleting clients all the time.

So if there's a pageup or down requested, the program does SETLL, READE
from the last row on displayed or SETGT READPE from the first row.


Now I've been asked to take into account a search on the client's maiden
name. Normally I would have created a 3rd LF. Unfortunately the procedures
will have to use another parameter and juggle now with 3 LF's.

This 3rd type of search, on the maiden name, would only be asked rarely.
There will be proportionally very few clients in the LF: only married
women!
I was just wondering about SQL that's all.

And well you should. This is a great example of a real world business
requirement that really reflects the strengths and weaknesses of both
approaches.

First off, you will need indexes, whether logical files or SQL indexes, on
all three fields. That's simply a given for performance reasons, and I
don't think the SQL folks will argue with me there, although more than
likely they'll suggest SQL because they perform better.

This situation has things that lean it more towards SQL and more towards
ISAM depending on your viewpoint. From a programming maintenance
standpoint, SQL is easier because you'd be able to simply modify your SELECT
statement and use pretty much the exact same code for all three fields. And
that really is the beauty of SQL; a lot of the work is done for you by the
SQL engine.

But from the standpoint of your logic, where you want to do a SETLL/READE,
there really is no equivalent. So if you wanted to stick with that logic,
you would have to recreate the cursor each time. But it seems to me that
you might not need the set logic at all; why don't you just read forward and
backward? In my experience, logical files automatically include new records
as they are added to the database.

But then if you can get rid of the SETLL, SQL cursors also include new
records, so we're back to the initial question and frankly it looks to me
like an SQL cursor would work fine in your particular case, since you really
aren't positioning to a specific key, you're only using the SETLL for paging
purposes, and I don't think you need to do that.

Joe