Visual Basic

Getting Data Back

This was an unexpected piece of luck. My data were coming more quickly than I could have reasonably hoped.
Sir Arthur Conan Doyle,
The Musgrave Ritual:
The Memoirs of Sherlock Holmes
All we want to display in the user interface is read-only data. So we confront the cursor decision, a danger to all clean-living developers who want to keep their language clean. Some database developer's suffer from a kind of Tourette's syndrome, and can only choose cursors. Most know, however, that cursors are a very expensive way to fetch data and are rarely used in production-class applications.

Though I had hoped to avoid it, I knew that I wouldn't be able to escape talking about cursors here. So let's look at selecting an appropriate data-access strategy. This is more than just picking between RDO, ADO, DAO, ODBCDirect, ODBC API, and proprietary APIs. It is even more than picking what kinds of cursors (if any) you will use. It is also about deciding when and where we will open connections to the database, how long we will hold them, when and how to use transactions and locking, and how we will get our data to all the pieces of our application in this distributed, component-based architecture we are considering.

First let's deal with the choice of data-access libraries. If you are starting a new project in Visual Basic 6, and you have no particular allegiance (i.e. you or your team are not very skilled in another technology and don't have a large investment in an existing code base), the choice is really a strategic no-brainer-choose ADO. However, if there are other factors (for example, you are starting with an MDB back-end and moving to an RDBMS when ODBCDirect suggests itself), weigh the decision up. As a little aide-memoire ("memory aid" for the uninitiated).

The latest version of ADO (ADO 2.0, which ships in Visual Basic 6) is fast. (Check out RDOvsADO in the sample code for this chapter.) It has a low memory and footprint size-although until OLE DB providers for your databases are available, you may have to use an existing ODBC driver along with Kagera [MSDASQL.DLL], the ODBC generic OLE DB provider. Some are shipping with Visual Basic 6 (Jet, Oracle, and SQL Server, for example). However, if you do need to use Kagera, this ups the total footprint size (and incidentally, it doesn't support independently addressed multiple resultsets). ADO 2.0 also now supports events, so you can connect and fetch data asynchronously and react to events when operations have been completed. ADO creates free-threaded objects that are speedy, especially for Web-based applications. If you are familiar with RDO, you'll find navigating the ADO object hierarchy a little different, mainly because you don't have to. Most important ADO objects can be instantiated independently. This seems more convenient, but under the covers many of the other objects in the hierarchy need to be instantiated-albeit not by you directly-and this necessitates more parameters on the instantiation methods you do have to call. ADO allows batch updates, as does RDO. (One of the most interesting things, from a tiering perspective, is the remotable resultset, but we'll come to that later. It has some particularly interesting ways of handling parameter queries).

And now for cursors. At the risk of trying to teach my intellectual betters, I have included a sidebar on cursors. Experienced database programmers will, or at least should, know about cursors. But a number of Visual Basic programmers are a little vague, and this information is basically a prerequisite for making an informed decision on a data-access strategy. If you are confident in your cursor knowledge, feel free to skip the sidebar-I always have difficulty deciding whether and when to read sidebars myself. If you are going to, now is a good time.

We're back from the valley of the cursors, and if you remember, we've dealt with getting the data we need down and into our user interface. Now, let's deal with changing and updating it.