Introducing Recordsets | Ado Examples and Best Practices

Team-Fly

When ADO runs your query, it returns the qualifying rows using a Recordset object.^[1] However, you don't necessarily need to create a Recordset to retrieve information. You can run a query to return a set of results in the form of output parameters or simply as an integer passed back as the return status of a stored procedure. Output parameter information is all passed back in the Command object—not the Recordset.

What you need to understand is that ADO (and RDO, DAO, and the ODBC API) all return result sets that contains the answer to your SQL query—your question posed to the data provider. Result sets are generated whether or not your query includes a SELECT statement.

So, if your SQL statement simply executes an UPDATE SQL statement, a result set, but no rowset, is returned. The UPDATE result set simply contains the number of rows affected. But if your query executes a SELECT statement that fetches rows from the data source, the result set contains a rowset. Although this rowset might not contain any rows, it does include the Data Description Language (DDL) schema information from the table(s) referenced. If ADO finds a rowset in the result set, ADO generates a Recordset object to manage it—even if there are no rows.

For example, you can code the following routine to fetch the schema of a selected table. (This is one of the few times when SELECT * is okay.)

 rs.Open "select * from titles where 1=0", cn Debug.Print rs.State, adStateOpen, rs.EOF For Each fld In rs.Fields     Debug.Print fld.Name, fld.Type, fld.DefinedSize Next fld

Unbeknownst to the MSDN documentation, a Recordset is not simply a cursor. A Recordset is a COM object either defined in your code, or manufactured by an ADO method that might contain a rowset and quite a bit of DDL information about the origins of the data. This schema description describes where the data came from (the query, table, and field names), as well as including a bevy of other properties used to describe the data. The set of rows returned (the rowset) can be returned in the form of a cursor—a mechanism used to logically (and physically) manipulate the rowset. However, the default mechanism implemented to return the rowset is a cursorless result set, because it supports neither scrolling nor updatability. So, a Recordset object can return far more than just a cursor.

What's in a Result Set

Throughout the book I refer to a number of terms that might need clarification. While I've been known to make up terms from time to time, these terms are (mostly) from the textbooks I read many years ago when working with mainframe databases.

When you execute a query against any relational database provider, it responds by sending back a result set.^[2] A result set can contain:

0 to 1 rowsets. These are sets of rows returned by a SELECT statement in the query.
0 to 1 rows affected value. This is returned by an action query, such as an INSERT, DELETE, or UPDATE SQL statement.

If you execute a stored procedure, your result set can also return multiple result sets, which can each contain a rowset or rows affected values but can also contain:

0 to 1 return status value. This is the integer returned when you execute the RETURN TSQL statement in a stored procedure.
0 to n OUTPUT parameters. The upper limit of these parameters is about 1000.
0 to n COMPUTE or COMPUTE BY rowsets and aggregate totals.
0 to n PRINT messages.
0 to 1 RAISERROR messages and error codes.

A result set can be as simple as a single rowset or it can be highly complex, with many different structures, depending on the logic in the query.

Development Strategies

The first question you should ask at this point is, "Do I need to create a Recordset at all?" The answer is maybe. Recordsets are only needed if the answer to your question must take the form of a rowset. If I were to ask you, "How many bathrooms does your house have?" you would not provide me with a detailed list of the locations of those rooms and the color of towels hanging in each—unless you were Martha Stewart. You would probably just answer "two."

The same principle applies when asking the server for information. You don't always need to return the answer to your SQL questions in the form of rows. If you have situations where the query can be answered with a Boolean (True or False), a number (2), a string "none", or even a set of values, you can pass these back to your application or component using OUTPUT parameters or a variety of other techniques—especially if you are communicating with the database through an intermediate middle tier. Just remember that the Recordset mechanism is relatively expensive to create and manage, so its use should be weighed against the resources it consumes.

Creating Recordsets is not that hard. Too many times, however, I've seen queries that bring perfectly healthy systems to their knees. No, it's not usually one query that does the job. It's more likely to be a dozen poorly written queries that are run too frequently that cause the most degradation. While a lot of emphasis is placed (correctly) on the Recordset CursorType and LockType properties, more attention needs to be paid to what gets shoved into the Source property.

Basically, the Source property or Source argument of the Recordset Open method is the "question" you need to get answered by the data provider. It can be the name of a table, stored procedure, or SQL query. Constructing this query can be a challenge. Remember that each provider requires its own dialect of SQL—it's like someone from Minnesota talking to someone from Alabama. Although they speak the same language, there are times where "you betcha" doesn't come across in quite the same way.

Visual Basic has tried to make construction of SC (Syntactically Correct)^[3] SQL queries easier. However, my experiments have resulted in a couple of frustrating experiences that I won't share with you here. Instead, to make a long story short, it seems that what the Visual Database Tools thinks is an efficient Jet query is not what Access would construct for the same query against the same database—and I, for one, agree with Access' approach. Then again, the queries that Visual Database Tools generate for SQL Server seem to be fine—not only are they SC, but they adhere to the new ANSI JOIN syntax. I encourage you to try the four-paned Query Builder window (see Figure 6-1). This interactive graphical user interface (GUI) tool permits you to drag tables from the Data View window, select columns, criteria, and other filters, and see the generated SQL—as well as the rows returning from the query.

click to expand
Figure 6-1: VDT Query Designer

You can leverage this generated SQL in an ad hoc query or in the AS clause of a stored procedure. These queries can use parameters—simply reference a parameter marker "?" in the query (in appropriate places in the WHERE clause). While the Data Environment Designer can construct a Data Environment Designer object to execute the query, the problems associated with the Data Environment Designer keep me from recommending it for most serious development.

You can also use Microsoft Access' query Design window to drag-and-drop your SQL queries. After the query is constructed to spec, simply copy the query from the SQL window. I would not depend on the Visual Database Tools to construct these queries, as they do not use optimal SC SQL; they use common joins instead of the ANSI JOIN syntax.

However, being able to code a SC query is not the complete path to a scalable, high-performance application. In this case, it's not always how you say it; it's what you say. The query processor is not easily offended, so you don't have to worry about hurting its sensibilities.

Working with the Biblio Sample Database

All of the examples used in this book access either the SQL Server 7.0 Biblio database or the two Jet 4.0 databases. All three databases are supplied on the companion CDCD.

The Biblio database has been saved and written to disk using SQL Server 7.0's new sp_detach_db stored procedure, which releases control of the .MDF and .LDF (data and log) files. These two files must be "reattached" to your SQL Server 7.0 database to use them. Sorry, there is no Oracle, Jet, or DB2 version of the Biblio sample data.

To reattach these files, follow these steps:

Copy the two files (workdata.mdf and worklog.ldf) from the CD to a convenient place on your hard disk. These two files are located in the \Databases directory on the CD. (The code example just below uses "C:\ADO\Databases" as a target directory.)
Make sure you don't already have a Biblio database on your SQL Server If you do, you'll have to rename the existing Biblio database (recommended), drop it, or rename the new version (not recommended).
Run the SQL statement to attach the database files to the server, thus installing the Biblio database. The file attachbiblio.sql contains this SQL statement:
```
 sp_attach_db biblio ,'C:\ADO\Databases\workdata.mdf', C:\ADO\Databases\worklog1.ldf' 
```

After the database is installed, you'll discover it comes with only one UserID, Admin with the password pw. You can add yourself as a user (recommended), but if you do, you also need to configure the Biblio database permissions to let you have access to the database. You'll also want to modify the sample applications to use your UserID. No, I do not recommend using the SA UserID. The CD also contains an SQL script that can be used from Query Analyzer or ISQL to grant all permissions to your new UserID. Log on to QA, select the target database (Biblio), and execute the following stored procedure with your new user ID:

 :sp_GrantAllPermissionsTo <your UserID>

^[1]Qualifying rows means rows returned from your database table(s) that meet the qualifications described in the SELECT statement and its WHERE clause.

^[2]Result set: This is why we called the RDO object that handled result sets the Resultset object.

^[3]SC queries are designed to not offend the query processor's sensibilities (it's so sensitive!)—just as politically correct comments are designed to not offend the reader.

Team-Fly