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.
NOTE
This tutorial only decribes a fraction of what can be done with ADO.
There are many ways to retrieve, add and change data using ADO. This tutorial only describes:
- How to read data
- How to use the OLEDB provider, ODBC and other providers are not part of this tutorial
- The command to retrieve data is specified with the Open method for the RecordSet object. The Command object is not part of this tutorial
Internals
ADO is made up of four collections and twelve objects. For details please see page → ADO Internals.
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
- 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
- Close the recordset
- 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
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 and is composed of records (rows) and fields (columns).
The Recordset object is the heart of ADO. Via this object, we can select desired data and change the data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.
RecordSet.Open(Source, Connection, CursorType, LockType, Options)
The Open method is called on a Recordset object to open a cursor which gives you access to the records contained in the base table, the results from a query (used in the examples of this tutorial), or a previously saved Recordset.
For details please see page → ADO RecordSet Populate.
Search/process the fetched data
ADO provides methods and properties to
- move around in the RecordSet
- search for rows
- write the recordset to a 2D array or a record to a string
- save the recordset to a file
- sort the records
- delete rows from the RecordSet
and much more.
For details please see page → ADO RecordSet Process.
Commit the changes
Not covered by this tutorial (at the moment).
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.