Here's a different kind of Smart Access article. In addition to some in-depth information on ADO and ODBC (including some comments from Kyle Geiger, the father of ODBC), it's a look at what life is like for a Microsoft Support Engineer.
This article describes some problems identified with the Access Web wizard. It also shows the dynamics between three Microsoft Developer Support Engineers in three related Microsoft technologies: Access, Active Server Pages, and Dynamic HTML. In this article and its two sequels, I'll describe the problems discovered with the Web wizard and show you how to fix them. I'll also show you how to extend the Web wizard beyond what it's built to do. If you're using Access to create Web applications or you're interested in ADO -- the next generation of data access -- you'll find a lot of cool stuff in these articles. Among other things:
|•||See how to improve the efficiency of Web wizard code, especially with respect to populating the combo box.|
|•||Get a detailed discussion of ADO cursor and lock types.|
|•||Find two anomalies when editing fields.|
|•||Learn how to trap for ADO errors.|
|•||See how to create a parameterized ASP form.|
|•||Replace the Web wizard's HTML Layout control with the hottest new technology around -- Dynamic HTML.|
Richard Dickinson is the Lead Access Developer Support Engineer for Microsoft. Richard and I met at his desk to discuss some problems that I felt I'd discovered while writing the article "An ADO/ASP Tutorial" for the April 1998 issue of Smart Access. Richard is the Beta Lead for a future version of Access that's so cloaked in secrecy that just sitting next to him lets me sense that what's coming is very cool.
Time is usually not our ally in Developer Support. As a result, Richard and I have developed a communication style that can only be described as "intense." We speak quickly, and our brains are always just a few steps ahead of our mouths. The result must look like a dubbed Japanese Godzilla movie. In this meeting, we focused on the issues I'd discovered with CursorTypes, the MoveNext method, and arrays.
It might surprise you to learn that, when it comes to figuring out how Microsoft tools work, we Developer Support Engineers are frequently on our own. We have liaison engineers who interact with the Microsoft development community directly, and, when necessary, we enlist the liaison's support for answers to questions we can't come up with ourselves. By and large, we rely on the program specifications, internal e-mail aliases, and our own resourcefulness to come to terms with some of the more mysterious manifestations of Microsoft technologies. The use of ADO cursors by the Web wizard is an example of the kind of problem we have to tease out on our own.
The developers of the Web wizard evidently wanted to use the cursor with the smallest memory footprint that also returned a RecordCount property. The ADO forward-only cursor is the most efficient cursor in ADO, but it doesn't have a RecordCount property. So the designers wrote the Web wizard to invoke a static cursor (a static cursor is similar to a Jet snapshot). Using a static cursor means that you don't see changes made by other users to the records that you've retrieved. Except the cursor wasn't static. The cursor that the Web wizard used was a keyset cursor (the ODBC keyset cursor is synonymous with the Access dynaset).
How did I know the cursor used by the Web wizard code was keyset? I had two pieces of evidence: 1) The opened cursor's CursorType property is set to 1 (keyset); and 2) The behavior of the form itself was indicative -- as you move around the table, you see changes made by other users. So the code asked for a static cursor and got a keyset.
Richard and I don't do all our collaborations over e-mail and at the office. Sometimes engineers just have to get away from distractions and focus. One of the best ways to do this is over a fabulous plate of pasta and some good Seattle coffee. That's exactly what we did -- he and I puzzled over cursors at a local Italian café we like. The puzzle at hand was whether the phenomenon of static cursors converting to keyset is by design or unintended by the developers of ADO and/or ODBC.
Richard and I held that if all cursors supported by ADO (including static ones) were updateable, then requesting a static cursor in your code should give you a static cursor. Some of the engineers we discussed this with didn't agree: If you wanted an updateable cursor, then ADO gave you an updateable cursor -- whether it was a static or keyset cursor didn't really matter.
Without a categorical position from development, Richard and I looked to two sources to try to figure out what was going on -- the documentation and the behavior of the product. First, here's what the documentation said. From the ODBCJT32.DLL Help file, we got these informative notes:
SQL_KEYSET_SIZE -- The only valid keyset size is 0, because mixed and dynamic cursors are not supported. If this value is set to any other number, it will be changed to 0 and the call will return SQL_SUCCESS_ WITH_INFO and SQLSTATE 01S02 (Option value changed).
Forward and static cursors are supported for SQL_CONCUR_READ_ONLY. Only keyset-driven cursors are supported for an fConcurrency argument of SQL_CONCUR_LOCK. An fConcurrency argument of SQL_CONCUR_ ROWVER is not supported. Dynamic cursors and mixed cursors are not supported.
We then turned on the ODBC Trace facility and tested some code. Throughout the log, we found the following error:
DIAG [S1C00] [Microsoft][ODBC Microsoft Access 97
Driver]Driver not capable (84)
When a locked static cursor was used, that error occurred after each of the following commands:
inetinfo e4:d9 EXIT SQLDriverConnectW
with return code 1 (SQL_SUCCESS_WITH_INFO)
inetinfo e4:d9 EXIT SQLSetConnectAttrW
with return code -1 (SQL_ERROR)
inetinfo e4:d9 EXIT SQLExecDirectW
with return code -1 (SQL_ERROR)
WCHAR * 0x002a8700 [ 23]
"select * from customers"
When a locked forward-only cursor was attempted, only the following two lines threw the "Driver not capable" error:
inetinfo e4:d9 EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)
inetinfo e4:d9 EXIT SQLSetConnectAttrW with return code -1 (SQL_ERROR)
What had we learned? The forward-only cursor didn't have any trouble when the SELECT statement was attempted. When the static cursor tried the same thing, it also succeeded, which surprised us until we looked closer at the log: Between the SQLSetConnectAttrW and the SQLExecDirectW commands, the ODBC driver changed the concurrency control type.
From this, Richard and I decided that ADO static cursors can be updateable, depending on the ODBC driver. In the case of odbcjt32.dll, for instance, static cursors aren't updateable, and forward-only cursors -- at least from the evidence we gleaned from the ODBC trace logs -- are updateable (when there are no violations of referential integrity by the update). However, since the odbcjt.hlp file clearly states that forward-only cursors aren't updateable, we were left with the final mystery of why the CursorType property says that the cursor was forward-only when the recordset's LockType said the cursor was updateable.
What had happened in the Web wizard? Since forms are clearly intended to be able to update the records they display, the developers wrote their code to invoke optimistic locking. Since any LockType other than Read-Only can cause the ODBC driver to return a keyset cursor, the Web wizard doesn't end up with a static cursor, no matter what the code says. It's not clear that the developers knew that this combination of CursorType and LockType would result in ODBC giving them a keyset instead of a static cursor.
The last problem Richard and I had to deal with was that we talked so much the pasta got cold, and I had to leave because I received a page from another engineer that 7X24 support services were required.
I'm going to pause my story here to describe the implications of the ADO keyset cursor's ability to show you records that reflect the changes that other users have made.
A recordset's CacheSize property controls when records changed by one user will be seen by other users. By default the CacheSize is 1, and the code behind the customers.asp form uses that default. With a CacheSize of 1, records are fetched from the database to the PC one record at a time. Even if the form were a list view where you could see more than one record, there would still be only one record fetched from the database at a time. If you move forward or backward in the form (or forward or backward one "page" in the list view), you'll get the data for that page by refetching it, record by record, from the data provider. This means much more network traffic than if you fetched, say, 10 records across the wire at a time. But once you've retrieved those 10 records into local memory, you won't see any changes made to those records on the server. So, increasing the CacheSize makes the network happier and the users (potentially) unhappier.
You can experiment with this property using the accompanying Download file, which was also included with my April article, "An ADO/ASP Tutorial." First, enable the "Browse in a new process" property of the Advanced tab from the View | Internet Properties menu. This ensures that each time you page forward in the form, you generate a new session ID. Without this setting, you can end up sharing an ASP Session variable over each of your visits to the site. Now, at line 38 in customers.asp from the April download, add the following line:
response.write "Cachesize of " & rs.cachesize & _
" being used in session " & session.sessionid
With that done, open two instances of Internet Explorer and load customers.asp into both instances. In one of the instances of IE, change some data in the first record displayed in the form. Then switch to the other version of IE, where the same record will already be displayed. You won't see your change, because the data has already been fetched to local storage. In that second instance of IE, move to the second record in the form, and then back to the first. ASP refetches the record, and you see the changes that you made in the first version of IE. To see the results of changing the CacheSize property, alter customers.asp by inserting this statement before the rs.Open method:
Repeat the experiment. You'll find that you have to page four records ahead before you can page back and see the changes to the first record. A cursor with a CacheSize of 1 is called a "thin" cursor. Larger CacheSize values create a "fat" cursor.
Knowing what I did about the Web wizard's keyset cursor, I next pursued the question of why the Web wizard adds a refresh button to each form. A CacheSize of 1 guarantees that all changes will be visible, so why include the Refresh button? Richard's answer was, "to handle that point where you've been looking at a record for a long time and need to verify that it hasn't changed before editing it. Also, this is historically the way Jet's cursor model works; at the time the wizard was written, the full ADO cursoring behavior was not complete."
Before paying for the best chicken scampi I ever ate, Richard and I agreed that we'd better arrange a lunch with our colleague on the ADO Test team, Jim "Jimbo" Wilson. However, Jimbo is totally absorbed in getting ADO 2.0 Release Candidate out the door (he's already spending his "spare time" working on getting ADO 2.1 Beta ready). Sometimes I wonder if there's ever a good time to interrupt these guys, and we had to accept Jimbo's rain check.
Luckily for me, I'd hedged my bet with Jimbo and had sent an e-mail to the Father of ODBC himself, Kyle Geiger. I knew if I asked Kyle directly ("Kyle, are static cursors updateable?"), he'd give me a clear and concise answer (traits I'll always be jealous of). I was correct. Here's Kyle's e-mail:
From: Kyle Geiger <kyleg at integrityds.com>
To: Michael P. Corning <mcorning at geckotech.com>
Date: Monday, April 06, 1998 10:03 AM
Subject: Re: static cursors
Hope all is well with you. The answer to your question is, unfortunately, driver dependent.
1) Yes, in theory it is possible for a static or snapshot cursor to be updated. However, ODBC neither mandates nor prohibits support for updates for static cursors.
2) Strictly speaking, an update could be supported, though by definition (i.e., the values, ordering, and membership of a static cursor cannot change while the cursor is open) the application issuing the update would not see its own update reflected when scrolling to the updated row! In practice, this would be fairly useless, so I imagine any drivers that do support updates through static cursors would, in fact, see their own updates. Depending upon your point of view, the fact that ODBC does not prescribe exact behavior here is either wonderful flexibility or sloppy engineering (I've heard both points of view).
3) The underlying implementation of updates is tricky. The easiest way to support static cursors is to materialize the results into a temporary table either locally or on the server (SQL Server 6.5 does the latter). So all normal rules of locking and concurrency control are abandoned in the original data. Applying an update correctly would require the DBMS and/or driver to respect proper transactional semantics with respect to the open cursor (in memory and whatever portion might be persisted to disk somewhere) and all other transactions.
In ADO, if you want to be able to see changes made by others without closing and re-opening the cursor (or doing a Requery), you should use a keyset cursor. And, depending on your ODBC driver, if you select static/updateable you'll get a keyset/updateable cursor, anyway.
Space doesn't permit me to include the contributions made by others at Microsoft as Richard and I did our due diligence tracking down firm and clear statements of position on the issues raised by our research. Let me add here that there are few places on the planet as tightly knit and wondrously wired as Microsoft. For all of us, Exchange Server is a mission-critical application. I still think our success as a creative organization is the greatest confirmation of the Law of the Network: The value of a network grows polynomially with the number of nodes.
Next month, I'm going to take a look at how ADO and ODBC cursor types can be exploited. I'll point out a major problem with error handling in the applications created by the Web wizard, and I'll also show you how to rewrite some code that the wizard produces to get substantial performance gains.
Read about the download ADOTUTOR.ZIP on this page
Michael Corning is an ASP/DB Developer Support Engineer at Microsoft Corporation. He co-authored (with Steve Elfanbaum and David Melnick) the best-selling Que title, Working with Active Server Pages.