次の方法で共有

ADO Cursors

  • [アーティクル]

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.

Anytime I specify a cursor location of adUseClient I get a static cursor no matter what I have specified in the cursor type property.

CursorLocation Property

- Determines where the records are stored

- Determines where the processing of the records takes place

- Two different locations

Client

Server

- Default is adUseServer

The cursorlocation property of the ADO Recordset object determines where the records are stored and where the processing of these records will take place.

There are two options available for the cursor location, adUseClient and adUseServer. Each of the options has certain functionality the back end database.

If you take a look in the code sample on this slide, we again have two ways to explicitly set the cursor location. The first is using 4th argument on the Open method of the recordset object and the second is to pull out the cursorlocation property and set this. In this code we are setting this to adUseClient.

Just like the cursortype property the cursorlocation has a default value so this does not have to be set in order for your code to work. The default value for the CursorLocation is adUseServer or server-side cursors.

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

(OR)

Rs.CursorLocation = adUseClient
Rs.ActiveConnection = cn
rs.Open "select au_fname from authors"

Server-Side Recordsets

- Dependent upon the OLE DB Provider and the data store

- Not available with all data sources

- Some providers will simulate this functionality

- SQL Server allows true server-side cursors

Server-side cursors or recordsets are the default and that sometimes cause confusion. When you begin researching ADO guidelines and even when we discuss some of this guideline here you will notice that we say client-side is better. But server-side is the default.

A server-side recordset is a recordset where the results of the query are stored and processed on the server. So in an application where I have a client machine that uses ADO to request a cursor of records using a cursor location of adUseServer. The application makes a call to the database and requests the records, the records are then retrieved and stored in the cursor on the server machine. This uses the resources of the server machine to store and process the records. You can imagine that this can cause performance issues for your server.

Since with a server-side cursor you are expecting a database to have certain functionality, the ability to create a server-side cursor is dependent upon the database you choose. SQL Server allows true server-side cursors, but Access does not. You will want to consult the documentation of your particular database to determine how server-side recordsets are handled. Some database will disallow the use of server-side cursors and some will simulate this functionality.

Many developers immediately begin to create their application using server-side cursors because these cursors are very fast and this is the only way that you can create a recordset with a cursor type of Keyset, or Dynamic. So the developer determines that is is necessary to see other user’s changes to records so he makes the decision to use a keyset cursor. He then creates this server-side but has not considered the number of users of the application.

Remember that server-side cursors rely on the resources of the server to run. So let’s say we have 500 users of our application all creating server-side recordsets at the same time, as you can imagine this could cause a performance problem on the server.

So perhaps the developer should have considered a different design using Client-side recordset instead.

Client-Side Recordsets

- Records are fetched from the data store and processed on the client machine

- Very scalable

- Can only get a static CursorType

- Can be updated

When we request a client-side recordset the requested records are streamed back to the client machine and if we check the cursortype property we will always see that we are getting a static cursor.

When we create a client side ADO recordset, ADO passes our query to the appropriate OLEDB Provider and a copy of the data is returned to the client. ADO then takes these results and stores them on the client machine in ADO’s very own client cursor engine therefore we must have a static cursor that is maintained on the client. And as we have already discussed our static recordset is scrollable and updatable.

Since the processing of the records is taking place on the client, in our last example where we had 500 users, we now have this overhead spread out on 500 client machines rather than taking place centrally on the server. Client-side recordsets are a much more scalable solution than serverside.

Although we will not go through this specifically, it is good to know that when using client side recordsets although they are scalable they also bring concurrency issues to the table in our design plan. Concurrency issues can occur when two user make changes to the same record and then try to update these changes back to the database. The database has to be told how to handle this situation.

Putting concurrency issues aside, client-side cursors are a good choice for most applications and they allow the database to do what it was designed to do, store information and process queries to return information.

It is difficult when designing an application to determine how all this works together and what the best options are for you. And the worst part is that no one can tell you specifically what you should do. But in general there are questions we can ask and guidelines we can use to try and get the best possible design.

More References

194973 PRB: ADO: Recordcount May Return -1
https://support.microsoft.com/?id=194973

253248 PRB: Setting the Sort Property of an ADO Recordset Object Causes Error 3251
https://support.microsoft.com/?id=253248

194610 How To Demonstration of ADO Bookmarks and Move Methods
https://support.microsoft.com/?id=194610

306385 PRB: CursorType Returns adOpenStatic When You Request adOpenDynamic from
https://support.microsoft.com/?id=306385

278408 PRB: Cannot Bind OLEDB DataGrid Control to Recordset That Does Not
https://support.microsoft.com/?id=278408

Comments