|
Data Access Methodologies ODBC is the most widely used interface to relational data. It's also quite fast, but you pay for the fast access with complex application code.
The general characteristics of ODBC are:
Highly efficient performance.
Coding difficulty.
Reasonable memory requirements.
Compatibility with existing database technologies.
Portability across many operating system platforms.
A connection model that allows for different networks, security systems, and database options.
As the standard interface to relational data, your application can access a lot of data using ODBC. But ODBC does require that your data look like a relational database, so it's not always the best way to expose data. If you don't have a relational database, it can be very difficult to write an ODBC driver to expose your data because you basically have to write a relational engine on top of the existing data structure. Non-relational data access is also required in applications. Examples of non-relational data are information about the global address list in your messaging system, file system, proprietary data files and so on. Any kind of data should be accessible using a single functional technology. This is called "Universal Data Access."
OLEDB and ADO for Universal Data Access
ODBC is designed to provide access primarily to SQL data in a multi-platform environment. OLE DB is designed to provide access to all types of data in an OLE Component Object Model (COM) environment. OLE DB includes the SQL functionality defined in ODBC but also defines interfaces suitable for gaining access to data other than SQL data. Universal Data Access components consist of data providers, which contain and expose data; data consumers, which use data; and service components, which provide common services that extend the native functionality of the data store.
Data providers are components that represent diverse sources of data such as SQL databases, indexed-sequential files, spreadsheets, document stores, and mail files. Providers expose data uniformly using a common abstraction called the rowset.
Data consumers are components that consume OLE DB data. Examples of consumers include high-level data access models such as ADO; business applications written in languages like Visual Basic, C++, or Java; and development tools themselves.
Services are components that extend the functionality of data providers by implementing extended interfaces, where not natively supported by the data store. For example, a cursor engine is a service component that can consume data from a sequential, forward-only data source to produce scrollable data. A relational query engine is an example of a service over OLE DB data that produces rowsets satisfying a Boolean predicate.
OLE DB is a system-level programming interface, whereas ADO is an application-level programming interface. ADO, based on Automation, is a database-programming model that allows enterprise programmers to write applications over OLE DB data from any language including Visual Basic, Java, VBScript, JScript, and C/C++.
Most developers are not interested in managing memory resources, manually aggregating components, and other low-level operations. Additionally, developers often use high-level languages that do not support function pointers and other C++ call mechanisms. ADO provides the following advantages to programmers:
Ease of use: The data-access task analogous to "Hello World" requires only three lines of code.
Programming language neutral: ADO can be used from languages such as Visual Basic, Java, C++, VBScript, and JScript.
Provider neutral: ADO can access data from any OLE DB source. In addition, ADO adapts gracefully to support less functional OLE DB providers.
No loss of OLE DB functionality: ADO allows C++ programmers access to the underlying OLE DB interfaces.
Extensible: ADO can dynamically expose properties specific to a data provider via collections of provider properties. In addition, it provides type extensibility by allowing access to COM objects exposed as column values (for example, rowset and stream).
Which data access technology to use?
It's possible that one is now confused about what data access methodology to use. Here's a quick functional summary:
ADO
Your best choice is ADO if your application requires:
Mainframe data or program communications: With Microsoft SNA Server, you can set up OLE DB data providers for mainframe data sources such as VSAM, CICS, IMS, and AS/400 files.
Re-engineering: For existing applications, you should consider re-engineering with ADO. As an alternative, you could continue with previous data access methods.
New development.
Uniform access to a variety of data sources and data types.
Fast development: ADO helps minimise development cost because it is uniform, consistent, and easy to use.
High performance.
Internet Information Server's (IIS) Active Server Pages (ASP): If your application uses IIS with ASP to generate browser-independent HTML from databases, you must use ADO.
OLE DB
Your best choice is OLE DB if your application requires:
Custom file access: You can write custom OLE DB data providers for virtually any data source. You can then use ADO as the data access technology.
RDO
Your best choice is RDO if your application requires:
Fast access to existing ODBC data.
ODBCDirect
Your best choice is ODBCDirect if your application requires:
Access to ODBC data: ODBCDirect provides a performance improvement over the older DAO data access technology.
DAO
Your best choice is DAO if your application requires:
Enhancements to existing DAO data access: DAO provides a consistent programming model for situations where some of the data access services must be provided using Microsoft Jet. If you already have large amounts of DAO code, and are willing to overlook the design, coding, and performance benefits provided by ADO, there's no reason to change it.
Running within a 16-bit environment: DAO is the only choice.
ODBC
Your best choice is ODBC if your application requires:
Fast access to existing ODBC data: If you're willing to develop and maintain complex code using the ODBC API, this is a good choice.
Data access using ADO
As you will observe from the ADO Object Model given below, there is no hierarchy.
The Connection, Command, and Recordset objects are top-level objects that you can create and destroy independently of each of the other objects. Though you can create the Parameter object independently of a Command object, it must be associated with a command before you can use it. The Field, Error, and Property objects each exist only within the context of their parent objects, and can't be separately created.
The ADO Connection object encapsulates the OLE DB Data Source and Session objects. It represents a single session with the data source. The Connection object defines properties of the connection, assigns the scope of local transactions, provides a central location for retrieving errors, and provides a point for executing schema queries.
The ADO Command object encapsulates the OLE DB Command object. The Command specifies the data-definition or data-manipulation statement to be executed. In the case of a relational provider, this is an SQL statement. The Command object allows you to specify parameters and customise the behaviour of the statement to be executed. A collection of Parameter objects exposes the parameters.
The ADO Recordset object encapsulates the OLE DB Rowset object. The Recordset object is the actual interface to the data, whether it is the result of a query or was generated in some other fashion. The Recordset object provides control over the locking mechanism used, the type of cursor to be used, the number of rows to access at a time, and so on. The Recordset object exposes a collection of Field objects that contain the metadata about the columns in the recordset, such as the name, type, length, and precision, as well as the actual data values themselves. Use the Recordset object to navigate through records and change data (assuming the underlying provider is updateable).
Each of the top-level ADO objects contains a collection of Property objects. The Property object allows ADO to dynamically expose the capabilities of a specific provider. Because not all data providers have the same functionality, it is important for the ADO model to allow you to access provider-specific settings in a dynamic way. This also prevents the ADO model from being cluttered with properties that are available only in certain circumstances.
For this example to work, you must have OLEDB loaded on your machine. OLEDB drivers can be loaded using Microsoft Data Access Components. MDAC is a part of Visual Studio, SQL Server and other products. It can also be downloaded from Microsoft's site.
Dim objData As ADODB.Recordset
Set objData = New Recordset
objData.Open "select * from authors", "DSN=pubs"
While Not objData.EOF
Print objData("author")
objData.MoveNext
Wend
objData.Close
Please note that the recordset was created directly without any other item being initialised.
ADO supports many advanced features which were simply not available in the ODBC - RDO Combination.
If you are going to use a particular SQL command multiple times, it is better to use the Command object. The Command object contains the SQL command, parameters and other information about how to execute the query. The command object can then be passed as a parameter to the recordset.open method or executed directly. If it is execu-ted directly it returns a new recordset object.
Dim cSQL As CommandSet cSQL = _
New CommandcSQL.ActiveConnection = _
"DSN=pubs;UID=sa;PWD=;"_
cSQL.CommandText = "select * from authors "
|
