Review the historic roots of ActiveX Data Objects, and see how they extend existing systems like Open DataBase Connectivity.
Explore the exciting new software model that ActiveX Data Objects is based on. The prospects for this radically new approach to data management will take your breath away.
You get your first good look at the basic building blocks of ActiveX Data Objects.
Introducing the methods, and properties of ActiveX Data Objects.
See how the essential elements of an ActiveX Data Object program fit together and explore the interaction between object properties with a working .asp file.
As you read in Chapter 3, "Understanding Client/Server on the Internet," computing originally started on large machines with dumb terminals. With the advent of the personal computer and powerful workstations, much of the processing migrated to the desktop. The introduction of the Web server shifted the balance of power back to a centralized programming model. Today, Java and ActiveX controls have, yet again, enabled the desktop to reassert itself.
In this book, you have seen how the Active Server has enabled a more balanced balance of power. That is, instead of these polarizing swings of focus from server to desktop and back again, the Active Server has shown its "diplomatic" side, enabling the ASP developer to decide the best combination of technologies that optimize processing load without sacrificing fault tolerance or scaleability.
As important as this egalitarian trait of ASP is, everything done so far in the book is trivial when compared to the innovations in ActiveX that are covered in this part of the text. These innovations are called ActiveX Data Objects (ADO).
The major theme of this chapter, then, is distributed, object-centric database management. Before getting into it, however, you should be aware of a few things. First, bandwidth is scarce on the Internet. Second, packet-switched networks are inherently latent (that is, they are subject to delays). ActiveX Data Objects are so revolutionary that they (and you, as an ASP developer) are slightly ahead of their time.
The apparent impediments of bandwidth and latency may actually be a blessing. Most of your ADO learning curve will probably occur on intranets-where bandwidth is measured in tens, if not hundreds, of megabits and where latency is practically zero. By the time we break the bandwidth bottleneck with Asynchronous Transfer Mode (ATM) switches (or, better, when George Gilder's vision of the "fibersphere," the all fiber network, becomes reality), ASP/ADO development will be second nature to you. With those caveats in mind, get ready to take a deeper look into this radical new resource: ADO.
Until Microsoft's acquisition of Fox Software in 1992, the database was conspicuously absent from Microsoft's desktop arsenal. Today, FoxPro (now matured into Visual FoxPro), Microsoft Access, and SQL Server round out a balanced database strategy. Each product is designed for a particular market. For example, Visual FoxPro uses an Indexed Sequential Access Method (ISAM) file format and is generally recognized as faster than Access but slower than SQL Server. As the other desktop application, Access uses a special file format that is accessed through the Jet Data Access Object (DAO) model. DAO gives the programmer direct access to the database structure, something not as easily done in Visual FoxPro. SQL Server, on the other hand, is a database server, not a desktop application. It is an industrial-strength application that is highly scaleable. All three products use ODBC to share data with each other.
There is one other thing that these three products have in common, setting them apart from ADO: Their ability to be deployed in a distributed network is defined by the Remote Data Object (RDO) specification found in Visual Basic 4.0's Enterprise Edition. Specifically, they work in connected networks. The Internet, on the other hand, is a connectionless network-a packet-switched network.
In fact, Web servers are even more problematic for programmers, especially database programmers, because they are "stateless" as well as connectionless. They're called stateless servers because once they serve a client request, they forget that the client ever was served. The server does not keep track of anything going on with the client application. Web servers have short memories.
In the desktop world, when two applications communicate, they do so for a given period of time. For example, when an Access database is linked to external files, it tests for the presence of those files as soon as it is opened. As long as the Access database remains running, any interruption in the connection (for example, say that a Novell file server goes down) triggers an event in Access, and a warning flashes in the Access application that the connection has been broken. In SQL Server, the server is aware of the presence of a client application as long as that application is engaged in a transaction with the database server. The length of time of such a transaction is not necessarily as short as it is with a Web server.
A Web server knows about a client application only long enough to deliver a file, but an Active Server is different. While it, too, disconnects from the client as soon as a file is returned, it remains connected as long as necessary to do two things: first, process the database and produce a recordset; second, enable the application to make subsequent calls and still have access to previously returned data.
Before getting into the meat of ADO, take a closer look at the application program interface (API) on which ADO is built, OLE DB (OLE DB is Object Linking and Embedding applied to databases). ADO is the first Microsoft technology built on this exciting new initiative. If you thought that the Web spawned an avalanche of great software and business models on the Internet, just wait until developers hear about OLE DB! And, as an ADO programmer, you're leading the way!
First, it's best to set the context with a quick review of the interface, the Open Database Connectivity (ODBC) specification. ODBC is a desktop SQL-based specification whose API foundation is built on the C language. This means that it was designed for relational database systems, the kind with which you all are accustomed to working. ODBC is a real workhorse, enabling developers to build systems that integrate Jet databases, ISAM files in old FoxBase+ databases, and even SQL Server tables into one coherent user interface. ODBC is the lingua franca of all these relational dialects meaning it is the linguistic bridge connecting the disparate native languages of each database management system.
OLE DB, on the other hand, is a specification based on a C++ API, so it is object-oriented. OLE DB consists of data consumers and data providers. Consumers take data from OLE DB interfaces; providers expose OLE DB interfaces. ODBC now is a subset of OLE DB. Currently, Microsoft has developed an OLE DB data provider (code-named Kagera) that enables access to the old relational data. In fact, under some circumstances, OLE DB can access ODBC data faster than DAO or RDO. This is because DAO and RDO have to pass through the ODBC layer, and OLE DB connects directly to relational data sources. See Figure 15.1 for the subtle difference in the approach taken by ADO.
OLE DB simplifies the connection of your program to database information.
What's more, OLE DB can be used to extend the functionality of simple data providers. These more sophisticated and specialized objects are called service providers, and they can assemble everything needed by data consumers into a single table, regardless of data type (for example, ODBC, spreadsheets, e-mail messages, word processing documents, or file systems) or storage location (LAN, WAN, Internet, intranet). Service providers, therefore, are both consumers and providers. That is, a service provider could consume OLE DB interfaces and build a single table from that input; it then could expose OLE DB interfaces to a client application (such as an .asp file using ADO) for constructing its HTML output.
But OLE DB is, after all, a low-level specification. If you don't have time to learn a language lower down the food chain than Visual Basic, ActiveX is the answer. Specifically, ActiveX Data Objects use a language-neutral component technology to provide a high-level wrapper around the OLE DB API, which enables you to exploit all the power of OLE DB without resorting to low-level programming. On the one hand, this means that all Microsoft programming languages can use ADO to access data. On the other hand, ActiveX components (ADO is but one example) can themselves be built using any language that complies with the Component Object Model. All of these rather subtle relationships are depicted in Figure 15.2.
ActiveX is a language-neutral wrapper, giving the component developer free choice of the best breed of development environments.
To recap: ADO works just like DAO and RDO, only more efficiently, especially in a stateless and connectionless environment like the Internet/intranet. ADO, an ActiveX component technology, is based on a C++ API called OLE DB (which betrays its family heritage from ODBC, written in C). Because ADO is a component technology, it is highly extensible, tying ADO to Microsoft's programming paradigm for the next millennium.
The Active Server is a component of the Internet Information Server. Specifically, it is an Internet Server Application Programming Interfase (ISAPI) filter. This means that it is a Dynamic Link Library file that becomes part of IIS as soon as the operating system starts it running. This modular design of everything Microsoft publishes these days is a model for the way an ASP developer should write applications.
By exposing key ASP resources to the scripting engine running on the server (e.g., VBScript, JavaScript, Perl, and others), the Active Server enables the ASP developer to extend the functionality of the Active Server itself.
Objects are the first internals available to the ASP developer. The functions exposed by objects are the most common functions required by Active Server Pages. Examples include creating instances of server components (which you will read about next), manipulating text strings with URL and HTML encoding, and many other advanced functions that you will read about in the rest of this part of the book.
Components have a similar function; viz., to extend the functionality of the Active Server, but they have one fundamental difference: they are .dll files that run separately from the Active Server, but in the same address space as the server. Microsoft wrote these .dll files, but you can create your own components in any language that produces code compliant with the Component Object Model (COM) specification. By creating .dll files (and not .exe files that execute in their own address space), you give your components maximum speed.
Active Server components (both those components intrinsic to ASP and those you create yourself) do not have a user interface. Indeed, if you forget this and design a server component with a msgbox or inputbox function, you will hang the server. This is because the program (namely, your errant server component) that is running is waiting for user input that never comes, because the user interface is invisible. In addition, server components need to support only three interfaces (IUnknown, IDispatch, and IClassFactory).
Finally, VBScript does not have a type library, so if you want to use variable types other than Variant, write a component. For example, if you want to extend the Active Server's mathematical power, you DO NOT want to use the Variant data type; it will slow your mathematical processing to a crawl. Instead, create a program in Visual Basic, and compile it as an in-process .dll file; register it with the operating system, and call it in your Active Server Pages.
Remember, almost all new developments in computer programming today are innovative uses of objects. Whether you program in Visual Basic 4.0 or in Active Server Scripting, you are object-centric. Get ready to relax your brain-you're going to need a lot of mental flexibility as the section proceeds.
To start, here's a simple example. Say that, using the TextStream Component, you treat a file as an object. That's not hard to imagine. One of the methods exposed by this Component is OpenTextFile; again, fairly straightforward. But what happens in your brain when you think of data as an object? Treating a file as an object is simple-there's an object, a single object, and it's called a file. It's not a file that's part of a bigger file (unless you take the idea of a folder literally); it's just a file.
But data? A single data (okay, datum) is trivial, and ADO is far from trivial. You probably have to think of a data source in order to feel comfortable with the concept of data being an object. And what about queries and stored procedures? They result in recordsets that can range from zero rows to the total number of rows in the data source. One of the first things that we recommend you do when you start working with the ADO is stop thinking of database tables literally. Instead, think of them as abstract objects with their own collections, properties, and methods.
Most Access developers already are familiar with a data object model, which is exactly what the Access Data Access Object (DAO) model is. These developers should recognize nearly all the terms used in ADO, other than the Connection Object. The name is different, but the functionality is familiar; ADO's Connection Object basically provides the same functionality as the Workspace Object in Access.
Familiarity, however, won't prepare even seasoned Access developers for some of the stumbling blocks that they'll confront in ADO. For example, if your programming career developed as ours did, you went from an ISAM world like FoxPro to the DAO world of Access, and the first thing you gave up was the record number. In Access (and ADO), the record number doesn't exist. Access introduced developers to the bookmark, instead. ADO follows this tradition, with its own twist: pages.
Absolute pages are navigated in ways allowed by the nature of the underlying recordset. That is, some recordsets can be navigated freely, up and down; others permit only row-wise, forward movement. You'll see examples of both in a moment. For now, be aware that there are subtle nuances lurking in ADO, and the Active Server developer may need to take great care in specifying how and from where recordsets are fetched. As you'll see shortly, the other new demand of ADO programming for those without SQL Server experience is having to understand new terms; for example, cursor. (If you don't know it now, that's okay; you will in a moment.)
Using ADO, perhaps more than any other Server Component highlights the symbiosis between Server Objects and Components. The main reason for this interdependence is that, because Web applications are based on individual HTML pages, these applications can be problematical for the Active Server developer. The problem arises because, while on one hand, the application moves freely and easily between several forms or instances of the same form (as you will see demonstrated later), at the same time, a stable substrate of data underlies the dynamic interplay of user interface. The developer's challenge is to keep this data substrate available to any and all pages that need it.
There are two specific issues facing the ADO developer- performance and reference. Performance issues arise because you cannot spare the bandwidth to make repeated calls to server data stores for the same recordset (just because different pages need it). All pages should refer to the same fetched data.
Reference issues are similar to performance issues but different, because pages often will have to exchange data entered by the user; even more often, they will have to exchange data with each other (whether the data was fetched or entered). One or more pages needs to know what that data is and must be updated if that data is changed by another page in the application.
Figure 15.3 depicts these inter-relationships. A database contains information needed by an ASP application. The Active Server makes the call to fetch the data. If you use Session properties, discussed in the next section, you only have to fetch this data once; all the Active Server Pages will reference this copy of the data as necessary. If you do not use Session properties, then every time an .asp file needs the data, it has to requery the data provider. Also note in the figure that data can "trickle down" from one page to several others. This data may affect and be affected by the data in the original database.
The problem of ADO programming centers in repeated calls to a data provider for data.
ASP has something for just this purpose, Session properties. Remember that VBScript is not yet able to reference a type library at design time and perform early binding. Therefore, all variables in VBScript are of type Variant. The performance issues that normally attend the exclusive use of variants usually are not issues in Active Server programming, because the client apps tend to remain thin. Intelligent design of server components enables the high-performance processing of variable data.
At any rate, Session properties can be created simply by setting them to a value. For example, a RecordSet Object can be created as a Session properties simply by stating the following:
Session("rstAuthors") = rstAuthors
As mentioned, of all the Server Components that ship with ASP, ADO is the most complex and the one with the most long-term impact on the future of programming. In this chapter, we focus on the highest level of the object model and highlight some of the key properties and methods necessary to make ADO work at its most basic level. A key objective of this chapter is to stress that understanding the relationships between features of ADO is more important than an understanding of its parts.
ADO exposes three primary objects to the developer: the Connection Object, the Command Object, and the RecordSet Object. For all practical purposes, the RecordSet Object is the most important; the Connection and Command Objects serve to enable the RecordSet Object's creation.
With version 1.0, everything in OLE DB exists so that data providers can present their data in tabular form (later versions of OLE DB may be able to output object-oriented data and semistructured data). From the perspective of Active Server Pages, the RecordSet Object is the key to the new level of interactivity that data-driven HTML enables.
The Connection Object
ADO's advantage comes from its ability to work in stateless environments. The Connection Object is responsible for recording the necessary information about the data provider from which the RecordSet Object will be created. ADO needs to inform the Windows NT server of the existence of an ODBC data provider by citing a Data Source Name (DSN). Recall that each DSN receives a name; the Connection Object refers to that name with its Open method and records the name in its ConnectionString property. The Open method also needs optional UserID and Password values, should the DSN require them.
Another feature of Connection Objects will be familiar to Access and SQL Server programmers. Like its predecessors, ADO can exploit the I/O efficiency of transactions using the BeginTrans, CommitTrans, and/or RollbackTrans methods. These methods-at least, in Access-are part of the Workspace Object, and you can see how it and the Connection Object exist for the same reason. Think of the Connection Object as the telephone and circuits that enable you to call your mother; they don't do the talking, but without them, there's no conversation.
If the Connection Object is like the telephone, then its Open method is like placing a call, and its Execute method is like opening your mouth. Actually, there are several ways to create a RecordSet Object. Essentially, you can do it with the Connection, Command or even the RecordSet Object itself. For the moment, we remain focused on the Connection Object. shows how you instantiate a Connection Object from the Database Access Component (identified by the ADO progid). You open this object by assiging it to the DSN named "Blotter". And you create a new recordset by invoking the Execute method (in this instance you fetch all the records in a table named "tblBlotter").
Listing 15.1 CH15.ASP- Using the Connection Object to Create a recordset
Set objConn=Server.CreateObject("ADODB.Connection")
objConn.Open("Blotter")
Set objRst=objConn.Execute "tblBlotter"
As mentioned, the Execute method takes a given SQL command and interrogates the DSN with it. Success yields a RecordSet Object that is created with the VBScript Set command. Using this approach creates an implicit RecordSet Object, by the way. This means that ADO has given you a minimalist RecordSet Object; more important, it generates the least powerful cursor. Specifically, the resulting cursor is the row-wise, scroll-forward, read-only variety. ADO, like good programmers, is profoundly lazy (the good kind of laziness, the kind that enables programmers to write great code).
The Command Object
The Command Object provides the second way to create a RecordSet Object. This object also creates a minimal cursor, but it was designed to exploit a key concept in database management: passed parameters. Parameters are variables stored inside queries and stored procedures. Think of queries, and especially stored procedures, as mini-programs compiled by the data provider. Like normal programs, these objects can accept data at runtime that affect how the object behaves. Queries designed like this are called "parameterized queries." For example, if you want to list only certain records from a given table, you pass the name of the field and the value of interest to the parameterized query, and it filters out all other records from the resulting recordset.
Of course, you can always do this by stuffing variables into the SQL statement, but this can be very time consuming and frustrating, especially when you have to contend with embedded double and single quotes. A thorough discussion of the implementation details for parameters is covered in Chapter 16, "Working with ADO's Connection and Command Objects." For now, demonstrates the simplest way to open a recordset with the Command Object.
Listing 15.2 CH15.ASP-Opening a Simple recordset with the Command Object
Set objCmd = Server.CreateObject(ADODB.Command) objCmd.ActiveConnection ="intranet" objCmd.CommandText="qryPhoneMessagesFor" Set objRst = objCmd.Execute
See "Utilizing Command Objects to Their Full Potential" for more information about Command Objects, in Chapter 16.
As you can see, the structure of these commands is very similar to those used by the Connection Object. In order to execute a Command Object you need to tell it which connection to use and what SQL statement to use. In Chapter 17 you will see that there are many other ways to implement these basic requirements, but in all cases, the result is the same as that in .
You have a choice when it comes to creating recordsets with the Connection Object and the Command Object: Refer to database objects such as queries, stored procedures, or table names; or use explicit SQL statements such as "SELECT * FROM tblBlotter". Referring to objects can yield dramatic improvements in performance because they can exploit all the processing power of the data provider. However, using a SQL statement makes your code self-documenting; i.e., you can tell exactly what your program is doing as it interacts with the data provider. In most cases, this advantage of self-documenting code is more than offset by the loss in performance; and besides, you can always explicitly document calls to database objects.
The RecordSet Object
When you use the RecordSet Object to create a recordset, you are using the CreateObject method of the Server Object to instantiate an explicit RecordSet Object. This means that you are responsible for specifying all the properties of the resulting recordset (unless you accept default values). Alternatively, you can create a recordset implicitly by using a Connection Object or the Command Object-but then you have no control over properties. If you need a dynamic cursor that is fully scrollable and permits batch updates, by using the RecordSet Object, that's exactly what you'll get.
Cursors and related constructs are important in ADO, because the ADO developer has complete control over details that can be overlooked safely when using DAO. We focus on cursors as follows, and at the end of this chapter we provide a useful applet that demonstrates the interplay between cursors and concurrency and how both are controlled with RecordSet Object properties.
At this point, you have made the call, connected with the other end, and are ready to start talking. In the same way that a conversation is full of words, recordsets are full of data. To fill the recordset with data, then, you don't use an Execute method; you use the Open method. Like the Execute method, the Open method in a Connection Object context is different from the Open method with the RecordSet Object. With the Connection Object, the Open method opens a channel permitting data to flow; with the RecordSet Object, the Open method fills a recordset with data.
By now code like should be familiar. Regardless of the object and method you use to create a recordset of data, you always need to tell ADO where the database is stored and which table you need to manipulate. Again, the primary syntactical difference with the RecordSet Object is that it is populated with the Open method, not the Execute method.
Listing 15.3 CH15.ASP-The Code Necessary to Create a RecordSet Object
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.Source = "qryBlotterByDate"
objRst.ActiveConnection="Blotter"
objRst.Open
Each RecordSet Object contains a Fields collection of all the Field objects that are in the recordset. By manipulating the Fields collection, you change the structure of the underlying database table. That is, by referring to the Fields collection, you can construct SQL commands to update or otherwise modify the structure of the underlying tables at the data provider.
The real meat of the RecordSet Object, however, is in its methods and properties. To the extent that you do any serious database management using an HTML user interface, your ADO programs probably will use all those methods at one time or another.
Remember, the mission of ADO's underlying interface, OLE DB, is to be able to work with any data provider. Therefore, you would expect a rich set of methods and properties to accommodate all the different flavors, dialects, and technologies used to store data. OLE DB is designed to eventually embrace all formats from data streams to text files to object stores-an ambitious mission.
Properties may see less widespread use than methods (unless you find yourself immersed in such relational arcana as heterogeneous joins). Why? Because some ADO properties are not supported by some data providers. For example, not all data providers support bookmarks-Access does; FoxPro does not-nor do all Access cursors support bookmarks.
We selected the methods and properties in the following sections from the entire ADO object model because they are used in the demo program in the "Database Programming" section at the end of this chapter. Nearly all the ADO code that you write will use them. The chapters that complete this part of the book give more ambitious treatment of ADO coding, and we defer discussion of subjects such as pages and of methods like AddNew, GetRows, and Move to those chapters.
At the end of this section is a table that summarizes the list of methods and properties for each object.
Abandon
The Abandon method applies to Active Server Session Objects. Sessionsare created as soon as a user opens an .asp file in a virtual directory of the Internet Information Server. This session stays open until one of two things happens. Either there is no activity from the user for 20 minutes (or the interval specified in the Timeout property of the Session Object) or an Abandon method is invoked.
If you need access to any connected database before the session expires (for example, to back up the database), you need to Abandon it first.
CreateObject
The CreateObject method applies to the Server Object. This method creates instances of server components (such as TextStream) and ADO objects (such as Connection and RecordSet). The similarity with this method to its namesake in Visual Basic is that the Server Object must be part of the call; namely, Server.CreateObject(), not merely CreateObject().
Open
The Open method applies to Connection and RecordSet Objects. With the Connection Object, the method opens a connection-a channel of communication-to a server; specifically, to a Data Source Name. When invoked by a RecordSet Object, this method opens a cursor in a table in the DSN. The cursor is a current row-pointer within the recordset created with the Open method.
Requery
The Requery method applies to the RecordSet Object created with the Open method. Its function is to re-fire the query that populated the recordset, fetching the current-perhaps updated-values from the underlying database table.
Update
The Update method also is used by the RecordSet Object. It moves the data in the copy buffer to the RecordSet Object. Until this event occurs, the underlying table can have one value and the RecordSet Object another; after the Update method, they have the same data-unless something interfered with the routine processing of updates. Examples include incompatible combinations of CursorType and LockType properties (such as keyset cursors with batch updating in Microsoft Access).
One of the design goals of ADO was to make coding more efficient for programmers. One of the first thing ADO changes is the old Edit method. In ADO, if you want to change a field's value, you simply change it; you no longer need to explicitly invoke the Edit method first (it's implied). The AddNew method, however, is still required, for there is no other way for ADO to know your intentions.
As with methods, we discuss more sophisticated ADO properties in Chapter 16 and Chapter 17. The properties in the following sections are listed in alphabetical order.
ActiveConnection
The ActiveConnection property tells ADO where the data is and how to access it. ActiveConnection functions like a telephone connection in that it enables communication but does not communicate directly. Some ADO connections are like station-to-station long distance. Others are more restricted, like a person-to-person call, limiting data access to only certain people with specific passwords.
CursorType
CursorType is an important property that applies to the RecordSet Object. It determines how hard the data provider has to work to make the records it stores available to your ADO program. The simplest cursor is a forward only, row-wise, read-only cursor. Other data providers can provide dynamic cursors that keep track of the status of underlying data.
LockType
The LockType property applies to RecordSet Objects and controls what results when the RecordSet Object executes its Open method. This property is important because it tells the data provider how to handle concurrency issues, should they arise.
Name
The Name property applies to the Field Object of the RecordSet Object.
Source
The Source property applies to RecordSet Objects. Usually, this is a text string of SQL commands to fetch data from the data provider. A shortcut is to use the name of the table alone; this is quicker than typing SELECT * FROM tblBlotter. Note, however, that if you want only a selected group of records or set of fields, you will have to use a SQL command, a query, or a SQL Server stored procedure.
In this final section of this overview of ADO, you will take a quick look at some database theory, and you will see the source code for the demo program, ch15.asp, that you can download off the book's Web site.
| ON THE WEB |
|
http://www.quecorp.com/asp—Go to this book's Web site to download the demo program, ch15.asp. |
To help you come to terms with recordsets, we take a brief detour into the world of relational database management. The comments that follow are for those who have had little use for databases until now or those who have used database systems but didn't have a need to get into the theory of database management. More experienced readers can safely skip this section. You will read even more about concurrency control in Chapter 17 "Working with ADO's Recordset Object."
I Thought a Cursor Was Only Needed for the Screen All of you are familiar with a cursor. Nearly all are aware of the feeling of panic and helplessness that comes when the cursor becomes invisible or freezes in its (mouse) tracks. Cursors tell you where you are and tell the computer what you want to do next.
In relational database management systems, and in SQL-based systems especially, cursors have a similar but deliberately invisible role to play.
Structured Query Language is called set-based and is fundamentally different from the row-based method used by business applications. It is small wonder that the spreadsheet was the first business application created for the personal computer. Virtually everything done in business can be best expressed in columns and rows.
There's more to life for SQL than business. SQL experiences the world with sets. Granted, the underlying tables of data that SQL uses may be stored in rows and columns-though not always-but the results of combining certain rows and columns results in a set. If you remember Venn diagrams from high school, you know that intersecting sets are the product of combining other sets. Those result sets were the areas where two or more circles overlapped or intersected.
Cursors originally were conceived to bridge the gap between these two worlds of sets and rows. They are a logical entity that represents the sets that SQL produces in a structure that business applications can manipulate. Without them, ADO wouldn't exist.
Concurrency and Locking Issues
There is an adage as old as the Internet that goes something like "Information yearns to be free." In the database business world, there is a related truism: Data needs to be changed. The subject of this word play raises two serious problems for database developers.
On one hand, displayed data (especially when more than one row is being displayed) often needs to be up-to-date. This means that when someone adds or deletes a record, all other displays of data from that table need to reflect the change.
On the other hand, when the value of one or more fields of an existing record gets changed by more than one person and at the same time, there is a potential conflict, a collision of wills. The DBMS must be able to sense these collisions and manage them effectively.
Cursors play a part in both of these situations. More precisely, different cursor types play different roles in these different circumstances. Cursors have two primary flavors-static and dynamic. Static cursors, as their name suggests, can't see additions and deletions made by other users. For example, If a business application that works with only one record at a time is being built, it need not concern itself with the need to update the number of rows in a recordset. This is the kind of recordset that ADO creates by default. Other times, a dynamic cursor is needed.Dynamic cursors sense, on their own, when the number of rows or the content of fields changes.
Not all data providers support dynamic cursors.
The ASP developer also needs to address the issue of concurrency and the related issue of locking. Locking techniques fall into two categories: optimistic and pessimistic. Optimistic locking is relatively easy for the DBMS to implement, for it assumes that collisions and conflicts will be rare and doesn't activate locks until just before updating, and only if a conflict exists. Pessimistic locking assumes the opposite, and locks on data are required before processing a record can even begin. Again, not all DBMSs support both, and when some do, they don't give you a choice between the two.
In one sense, these issues make coming to terms with relational databases a little easier. The choice isn't whether to use optimistic or pessimistic locking; it's whether to buy Microsoft Access (where locking is automatic and both types are supported) or to use an old version of FoxBase+ (where pessimistic locking must be done manually in code) as your DBMS. The point is, if your database engine doesn't support a relational feature, you have to design around that limitation or pick another engine.
See "Understanding Concurrency Control" for more information about concurrency and locking, in Chapter 17.
Keys, Indexes, and Bookmarks
Relational Database Management Systems always work more efficiently if they can uniquely identify individual records. They also work more efficiently if certain fields are indexed; that is, put in order, such as last names in alphabetical order. Tables need keys to do both these things. Indexes sort records based on the values of these key fields; if these values are unique, they serve double duty-they sort and uniquely identify records. In addition, if a SQL Server table has a key, it can have a keyset cursor. Access isn't as picky; a table of two fields and no indexes does not return an error when a dynaset (the closest thing to a keyset cursor that Access has) is created and updated.
In the demo program in the next section, you will see for yourself the effect on the underlying database table when you combine the CursorType and LockType properties with an Access database.
One more concept: bookmarks. Bookmarks are to cursors what cursors are to recordsets-they are placeholders. Some DBMSs (like FoxPro) keep track of record numbers. The Jet engine in Access and ADO do not. Instead, they rely on bookmarks to move the cursor to a previous location in a recordset. As you might guess, bookmarks are not supported by all cursors. Remember that dynamic cursors get updated when records are added or deleted. As a result, bookmarks aren't supported (in part, because the row that they used to represent may be gone). Only static and keyset cursors (and dynasets in Access) support bookmarks.
So if you are working on recordsets with more than one record, and you need to be able to wander around this recordset as if it were a spreadsheet, you need to choose your cursor type carefully.
SQL can get complex quickly; it was not designed to be read by humans. You can write your own SQL code by hand, if you want, but we don't recommend it. We recommend that you create SQL code in your application of choice. Our favorite is Microsoft Access; its QBE grid is the most amazing piece of code that we have come across in our years of programming. Perhaps SQL Server or something else works better for you. Regardless of the modeling method, when you have a SQL command that does what you need, cut the SQL code and paste it into your .asp file.
As this book was going to press, Microsoft's Visual InterDev application was coming out of beta testing. One of it's most important features is modeled after, and significantly extends, the QBE grid of Access.
We discuss the following demo program in three sections: initialization code, form processing, and database programming. This program has the great virtue of simplicity, and at this stage, clarity is more important for you than virtuosity.
| ON THE WEB |
|
http://www.quecorp.com/asp—The entire .asp file, ch15.asp is available for download from this book's Web site. |
One of the greatest virtues of publishing on the Web is that we can make changes quickly and often with almost zero cost of production. For that reason, the actual code you download from the Web will be named ch15.asp, but it may not look exactly like the code printed in the book. If the Web code is different, it's because it's better. If you think it can be improved yet again, by all means let us know.
Initialization Code
Listing 15.4 describes what the Chapter 15 demo program is for. It includes the standard HTML code for the beginning of HTML files.
'******************************************************************* ' Description: ' An Active Server Program (ASP) file that demonstrates ' many of the tips and techniques discussed in the book. ' Most importantly, it illustrates the effect of the interaction ' of two key recordset properties: CursorType and LockType. ' It also demonstrates the reliance these properties have on the ' underlying database management system (i.e., some DBMSs ' do not support all CursorType or LockType values. In the context ' of a cosmopolitan programming world like ADO, this is important ' information. ' Arguments: ' This program calls itself so when the txtCursorType and ' txtLockType fields have value, they are passed back into the ' program. ' Returns: ' Nothing ' Modified: ' Michael Corning 10/20/96. Final preparations for publication. '******************************************************************* <HTML> <HEAD> </HEAD> <BODY BGCOLOR="#EEEEEE" TEXT="#000000" LINK="#CC0000" ALINK="#FF3300" [ic][ccc]VLINK="#330099"> <FONT FACE="Verdana" SIZE=2>
Next, some housekeeping chores. The two ways of indicating a comment in VBScript are shown in , as well as a test to see if you need to abandon an ADO session.
Listing 15.5 CH15.ASP-The Two Ways to Comment VBScript Code
' Note: using apostrophe for comments only works if they
' are inside the vbs tags (which you cannot use inside comments
' like this). Compare below using HTML comment.
REM You can also use the explicit REM statement instead of the
REM apostrophe.
REM When you're done, abandon the session so that you can
REM backup, move, or otherwise manipulate the underlying database
REM file. The session will abandon in 20 minutes, otherwise.
If Request("cmdQuit") ="Close" Then%>
<FONT COLOR="Red">Closing connection</FONT><P>
<%Session.Abandon
Else
If IsObject(Session("SessionConnection")) Then
Set objConn = Session("SessionConnection")
%>
<FONT COLOR="Green">Using the Cached Session connection</FONT><P>
<% Else
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Blotter")
Session("SessionConnection") = objConn
' Since we are experimenting with different Recordset
' Objects in this ASP file, we have moved the
' instantiation of it to the code block below that
' sets the recordsets
' properties for use by its Open method.
' Set objRst = Server.CreateObject("ADODB.Recordset")%>
<FONT COLOR="Yellow">Opening the Connection</FONT><P>
<% End If
' Following two code blocks are necessary if you want to
' be able to use a default value
' AND be able to maintain your field entries between
' successive ASP file calls.
If Request("txtCursorType") ="" Then
intCursorType = 3
Else
intCursorType = Request("txtCursorType")
End If
If Request("txtLockType") ="" Then
intLockType = 3
Else
intLockType = Request("txtLockType")
End If
%>
Form Processing
Listing 15.6 continues from the previous listing. It is simple, straightforward HTML form code. The only things that differ from standard HTML are in the beginning of this section: The form action calls the same file (namely, adoBasics.asp) that contains itself. This is similar to recursion but without the overhead or risk of running out of stack space. The second difference from orthodox HTML code is that values for form controls are variables (and, in Listing 15.5, are passed between calls to the adoBasics.asp file).
Listing 15.6 CH15.ASP-How to Set Up a Form in an .asp File
<FORM ACTION="adoBasics.asp" METHOD="POST">
<TABLE CELLPADDING=3 BORDER=1 CELLSPACING=0 WIDTH=600>
<TR>
<TD COLSPAN=2>Cursor Type:
<!--
Remember to use HTML comment delimiters when you are commenting outside the vbs delimiters. Remember to put "=" before the Request object so that its value is returned for the default value of the TEXT box.
-->
<INPUT TYPE="TEXT"
NAME="txtCursorType"
SIZE=3
VALUE="<% =intCursorType %>"></TD>
<TD COLSPAN=2>Lock Type:
<INPUT TYPE="TEXT"
NAME="txtLockType"
SIZE=3
VALUE="<%= intLockType %>">
<!--
These command buttons have a name,
so the subsequent calls to this ASP file
can tell when they've been pushed. Without a
name, a command button serves only to fire the
form.
-->
<INPUT TYPE="SUBMIT"
VALUE="Edit"
NAME="cmdEdit">
<INPUT TYPE="SUBMIT"
VALUE="Close"
NAME="cmdQuit"></TD>
</TR>
<TR>
<TH><FONT SIZE="1">CursorType</TH><TH><FONT SIZE="1">
Description</TH>
<TH><FONT SIZE="1">LockType </FONT></TH><TH><FONT SIZE="1">
Description</FONT></TH>
</TR>
<TR>
<TD><FONT SIZE="1">0 </FONT></TD>
<TD><FONT SIZE="1">Keyset</FONT></TD>
<TD><FONT SIZE="1">-1 </FONT></TD>
<TD><FONT SIZE="1">Provider determines (usually read-only)
</FONT></TD>
</TR>
<TR>
<TD><FONT SIZE="1">1 </FONT></TD>
<TD><FONT SIZE="1">ForwardOnly</FONT></TD>
<TD><FONT SIZE="1">1 </FONT></TD>
<TD><FONT SIZE="1">Read-only</FONT></TD>
</TR>
<TR>
<TD><FONT SIZE="1">2 </FONT></TD>
<TD><FONT SIZE="1">Dynamic</FONT></TD>
<TD><FONT SIZE="1">2 </FONT></TD>
<TD><FONT SIZE="1">Row-by-row pessimistic locking
</FONT></TD>
</TR>
<TR>
<TD><FONT SIZE="1">3 </FONT></TD>
<TD><FONT SIZE="1">Static</FONT></TD>
<TD><FONT SIZE="1">3 </FONT></TD>
<TD><FONT SIZE="1">Row-by-row optimistic locking
</FONT></TD>
</TR>
<TR>
<TD><BR></TD>
<TD><BR></TD>
<TD><FONT SIZE="1">4 </FONT></TD>
<TD><FONT SIZE="1">Optimistic batch updates</FONT></TD>
</TR>
</TABLE>
</FORM>
Database Programming
This final section, Listing 15.7,is the important stuff. A few things are worth emphasizing. First, in all ADO code there is no Edit method for the RecordSet Object. When you want to change a recordset field value, just change it. The Update method takes care of the rest of the overhead.
Second, the Requery method flushes out the data of local storage and attempts to permanently change the underlying database cursor. If everything worked as expected with the Update method, the Requery results in the new value.
Also note that we use a compiled query stored in the Access DSN. This provides any performance gains that might be invested in a cleverly designed Jet query. Finally, note the use of variables for setting the values of the key properties, CursorType and LockType. The user of the HTML form specifies these variables.
Listing 15.7 CH15.ASP-Real Database Programming in an .asp File
<%
If intLockType < -1 Or intLockType > 4 Or intLockType = 0 Then
Response.Write("Select a lock type value between -1 and 4 (but not 0)")
ElseIf intCursorType < 0 Or intCursorType > 3 Then
Response.Write("Select a cursor type value between 0 and 3")
Else
' When ASP first called, Edit button has null value.
If Request("cmdEdit") = "Edit" Then
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.LockType = intLockType
' Pick up the entered type
objRst.Source = "qryBlotterByDate"
objRst.CursorType = intCursorType
Set objRst.ActiveConnection=objConn
objRst.Open%>
<TABLE CELLSPACING=0 BORDER=1 WIDTH=600>
<TR>
<TD><BR></TD><TH>Before</TH><TH>After</TH>
</TR>
<%For i=1 to objRst.Fields.Count-1%>
<TR>
<TD ><FONT SIZE="2">
<%If objRst(i).Name="Rep" Then%>
<B>Does Rep field value change?</B>
<%else
=objRst(i).Name
End If%>
</FONT></TD>
<TD>
<FONT SIZE="2"><%=objRst(i)%></FONT>
</TD>
<% If objRst(i).Name = "Rep" Then
' oldRep used below to see if the update
' succeeded
oldRep = objRst("Rep")
' We're hard coding the field change here.
' Normally, the field data is edited
' with forms; but since there are no data
' bound HTML form controls, getting
' these new values into the recordset and
' the cursor at the data provider is
' not trivial. We take up the challenge in
' Chapter 17.
objRst("Rep") = objRst("Rep")+1
objRst.Update
' Note: If you don't requery the recordset
' after a failed update then the recordset
' value remains changed, but the underlying
' cursor is not.
objRst.Requery
End If %>
<TD>
<% If objRst(i).Name = "Rep" Then
' See if the correct combination of
' CursorType and LockType properties
' were selected for the Access database.
' If so, the new value will be on
' the screen and in the cursor. SQL Server
' and other data providers may
' respond differently than Access using the
' same property values.
' Caveat developer.
If oldRep = objRst("Rep") Then%>
<FONT SIZE="2"><%=objRst(i)%> <B>NO</B></FONT>
<%Else%>
<FONT SIZE="2"><%=objRst(i)%> <B>YES</B></FONT>
<%End If
Else%>
<FONT SIZE="2"><%=objRst(i)%></FONT>
<%End If%>
</TD>
</TR>
<%next%>
</TABLE>
<%End If
End if
End If%>
</FONT>
</BODY>
</HTML>
For Your Part
We encourage you to try the .asp file for yourself. Experiment with it and keep track of the combinations of property values and outcomes. Remember that you are using an Access database and that other data providers might produce different results.
We also encourage you to make a working copy of this file on your own hard drive and experiment with other DSNs or tables in the referenced Access database. The program is fairly generic (except for the explicit field name, Rep), so relatively few changes will be necessary for you to use it with other Access tables, SQL Server, or other DBMSs.
This chapter introduced what may be the most important part of the Active Server: ActiveX Data Objects. ADO is the next generation of database technology from Microsoft. It retains all the power of the OBDC specification but extends this power to the Internet. Combined with DCOM to access data providers in widely dispersed locations, ADO will be even more powerful than RDO.
Also in this chapter, you explored some of ADO's most fundamental aspects and saw some of its most important points in a demonstration program. As exciting as this is, it still isn't "real database programming." We still haven't discussed what you can do with this new power.
For that, turn to the following:
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.