ASP ScriptingJava ScriptingCGI ScriptingHTMLJavaLanguage CERP Education Links

Introduction to ADO and OLE DB

  Looking Back

     Databases and database management systems have come a long way since the primitive ISAM (Indexed Sequential Access Method) files, like those of Foxpro and Visual Foxpro. Microsoft Access uses a file format that is accessed using DAO (Data Access Object), or, specifically, Jet DAO. With the varying kinds of databases and the growing need to share data, ODBC (Open Database Connectivity) was developed. ODBC is like an interpreter-it can talk to most databases and transfer information from one to another.

     Over the years, numerous applications have proven the importance of databases as both, single user applications as well as client/server applications over a network. The primary difference between any of these applications and a Web application is the connection. Any desktop application creates a persistent connection to the database between the client and the server, which terminates after the entire transaction is complete. The Web, on the other hand, is a connectionless and stateless network. There are no means for maintaining persistent connections to the database from the client. Once a request is made to a database, the database is opened, the request is executed and the database is closed. For any further requests, or even the same request, the client has to connect to the database again.

  ADO and OLE DB

     The ODBC API was built on the C language. It was meant to work with relational database management systems. The API for OLE DB is based on the C++ language, which essentially means that it is object-oriented. Objects hold methods and properties. Hence, OLE DB allows for extension of the Web server by providing objects which expose these methods and properties for use.

     An advantage of OLE DB over DAO or RDO (Remote Data Object) is that it connects directly to the data source. DAO and RDO, on the other hand, have to pass through the ODBC layer. This enhances speeds for most database requests.
However, OLE DB only provides access. It is a low level specification and its use would mean learning a new language altogether. This is where ADO plays an important part. ActiveX components can be built using any language that complies with the Component Object Model. ADO is one such component that can form an interface to the OLE DB API and allows you to access all features of OLE DB without the need to learn any new technology. Think of ADO as a fan switch . You don't have to turn the blades of the fan around for it to rotate (although you could). You simply flick the switch and the rest of the job is taken care of by the electrical network between the switch and the motor. ADO allows you to do just that by providing a simple interface to a complicated object.
The most important factor is that OLE DB and ADO are constructed to work in a stateless and connectionless environment. The server (rather, the Active Server) does disconnect from the client after the request is processed, but it remains connected long enough to enable the application to make subsequent calls and still have access to previously returned data.

  The ADO Object Model

     ADO primarily provides three objects: the Connection Object, the Command Object and the Recordset Object. Let's take a look at these briefly and study them in detail later.

  The Connection Object

     The most important factor of ADO is its ability to work in a stateless environment. The Connection Object provides this functionality by keeping a tab on the data provider from where the necessary data is retrieved. The Connection Object represents the connection made to the data source through the OLE DB data provider and handles all communication between the application and a data source. It stores the information about the ODBC data provider, the DSN (Data Source Name) and the user Id and password required to access this data source. You open and close a Connection Object by using the Open and C 1 o s e methods. Once the data connection is opened, you can retrieve data as a recordset using the Connection Object itself. This is an implicit recordset which generates a very simple read-only cursor that cannot be controlled much by the developer.

  The Command Object

      The Command Object can also create read-only cursors, similar to that of the Connection Object. The Command Object represents any command that can be processed by the data source. The strongest point of this object is the ability to pass parameters to and from parameterised queries. The Command Object includes a Parameters collection that can accept input and output parameters. You can execute a command string on a Connection Object or pass a query as part of opening a Recordset Object without explicitly creating a Command Object.

  The Recordset Object

     When you instantiate a Recordset Object, you create an instance of an explicit Recordset Object. This allows you more control over the object and you can create dynamic cursors that permit updates. The Recordset Object represents a set of records returned from a query. You can open a Recordset Object without explicitly opening a Connection Object by passing a connection string to the Recordset Object's Open method. Using a Connection Object, however, lets you open multiple Recordset Objects on the same connection. We now have a brief idea of the objects we will be working with for database programming with VBScript. Let us go on to see these objects in action.