ADO

This page is still a work in progress.

Most of the following text was taken from Wikipedia, connectionstrings.com:

Microsoft's ActiveX Data Objects ( ADO) is a set of Component Object Model (COM) objects for accessing data sources. As part of MDAC, it provides a middleware layer between programming languages and OLE DB (a means of accessing data stores, whether they be databases or otherwise, in a uniform manner). ADO allows a software developer to write programs that access data without knowing how the database is implemented; developers must be aware of the database for connection only. No knowledge of SQL is required to access a database when using ADO, although one can use ADO to execute SQL commands directly. 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 Remote Data Objects (RDO) and Data Access Objects (DAO). ADO was introduced by Microsoft in October 1996.

Internals

ADO is made up of four collections and twelve objects.

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 (used for semi-structured 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 is 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.

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.

Usage

Some basic steps are required in order to be able to access and manipulate data using ADO:

1. Create a connection object to connect to the database
2. Create a recordset object in order to receive data in
3. Open the connection
4. Populate the recordset
5. Do all the desired searching/processing on the fetched data
6. Commit the changes you made to the data (if any) by using Update or UpdateBatch methods
7. Close the recordset
8. Close the connection

Create a connection object

In AutoIt this is simply done by:

Global $oConnection = ObjCreate("ADODB.Connection")

Create a recordset object

In AutoIt this is simply done by:

Global $oRecordSet = ObjCreate("ADODB.Recordset")

Open the connection

Application-ADO-Provider-Database

To open the connection you have to build a connection string or directly set the properties of the connection.

When your application connects to a database or a data file you let ADO utilize a provider to do the job for you. The connection string contains the information that the provider need to know to be able to establish a connection to the database or the data file. Because there are different providers and each providers have multiple ways to make a connection there are many different ways to write a connection string. It's like the address when sending a regular mail. Depending on the origin and destination and who is going to make the transport you need to write down the address in different ways.

Connection string

The connection string is really just a string inside the application you pass on to the connection object. The property is named ConnectionString or is passed through the Open function of the connection object.

As a string, there are no built in checks or constraints on how to format the connection string. A few rules to follow taken from www.connectionstrings.com

• All blank characters, except those placed within a value or within quotation marks, are ignored
• Blank characters will though affect connection pooling mechanism, pooled connections must have the EXACT same connection string
• If a semicolon (;) is part of a value it must be delimited by quotation marks (")
• Use a single-quote (') if the value begins with a double-quote (")
• Conversely, use the double quote (") if the value begins with a single quote (')
• No escape sequences are supported
• The value type is NOT relevant
• Names are case iNsEnSiTiVe
• If a KEYWORD=VALUE pair occurs more than once in the connection string, the value associated with the LAST occurrence is used. But!... if the PROVIDER keyword occurs multiple times in the string, the FIRST occurrence is used.
• If a keyword contains an equal sign (=), it must be preceded by an additional equal sign to indicate that it is part of the keyword.

A connection string consists of a list of argument/value pairs (that is, parameters), separated by semi-colons. For example: "arg1=val1; arg2=val2; ... argN=valN;" All the parameters must be recognized by either ADO or the specified provider. ADO recognizes the following five arguments in a connection string.

Argument	Description
Provider	Specifies the name of a provider to use for the connection.
File Name	Specifies the name of a provider-specific file (for example, a persisted data source object) containing preset connection information.
URL	Specifies the connection string as an absolute URL identifying a resource, such as a file or directory.
Remote Provider	Specifies the name of a provider to use when opening a client-side connection (Remote Data Service only).
Remote Server	Specifies the path name of the server to use when opening a client-side connection (Remote Data Service only).

Other arguments are passed to the provider named in the Provider argument, without any processing by ADO.

Depending on the data source you want to connect to the connection string varies.
The following pages document connection strings for OLEDB providers only.

• IBM DB2
• Microsoft Access
• Microsoft Excel
• Microsoft Project
• Microsoft SQL Server
• MySQL
• Oracle Database Server
• SQLite
• Text files

Connection properties

To be added.

Populate the recordset

The ADO Recordset object is used to contain the set of data extracted from a database.

The Recordset object is composed of records (which are also referred to as rows) and of fields (which are also referred to as columns).

The Recordset object should be considered to be the heart of ADO. Via this object, we can select desired data and change the data with additions, deletions, and updates. Equally important is the ability to move around inside the database. In fact, the Recordset object is blessed with an extremely comprehensive selection of properties, collections, methods, and events that allow extensive manipulation of the retrieved data and interpretation of the operational environment. However, the functionality of the provider may impose limitations. For example, some properties may not be available to the Recordset object depending on which provider is being accessed. You can use the Supports method to predetermine if a Recordset object will support a specific type of functionality.

In AutoIt we use:

Global $oRecordSet.Open($sSQL, $oConnection, $iCursorType, $iLockType, $sOptions)

Search/process the fetched data

To be added.

Commit the changes

To be added.

Close the recordset

Close the recordset and assign a new value to the object to release it:

$oRecordSet.Close()
$oRecordSet = 0

Close the connection

Close the connection and assign a new value to the object to release it:

$oConnection.Close()
$oConnection = 0

Examples

• IBM DB2
• Microsoft Access
• Microsoft Excel
• Microsoft Project
• Microsoft SQL Server
• MySQL
• Oracle Database Server
• SQLite
• Text files

Tools

Here are some (AutoIt) tools to make life easier.

External links

ADO

• Microsoft ADO page
• ADO object model
• DevGuru ADO Quick Reference
• w3schools - learn ADO

Connection Strings

• Database connection strings
• ODBC DSN-Less connection strings