Chapter 15, "Introducing ActiveX Data Objects," was devoted to the significance of the ActiveX Data Object (ADO) innovation. We hope that you now feel as strongly as we do that ADO change everything about how Internet programming will evolve and who will be doing most of it. Specifically, we believe that full-scale n-tier client/server programming will be adopted widely. Further, we believe that more of this kind of programming will be done by power users than could ever be done with traditional client/server development tools.
Knowing that something incredibly important has come along can be frustrating, though, if you don't know enough about it to get involved in the revolution. This chapter's mission is to ensure that you know which of ADO's dozens of features are the most important and why. The chapter was written for the ASP developer who wants to understand ADO, not merely use it. In Chapter 17, "Working with ADO's RecordSet Object," you will see a similar bifurcation: There is more to database programming than throwing code at an application. If you don't know what you're doing, it will be your undoing.
The strategy in this chapter, then, is to cover ADO's key features, the ones you will use all of the time. You might apply to this chapter the old scholastic truism that "Nothing is taught but for example." You will read text that describes the objects and their constituencies and that provides practical examples of how they work, as well.
Finally, in the chapter we use a powerful graphical formalism to model the model. The tools offered are for the developer who not only needs to know how one object works but also how that object affects all other ActiveX Data Objects.
You are about to enter a doorway to a new world on the Web. Many of those who have been on the Internet for some time have been waiting for this moment. Your day has come.
In this chpater you learn:
An object model as complex as ADO's needs a good modeling language. Integration Definition For Function Modeling (IDEF0, pronounced IDEF0-Zero) fits the bill.
The first intrinsic object in ADO is the Connection Object. Everything in ADO happens inside a connection.
Command Objects are extremely efficient, flexible, and easy to use. Learn how to shave hours of your ADO development time.
This chapter steps to the next level of complexity in the presentation of ActiveX Data Objects. Because there are three intrinsic objects in ADO, you will learn about the ADO object model from the perspective of each intrinsic object: Connection, Command, and RecordSet. The ADO object model is huge, and the application of it to the various data providers is so rich that to cover it exhaustively would mean writing a separate book.
To make this part of the book as practical as possible, we introduce a specific modeling formalism that is particularly effective at modeling relationships. The ASP documentation discusses each item in the ADO model individually, and there is a place for this. In this chapter, however, we take a different approach. While you literally will see ADO in all its complexity, you will not be overwhelmed by it. The key to making this possible is Integration Definition For Function Modeling (IDEF0).
![]()
IDEF0 was named by someone in the United State Government. We're am at a loss to explain why its acronym doesn't better match its full name, nor can we explain why zero was added (except to distinguish the formalism from other IDEF standards such as IDEF1X).
IDEF0 is the public domain version of a graphical formalism developed by Doug Ross and his crew at SoftTech, Inc. in the 70s. In this section, you learn the handful of key points needed before you can fully utilize this powerful modeling tool.
ON THE WEB |
http://nemo.ncsl.nist.gov/idef/standsp/idef0.htmlThe complete documentation for IDEF0 can be found at this Web site. |
![]()
The IDEF0 diagrams in this chapter were created with Visio 4.1, using stencils and templates that we developed specifically for IDEF0. The Visio models are interactive; that is, you can double-click various parts of the diagrams to navigate their hierarchical structure. Further details are available on the 4GWeb site.
The first thing that IDEF0 does is constrain a system to a well-defined frame of reference. The primary means to this end is in carefully selecting the system activities that are key to the person the model is meant to serve. You can see from the activities depicted in Figure 16.1 (namely, the labels of each box) that the model is built to serve the ASP developer, not the user.
This overview of ADO provides the reader with a visual frame of reference.
Once the activities are specified, IDEF0's second most important feature is its identification of the constraints controlling those activities. In , these constraints are depicted by arrows entering from the top of each box. Constraints are like inputs, except they always contain information necessary to properly complete the activity.
![]()
All activity boxes must have at least one constraint. Be careful to properly model each input to an activity: If the input contains information or instructions, the input is a constraint; otherwise, it is merely an input.
Simple inputs to activity boxes, such as data, are depicted in Figure 16.1 as arrows entering from the left. A specification such as HTTP/1.0, on the other hand, is data, too, but HTTP/1.0 is also a protocol. For that reason, HTTP/1.0 is a constraint, and form data is an input. Recordsets are considered input. However, the CursorType is a property and is therefore a constraint, not an input.
Arrows leaving the activity box from the right represent outputs. As with the inputs, they tend to be things. Outputs can be inputs to other boxes or can be constraints, depending on their nature. Outputs also can be inputs to the same activity, though this is rare. In ADO, outputs often are objects; for example, an intrinsic object such as a Connection, Command, or RecordSet or an implicit object such as Error, Parameter, or Field.
Arrows entering an activity box from below are called mechanisms and are anything that helps you complete an activity. In ADO, this most often is a method or a collection.
The final important point about IDEF0 is that it is hierarchical; that is, each box can be further refined in its own box in a separate diagram. You will see this feature in action throughout the chapter. Wherever there is a shaded box, it means that there is at least one more level to explore. As an exercise, study these boxes, moving into ever greater detail; all the arrows at an upper level also must appear (with new ones, as well) on lower levels. This way, you leave no details behind as you move into greater complexity.
That's all you need to know to start using the IDEF0 methodology. There is, of course, more to say about the technique, but that's beyond the scope of this book.
You gain access to ActiveX Data Objects through the Database Access component and gain access to the constants used by ADO through the adovbs.inc file. Between the two, you won't believe what you can do on a Web page!
![]()
Each file that needs access to constants needs to include the adovbs.inc file. If you use a text editor that permits creating templates, add the #INCLUDE directive to the top of your ADO template.
The adovbs.inc file is stored in \inetpub\aspsamp\samples\ directory when IIS 3.0 is installed. You might find it helpful to make a copy of this file in either your own application directory or the subdirectory off it where you store your include files. If you left it in the installed directory, the syntax for including the constants file would look like this:
<!--#INCLUDE VIRTUAL="/ASPSAMP/SAMPLES/ADOVBS.INC"à
As you can see from Figure 16.1, ADO consists of three intrinsic objects; that is, an object that must be created outside itself. Connection, Command, and RecordSet all are instantiated with the Server.CreateObject method, the only ADO objects that are. All other ADO objects are created from these three primary ones. Refer to the "Methods" label entering each box in Figure 16.1. When you go down a level in the diagrams for each of those boxes, as in Figure 16.2, for example, you see the actual Server.CreateObject statement that you will use to create each of these intrinsic ADO objects.
These are the activities necessary to manage Connection Objects.
![]()
Notice a few typographical conventions used in all the IDEF0 diagrams in this book. Italicized characters are given as examples; you can use any characters or conventions you prefer. Bold characters highlight the reserved words used by ADO. Normal text is used for literals.
Variable-naming is always a matter of personal style. We happen to appreciate the extra information contained in a variable named objConn, for example; it serves as a reminder that this is both an object variable and a Connection Object. Should you need to give this variable a value, you likely would remember to use the Set statement to do so, because you know that the variable is an object.
In other places, we use txtWho to identify a text box control and to create its counterpart, strWho, to identify the memory variable that we use to test and set the default value of the form control.
The point is to take the time to think about your variables. Einstein once said that "A well-phrased problem is half answered." Likewise, a well-named variable is half self-documented. (If it's good enough for Albert, it's good enough for us.)
For those who want to argue about such matters, remember something else the ancients taught: De gustibus non disputandum est-in matters of taste, there is no argument.
Creating the Connection Object
Think of the Connection Object in the context of a phone conversation. When you want to make a call, you lift the receiver, perhaps push a button, and then wait for a dial tone. Once you hear that tone, you enter the address-oops, phone number-of the person-or machine-with whom you want to talk. Pretty simple. The Connection Object isn't much more complicated than that.
The first step, then, is to get your "dial tone." The Server Object and its CreateObject method provide this dial tone. Look in the lower-left corner of Figure 16.2, where the statement listed is the following:
Set objConn = Server.CreateObject("ADODB.Connection")
Once the Connection Object has been instantiated on the server and you have your dial tone, the object must obtain a value. This value is retrieved from a given Data Source Name (DSN). Recall from Chapter 2, "Understanding Windows NT and Internet Information Server," that you need to tell the operating system how to connect to an ODBC data source, and you must give the operating system a System DSN identity.
![]()
Remember that ASP expects a System DSN, not a User DSN or a File DSN. What's the difference? As the name implies, a System DSN is available to all users on the NT Server, including NT System Services (such as ADO). Only you or your machine, however, can access User DSNs. Finally, a File DSN tells anyone with the same drivers installed how to access any given ODBC data source.
At this point, you have a couple of choices: Refer to an installed DSN by name, or use a detailed connection string. In either case, you give the Connection Object its most important property, ConnectionString. This property is like a phone number. Without it, all you get is a dial tone. With it, you can talk to any database on the Internet (well, almost).
Maintaining Connections with Application and Session Objects
The most popular thing to do with Connection Objects is store them as Application Object or Session Object properties. This has the same effect as something called "connection pooling," but as you will see at the end of this section, scoping connections is not as efficient as connection pooling. At any rate, the easy part of scoping properties is creating the property itself. The hard part is deciding when and where to create it and how to check if the property currently exits.
First, see how easy it is to set a Session property? This example came out the source code from Figure 16.2:
Set Session("ConnectionObject") = objConn
![]()
Because it's so easy to forget, we wanted to remind you again: Use the Set statement when assigning values to object variables.
Now let's review some of the issues to keep in mind when you set properties this way. First, how should you scope this property: at Application scope or Session scope?
Application scope means that one instance of an object or property is stored in the Application Object and is available to all sessions currently running. Session scope means that each ASP session has its own copy of whatever was cached.
When making decisions like this one, it's important to remember that as soon as you create these properties, you turn your ASP application into a database-even if you don't use any ActiveX Data Objects.For example, if you choose to give your property Application scope, you introduce a type of issue that database programmers have to worry about: concurrency.
Concurrency issues are managed with locking mechanisms in databases, and so it is with Application Object properties. When anyone needs to access that property, the server must use the Application Object's Lock and Unlock methods. Otherwise, someone can change the property before or after access and introduce the threat of unexpected outcomes into your code.
![]()
Threading Models and Scoping Properties Another concurrency issue applies to an instance of a component's object stored with Application scope; i.e., stored on the Application Object. Remember that the Application Object can be accessed by more than one user at a time; that is, accessed concurrently. Only one user, on the other hand, may use Session Objects, though there may be many simultaneous instances of the Session Object in the Active Server's memory at one time. The terms simultaneous and concurrent are not synonymous. If you don't keep the distinction straight, you will probably be confused by any discussion of threading models, such as the one you are in the middle of right now.
Okay, for an instance of a component object to be granted Application scope, it must be marked in the Windows Registry as Both, meaning such ActiveX objects can be used in either apartment-threaded or free-threaded modes. Objects marked Both permit access to the object by more than one thread, protect their data from thread collisions, and do not contain thread-specific data. By the way, collisions are what component threading and database management have in common. Single-threaded and apartment-threaded models can only be accessed by a single thread-something inconsistent with a multi-user environment like Active Server Pages with concurrent accesses to a single Application Object. So, if your component was created using either single-threaded or apartment-threaded models (and Visual Basic 4.0 components all are single-threaded), they cannot obtain Application scope at all.
The final issue to think about when scoping components is whether you need access to the server's built-in ASP objects (namely, Response, Request, and Server). This access is gained through pointers to interfaces exposed by these objects whenever someone requests the page that your component is on. The OnStartPage and OnEndPage events don't fire for objects with Application scope. OnStartPage and OnEndPage are special methods that ASP calls when an object is created on a page.
Scoping your objects to the level of the Application Objectmay look inviting, because there's only one instance of it, regardless of how many sessions are running concurrently; still, you cannot make this decision with impunity. Again, you must understand Active Server Pages (and ActiveX Data Objects) to effectively build applications that meet your needs.
You may have noticed that this discussion of scoping has focused on the Application Object. The reason is that this object is more tempting than the Session Object, and the consequences of cavalier use are more pernicious. By comparison, the Session Object is less problematic. However, like the Sirens of Oddyseus, you must be wary of their allure.
The problem with caching database connections is that most of the time a user is on a page, the connection to the database is idle. That is, the Connection Object is needed to populate a recordset on the Active Server for the user, and one is needed to update the recordset in the event of an appended or deleted record or edited fields. The rest of the time, the Connection Object isn't necessary.
It's one thing to persist a recordset in a Session Object so that the user's data moves through all the pages of the application as the user moves. The Connection Object need not move too. Use the Session Object to cache recordsets; don't use it to cache the Connection Object (unless you're designing a low traffic intranet). Use "connection pooling" instead.
When the Active Server is installed on your computer, version 3.0 of the ODBC manager is installed as well. One of the key features of this version of ODBC is something called "connection pooling." Connection pooling means that open database connections in all sessions are managed in a pool. If a session's connection is idle for longer than 60 seconds, it is closed automatically. Before a new connection is opened, the pool of connections is checked for a temporarily idle connection first. If one is found, it is used. This strategy can have a dramatic improvement on throughput of the application.
![]()
Connection pooling is disabled by default. To turn it on, change the registry entry StartConnectionPool to 1.
![]()
Use extreme care when modifying the Windows Registry. If you make a mistake you run the risk of disabling your computer. If you have recently made an emergency backup disk for Windows NT or Windows 95, then you can always restore your system after such a disaster. You have made the emergency disk lately, haven't you?
![]()
If you use Microsoft Access in your ASP applications, be sure to install Windows NT Service Pack 2 (SP2) before you enable connection pooling. If you don't you may crash your system when you shut down IIS. If the only database you use is SQL Server, then you can ignore this caution.
![]()
You can tell which Service Pack is currently installed by looking at the first line on the blue screen you see when Windows NT first boots.
To implement connection pooling we recommend you store the ConnectionString property of the Connection Object in the Session Object. Since this property is itself a simple string, there is no harm in making it persistent in this manner.
First, in the global.asa file, assign the connection string to the Session Object in the Session_OnStart event handler. As an example (use your own valued for the ConnectionString arguments):
Session("ConnectionString")="DSN=MyDSN;UID=master;PWD=slave"
Each .asp file that needs to access this database needs to have something like the following line in it:
<OBJECT RUNAT=Server ID=objConn PROGID="ADODB.Connection"></OBJECT>
You can also use the alternative syntax
Set objConn =Server.CreateObject("ADODB.Connection")
You open the Connection Object by invoking it's Open method and passing the Session property you set previously as its only argument.
objConn.Open Session("ConnectionString")
![]()
We also recommend that you do not store an instance of the Database Access component in the Session Object. Instantiate it from each page separately. In this way you can avoid the threading issues discussed above. If you must persist the component, and you never use Microsoft Access, however, then be sure you mark the the threading model for ADO to both in the Windows Registry.
The safer bet is to use connection pooling as described in this section.
Closing Connections
Closing a Connection Object does not destroy the object any more than hanging up the phone means you cannot turn right around and make another call. There are two reasons for using the Close method on a Connection Object.
First, if you are using connection pooling (described in the previous section), close the connection at the end of each page. Remember that ODBC will close your connection for you if it is idle for 60 seconds. Once the recordset you need is fetched, often you can close your connection until you need to update or change the underlying database table.
The second reason for using the Close method is that those properties that are writeable are writeable only when the Connection Object is closed. Again, it's exactly the same for phone calls: All you get when you dial another number when you already have a listener on the other end is an irritated listener.
If you need to free up system resources and memory, and if you're sure you won't be needing the Connection Object (or Command and RecordSet Objects that may be attached to the connection), you can do so by setting the Connection Object to Nothing.
When you call the Close method on a Connection Object, its ActiveConnection property (and the ActiveConnection property of any RecordSet Object that uses the connection) becomes null, any parameters collection is emptied, and fields may lose data or report an error. Table 16.1 summarizes what happens to Command and RecordSet Objects when you tweak the Connection Object.
Table 16.1 Consequences of Closing Connections
Object | Property | Status | Comment |
Command | ActiveConnection | Null | The Command Object persists but is dissociated with any Connection Object. |
Parameter | All | Collection is emptied. | If ActiveConnection is changed from one open Connection Object to another, the Parameters collection remains intact. |
RecordSet | ActiveConnection | Null | Data and exclusive access are released. |
Field | Value | Error (during immediate updates). | Call Update method before Close. |
Field | Value | Lost (during batch updating). | You can save previous batch edits if you call Update before Close. |
The designers of ADO did a great job distributing power to each of the three intrinsic ADO objects. The Connection Object gets to control a very important function in database management, and this is especially important when it's done on the not-always-reliable Internet (you'll see why in the "Programming By Trial and Error" section). Later in this chapter, you will explore the power of parameters using the Command Object.
![]()
See "Creating RecordSet Objects" for more information about the subtle properties of concurrency control, in Chapter 17.
Taken as a "Gang of Three," ADO is a potent system for database development. Never before in our years of database development have we encountered this degree of power at this level of usability.
Other than transaction methods (which you will learn about in the "Transactions with a Huge Return on Investment" section), how much data management can you do with the Connection Object? As you can see in Figure 16.3, not that much; but then, there are times when that's just enough.
Manage Recordset Objects.
Quick and Dirty Data Retrieval
Using the Connection Object to retrieve a recordset gives you an "economy class" cursor. ADO figures that if you're using only the Connection Object, you aren't very picky about what you want, so you get what you pay for: a read-only, forward-only cursor, sometimes called a "firehose" cursor. These little guys are great when you need access to database records but don't need to change their values. With a forward-only cursor, you can move only from the current record to those later in the recordset (unless you cache the records). Therefore, if you need to know what a previous value was, you have to Requery the base table and start over.
![]()
See "Navigating Among Records" for more information about caching recordsets, in Chapter 17.
The Connection Object uses the Execute method to create recordsets and work batch operations such as delete and append on the recordset's base table. The function syntax of the Execute method (e.g., when the method results in a recordset) is:
Set objRst=objConn.Execute(CommandText,RecordsAffected,Options)
When no result is returned, such as when an action query like an update is executed, use the subroutine syntax:
objConn.Execute CommandText,RecordsAffected,Options
The difference, of course, is that there is no Set statement and no parentheses, just like a VBScript subroutine.
The three arguments are simple. The CommandText argument is where you pass the SQL statement to the Execute method. The second argument is a variable whose value the data provider returns. In the case of the function syntax, this value is always -1. The Sub syntax is more interesting; the data provider returns the numbers affected by the batch operation. After calling the Execute method, refer to this value to ensure something happened, or the expected number of records were affected. The final argument helps squeeze the last bit of performance from this method. Select from the options in to help the data provider find the fastest way to implement the Execute CommandText.
Table 16.2 Options for the Execute Method, Called by a Connection or Command Object
Value | Constant | CommandText is: |
1 | adCmdText | textual |
2 | adCmdTable | a table name |
4 | adCmdStoredProc | a stored procedure or querydef |
8 | adCmdUnknown | unknown; data provider resolves identity |
![]()
These Option argument values apply to the Execute method when invoked by the Command Object, as well.
![]()
Performance is Everything Using the Option argument in the Execute method is very important with the Connection Object-perhaps even more so than with its more powerful cousins, Command and RecordSet Objects-if you want maximum performance from your query or stored procedure.
Both queries and stored procedures are compiled by the data provider and optimized in the process. When you use the Option argument to tell the provider that the CommandText argument is either a query or a stored procedure you shave a few milliseconds off the processing time. Remember what the good Senator said: "A [millisecond] here, a [millisecond] there, and pretty soon, you're talking about real [time]!"
On the other hand, if you have to embed parameters in the CommandText argument, you give up performance, because you're sending raw, uncompiled text to the data provider. Still, in a world where a second is like a thousand years, telling the provider that a SQL string is coming saves it the time of figuring that out for itself.
The Connection Object collects errors. This makes sense, because all access to the data provider is through the Connection Object, and it's the data provider that returns the errors in the first place. In each of the IDEF0 models, you probably will see at least one activity that outputs an Error object. We take editorial license with that, intentionally sending you to the "Roadmap" documentation (that was copied to your system when you installed Active Server Pages) to find out under what circumstances you create the error.
In Chapter 9, "Calling Procedures: Functions and Subroutines," you saw the basic error-handling mechanisms provided by VBScript. Nothing's different in ADO; you're still limited in what you can do when an error occurs. What ADO does give you is a rich Errors collection that helps you pinpoint what went wrong and why.
![]()
A Note from the Authors Many years ago, one of this book's authors made a trip to a small community outside Saint Charles, Illinois, to perform a rite of passage. Every member of the Arthur Andersen & Company's Management Information Consulting Group had to prove his or her mettle at Computer Fundamentals School (not to learn about disk drives and keyboards, to learn IBM's Assembler language).
There are two things the author never will forget about that experience. The first is that, because the students used an IBM mainframe in those days (1980- 1981), they ran their Assembler programs from punch cards. Processing cycles were so precious that students weren't allowed to develop their assigned payroll program using the venerable method of trial and error.
The second thing is that each student's program had to match the answer key, byte for byte-you worked on your code till there wasn't a single error left. A blank, a zero-length string, and Null all were different; in the answer key, only one was correct.
For warhorses like this author, ADO's Errors collection is more than extremely useful-it's liberating, as well. We will hazard a guess that most readers of this book have never programmed with punch cards. This vignette underscores the value of modern technology, especially debugging features-something we all (including the warhorses) tend to take for granted.
Before getting into the ADO Errors collection, be aware that there are several kinds of errors. VBScript returns either compile-time errors or run-time errors. You see compile-time errors when you execute any errant Visual Basic scripts. The ASP page tells you that the error is a compile-time error and halts the program. You also can tell it's a compile-time error if your ASP page uses a background color, because then you won't see that color. Instead, the code halts and is displayed with a little marker indicating where you need to look.
Run-time errors, on the other hand, run the .asp source code until the errant line is attempted. Again, the compiler will display hints about the problem. For example, here's our favorite: if you try to use the Object tag to instantiate a Connection Object with Session scope in your .asp file, here's what the compiler will display:
Active Server Pages error 'ASP0121'
Invalid Scope in object tag
/lab/_error.ASP, line 1
The object instance 'objConn' cannot have Application or Session scope. To create the object instance with Application or Session scope, place the Object tag in the Global.asa file.
ADO errors, however, are the only ones collected in the Errors collection. That is, only errors returned by a data provider trigger the ADO Error collection system. Other errors, including some errors sent by the ADODB component, are handled by the compiler or the Visual Basic Err object. The unique thing about ADO errors is that the collection can have more than one Error object in the Errors collection. These multiple errors are created by the data provider. Multiple errors can be much more helpful in tracking down complex database management errors.
ON THE WEB |
http://www.quecorp.com/aspEnumerating the Errors collection is like enumerating the other collections you have encountered in this book. An example code snippet is taken from ch16.asp (available for download from this books Web site) and shown in Listing 16.1. |
Listing 16.1 ch16.asp-Enumerating the Errors Collection
If objConn.Errors.Count > 0 Then For each error in objConn.Errors Response.Write("Error Nr: " & Error.Number &"<BR>") Response.Write(Error.Description & "<BR>") Response.Write("Source: " & Error.Source & "<P>") Next End If
Transactions are a big thing in the database world, and if Microsoft's Transaction Server is a success, they're going to get bigger. We leave the high-tech transaction stuff to another book. In ADO, as in DAO before it, transactions can make an enormous difference in performance.
Several years ago, one of this book's authors incorporated database transactions into the Retirement Capital Modeling software he built with Access. He shaved an order of magnitude off the processing time; that made it ten times faster.
Even more amazing, he had to add only a couple of lines of code to his existing module. Listing 16.2 borrows a little from Listing 16.1 and adds three statements of its own. Essentially, that's all there is to implementing database transactions.
Listing 16.2 ch16.asp-Adding Transaction Processing
objConn.BeginTrans [database processing] If objConn.Errors.Count = 0 Then objConn.CommitTrans Else objConn.RollbackTrans End If
What accounts for this easily tapped power? RAM is faster than the hard drive. When a transaction is pending, any disk I/O is suspended until a CommitTrans method is invoked. Should there be an error, the RollbackTrans method puts any changes back in their original state and ends the transaction. As long as the transaction is open, the disk is buffered by memory.
![]()
In Listing 16.2, [database processing] (the second line) is where you insert your normal ADO code. Because you already may have such code written, you might want to think of transaction code as a wrapper around your routine database programming. This emphasizes the fact that you can retrofit nearly all your database access commands with transactions and see an immediate increase in performance. This increase will be especially impressive when your code is like the code that we originally started using transactions to improve: writing a temporary table to disk. The bigger the table, the better the performance.
The Attributes property of the Connection Object effects what happens after the transaction is closed. If the Attributes property is set to adXactCommitRetaining, ADO automatically starts another transaction after the CommitTrans method is finished. The same thing happens after a RollbackTrans method is done, if the Attributes property is set to adXactAbortRetaining. The bottom line on database transactions-you won't get better bang for your ADO buck with anything else.
The next object in the ADO "Gang of Three" is the Command Object. As with the other two intrinsic objects, you create instances of the Command Object with a call to the ADO Server Object:
Set objCmd = Server.CreateObject("ADODB.Command")
The Command Object was created to optimize performance of queries and stored procedures, especially when parameters are used. Once you get the hang of the Parameters collection, you never will go back to hassling with embedded parameters in SQL strings. Now see what else you can do with the Command Object.
One of ADO's more interesting innovations is that it has abandoned a strict adherence to object hierarchy. If you have come to ADO from Microsoft Access, you know that you had to instantiate a database object before you could instantiate a QueryDef or a RecordSet Object. With ADO, this no longer is required. One of the benefits of this design policy is that your ADO programs are thinner than their DAO relatives-they contain a little less code. This is most obvious when the Command Object and RecordSet Object create their own Connection Object; for example, when the ActiveConnection property is assigned to the Command Object and during the Open method for the RecordSet Object.
Figure 16.4 gives you an overview of the Command Object. Refer to Figure 16.1 to see how the Command Object fits in with the other three intrinsic ADO objects. In this section, you learn about the first two boxes in Figure 16.4. The section "Opening Recordsets" drills down into box 3 of Figure 16.4 (this will be "Node A123," and it renders the detail of box 3 in Figure 16.4).
Managing Command Objects.
Now take a closer look at how to get a Command Object ready to create a recordset or otherwise operate on a base table (for example, update or delete records, modify table structures, and so on).
In the previous section, "Managing Connection Objects," you saw how a Connection Object was created and the advantages to creating persistent Connection Objects. If you want to take advantage of these features, merely assign the Connection Object to the ActiveConnection property of the newly created Command Object, like this:
Set objCmd.ActiveConnection = objConn
Next, tell the Command Object where the SQL statement is. You can use a simple table name (to fetch all the records of the table), a query name (which is important when this section discusses parameters), or a raw SQL string (when you use the INSERT INTO statement). For present purposes, use a parameterized query:
objCmd.CommandText="qryPhoneMessagesFor"
That's all there is to setting up a Command Object. After you next see a comparison of Connection Objects to connection strings, you will learn that getting the Command Object to do something useful remains fairly straightforward.
If you don't need the overhead of a cached, persistent Connection Object, ADO permits you to create a Command Object with a private Connection Object. The first of three options to create a new Connection Object is to assign the ActiveConnection property a DSN; the second option uses a connection string. The advantage of the latter is that you can override the default values in the DSN. The third option permits you to create a DSN "on-the-fly."
Here's the first version:
objCmd.ActiveConnection ="intranet"
Here's the second alternative, a connection string that specifies the arguments for the new connection:
objCmd.ActiveConnection ="dsn=intranet; database=intranet;uid=sa;pwd=;"
![]()
Note the use of semicolons in the preceding line of code. When ADO sees the equal signs in the string, it knows that a connection string-and not a DSN-is being used. You don't use the Set statement when you use your own private Connection Object, because the object hasn't yet been created; it's implicit in the use of the connection string. That is, when ADO sees the connection string, it creates a new Connection Object on its own. You don't have to tell it to do so with the Set statement.
And finally, here's the real trick: a DSN with no muss, no fuss:
objCmd.ActiveConnection="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=c:\data\lab\intranet.mdb"
![]()
If you are programming in a large shop and you're not the system administrator, using this last alternative enables you to get to a database without troubling the network administrator for a hard-wired DSN.
Okay, ADO understands where the data provider is and how to connect to it, as well as what you want to do with the data stored in that location. Now how do you activate this command? The answer is in the following section.
The RecordSet Object is the ADO "workshop". You get nearly all of your database management tasks accomplished there. However, when your objective is not to retrieve data but instead to directly modify it or modify its structure, a recordset is not returned from the data provider. In this case, a Null object reference is returned along with any Error objects, should an error occur during command execution.
Figure 16.5 shows the overview of these scenarios and how each plays out in turn. Even though the "Manage Parameters" activity box appears first in the diagram, it is discussed last. As you will see, there's a good reason for this delay. When you're finished reading this chapter, you also will appreciate why "Manage Parameters" appear first in Figure 16.5.
Manage recordsets with Command Objects.
Cursors represent records stored in base tables. They are like shadows of data-not the real thing but nonexistent without the real thing. Likening a cursor to a shadow, however, presents only a partially accurate metaphor. It highlights (pun intended) the fact that, in a sense, cursors are separate from data, but while shadows are two-dimensional, cursors come in many varieties.
We give an in-depth discussion of concurrency issues in Chapter 17, "Working with ADO's RecordSet Object." For the moment, we stratify cursor types into two layers: safe and risky. Safe cursors do not permit changes, so concurrency issues are moot. Risky cursors are those in which more than one user can access or change the same underlying base table record at the same time. That is, when two different cursors represent the same real record, and when these two cursors can be used to change the real data stored in that real record, you run the risk of conflicting changes to data.
Both the Connection Object and the Command Object produce safe cursors. If you try to change a record in one of these cursors, you get an error. So what good are these cursors?
They excel at displaying base table data. Because Web pages originally were read-only, routinely using this kind of cursor was fine. With the advent of ADO, Web pages now are capable of all the dynamic features of desktop applications. If you stick to intrinsic controls on ADO-enabled Web pages, you still are fairly safe using the safe cursors of Connection Objects and Command Objects. Remember this, though: safe cursors do not lock records; they merely fetch data in the state it's in at the instant that it is accessed. The data is subject to change by other users while being viewed by one user, without that user knowing of the modifications. This particular process goes by the rather earthy description of "dirty reads."
This isn't a serious problem, just something to be careful about. Perhaps nothing more than a warning to users that the data they are viewing is subject to change is all that's necessary to minimize confusion. Indeed, viewers might need to make changes themselves. This is where the RecordSet Object comes in handy.
For now, remember that safe cursors are made for displaying data. Connection Objects do it with minimal code and are extremely useful when you want to see everything in a table. The Command Object is more flexible, because it can handle parameters with ease, and you can optimize Command Object performance by minimizing the amount of "conversation" between the data provider and the ASP program. You'll see the details of this optimization in the section "Using Parameters."
You create a RecordSet Object (using a Command Object to access a compiled query or stored procedure that does not need parameters) with a statement like the one shown here:
Set objRst = objCmd.Execute(RecordsAffected,,adCmdStoredProc)
Operating on Tables Without Cursors
When you need to execute a batch operation on data or to modify the structure of tables in a database, the Command Object is really in its element. Here, none of the issues surrounding concurrency plague you. The data provider manages the only locking issues that arise due to your changing data. You don't even have access to the CursorType and LockType properties when you execute action queries.
To execute batch operations with the Command Object , there are two places where you need to make changes: at the data provider (to set up queries or stored procedures) and in ADO (to configure the Command Object and its Parameters collection, if necessary). You're on your own when it comes to exactly what to do in your data provider to create the query or stored procedure. After you create the query or stored procedure, you need its name and parameter information when you create the Command Object and Parameters collection in ADO.
![]()
It's all too easy to misspell long query or stored procedure names. After creating ours, we go to the object and select the Rename option. Then we copy the file name to the Clipboard and cancel the Rename operation. Back in ADO, we merely paste the name in the appropriate place in the source code and eliminate the occurrence of this all too common error message: "Invalid SQL statement."
Once all of the housekeeping is done at the data provider, you need to inform ADO of the details. In the case of a simple query or stored procedure, all you need to do is set the CommandText property to the name of the query that you created in your data provider. For example:
objCmd.CommandText="qryPhoneMessages"
At this point, all that's left is to execute the query. The general syntax of the Execute method when invoked by the Command Object for batch operations (without parameters) is the following:
objCmd.Execute RecordsAffected,,adCmdStoredProc
Adding the two arguments, RecordsAffected and adCmdStoredProc, takes no time to complete. If you use an editor like HomeSite, you can create keyboard macros that do your typing for you. For the marginal effort, the RecordsAffected and adCmdStoredProc arguments give you both information (in the RecordsAffected variable that is returned by the data provider) and performance (by instructing the data provider to look for a query or stored procedure) in exchange.
ON THE WEB |
http://www.dexnet.com/homesite.htmlYou can download a shareware version of HomeSite from this Web site. |
The RecordsAffected argument is a long variable that tells you how many records were affected by the command. This can be very helpful to your user, and if zero is returned as an unexpected result, you might want to explore the reason. The adCmdStoredProc argument simply informs the data provider that the named query or stored procedure is a query or stored procedure. Without this information, the data provider needs to take the time to find the name.
![]()
We have noticed that when we use the arguments in the Execute method, we may get slightly more lucid error messages from our data provider than at other times. This makes sense, because we took the time to help the data provider look for the object.
Passing the adCmdStoredProc argument in the Execute method is kind of like including your phone number when you leave a message on a friend's answering machine. He knows who you are and may even remember where he keeps your number, but why make him work that hard? Give him the number-and give your data provider the identity (and therefore the location) of your command. This works fine when your batch operations are simple, but what about when you want to update only a few records or fields?
One of the ADO methods that make ADO simple to program is the AddNew method. We recommend that you use this method only when you have a good reason to do so. Instead, we suggest that you use Command Objects and their Parameters collection.
Remember a motto of this book: De gustibus non disputandum est (in matters of taste, there is no argument). When we offer a personal stylistic preference, don't assume it's the only way to do things.
If you retrace the hierarchical path from to , you see that the Parameters collection belongs to the Command Object, so any reference to this collection must be preceded by the object variable assigned to the appropriate Command Object (again, our favorite is objCmd).
Because you just created a query or stored procedure in your data provider, all the Parameter metadata is stored there. To populate the Parameter collection of the Command Object, you have two choices: interrogate the data provider (assuming that it can hear you and can respond-Microsoft Access can't; SQL Server can) or hardwire the metadata into your .asp code yourself.
We recommend that you choose the latter. The Refresh method of the Parameters collection interrogates a data provider at runtime, but you should use the objCmd.CreateParameter method, instead. You'll see why at the end of this section.
![]()
You can let ADO interrogate the data provider at runtime, but this usually is a waste of time because query and stored-procedure designs tend to be static. There's a compromise between sloth (relying on run-time interrogation) and working too hard at design time (especially if there's a long lag between designing a query and using it in ADO code, i.e., if the lag is long enough to have forgotten how the query was designed). Create an Active Server custom component that interrogates the data provider at design time and reports the latest facts about the data provider's object. Not surprisingly, these are called design-time controls.
Actually, the objCmd.CreateParameter method is one of a pair of twins. Its counterpart is the objCmd.Parameters.Append method. We prefer keeping each of the two on its own line. Look closely at for the specifics of this coding strategy.
![]()
Another matter of taste is selection of a text editor. Our hands-down favorite is Nick Bradbury's HomeSite, version 2.0.
Our number one favorite feature is HomeSite's capability to store keystrokes in toolbar buttons. We have a slew of macros. Perhaps the most productive is the one that generated Listing 16.3.
' Begin parameter configuration... strName="whom" 'enter name of parameter lngDirection=adParamInputlngType =adVarChar lngSize = 3 ' enter string length or else remove it from argument list varValue = "mpc" 'enter parameter value Set objParam = objCmd.CreateParameter(strName, lngType, lngDirection, lngSize, varValue) ' ...end parameter configuration. objCmd.Parameters.Append objParam
Notice again how picky we are about naming variables. The variable names include their type, and the constants selected are either default (for example, adParamInput) or most common (for example, adVarChar). The advantage of this technique is that you don't have to worry about the argument list in the CreateParameter method. The single exception is when you are not using a parameter that requires a Size argument (for example, adInteger); in that case, you need to delete the lngSize argument from the list (you can leave the memory variable alone, though).
Table 16.3 was taken from the ADO documentation that ships with Active Server Pages. It lists all the permissible values for the Type property of the Parameter object.
Table 16.3 Constants Available to Define a Parameter's Type Property
Constant | Value | Description |
adBigInt | 20 | An 8-byte signed integer |
adBinary | 128 | A binary value |
adBoolean | 11 | A Boolean value |
adBSTR | 8 | A null-terminated character string (Unicode) |
adChar | 129 | A String value |
adCurrency | 6 | A currency value (8-byte signed integer scaled by 10,000) |
adDate | 7 | A Date value |
adDBDate | 133 | A date value (yyyymmdd) |
adDBTime | 134 | A time value (hhmmss) |
adDBTimeStamp | 135 | A date-time stamp (yyyymmddhhmmss plus a fraction in billionths) |
adDecimal | 14 | An exact numeric value with a fixed precision and scale |
adDouble | 5 | A double-precision floating point value |
adEmpty | 0 | No value was specified |
adError | 10 | A 32-bit error code |
adGUID | 72 | A globally unique identifier (GUID) |
adIDispatch | 9 | A pointer to an IDispatch interface on an OLE object |
adInteger | 3 | A 4-byte signed integer |
adIUnknown | 13 | A pointer to an IUnknown interface on an OLE object |
adLongVarBinary | 205 | A long binary value (Parameter object only) |
adLongVarChar | 201 | A long String value (Parameter object only) |
adLongVarWChar | 203 | A long null-terminated string value (Parameter object only) |
adNumeric | 131 | An exact numeric value with a fixed precision and scale |
adSingle | 4 | A single-precision floating point value |
adSmallInt | 2 | A 2-byte signed integer |
adTinyInt | 16 | A 1-byte signed integer |
adUnsignedBigInt | 21 | An 8-byte unsigned integer |
adUnsignedInt | 19 | A 4-byte unsigned integer |
adUnsignedSmallInt | 18 | A 2-byte unsigned integer |
adUnsignedTinyInt | 17 | A 1-byte unsigned integer |
adUserDefined | 132 | A user-defined variable |
adVarBinary | 204 | A binary value (Parameter object only) |
adVarChar | 200 | A String value (Parameter object only) |
adVariant | 12 | An OLE Automation Variant |
adVarWChar | 202 | A null-terminated Unicode character string (Parameter object only) |
adWChar | 130 | A null-terminated Unicode character string |
![]()
Be sure to use the #INCLUDE directive in any .asp file that uses Visual Basic or ADO constants.
Programmers who practically grew up writing desktop applications need to remember to do two things when ADO calls them to the big leagues of client/server programming. First, give the client/server database management system very precise instructions-don't make it waste a nanosecond of its time. Remember, the DBMS is serving perhaps thousands of people at once. Gone are the days when the only database traffic to worry about was between your hard drive and your CPU.
Second, because the DBMS is designed to do all the processing on the server side and sends back to the client only the results of those operations, make sure it sends back the smallest amount of data that will meet your needs.
You might call this "parsimonious processing." When you use a Command Object and its Parameters collection, you're being as parsimonious as possible-good for you. This may make your programming job a bit more challenging, but you're developing Active Server Pages now. You have arrived. You can do it.
Now take a close look at what happens when you wisely use the Command Object but relax your diligence and revert to the objRst.AddNew method.
Say that you opened a recordset by referring only to the table name, something like this:
Set objRst.Open "tblPhoneMessage", objConn, adOpenDynamic, adLockPessimistic
What you did was ask the data provider to produce a cursor as rich as can be, to work as hard as is capable to preclude interference between concurrent accesses. Add to this overhead your request for all the records in the table, just so you can insert one more, and you're an accident waiting to happen.
Discussion of concurrency and isolation levels comes in Chapter 17, "Working with ADO's RecordSet Object." At this point, we lobby for an alternative to editing your new record, one field at a time (depicted in Listing 16.4).
Listing 16.4 ch16insert.asp-Using the AddNew Method
objRst.AddNew objRst("for")="mpc" objRst("caller")="katy" objRst("message")="The check just arrived!" objRst.Update
![]()
When you see consecutive commas in argument lists, it means the missing argument is optional, and you are skipping it.
Using a Command Object (and an abbreviated version of parameter assignment), your code might look like Listing 16.5.
Listing 16.5 ch16insert.asp-Using a Command Object and Parameters
objCmd.CommandText="INSERT INTO tblPhoneMessage (for, caller, message) VALUES(?,?,?)" Set objParam=objCmd.CreateParameter(, adVarChar, , Len(strFor), strFor) objCmd.Parameters.Append objParam Set objParam=objCmd CreateParameter(, adVarChar, , Len(strCaller), strCaller) objCmd.Parameters.Append objParam Set objParam=objCmd.CreateParameter(,adVarChar, , Len(strMessage), strMessage) objCmd.Parameters.Append objParam objCmd.Execute RecordsAffected,,adCmdText
Using the Len(variable) in Listing 16.5 is a shortcut to referring to your database schema for the size of variable-length fields. The risk that you run using this technique is in coming across a string that's longer than the field in the base table. Should this happen, this code raises an error. Besides, using the Len() function betrays the message that we have preached throughout this chapter: Know your database schema before you start writing ADO code.
Purists among you, then, will want to hardwire the field length in the fourth argument. You be the judge.
Did you catch the three important differences between Listing 16.5 and Listing 16.3? If you miss any one of them, you'll see errors. Listing 16.5 has the following:
Before our closing argument for parsimonious processing with parameters, here are a few more points to note about Listing 16.5:
As you can see, precious little server activity is called for using Listing 16.5. You don't have to create a cursor, make repeated INSERT calls to the data provider for each field that you edit, or invoke the Update method. Even your brain takes a rest in Listing 16.5, because you don't have to think about what kind of cursor you want to create or evaluate different record locks.
The data provider handles all of those details with a sagacity that most programmers never will attain. That's why they're called client/server database management systems.
Listing 16.5 comes to the data provider on its terms. The results of this and all other examples of data access covered in this chapter are nothing short of dramatic. You're off to a good start in an ADO programming career, don't you think?
This chapter was your first step from Chapter 15, "Introducing ActiveX Data Objects," where you learned about what made ActiveX Data Objects unique and innovative. This chapter also is a bridge to Chapter 17, "Working with ADO's RecordSet Object." In this chapter you concentrated on the contributions that two intrinsic ADO objects, the Connection Object and the Command Object, make to Active Server Pages technology. You saw that ADO was a break with tradition, not adhering to a strict hierarchy of objects. You also learned how to maximize performance using Command Objects and their Parameters collection. You now know that from simple cursors to complex batch operations, Connection Objects and Command Objects can do a lot of work for you.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.