what is: CursorLocation Parameter?

[jamest85]

Hi:
I am trying to understand: CursorLocation Parameter
1. adUseServer
2. adUseClient
3. adUseNone (this is obsolete)

From a book: "A cursor is a memory structure, somewhat similar to an array, Recordsets make use of cursors, and several different kinds of cursors are available in ADO"

my question is: why we need adUseClient and adUserServer for the cursor location?

Thanks a lot.

JT

 

[Banana]

This is kind of hard question because this depends fairly a lot on what provider you are using. But I can give you a general idea.

First, the big picture so we don't see trees for forest.

When client talks with server, both need to know how to reference to records stored on records and where they are. There are several ways to do this, from having a read-only, forward only recordset to totally scrollable and dynamic recordset.

When we designate a server side cursor, we are basically saying that server is responsible for remembering where the client is at which row for a given recordset. It may then prepare the data for surrounding rows to improve the performance. In case of a forward-only recordset, server merely gets list of records in a certain order and sends the client a row from that list, and cross it off. That cannot be backtracked (hence forward only).

OTOH, having a keyset-driven recordset means that server has a list of all keys to help the client navigate to any place within the recordset at any time.

Now, with client-side cursor, it means that client takes the responsibility of managing the position within the recordset. Depending on what provider you use, you may be only confined to Static recordset which let you move to anywhere in recordset but cannot see others' edits or updates unless you refresh the recordset, or may have the Keyset, which server gives you just a list of keys to navigate around.

I hope that helps.

 

[KenHigg]

This is kind of hard question because this depends fairly a lot on what provider you are using. But I can give you a general idea.

First, the big picture so we don't see trees for forest.

When client talks with server, both need to know how to reference to records stored on records and where they are. There are several ways to do this, from having a read-only, forward only recordset to totally scrollable and dynamic recordset.

When we designate a server side cursor, we are basically saying that server is responsible for remembering where the client is at which row for a given recordset. It may then prepare the data for surrounding rows to improve the performance. In case of a forward-only recordset, server merely gets list of records in a certain order and sends the client a row from that list, and cross it off. That cannot be backtracked (hence forward only).

OTOH, having a keyset-driven recordset means that server has a list of all keys to help the client navigate to any place within the recordset at any time.

Now, with client-side cursor, it means that client takes the responsibility of managing the position within the recordset. Depending on what provider you use, you may be only confined to Static recordset which let you move to anywhere in recordset but cannot see others' edits or updates unless you refresh the recordset, or may have the Keyset, which server gives you just a list of keys to navigate around.

I hope that helps.

Thats pretty good, Thanks - You wouldn't happen to any additional resources to do some more reading on it would you -

 

[Banana]

Ehhhh....

It was bit here and there.

Listing the sources:

Access Help (I sometime just type in words I find in Object Browser and read up on it to see if it's relevant to my needs)
MSDN (practically verbatim to Access Help, but easier to navigate)
Programmer's Guide to .... (ADO, DAO, Jet Engine) from Mircosoft. (they have a few chapters available for reading)
Alison Baltier's Developing with Access 2003 (great reference book and good way to get the basics in place)

HTH.

 

[jamest85]

This is kind of hard question because this depends fairly a lot on what provider you are using. But I can give you a general idea.

First, the big picture so we don't see trees for forest.

When client talks with server, both need to know how to reference to records stored on records and where they are. There are several ways to do this, from having a read-only, forward only recordset to totally scrollable and dynamic recordset.

When we designate a server side cursor, we are basically saying that server is responsible for remembering where the client is at which row for a given recordset. It may then prepare the data for surrounding rows to improve the performance. In case of a forward-only recordset, server merely gets list of records in a certain order and sends the client a row from that list, and cross it off. That cannot be backtracked (hence forward only).

OTOH, having a keyset-driven recordset means that server has a list of all keys to help the client navigate to any place within the recordset at any time.

Now, with client-side cursor, it means that client takes the responsibility of managing the position within the recordset. Depending on what provider you use, you may be only confined to Static recordset which let you move to anywhere in recordset but cannot see others' edits or updates unless you refresh the recordset, or may have the Keyset, which server gives you just a list of keys to navigate around.

I hope that helps.

Woo, this is really helpful, thanks a lot.

JT

 

[The_Doc_Man]

I'll add that in ORACLE and on the old ShareBase machines (now defunct), that nomenclature (cursor) as a reference to a position in a recordset was in use 15+ years ago, so this is not a new concept. All it really means is, it is a placeholder data structure with lots more options than you really needed.

I would say that in Word we would call it a bookmark. In databases we would call it a cursor. EXCEPT of course for Access, which also refers to "bookmarks" in recordsets. Leave it to Access to have non-standard nomenclature yet again.

 

[gemma-the-husky]

in languages like c there is a data type called a pointer, which points to an area of memory or similar, and can be referenced directly, and manipulated in record structures, so you can get chains of pointers, and pointers to pointers.

Unfortunately VB doesn't have a pointer type, so you can't manipulate them directly in VB, which is a shame as they are very useful in some situations?

So, is a cursor implemented as a pointer, or is it something more?

 

[LPurvis]

Just to go on a bit more about the decision process between the two.

Banana's description of a the cursor types essentially says it all - but you're only going to get that choice in a server side cursor. (The actual choices and returned types if you don't correctly adhere to those choices varies with the provider employed).
With a Client side cursor, I'm aware of no provider that returns other than a Static cursor type. (But I don't use that many really).
While this may appear limiting - it's due to the fact that, client side, ADO is providing the cursor service. Upon opening the recordset you're copying the actual data locally into that ADO cursor service. (As you can imagine - if that's a big recordset - then that's a big memory footprint and time to execute).

So why would you ever do this?
Well - one main advantage of client side cursors is that they can be disconnected from their opening connection (by the virtue of that fact above).
This allows you to hold a set of records locally in memory - without maintaining a connection open on the server. (Depending slightly upon the statement you've used to open the recordset - there's a bug in ADO this regard, but it's small and fairly unlikely to be encountered).

Just don't confuse Client Side with the resulting Static Cursor type.
Loading that data locally is a result of the service being Client Side. Filling of the cursor with all records is due to the Static type (so it could still take a good while to fill - it's just not ADO supplying the service and memory).
It's actually quite hard to get a Static cursor type when working with a Server Side cursor - but it's possible. And so should only be done with good reason and an awareness of the consequences.

(As always with client server development - be kind to your server and network!

Bear in mind through all of this that, in Jet, the db engine is running on the local machine anyway. So even a server side cursor is running locally - just under the jurisdiction of the engine.

Cheers.

 

[LPurvis]

Just to mention briefly the Bookmarks, Pointers thing.

If anything were to be thought of as the pointer - I'd say it would be the recordset itself. The cursor service does the heavy lifting which supplies the recordset with it's required results.

And Bookmarks. As in the DAO concept? Well - I'd say it'd be more fair to be levied at DAO as an implementation of convenience rather than Access (though partly Jet - mainly because it supports them). I don't see the implication from MS as having a Bookmark be Jet's answer to a cursor. Though, I suppose, perhaps a simplification (dumming down? loL) of the means of navigating within a recordset (and hence cursor).