How ADO Gathers Metadata

[Previous] [Next]

Metadata is a term that we use a lot in product support. Essentially "metadata" means data about data. The ADO Cursor Engine needs very little metadata to retrieve the results of your query—only the data type and the name of the field. But to build the action queries needed to modify the data in the database, the ADO Cursor Engine needs more information.

Base Table and Field Names

In order to update your database, the ADO Cursor Engine needs to know which table (or tables) you want to modify. There is no way you can programmatically supply that information to ADO. The ADO Cursor Engine also needs to know the names of the fields in the tables you want to modify. But can't the Cursor Engine get that information? Doesn't the Name property for each Field object in the Recordset correspond to the actual field in the table?

Not always. Maybe your query calls a stored procedure, or maybe you use a different name for a field in the results by using an alias. The name of a field in your query results is not necessarily the name of the corresponding field in your table.

So where does the table and field information come from? From the same place the data came from—your OLE DB provider. Just before retrieving the results of your query, the ADO Cursor Engine asks your OLE DB provider (or ODBC driver) for the metadata it needs to build the action queries that update your database.

The ADO Cursor Engine requests this information from the OLE DB provider immediately after retrieving the names and data types of each field, just prior to retrieving the results of your query. However, many providers and drivers do not support returning this information along with the results of the initial query. But the ADO Cursor Engine doesn't give up that easily.

If the OLE DB provider or ODBC driver doesn't support returning the base table and field names, ADO will attempt to determine the table names by parsing the query in the strSQL string. The Cursor Engine then retrieves information about the different fields in those tables by issuing a SELECT * FROM TableName query and storing metadata about the fields in the query's result set (and discarding the data returned by the query). This method is neither efficient nor foolproof, but it's still the best option short of allowing the developer to supply this metadata.

If the OLE DB provider or ODBC driver returns inaccurate data or the ADO Cursor Engine can't gather the base table and field names, you'll likely receive the "Insufficient base table information for updating" error message when you try to update your database.

Locating the Record to Modify

You've supplied the new data for the record you want to modify by changing the Recordset object. You now understand how the ADO Cursor Engine determines the base table and field information. But how does the ADO Cursor Engine locate the correct record to update?

For a moment, forget about the fact that ADO is essentially an ad hoc query tool. If you wanted to build a table of customer data and use action queries to modify its contents, how would you set up the table so that you could easily specify in your action queries which record you want to update?

I hope that your answer is, "Dave, I can't believe you'd ask me such a simple question. In fact, I'm a little insulted. Any database developer worth her salt knows that you should build a primary key into your tables." (If that wasn't your answer, take a database implementation class for your database of choice.) There are isolated cases in which you might not want a primary key but have the need to update data through ADO. However, these instances are few and far between.

So—you have a primary key on your table. As you might have noticed earlier in our example, the primary key field, CustomerID, was one of the fields that the ADO Cursor Engine used in the WHERE clause of the action query to update the database. Like the base table and field names metadata, the ADO Cursor Engine asks the OLE DB provider or ODBC driver for information about the primary key for each table it needs to update. If the table you want to update does not contain a primary key, the ADO Cursor Engine will try to locate a unique index instead.

This series of API calls is one of the more expensive sets of calls that ADO makes (which is why I'm hopeful that a future release will offer a programmatic way to supply such metadata). These calls are expensive because the OLE DB provider and ODBC driver query the database's tables to retrieve the primary key information. The Microsoft SQL Server OLE DB provider and ODBC driver implement an optional feature that returns the primary key data rather efficiently by stating whether a particular field in the results is considered a key field. Those are the only components I'm aware of that implement such a feature, however. With other OLE DB providers and ODBC drivers, the ADO Cursor Engine must query the database to retrieve information on the primary key for each table in your query, and then determine whether your query retrieved the fields that make up the primary key. Though the ADO Cursor Engine's logic here is sound and its code is efficient, the requests it must make to retrieve this information are still expensive.

There is an exception to this costly process of retrieving primary key data for a given table that's worth mentioning. The Microsoft OLE DB Provider For Oracle avoids this process by requesting the row ID and using that row ID, rather than the primary key value, in the action query. This feature alone has significantly improved performance in applications that use the ADO Cursor Engine to modify data in Oracle databases, and it has convinced many Oracle programmers to migrate to the Microsoft OLE DB Provider For Oracle.

In ADO 2.5, you can force the Microsoft OLE DB Provider For Oracle to use key fields in the ADO Cursor Engine's query-based updates by setting the value of the Determine Key Columns For Rowset dynamic property on the Recordset object, as shown in the following code. This dynamic property is targeted to a small group of Oracle "power users" who might not want to use row ID values in the ADO Cursor Engine's query-based updates.

 strSQL = "SELECT * FROM Customers" Set rsCustomers = New ADODB.Recordset With rsCustomers 'Make the connection to the Oracle database the active connection. Set .ActiveConnection = cnOracle 'Set the cursor location to use a server-side cursor. .CursorLocation = adUseServer 'Set the dynamic property to use key fields in the query-based ' updates. .Properties("Determine Key Columns For Rowset") = True 'Reset the cursor location to use a client-side cursor. .CursorLocation = adUseClient .Open strSQL, , adOpenStatic, adLockBatchOptimistic, adCmdText MsgBox .Fields("CustomerID").Properties("KeyColumn") .Close End With 

NOTE
The code example for the OLE DB Provider For Oracle looks a little odd, but it's the only way I've found to use the Determine Key Columns For Rowset dynamic property successfully as of ADO 2.5. The property is available in the Recordset object's Properties collection only after you set the Recordset's ActiveConnection property to a Connection object that's using the Microsoft OLE DB Provider For Oracle, and only if the Recordset's CursorLocation property is set to adUseServer. Don't ask me why the restriction on the CursorLocation exists, since this property is designed for the ADO Cursor Engine. Once you set the dynamic property, set the Recordset's CursorLocation property to adUseClient and open the Recordset object using the parameters shown in the example. Leave the ActiveConnection parameter empty or you'll reset the Recordset's connection-specific properties, thereby undoing the change you went to great lengths to make.

If you insist on using a table that does not contain a primary key or a unique index, ADO has no reliable way to locate the record you want to modify. So the ADO Cursor Engine gives it the old college try. Rather than considering a field or group of fields to constitute a unique way to locate the desired record, the ADO Cursor Engine uses all the objects in your Recordset's Fields collection in the WHERE clause of the action query.

Where Is This Metadata Stored?

Each of the Field objects in your Recordset contains dynamic properties to store this metadata: BaseColumnName, BaseTableName, BaseCatalogName, BaseSchemaName, and KeyColumn. I've spoken with a few developers who were building ad hoc query applications and wanted to rely on ADO to report this metadata. The ADO Cursor Engine populates the values for these properties only if you request an updatable Recordset. If you request a read-only Recordset, the ADO Cursor Engine doesn't bother to retrieve this metadata. These properties are still available on client-side Recordsets that are not updatable, but the properties will not return valid values. Sorry.



Programming ADO
Programming MicrosoftВ® ADO.NET 2.0 Core Reference
ISBN: B002ECEFQM
EAN: N/A
Year: 2000
Pages: 131
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net