Component Object Model is a binary-interface standard for software componentry introduced by Microsoft in 1993. It is used to enable interprocess communication and dynamic object creation in a large range of programming languages...
(COM) objects for accessing data sources. A part of MDAC, it provides a middleware
Middleware
Middleware is computer software that connects software components or people and their applications. The software consists of a set of services that allows multiple processes running on one or more machines to interact...
A programming language is an artificial language designed to communicate instructions to a machine, particularly a computer. Programming languages can be used to create programs that control the behavior of a machine and/or to express algorithms precisely....
OLE DB is an API designed by Microsoft for accessing data from a variety of sources in an uniform manner. It is a set of interfaces implemented using the Component Object Model ; it is otherwise unrelated to OLE...
(a means of accessing data stores, whether they be database
Database
A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...
s or otherwise, in a uniform manner). ADO allows a developer
Software developer
A software developer is a person concerned with facets of the software development process. Their work includes researching, designing, developing, and testing software. A software developer may take part in design, computer programming, or software project management...
to write programs that access data without knowing how the database is implemented. He must be aware of the database for connection only. No knowledge of SQL
SQL
SQL is a programming language designed for managing data in relational database management systems ....
is required to access a database when using ADO, although one can use ADO to directly execute SQL commands. The disadvantage of the latter is that it introduces a dependency upon the type of database used.
ADO is positioned as a successor to Microsoft's earlier object layers for accessing data sources, including RDO
Remote Data Objects
Remote Data Objects is the name of an obsolete data access application programming interface primarily used in Microsoft Visual Basic applications on Windows 95 and later operating systems. This includes database connection, queries, stored procedures, result manipulation, and change commits...
Jet Data Access Objects is a deprecated general programming interface for database access on Microsoft Windows systems. It is unrelated to the data access object design pattern used in object-oriented software design.- History :...
(Data Access Objects). ADO was introduced by Microsoft
Microsoft
Microsoft Corporation is an American public multinational corporation headquartered in Redmond, Washington, USA that develops, manufactures, licenses, and supports a wide range of products and services predominantly related to computing through its various product divisions...
in October 1996.
ADO collections
Fields
This collection contains a set of Field objects. The Collection can be used in either a Recordset object or in a Record object. In a Recordset object, each of the Field objects that make up the Fields collection corresponds to a column in that Record set object. In a Record object, a Field can be an absolute or relative URL that points into a tree-structured namespace
Namespace (computer science)
A namespace is an abstract container or environment created to hold a logical grouping of unique identifiers or symbols . An identifier defined in a namespace is associated only with that namespace. The same identifier can be independently defined in multiple namespaces...
Semi-structured data is a form of structured data that does not conform with the formal structure of tables and data models associated with relational databases but nonetheless contains tags or other markers to separate semantic elements and enforce hierarchies of records and fields within the data...
providers like the Microsoft OLE DB Provider for Internet Publishing) or as a reference to the default Stream object associated with that Record object.
Properties
An object can have more than one Property object, which are contained in the object's Properties collection.
Parameters
A Command object can have several Parameter commands to change its predefined behaviour, and each of the Parameter objects are contained in the Command object's Parameters collection
Errors
All provider created errors are passed to a collection of Error objects, while the Errors collection itself is contained in a Connection object. When an ADO operation creates an error, the collection is cleared and a new group of Error objects are created in the collection.
ADO objects
Connection
The connection object is ADO's connection to a data store via OLE DB. The connection object stores information about the session and provides methods of connecting to the data store. As some data stores have different methods of establishing a connection, some methods may not be supported in the connection object for particular OLE DB providers. A connection object connects to the data store using its 'Open' method with a connection string which specifies the connection as a list of key value pairs (for example: "Provider='SQLOLEDB';Data Source='TheSqlServer'; Initial Catalog='Northwind';Integrated Security='SSPI';"). The start of which must identify the type of data store connection that the connection object requires:
an OLE DB provider (for example SQLOLEDB), using the syntax "provider=";
a file name, using the syntax "file name=";
a remote provider and server (see RDS), using the syntax "Remote provider=" and "Remote server="; or
an absolute URL, using the syntax "URL="
Command
After the connection object establishes a session to the data source, instructions are sent to the data provider via the command object. The command object can send SQL queries directly to the provider through the use of the CommandText property, send a parameterised query or stored procedure through the use of a Parameter object or Parameters collection or run a query and return the results to a dataset object via the Execute method. There are several other methods that can be used in the Command object relating to other objects, such as the Stream, RecordSet or Connection objects.
Recordset
A recordset is a group of records, and can either come from a base table or as the result of a query to the table. The RecordSet object contains a Fields collection and a Properties collection. The Fields collection is a set of Field objects, which are the corresponding columns in the table. The Properties collection is a set of Property objects, which defines a particular functionality of an OLE DB provider. The RecordSet has numerous methods and properties for examining the data that exists within it. Records can be updated in the recordset by changing the values in the record and then calling on the Update or UpdateBatch method. Adding new records is performed through the AddNew function and then by calling on the Update or UpdateBatch method. Records are also deleted in the recordset with the Delete method and then by calling on the Update method. However, if for some reason the deletion cannot occur, such as because of violations in referential integrity
Referential integrity
Referential integrity is a property of data which, when satisfied, requires every value of one attribute of a relation to exist as a value of another attribute in a different relation ....
, then the recordset will remain in edit mode after the call to the Update method. The programmer must explicitly call on the CancelUpdate function to cancel the update. Additionally, ADO can rollback transactions (if this is supported) and cancel batch updates. Recordsets can also be updated in one of three ways: via an immediate update, via a batch update, or through the use of transactions:
Immediate
The recordset is locked using the adLockOptimistic or adLockPessimistic lock. The data are updated at the data source after the record is changed and the Update method is called.
Batch
The recordset is locked using adLockBatchOptimistic and each time Update is called the data are updated in a temporary buffer. Finally, when UpdateBatch is called the data are completely updated back at the data source. This has the advantage of it all being done in memory, and if a problem occurs then UpdateCancel is called and the updates are not sent to the data source
Transaction
If the OLE DB provider allows it, transactions can be used. To start the transaction, the programmer invokes the BeginTrans method and does the required updates. When they are all done, the programmer invokes the CommitTrans method. RollbackTrans can be invoked to cancel any changes made inside the transaction and rollback the database to the state before the transaction began
Record
This object represents one record in the database and contains a fields collection. A RecordSet consists of a collection of Record objects.
Stream
A stream, mainly used in a RecordSet object, is a means of reading and writing a stream of bytes. It is mostly used to save a recordset in an XML format, to send commands to an OLE DB provider as an alternative to the CommandText object and to contain the contents of a binary or text file.
Parameter
A parameter is a means of altering the behaviour of a common piece of functionality, for instance a stored procedure might have different parameters passed to it depending on what needs to be done; these are called parameterised commands.
Field
Each Record object contains many fields, and a RecordSet object has a corresponding Field object also. The RecordSet object's Field object corresponds to a column in the database table that it references.
Property
This object is specific to the OLE DB provider and defines an ability that the provider has implemented. A property object can be either a built-in property — it is a well-defined property implemented by ADO already and thus cannot be altered — or can be a dynamic property — defined by the underlying data provider and can be changed
Error
When an OLE DB provider error occurs during the use of ADO, an Error object will be created in the Errors collection. Other errors do not go into an Error object, however. For instance, any errors that occur when manipulating data in a RecordSet or Field object are stored in a Status property.SK
Basic usage
Some basic steps are required in order to be able to access and manipulate data using ADO :
Create a connection object to connect to the database.
Create a recordset object in order to receive data in.
Open the connection
Populate the recordset by opening it and passing the desired table name or SQL statement as a parameter to open function.
Do all the desired searching/processing on the fetched data.
Commit the changes you made to the data (if any) by using Update or UpdateBatch methods.
Active Server Pages , also known as Classic ASP or ASP Classic, was Microsoft's first server-side script engine for dynamically-generated Web pages. Initially released as an add-on to Internet Information Services via the Windows NT 4.0 Option Pack Active Server Pages (ASP), also known as Classic...
example using ADO to select the "Name" field, from a table called "Phonebook", where a "PhoneNumber" was equal to "555-5555".
dim myconnection, myrecordset, name
set myconnection = server.createobject("ADODB.Connection")
set myrecordset = server.createobject("ADODB.Recordset")
set myrecordset = nothing
set myconnection = nothing
This is equivalent to the following ASP code, which uses plain SQL instead of the functionality of the Recordset object:
dim myconnection, myrecordset, name
set myconnection = server.createobject("ADODB.connection")
myconnection.open mydatasource
set myrecordset = myconnection.execute("SELECT Name FROM Phonebook WHERE PhoneNumber = '555-5555'")
name = myrecordset(0)
Software support
ADO is supported in ASP, CodeGear Delphi, PowerBuilder
PowerBuilder
PowerBuilder is an integrated development environment owned by Sybase, a division of SAP. It has been in use since 1991, peaking around 1998 with around 100,000 users....
