Re: Equivalent of Get Next Extended to retrieve only unique keys?

Thanks again for your input, and sorry for not responding sooner; I've been sidetracked by various other things. Just a couple quick questions...

1. Do you know of any good resources for the ins & outs of accessing a btrieve database through SQL to supplement Pervasive's documentation?

2. You mentioned some database engines offering "high-precision" scrollbar functionality. What are some examples that you know of that offer this? I'm afraid that this may be the only viable option.

Jason Gilbert
Comp Pro Med, Inc.

Bill Bach wrote:
A few random thoughts:

1) The SQL interface will give you an easier way to sort the data by
ANY column, and JUST get back the data fields you want to see. This
usually slows things down (and users should be cautioned that if they
sort by a non-indexed field, it will take a lot of time, but you can
use this to make the UI a LOT more flexible. Just be aware that
performance will NOT be what you are used to, due to the SQL statement
overhead. Keeping the query simple (i.e. a single table when possible)
is the best idea.

2) Why do you retrieve one byte of each record? I believe it is ALSO
possible to retrieve 0 Bytes from each record, which my be more
efficient. Alternatively, you can retrieve the primary key or some
other identifying information for access.

3) Another possibility is to use the Percentage operations, which were
intended for scroll-bar functionality. Newer database engines have a
"high-precision" version of these operations, which actually may be
helpful in your application.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***


Jason Gilbert wrote:

Thanks for the reply. It was quite informative. Maybe if I provide
a little more detail about what this program needs to do, you might
be able to help me a little more with some ideas, since you obviously
know what you're talking about.

Our software deals with medical records, and we have some customers
with upwards of 500,000 records. The program I'm writing is designed
to give our customers an easier way to browse through those records
by providing a string-grid view of the database that allows them to
sort by various columns of data and to also filter on the columns.
To do this, my idea was to use the GNE function to load the position
info in the background (only retrieving one byte of each record), and
then when the user scrolls, use that stored info to jump to an
appropriate position. Since GNE also offers filtering abilities, it
seemed to be an ideal solution, since I could use the same technique
to pre-load my scrollbar info with only the info needed for records
that match the filters. Retaining the ability to filter on multiple
items would probably make the GetKey.

The hitch comes with another feature that's needed with this program;
the ability to group records by patient, so that the string grid
doesn't contain dozens of entries for the same patient. The idea is
to just display one line for each patient, and then have a little
plus button or something to expand the view to show all records for
that patient. This means that the duplication rate would be
relatively high.

Up until this point, we've never used the SQL capabilities of
Pervasive, but perhaps this will finally force us into it, but I'm
not sure how much that will buy us either.

Thanks again for any help you can provide,
Jason Gilbert
Comp Pro Med, Inc.

Bill Bach wrote:
Do you actually need the entire record, or can you get away with
just the Key Value? The usual way to implement what you are trying
is to use GetGreater calls repeated as frequently as needed. A
GetGreater with a GetKey Bias (+50) would give you just the key
values, but probably won't save you much in the way of round trip
requests (though it will save on the data transfer).

I cannot think of any way to do a "GG" on the GetNextExtended,
though. By its very nature, it is a GetNext function. If your
level of duplication is low, then perhaps you can do a regular GNE
and filter out the extra records. If you get TOO many duplicates
in a row, you can then do a GetGreater to skip the rest of the
duplicates. The code'll be ugly, to be sure...

Might be better off doing a simply SQL query:
SELECT DISTINCT(...) FROM ...
But you won't get Btrieve record positions from that type of query.
Goldstar Software Inc.
Pervasive-based Products, Training & Services
Bill Bach
BillBach@xxxxxxxxxxxxxxxxxxxx
http://www.goldstarsoftware.com
*** Chicago: Pervasive Service & Support Class - 07/2007 ***

.

Loading