What are the different cursor types that can we create using ADO Recordset?

What are the different cursor types that can we create
using ADO Recordset?
Difference between these cursor types?

Question Posted / guest

2 Answers
8986 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What are the different cursor types that can we create using ADO Recordset? Difference between..

Answer / akash

The CursorLocation and the Cursortype properties of the ADO
Recordset object determine what we are allowed to do with
our underlying cursor of data.

These properties help to determine things like whether we
can move through the data one time or many times, whether
or not we can get a count of the records up-front and even
whether or not we can bind our data to a grid.

The location and the cursor type also affect how well the
application will perform and how well you will be able to
scale this application. Scalability is a term used to
describe how well an application can take on new users
without losing performance. For example if I have an
application and I add 2000 more users and the application
still is as fast as it was with 20 users, this is a very
scalable application.

One very basic example of how the CursorLocation and
CursorType can affect the functionality of an application
is through the Move methods.

Have you have ever created an ADO recordset with just the
bare minimum of code then tried to use a Move methods to
move through the records? You may have found that MoveNext
works fine, but if you try to call the MovePrevious method,
you get an error.

Or perhaps you have tried to use the RecordCount property
to determine how many records are in the recordset and
received a –1.

Both of these are the cursor location and cursor type
properties in action.

Let’s take a look at creating an ADO recordset with some
very simple ADO code and see how the CursorLocation and
CursorType properties come into play.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Open "Provider=SQLOLEDB;Data Source=server;" & _
"User Id=user1;Password=MyPassword;" & _
"initial catalog=pubs;"

rs.Open "select au_fname from authors", cn

In this code we have done the bare minimum to create a
recordset. We declared our ADO connection and recordset
objects, instantiated them by setting them = to a new
connection or recordset object and then opened our
connection and recordset.

Notice that we did not set any properties for the recordset
object in this code except those that are absolutely
necessary.

If we attempt to MovePrevious in this recordset we will
receive “Error 3219 The operation is not allowed in this
context.” This is one of the most common issues that new
users of ADO see.

By default, when I create a recordset and I do not
explicitely set the cursortype property, we get a
cursortype of adOpenForwardOnly.

This means that I can only move through my records one at a
time in a forward only manner. Therefore MovePrevious gives
an error.

So what we see is if we do not explicitly set the cursor
type property we get a default value of adOpenForwardOnly
and can only make one pass through the records in a forward
direction.

Default values are there to help in ADO so you do not have
to set every value, but if you are uncertain what the
default values are, you can run into errors.

Let’s take a look at the default values on the CursorType
and CursorLocation a bit more closely.

Default CURSOR values
CursorLocation = adUseServer

CursorType = adOpenForwardOnly

LockType = adOpenReadOnly

When we create a recordset and do not specifically state
the cursorlocation, cursortype or the locktype we then
receive the default values for these properties.

The defaults are adUseServer for the cursorlocation.
adOpenForwardOnly for the default cursortype which allows
us to move through our records in a forward-only manner.

And although we are not going to discuss this specifically
in this webcast, it is worth noting that the locktype on
the ADO recordset object is set to adOpenReadOnly by
default. So if I tried to update our basic recordset, we
would get an error. For more information on the LockType
property please see MSDN.

So by default we get back a recordset that is server-side,
Forward-only and Read-only. A Recordset with these
properties is a very special recordset that is commonly
known as a FireHose Recordset or Firehose Cursor.

CursorType Property Values
- Can be set on or before the Open method.

rs.Open "select au_fname from authors", cn, adOpenStatic

(Or)

Rs.CursorType = adOpenStatic
Rs.ActiveConnection = cn
rs.Open "select au_fname from authors"

To create another cursor type, we set the CursorType
property on the ADO recordset object.

In the code we see two ways to set this property.

In the first line we set the third argument to the
cursortype we want, in this example we are requesting
adOpenStatic.

The second method shown may be a bit more readable but both
function in the same manner. And in this code I am also
setting this to adOpenStatic.

In addition to adOpenStatic shown we also have
adOpenForwardOnly our default, adOpenDynamic and
adOpenKeyset.

Each of these provide different functionality. Let’s first
consider adOpenStatic.

Cursor Types
Firehose Cursor
- Forward-only, read-only cursor

- Optimized for SQL Server™

- Records are passed over the connection in a stream

- Move methods give an error message

- RecordCount property is incorrect

The Firehose cursor is a forward-only read-only cursor. The
name Firehose was given to this since the records are
passed over the connection one at a time in a stream of
data very much like water going through a FireHose.

This is the type of cursor that SQL Server is optimized to
work with and this type of recordset in general is the
fastest performing recordest.

But with this performance does comes a limitation of
functionality. As we noted MovePrevious, MoveFirst and
MoveLast will return errors. The recordcount property will
return a –1. This makes sense because the recordset object
has no idea how many records are left in the stream on the
connection until it hits the end.

Actually calling this a cursor is really a misnomer because
a cursor is never really created in this scenario. We
simply get a very quick stream of records from the data
base.

With so many limitations on the methods and properties
available, it seems unlikely that a developer would choose
to use this cursor, but many do for the reason of
performanc. Since the records are streamed across the
connection, this makes this a very fast performing cursor.

Firehose cursors are also very scalable cursors.

So if you are looking for a quick way to provide data to
the user AND they will only need to make one pass through
the information, this is your best option.

If you need to provide more functionality to your user then
you will need to specifically request a different cursor
type and check to ensure that your data provider will allow
this.

Static Cursor
- Static copy of the data

- Cursor is populated with all columns in the results

- Can be updated

- Cannot see changes other users make

- Move methods and RecordCount work

adOpenStatic when used as the cursor type creates a static
copy of the data from the database.

Many developers think that because this is a static copy of
the data that this cursor type is not very useful. But in
fact, this particular cursortype is typically the best
choice for most applications.

One of the reasons that this type of cursor is often
overlooked is that there is a misconception that you cannot
update this. A static cursor when created fetches all rows
and all columns from the database and places a copy of this
data in a cursor. Since it is a copy many think this cannot
be updated, this is not true. A static recordset created
using ADO can be updated using the Update and UpdateBatch
methods.

Since it is a copy of the data any changes, additions or
deletions that are made to the records since the time the
recordset was opened cannot be seen. On the flip side of
that also since it is a copy of the data the MoveNext,
MovePrevious, MoveFirst and MoveLast all work as expected
as does the Recordcount property.

The static cursor typically provides good performance and
this is also the only cursor type that you will get back if
you ask for a client-side recordset. We will get to client
side recordsets in just a moment.

If we need to be able to see the changes that other users
have made to the records since the recordset was created,
we will need to use some other cursor type, one possibility
is the Keyset.

Keyset Cursor
- Only the keys of the records are returned

- Shows updates on the data that others have made

- Does not show other’s additions and deletions to the data

- Move methods and RecordCount work

The Keyset cursortype is by setting the cursortype property
to adOpenKeyset.

When this type of Recordset is retrieved the cursor
contains only the key values of the records that meet our
criteria will be returned.

This allows the recordset to show any changes that have
been made to the records since the recordset was requested
but records that have been added to or deleted from the
database will not be shown.

So when I have a keyset recordset and I begin moving
through my records, I move to a record, look at the
keyvalue and then find that record with an current changes
in the data store. I can move forward and back through the
list of keys and also get a recordcount of the number of
keys that have been returned.

So this particular type of cursor provides more
functionality than the last two, but I cannot see any
additionl or deletions. If I need to see these also I will
have to choose a Dynamic Cursor.

Dynamic Cursor
- Keys for the rows are retrieved

- Changes that other users make can be seen

- Keyset is re-evaluated with each client request

- Additions and deletions can be seen

The Dynamic cursor is most like the Keyset cursor in that
this returns only a list of the keyvalues that meet the
criteria of the query so I can see other user’s changed.
But the big difference here is that the Dynamic cursor will
reevaluate the database for any newly added or deleted
records with each client request.

This constant reevaluation of the membership of the cursor
impacts the performance of this type of cursor and causes
this one to typically have a high overhead and be slow.

All of these options are pretty clear and this sounds
pretty easy, you decide what you need to do with your
records and balance this with the performance that you need
from the recordset and pick the correct cursor type. But
there are limitations to the cursors that you can create
and when.

Limitations on CursorType
- Dependent upon the provider used

- Check the CursorType property to confirm

Rs.open "Select * from authors", cn
Debug.Print rs.CursorType

0 adOpenForwardOnly

1 adOpenKeyset

2 adOpenDynamic

3 adOpenStatic

- Dependent upon the CursorLocation

When you set the cursortype in your application, this is
simply a request that you are making. And it may not
indicate the actual cursor type that you receive. The
cursor type that you actually get in your application is
dependent upon the data provider and the database that you
are using and also the cursor location.

So let’s say I create a recordset and I request a keyset
cursor but I place this request to XYZ database. ADO will
ask the data provider for XYZ database if a keyset cursor
is available. XYZ database may come back and say No keyset
is not available, but I can give you a static instead. ADO
will then create the recordset using the static cursor and
continue through the program.

Some developers have asked why ADO does not give an error
in this situation. ADO assumes that you have checked the
data provider and the database and you are picking
something that you can get back.

You can check what is returned this by printing the
cursortype property after you open the recordset. If we
take a look at the code on this slide we see under the
second bullet point that I have opened my recordset and
then I do a debug.print statement and write out the
cursortype property. You will receive an integer value in
the the immediate window but you can check in your object
browser or on the listing above for the type of cursor you
are given. O = forward only, 1 = keyset and so on.

In addition to the data provider and the data base limiting
the cursor type, the cursor type is also limited by the
cursor location.

Is This Answer Correct ?

6 Yes

0 No

What are the different cursor types that can we create using ADO Recordset? Difference between..

Answer / pratik

•A Static Cursor doesn't reflect data changes made to the DB once the ResultSet has been created whereas a Dynamic Cursor reflects the changes as and when they happen.

•A Static Cursor is much more performant than a Dynamic Cursor as it doesn't require further interaction with the DB server.

•A static cursor supports both Relative and Absolute Positioning whereas a Dynamic Cursor supports only Relative Positioning.

•A Static Cursor can be used for Bookmarking purposes as the data returned is static whereas a Dynamic Cursor can't be used for the same

Is This Answer Correct ?

0 Yes

0 No

Post New Answer