ADO: Difference between revisions

From AutoIt Wiki
Jump to navigation Jump to search
m (→‎External links: www.devguru.com: ADO Quick Reference)
 
(41 intermediate revisions by 4 users not shown)
Line 1: Line 1:
{{WIP}}
Most of the following text was taken from [http://en.wikipedia.org/wiki/ActiveX_Data_Objects Wikipedia] and [http://www.connectionstrings.com connectionstrings.com]:
Most of the following text was taken from [http://en.wikipedia.org/wiki/ActiveX_Data_Objects Wikipedia], [http://www.connectionstrings.com connectionstrings.com]:


Microsoft's '''ActiveX Data Objects''' ( ADO) is a set of [http://en.wikipedia.org/wiki/Component_Object_Model 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.
Microsoft's '''ActiveX Data Objects''' (ADO) is a set of [http://en.wikipedia.org/wiki/Component_Object_Model 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.
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.<br />
<br />


 
There are many ways to retrieve, add and change data using ADO. This tutorial only describes a fraction of what can be done with ADO:
'''NOTE'''<br />
This tutorial only decribes a fraction of what can be done with ADO.<br />
There are many ways to retrieve, add and change data using ADO. This tutorial only describes:
* How to read data
* How to read data
* How to use the OLEDB provider, ODBC and other providers are not part of this tutorial
* 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
* To open the connection a connection string is used. Setting the properties of the connection object to describe the connection is 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<br />
<br />
<br />


== Internals ==
Microsoft '''ActiveX Data Objects Extensions for Data Definition Language and Security''' (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.<br />
ADO is made up of four collections and twelve objects.
ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.<br />
''ADOX is not part of this tutorial!''<br />
<br />


== ADO collections ==
== How does ADO, OLEDB and ODBC fit together? ==
; Fields
This chart shows the "big picture" so you can see how this all fits together (Picture taken from http://community.qlik.com/thread/106540).<br />
: 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.
[[File:db-full-diagram.png|600px|How does ADO, OLEDB and ODBC fit together?]]


; Properties
=== ADO ===
: An object can have more than one Property object, which are contained in the object's Properties collection.
ADO (ADODB) is a generic (COM) database library, that can be used by programming languages to access any type of database for which an OLEDB Provider has been developed.<br />
In this context, ADO is an OLEDB ''Consumer''. It communicates with the OLEDB Provider, which in turn communicates with the database directly or a database server.<br />
For example, to open an Access database, the Connection object of ADODB would specify the Jet OLEDB Provider in its connection string to open and subsequently communicate with the database.<br />
There is also an OLEDB driver that can wrap any ODBC driver. Thus ADO can also talk to any database that exposes an ODBC driver.


; Parameters
=== OLEDB ===
: 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
OLEDB is a Microsoft standard that defines a set of API (Application Interface) functions for accessing all kind of databases (oracle, db2, MS-based databases etc.).<br />
It is a COM (Component Object Model) API that was a follow-up to the ODBC API.<br />
Typically OLEDB is used to create a database specific driver, known as a ''provider'', that can be implemented by a higher level data access library such as ADO.<br />


; Errors
=== ODBC ===
: 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.
ODBC is a standard API for accessing SQL based database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.<br />
This is accomplished by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS.<br />
There are commercial products available to access Applications and WebAPIs like Google Sheets, Microsoft SharePoint, Twitter and so on.


== ADO objects ==
== Internals ==
; Connection
ADO is made up of four collections and twelve objects. For details please see page → [[ADO Internals]].
: 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 ==
== Usage ==
Line 79: Line 48:
#Populate the recordset
#Populate the recordset
#Do all the desired searching/processing on the fetched data
#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 recordset
#Close the connection
#Close the connection
Line 96: Line 64:


=== Open the connection ===
=== Open the connection ===
[[File:ADO.gif|framed|right|Application-ADO-Provider-Database]]To open the connection you have to build a connection string or directly set the properties of the connection.
[[File:ADO.gif|framed|right|Application-ADO-Provider-Database]]To open the connection you have to build a connection string.
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.
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 needs 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.  
Because there are different providers and each provider has 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 ====
==== 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.  
The connection string is really just a string inside the application you pass on to the connection object. The property 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.
For details please see page → [[ADO ConnectionString]].
A few rules to follow taken from [http://www.connectionstrings.com/Articles/Show/important-rules-for-connection-strings 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.
{| class="wikitable"
|-
! 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.<br />
The  following pages document connection strings for OLEDB providers only.
* IBM DB2
* Microsoft Access
* [[ADO_ConnectionString_Excel|Microsoft Excel]]
* Microsoft Project
* Microsoft SQL Server
* MySQL
* Oracle Database Server
* SQLite
* [[ADO_ConnectionString_TextFile|Text files]]


==== Connection properties ====
==== Connection properties ====
To be added.
For details please see [https://msdn.microsoft.com/en-us/library/windows/desktop/ms681546%28v=vs.85%29.aspx Connection Object Properties, Methods, and Events].


=== Populate the recordset ===
=== 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).<br />
The ADO Recordset object is used to contain the set of data extracted from a database or file and is composed of records (rows) and fields (columns).<br />


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.<br />
The Recordset object is the heart of ADO. Via this object, we can select desired data. Equally important is the ability to move around inside the database. However, the functionality of the provider may impose limitations.<br />


  RecordSet.Open(Source, Connection, CursorType, LockType, Options)
  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.<br />
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.
There are five optional parameters.
; Source
: This optional parameter is a variant that can be any one of the following data sources:
:* SQL query string (used in the examples of this tutorial)
:* table name
:* Others: More details can be found under [[ADO#Ado|External Links - ADO]]


; Connection
For details please see page → [[ADO RecordSet Populate]].
: This optional parameter is a valid Connection object (used in the examples of this tutorial) or a connection string that defines the connection.
 
; CursorType
: This optional parameter specifies the type of cursor to use when you open a Recordset object.
: Possible values for the [http://msdn.microsoft.com/en-us/library/windows/desktop/ms681771%28v=vs.85%29.aspx CursorTypeEnum] enumeration are:
:{| class="wikitable"
|-
! Constant !! Value !! Description
|-
| adOpenDynamic || 2 || A dynamic cursor with both forward and backward scrolling where additions, deletions, insertions, and updates made by other users are visible
|-
| adOpenForwardOnly || 0 || (Default) A forward scrolling only, static cursor where changes made by other users are not visible
|-
| adOpenKeyset || 1 || A keyset cursor allows you to see dynamic changes to a specific group of records but you cannot see new records added by other users
|-
| adOpenStatic || 3 || A static cursor allowing forward and backward scrolling of a fixed, unchangeable set of records
|-
| adOpenUnspecified || -1|| Cursor type not specified
|}
 
; LockType
: This optional parameter indicates the type of lock in effect on a Recordset.
: Possible values for the [http://msdn.microsoft.com/en-us/library/windows/desktop/ms680855%28v=vs.85%29.aspx LockTypeEnum] enumeration are:
:{| class="wikitable"
|-
! Constant !! Value !! Description
|-
| adLockBatchOptimistic || 4 || Multiple users can modify the data and the changes are cached until BatchUpdate is called
|-
| adLockOptimistic || 3 || Multiple users can modify the data which is not locked until Update is called
|-
| adLockPessimistic || 2 || The provider locks each record before and after you edit, and prevents other users from modifying the data
|-
| adLockReadOnly || 1 || (Default) Read-only data
|-
| adLockUnspecified || -1 || Lock type unknown
|}
 
; Options
: This optional parameter tells the provider how to evaluate the Source parameter when it contains something other than a Command object. The appropriate use of this option can speed up performance since ADO will not have to determine the type of the data source.
: Possible values for the [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675946%28v=vs.85%29.aspx CommandTypeEnum] enumeration are:
:{| class="wikitable"
|-
! Constant !! Value !! Description
|-
| adCmdFile || 256 || Evaluate as a previously persisted file
|-
| adCmdStoredProc || 4 || Evaluate as a stored procedure
|-
| adCmdTable || 2 || Have the provider generate an SQL query and return all rows from the specified table
|-
| adCmdTableDirect || 512 || Return all rows from the specified table
|-
| adCmdText || 1 || Evaluates as a textual definition of a command or stored procedure call
|-
| adCmdUnknown || 8 || The type of the CommandText parameter is unknown. ADO will make several attempts to interpret the CommandText: adCmdText then adCmdStoredProc then adCmdTable
|-
| adCmdUnspecified || -1 || (Default) does not specify how to evaluate
|}
==== Recordset properties ====
List of useful RecordSet properties. A full list of properties, collections and methods can be found [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841%28v=vs.85%29.aspx here]
; BOF
: Returns a Boolean value indicating if the current record position is before the first record
; EOF
: Returns a Boolean value indicating if the current record position is after the last record
; Filter
: Sets or returns a variant value that is either a string, array of bookmarks, or a FilterGroupEnum value used to filter data. You also use this property to turn an existing Filter off
; MaxRecords
: Sets or returns a long value that specifies the maximum number of records that can be returned to a Recordset object as the result of a query
; RecordCount
: Returns a long value that is the count of how many records are in a Recordset object
; Sort
: Sets or returns a string value that is a comma-delineated list of the names of which fields in the Recordset to sort. After each name, you can optionally add a blank space and the keywords ASC or DESC to designate the sort direction
 
==== Recordset collections ====
List of useful RecordSet collections. A full list of properties, collections and methods can be found [http://msdn.microsoft.com/en-us/library/windows/desktop/ms675841%28v=vs.85%29.aspx here]
; Fields
: A collection containing all of the Field objects associated with a Recordset object
 
==== Recordset methods ====
A list of useful RecordSet methods can be found in section → [[ADO#Search.2Fprocess_the_fetched_data|Search/process the fetched data]].
 
==== Select Excel data ====
Excel data may be contained in the workbook in one of the following:
* An entire worksheet
* A named range of cells on a worksheet
* An unnamed range of cells on a worksheet
 
'''Specify a Worksheet'''<br />
To specify a worksheet as your recordsource, use the worksheet name followed by a dollar sign and surrounded by square brackets. For example:
$sSQL = "SELECT * FROM [Sheet1$]"
'''Specify a Named Range'''<br />
To specify a named range of cells as your recordsource, simply use the defined name. For example:
$sSQL = "SELECT * FROM MyRange"
 
'''Specify an Unnamed Range'''<br />
To specify an unnamed range of cells as the recordsource, append standard Excel row/column notation to the end of the sheet name in the square brackets. For example:
$sSQL = "SELECT * FROM [Sheet1$A1:B10]"


=== Search/process the fetched data===
=== Search/process the fetched data===
Line 271: Line 91:
* move around in the RecordSet
* move around in the RecordSet
* search for rows
* search for rows
* write the recordset to a 2D array or a record to a string
* write the recordset to a 2D array or a single record to a string
* save the recordset to a file
* save the recordset to a file
* sort the records
* sort the records
Line 277: Line 97:
and much more.<br />
and much more.<br />
For details please see page → [[ADO RecordSet Process]].
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===
Line 294: Line 111:
$oConnection = 0
$oConnection = 0
</syntaxhighlight>
</syntaxhighlight>
=== Handle events ===
To handle events you need to create an event object and set the name of a function which then handles the event:
<syntaxhighlight lang="autoit">
Global $oConnection = ObjCreate("ADODB.Connection")
Global $oConnectionEvent = ObjEvent($oConnection, "oConnection_")
Func oConnection_ConnectComplete($oError, $iStatus, $oConnection)
    If $iStatus = $iStatusErrorsOccurred Then _
        ConsoleWrite("CONNECTION ERROR: " & $oError.Description)
EndFunc
</syntaxhighlight>
For details please see page → [[ADO Event handling]].


== Examples ==
== Examples ==
* IBM DB2
* [[ADO_Example_DB2|IBM DB2]]
* Microsoft Access
* [[ADO_Example_Access|Microsoft Access]]
* [[ADO_Example_Excel|Microsoft Excel]]
* [[ADO_Example_Excel|Microsoft Excel]]
* Microsoft Project
* [[ADO_Example_Project|Microsoft Project]]
* Microsoft SQL Server
* [[ADO_Example_SQL_Server|Microsoft SQL Server]]
* MySQL
* [[ADO_Example_MySQL|MySQL]]
* Oracle Database Server
* [[ADO_Example_Oracle|Oracle Database Server]]
* SQLite
* [[ADO_Example_PostgreSQL|PostgreSQL]]
* [[ADO_Example_SQLite|SQLite]]
* [[ADO_Example_TextFile|Text files]]
* [[ADO_Example_TextFile|Text files]]


== Tools ==
== Tools ==
[[ADO Tools|Here]] are some (AutoIt) tools to make life easier.
Some (AutoIt) tools to make life easier can be found on page → [[ADO Tools]].
 
== UDF ==
mLipok has written an [https://www.autoitscript.com/wiki/User_Defined_Functions#Databases_and_web_connections UDF] to access all kind of databases using ADO.<br>
 
water has written an [https://www.autoitscript.com/forum/index.php?showtopic=106163 UDF] to access Microsoft Active Directory using ADO.
 
== Internal links ==
Links to ADO related threads on the forum:
 
* [http://www.autoitscript.com/forum/index.php?showtopic=180850 ADO (by mLipok)] - A modifed version of the _sql.au3 UDF to access all kind of databases.
* [http://www.autoitscript.com/forum/topic/169897-activex-data-objects-dsn-less-database-connection-demo/#entry1241099 ActiveX Data Objects (by GreenCan)] - DSN-Less Database connection demo.
* [http://www.autoitscript.com/forum/index.php?showtopic=105875 ADODB (by spudw2k)] - ADODB Example.
* [http://www.autoitscript.com/forum/index.php?showtopic=127101 MS SQL (by TheLuBu)] - MSSQL.au3.
* [http://www.autoitscript.com/forum/index.php?showtopic=51952 MS SQL (by ChrisL)] - _SQL.au3. ADODB Connection.
* [http://www.autoitscript.com/forum/index.php?showtopic=20814 MySQL (by cdkid)] - MySQL relational database management system UDF.
* [https://www.autoitscript.com/forum/topic/122360-mysql-odbc-connector-udf MySQL (by James)] - MySQL ODBC Connector.


== External links ==
== External links ==


=== ADO ===
*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675532%28v=vs.85%29.aspx MSDN: Microsoft ADO page]
*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675532%28v=vs.85%29.aspx Microsoft ADO page]
*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944%28v=vs.85%29.aspx MSDN: ADO object model]
*[http://msdn.microsoft.com/en-us/library/windows/desktop/ms675944%28v=vs.85%29.aspx ADO object model]
*[https://www.devguru.com/content/technologies/ado/home.htmlhome www.devguru.com: ADO Quick Reference]
*[http://www.devguru.com/technologies/ado/home DevGuru ADO Quick Reference]
*[http://www.w3schools.com/asp/ado_intro.asp www.w3schools.com: Learn ADO]
*[http://www.w3schools.com/ado/default.asp w3schools - learn ADO]
*[https://www.connectionstrings.com/ www.connectionstrings.com: Learn about many different Connection Strings]
 
=== Connection Strings ===
*[http://www.connectionstrings.com/ Database connection strings]
*[http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx ODBC DSN-Less connection strings]


[[Category:Tutorials]][[Category:ADO]]
[[Category:Tutorials]][[Category:ADO]]

Latest revision as of 16:28, 6 September 2020

Most of the following text was taken from Wikipedia and 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.

There are many ways to retrieve, add and change data using ADO. This tutorial only describes a fraction of what can be done with ADO:

  • How to read data
  • How to use the OLEDB provider, ODBC and other providers are not part of this tutorial
  • To open the connection a connection string is used. Setting the properties of the connection object to describe the connection is 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


Microsoft ActiveX Data Objects Extensions for Data Definition Language and Security (ADOX) is an extension to the ADO objects and programming model. ADOX includes objects for schema creation and modification, as well as security. Because it is an object-based approach to schema manipulation, you can write code that will work against various data sources regardless of differences in their native syntaxes.
ADOX is a companion library to the core ADO objects. It exposes additional objects for creating, modifying, and deleting schema objects, such as tables and procedures. It also includes security objects to maintain users and groups and to grant and revoke permissions on objects.
ADOX is not part of this tutorial!

How does ADO, OLEDB and ODBC fit together?

This chart shows the "big picture" so you can see how this all fits together (Picture taken from http://community.qlik.com/thread/106540).
How does ADO, OLEDB and ODBC fit together?

ADO

ADO (ADODB) is a generic (COM) database library, that can be used by programming languages to access any type of database for which an OLEDB Provider has been developed.
In this context, ADO is an OLEDB Consumer. It communicates with the OLEDB Provider, which in turn communicates with the database directly or a database server.
For example, to open an Access database, the Connection object of ADODB would specify the Jet OLEDB Provider in its connection string to open and subsequently communicate with the database.
There is also an OLEDB driver that can wrap any ODBC driver. Thus ADO can also talk to any database that exposes an ODBC driver.

OLEDB

OLEDB is a Microsoft standard that defines a set of API (Application Interface) functions for accessing all kind of databases (oracle, db2, MS-based databases etc.).
It is a COM (Component Object Model) API that was a follow-up to the ODBC API.
Typically OLEDB is used to create a database specific driver, known as a provider, that can be implemented by a higher level data access library such as ADO.

ODBC

ODBC is a standard API for accessing SQL based database management systems (DBMS). The designers of ODBC aimed to make it independent of database systems and operating systems.
This is accomplished by using an ODBC driver as a translation layer between the application and the DBMS. The application uses ODBC functions through an ODBC driver manager with which it is linked, and the driver passes the query to the DBMS.
There are commercial products available to access Applications and WebAPIs like Google Sheets, Microsoft SharePoint, Twitter and so on.

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:

  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. Close the recordset
  7. 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.

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 needs to know to be able to establish a connection to the database or the data file. Because there are different providers and each provider has 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 passed through the Open function of the connection object.

For details please see page → ADO ConnectionString.

Connection properties

For details please see Connection Object Properties, Methods, and Events.

Populate the recordset

The ADO Recordset object is used to contain the set of data extracted from a database or file 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. 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 single 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.

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

Handle events

To handle events you need to create an event object and set the name of a function which then handles the event: 

Global $oConnection = ObjCreate("ADODB.Connection")
Global $oConnectionEvent = ObjEvent($oConnection, "oConnection_")

Func oConnection_ConnectComplete($oError, $iStatus, $oConnection)
    If $iStatus = $iStatusErrorsOccurred Then _
        ConsoleWrite("CONNECTION ERROR: " & $oError.Description)
EndFunc

For details please see page → ADO Event handling.

Examples

Tools

Some (AutoIt) tools to make life easier can be found on page → ADO Tools.

UDF

mLipok has written an UDF to access all kind of databases using ADO.

water has written an UDF to access Microsoft Active Directory using ADO.

Internal links

Links to ADO related threads on the forum:

External links

Retrieved from "https://www.autoitscript.com/w/index.php?title=ADO&oldid=14394"