Ideas Needed for Quering Sql Backend

Hi, after reading one of Olaf's comments on another one of my question, he mentions CursorAttach and Detach that may work with this issue like:

1. Get 1st cursor (query hits) that matches the users query, (scoped to the first set of buttons),
2. When pressing the "NextPage" button, (scoped to the 2nd set of buttons)
a. Set a public or form variable to identify what record where we're at so we can get back... lcCurrentRec=PartyName.identity_id
b. Set a public or form variable to store the indexkey that would identify its position in the book/library) lcLibraryPosition=lib_sort
b. Issue a CursorDetach() to set this query hits cursor aside,
c. Using the same CA and cursor name issue another select for the next page using the library sort index with lcLibraryPosition + (whatever represents a next)
d. If user clicks "NextPage" again, then reset lcLibraryPosition=lib_sort for current record, and do item "c" again.
e. If user clicks "NextHit", then "use in select('crsCursor')" to close the single record cursor,
f. Issue a CursorAttach() to reinstate the previously detached cursor and issue a skip 1 to show the next hit record that matched the users query...

What are your thoughts about this, will it work as described?

Thanks, Stanley

Stanly,

The root of the problem is that, in a "real" SQL database, there is no such thing as the "current" record, and therefore no such thing as the "next" and "previous" records.

The usual solutions to the problem is one of the following:

1. Retrieve the entire table into a local VFP cursor. Navigate the cursor using your "next", "previous", etc. buttons in the usual way. This is the simplest, but it is the most inefficient, because it means retrieving all the records, regardless of which ones you actually want to view.

2. Retrieve a smaller cursor, containing ust the primary keys of all the records, in the order in which you want to view them. In your "next", "previous", etc. buttons, navigate that cursor. Each time you go to a new record, use the primary key to retrieve the actual record. This is overall more efficient than 1., but there will be a delay each time the user hits the navigation button, which might not be acceptable.

3. Change your user interface. Instead of "next", "previous", etc. buttons, retrieve each record in its own copy of the form. Create an efficient search function to let the user find a specific record. To find another record, they do another search. This is more in line with a true client-server system, because it does away with the concept of navigating to the "next", "previous", etc. record But it means more work in re-designing the UI for an existing application.

In all my applications, I use method 3. It gives a cleaner, more logical interface, which users generally prefer. But it's easier to implement if you are starting an application from scratch, rather than grafting it onto an existing app.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

You can do paging easier, if you simply change WHERE claus of the SelectCmd and REQUERY(). You lose the page you currently selected, if that matters then you can use the Detach/Attach solution. But you can also use a secondary reader CA to read a page and simply APPEND it to the pages cursor you have of the main pages ca. If it's just for reading, you can also just have the one reader CA and always add its result to the cursor you display and navigate in it.

Idea:
Create Cursor pageCache (bookid, pageno, page)
index on bookid tag booknav && to locate/seek a record of a certain book
index on bintoc(bookid)+bintoc(pageno) tag pagenav && have an index to skip through one book in all retreived pages so far, no matter in what order they are appended!

You need a CA class named "cPage" with userdefined bookid and pageno properties and SelectCmd="Select page from books where bookid=this.bookid and pageno=this.pageno" and alias "currentpage":
oCa = createobject("cPage")
oCA.bookid=...
oCa.pageno=...
oCa.CursorFill() && retrieve one page
Insert Into pageCache Values (curRentpage.bookid,curRentpage.pageno,curRentpage.page)

You never display the CA cursor in a grid, but you display pageCache, a cursor classically created by CREATE CURSOR. The grid won't reconstruct this way, you collect all pages retrieved and can skip forward/backward. You have to implement some logic telling you, if you skip to another book and then skip back, or use one pageCache cursor per book. Whatever. Once you start using CA you don't need to do everything with just CA cursors. You're still free to combine what is making sense.

If a user should be able to edit pages and save that back to the database, the Detach/Attach would be an option, but also not very easy to maintain, you would like to have a way to merge new pages into a central CA cursor like pageCache, only connected to the database. That could be done, by having a second CA for that. If you insert pages into it's cursor, they count as new records, and the CA writing back later, trigfgered by TABLEUPDATE() would then add that page to the backend data instead of updating it. But you have SetFldState() to manually override the state of the record to be interpreted as retreived from the backend instead of being interpreted as new. So you can mark these "new" pages as coming from the backend, as they did, just through another CA. When a user then edits these, they get updated.

In the readonly case I'd still rather use SPT overall.

Bye, Olaf.

Hi Mike,

>> The root of the problem is that, in a "real" SQL database, there is no such thing as the "current" record, and therefore no such thing as the "next" and "previous" records.

Yes, I know that, but as I said, the user does a query for "red oak tree" and that produces a cursor where there is the concept of a current, next and previous records. These records make up the "query hits cursor that the ca produced" and represents the records the user is interested in viewing as they match the query. So, at this point we are using vfp cursors with all its features.


>> 1. Retrieve the entire table into a local VFP cursor. Navigate the cursor using your "next", "previous", etc. buttons in the usual way. This is the simplest, but it is the most inefficient, because it means retrieving all the records...

Not possible as currently there are over 3 million records and each record has 2 ocr fields that contains between 2000 and 4000 characters, and this is done over the wan, with a sql backend and a vfp frontend.


>> 2. Retrieve a smaller cursor, containing the primary keys of all the records, in the order in which you want to view them. In your "next", "previous", etc. buttons, navigate that cursor. Each time you go to a new record, use the primary key to retrieve the actual record. This is overall more efficient than 1., but there will be a delay each time the user hits the navigation button, which might not be acceptable.

Still not doable, because the user needs to be looking at real data from the first query, which is what I described in message 2 above. By doing #1 above I get all the info the user expects to see and the data that I need to get the pages immediately before or after the current page.


>> 3. Change your user interface. Instead of "next", "previous", etc. buttons, retrieve each record in its own copy of the form.

I do not understand what you are saying here with: "retrieve each record in its own copy of the form"

The user interface has to have 2 sets of navigation buttons, one that contains a top, prev, next, and bottom buttons that navigates the "query hits cursor from the 1st query that contains the matches the user is interested in". The 2nd button set contains 2 buttons, "PageBefore" and "PageAfter". The 2 buttons pulls down on demand the page immediately before or after the currently viewed one, depending what button was pressed.

>> Create an efficient search function to let the user find a specific record. To find another record, they do another search. This is more in line with a true client-server system, because it does away with the concept of navigating to the "next", "previous", etc. record But it means more work in re-designing the UI for an existing application.

This is too restrictive and would make for a bad researcher experience as they have to do too much work. I am forced to craft a solution that gives even a better experience than my current vfp app they currently use. My vfp app also supports full text searches and lands them on pages that matches the query.


What are your thoughts about the layout I mentioned in the 1st message I added to the question above, just before your message.

Thanks, Stanley

Stanley,

Sorry, I didn't take in your second post. I think Olaf has answered it better than I can.

Let me just clarify my point about opening each record in its own copy of the form (although I appreciate this might not be relevant to your original question).

What I'm saying is that you should do away with the First, Previous, Next and Last buttons (the so-called VCR controls). This is mainly a user interface issue, not directly releated to client-server (although it's more important in a client-server system than one that uses DBFs).

Instead, each time the user wants to view a record, open a new form for that record. Instead of having, say, a customer form which lets the user navigate to Customer A, Customer B, etc, you have Customer A opening in its own form, ditto for Customer B, and so on. (Of course, it's the same actual form class each time.)

You might not like the sound of that, but it's much closer to the way users work in the real world. It's also how they work in many other applications. In a word processor, for example, you don't open a document window and then let the user step backwards or forwards through all the documents. You open a "Memo re AGM" document, or a "Recipe for carrot cake" document, or whatever.

The issue then becomes: how does the user tell the system which customer (or whatever) they want to open? That's why I said you need to focus on providing an effective search. There are other things you can as well. In my own apps, I have a "recently viewed" menu, which works on the basis that if the user is working in Customer A's record now, chances are they might want to do so again in the near future. I also have a "favourites" menu, where users can bookmark the customer they are currently working on.

I know this goes against the grain of traditional VFP applications with its VCR-style buttons and the concept of moving a record pointer. I can only say that I have used this interface on serveral major projects, and the users universally find it more intuitive and appealing.

As I said above, this doesn't directly address your question, but I hope it will be relevant to yourself and other developers who are desiging a UI for client-server apps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy