Solved

ADO Unique Table Property

Posted on 2001-08-12
6
2,031 Views
Last Modified: 2013-12-25
I would like to know the correct way to use the ADO Recordset's Unique Table, Unique Schema and Unique Catalog properties. I have a stored procedure that uses a join and before I am opening the recordset I am issuing these commands:
With rs
  .CursorType = adOpenStatic
  .LockType = adLockBatchOptimistic
  .CursorLocation = adUseClient
  .Source = strQuery
  .Properties("Unique Catalog") = "QFM"
  .Properties("Unique Table") = "RoomBooking"
  .Open
  .Fields("Name") = "NewName"
  .UpdateBatch adaffectCurrent
End with

The column name is not in RoomBooking but it still gets updated.

Is there an obvious explanation for this?

Martin
0
Comment
Question by:MHorner
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6378681
I have seen ADO doing some curious things updateing underlying tables when there are no primary keys and when the field had non-indexable* fields... (*non-indexable for ADO, not for the database)
What ADO does sometimes is ignore the primary key settings (and i guess, in your case it seems even to ignore the Unique XXX properties), and searches for it's own primary table/primary key...
Check out (using Trace or SQL profiler) which statement is submitted to the database, and you will see that it is ADO that does this ...
Cheers
0
 

Author Comment

by:MHorner
ID: 6379205
I can see that Trace or SQL profiler may be useful. The primary and foreign key constraints have been specified in this case and I have tried more than one query with a join so I am rellay am puzzled.
0
 

Author Comment

by:MHorner
ID: 6379208
I can see that Trace or SQL profiler may be useful. The primary and foreign key constraints have been specified in this case and I have tried more than one query with a join so I am rellay am puzzled.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 14

Accepted Solution

by:
wsh2 earned 100 total points
ID: 6382669
"In fact, the solution provided by ADO 2.1 still allows you to generate unwanted UPDATE or INSERT SQL statements if you change fields that aren't part of the "unique" table, so care is still required."

(Look at the
"*** What if the query is the result of a join? ***"
section below)

Interesting reading courtesy of MSDN (This article is reproduced from the May 2000 issue of Visual Basic Developer. Copyright 2000, by Pinnacle Publishing):

An Update on Updating
http://msdn.microsoft.com/library/en-us/dnvbdev00/html/vb00e1.asp

The text of which now follows:
=========================================================

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

-----------------------------------------------------------
An Update on Updating
Rob Macdonald

It's all too easy to gloss over the details when it comes to ADO?details such as how ADO sometimes adds fields to your query, marking them as "hidden" and for its own use. In this article, Rob Macdonald shares his real-world experiences and explains the ins and outs of updating and deleting data from back-end databases.

Maybe you don't want to know exactly how ADO updates a database when you make changes through a recordset. Maybe, that is, until you want to know why it doesn't seem to work in all circumstances (and how to correct this), or how ADO manages to simulate optimistic locking without holding onto any locks. In this article I'll explain how ADO's client cursor service generates SQL statements for recordset updates?not just for the usual "SELECT * FROM authors" query, but for real-life scenarios involving joins and common cases where you retrieve only >selected> columns from a table, rather than all (*) of them. You'll see not only how elegant the ADO mechanism is, but also how you can control it to get the results you need.

I'm going to present only what happens with client-side cursors, such as when CursorLocation is set to adUseClient, because the server-side mechanism varies from provider to provider. The client-side mechanism, however, requires only that the database supports SQL. It's the key to creating scalable database applications, either with traditional connected recordsets or the increasingly fashionable disconnected type.

There are some limitations to client-side cursors: They're static and lack support for pessimistic locking. Once the Open method on a recordset with a client-side cursor returns, the only server reference that's retained is the server connection, and even that is released for a disconnected recordset. Any server-side locks or cursor features used to create the recordset are freed up immediately.

This begs the question: How does the recordset know how to update a record if there's no server cursor to act on its behalf? This is one area where ADO behaves very differently from its DAO and RDO forerunners?Microsoft has learned a lot. The new ADO mechanism works very nicely?at least for SQL Server. In this article I'll focus primarily on SQL Server and its sample Northwind database, but I'll also show you how ADO works with other providers.

To begin our exploration of how client-side updating works, imagine the recordset, rs, with a client-side, optimistic locking cursor, based on "SELECT * FROM customers." Let's update the contact name for the first record:

Print rs!ContactName
rs.Update "ContactName", "Jeff Archer"
Print rs!ContactName

This prints:

Maria Anders
Jeff Archer

No big surprises here. When I check the database, the change has happened as expected. When I study the SQL trace generated by this process, I find the following statement:

UPDATE "Customers" SET "ContactName"= ?
WHERE "CustomerID"= ? AND "ContactName"= ?

In other words, the ADO Client Cursor Service has turned my cursor-based update into a SQL update. The above is a parameterized SQL statement (in which actual values from the current record are substituted for the question marks when the update executes). ADO calls this technique "Query Based Update" (QBU). With such a small table, it isn't immediately obvious how this SQL was generated. Here's what happens:

The table name is derived from the BASETABLENAME dynamic property. This is a property added to the properties collection of each field object in a recordset when an updateable client-side cursor is created. The name used for each field comes from the BASECOLUMNNAME dynamic property. This won't necessarily be the same as the field name you use in your code, and it certainly won't be if you use aliases in your SQL.

By looking at all the Value and UnderlyingValue properties of each field, ADO derives the elements of the SET part of the update. Where the values differ, a SET element is generated. The UnderlyingValue property holds the value that the database held for the field when the recordset was opened or last resynced. It's populated when you specify an updateable lock type. The Value property for the ContactName Field will be used for the first parameter in the SQL UPDATE statement above.

There are two parts to the WHERE clause. First, the primary key is used to identify the record uniquely, translating the cursor update into an equivalent SQL update (QBU). In this case, the CustomerID Field's value will be used as the second parameter. However, the WHERE clause also includes the ContactName field, which isn't part of the primary key. Its purpose is to simulate optimistic locking. The UnderlyingValue property of ContactName will be used for the third parameter in the SQL above. If the field I'm updating has changed since my recordset was created, the WHERE clause will be unable to locate a record and the update will fail, displaying the following message:

-2147217864 Row cannot be located for updating. Some
values may have been changed since it was last read.

This is how the client cursor service works when handling updates. Not only is it elegant, it's also fairly efficient?especially when we perform multiple updates with the same basic structure.

Generating the WHERE clause based on primary keys and changed field values works well in many cases. However, in certain situations, you might want the WHERE clause to be constructed differently. ADO allows you to control this by setting the "Update Criteria" dynamic property on the recordset, using values from the ADCPROP_UPDATECRITERIA_ENUM enumeration. This property is added to the recordset whenever you request an updateable client-side cursor. The options available are:

rs.Properties("Update Criteria") = adCriteriaKey
When you issue an UPDATE statement such as:

UPDATE "customers" SET "ContactName"=?
WHERE "CustomerID"=?

?only primary keys are used in the WHERE clause. You won't have optimistic locking.

rs.Properties("Update Criteria") = adCriteriaUpdCols
This, however, is the default option, and we've already seen the SQL it generates. The modified field(s) is/are added to the primary keys. This applies optimistic locking of a sort. However, optimistic locking is generally considered a record-level issue, while the optimistic locking applied by adCriteriaUpdCols checks only fields that have been changed in the recordset. It's possible that another user has modified the same record, but not one of the fields that you've changed. In this case, the update will succeed, even though the integrity of the record might have been compromised.

rs.Properties("Update Criteria") = adCriteriaAllCols
Here, all fields are included in the WHERE clause, whether or not they've been modified. Note that "all columns" doesn't include large data types, such as Memos and BLOBS, even if the large data type Fields were being modified. This is the only way to achieve genuine record-level optimistic locking unless the database supports timestamp fields.

rs.Properties("Update Criteria") = adCriteriaTimeStamp
If you're using SQL Server, MSDE, or another database that supports timestamp fields, you can achieve genuine optimistic locking without needing to include all of the fields in the WHERE clause. By setting the Update Criteria to adCriteriaTimeStamp, only the primary key and the timestamp field are included in the WHERE clause. This is the best approach to use if your database supports timestamp fields and you have one in your table. It's both safer and more efficient than the default.

What if the query lacks the required fields?
The above examples assume that all of the fields required for the update are available. For example, if the primary key isn't in your recordset, you might think that it can't be included in the SQL statement. In this case, you'd have no guaranteed means of identifying a unique record. To explore this possibility, I'll use the following query to create my recordset rs:

SELECT ContactName,ContactTitle,City
FROM Customers WHERE City = 'London'
AND ContactTitle = 'Sales Manager'

It returns just a single record:

ContactName       ContactTitle           City        
----------------------------------------------------
Hari Kumar        Sales Manager          London

This recordset doesn't contain the primary key for the Customers table, CustomerID. Let's say I change the ContactTitle for this record, to "Sales Director." Following the rules stated above for generating SQL, QBU would generate the following SQL:

UPDATE "Customers" SET "ContactName"=?
WHERE "ContactName"=?

The value of the parameter used in the WHERE clause will be "Sales Manager," but there's a problem that arises when executing this statement. Even though my recordset has only one record, there are numerous sales managers in the underlying table. ADO will recognize that this update will affect more that one record, and return an error telling me so. However, it still performs the update, so although I get a warning error, all records in my table that met the WHERE clause are updated as well. The implication of this is >very> important. Generally speaking, when using client-side cursors for updating purposes, you should make sure that the query includes the entire primary key of the table you're working on. I said "generally speaking" for a reason. Let's see the UPDATE statement that SQLServer generates in the exact same circumstances:

UPDATE "Customers" SET "ContactName"=?
WHERE "ContactName"=? AND "CustomerID"=?

This UPDATE statement includes the CustomerID Field?even though this field doesn't appear in the recordset. If your query supports updates but doesn't contain the primary key of the table being updated, SQL Server adds the primary key fields into the data it returns, >but marks them as hidden> so that they don't appear as part of the recordset itself. However, ADO can see these fields, and so can include them in the WHERE clause if appropriate.

The "Hidden Columns" dynamic property available on client-side cursor recordset objects gives a count of the number of fields treated in this way. It will always return 0 for "SELECT * ?" queries, but the number increases as you leave out primary key fields. Timestamp fields get the same treatment. SQL Server automatically includes them as hidden columns if they don't form part of your query and you ask for an updateable cursor.

Right now, SQLServer appears to be the only database that provides this feature. However, there's no reason why other providers or databases won't offer this in the future.

*** What if the query is the result of a join? ***

Understanding how modifications apply to recordsets based on single tables is a starting point. But to give the topic the treatment it deserves, we need to look at what happens when the recordset is based on multi-table operations. Let's explore the one-to-many relationship between the Region and Territories tables in SQL Server's Northwind database:

SELECT R.RegionID AS ID ,R.RegionDescription,
T.TerritoryID,  T.TerritoryDescription, T.RegionID
FROM Region R, Territories T
WHERE R.RegionID = 4 AND R.RegionID = T.RegionID

The resultant recordset includes eight records, the first two of which are displayed (in condensed format for printing reasons):


ID  RegionDesc. TerritoryID  TerritoryDesc. RegionID  
--------------------------------------------------
4   Southern    29202        Columbia       4                    
4   Southern    30346        Atlanta        4                                

When we make individual or concurrent changes (using QBU, remember) to the RegionDescription or TerritoryDescription fields from the Region and Territories tables respectively, then call Update, ADO makes the changes we expect. However, when we call rs.Delete, ADO attempts to execute the following SQL:

DELETE FROM "Territories" WHERE "TerritoryID"=?
DELETE FROM "Region" WHERE "RegionID"=?

This is something we probably didn't want. Not only did a record get deleted from the Territories table (we probably >did> want this to happen), but there was also a DELETE statement for the Region table. Because Northwind enforces referential integrity, we get an error if there are still any records in Territories dependent on the Region ADO tries to delete. If there were no referential integrity rules, we'd be left with a bunch of orphaned records in the Territories table that refer to a Region that doesn't exist. We'll see how to resolve this in a moment, but first, for completeness, let's see what happens when we try adding a record. The most likely situation is that we'll want to add a new Territory to the current Region. For example, if we want to create a new Territory based in Houston, we call AddNew. So long as we're careful to set only those fields that belong in the Territories table, we get the following SQL when we call Update:

INSERT INTO "Northwind".."Territories" ("TerritoryID","TerritoryDescription","RegionID")
VALUES (?,?,?)

This achieves the update we expect and everything looks fine. However, to avoid generating an INSERT statement for the Region table (which would create all sorts of problems), we weren't allowed to provide values for the ID and RegionDescription fields that derive from the Region table. Now we have a record in which these two fields contain empty values when they should contain the same values as all of the other records in the recordset. This can cause problems if we want to display or process the data after the update has taken place.

What we've seen so far is that QBU has two potential problems when the recordset is based on a join:

A Recordset Delete will attempt to delete a record from each table used in the recordset.
To avoid generating an unwanted INSERT after a recordset AddNew, we now have gaps in the recordset where data should be.
Specifying a unique table
Beginning in ADO 2.1, Microsoft provided a solution? of sorts. The basic idea is that you nominate one base table; then all subsequent modifications apply only to this "unique" base table. Any changes to fields with a different base table are ignored. There must be a one-to-one correlation between records in the recordset and records in the unique table. In my example, this means that Territories could be a unique table, but Region couldn't be.

In fact, the solution provided by ADO 2.1 still allows you to generate unwanted UPDATE or INSERT SQL statements if you change fields that aren't part of the "unique" table, so care is still required. However, it does allow us to deal with the two potential problems we ran into before, as we'll see.

You specify the unique table by setting the following dynamic recordset properties available for client-side cursors (collectively known as the Unique* properties):

Unique Catalog
Unique Schema
Unique Table
Once you've specified the Unique table, the Delete method begins to work in the way you'd want it to. For example, assume my recordset cursor is positioned on the "Houston" record that we added into Territories a few moments ago. We use the following code with SQL Server (where we don't need to specify the "Unique Schema" property):

rs.Properties("Unique Catalog") = "NorthWind"
rs.Properties("Unique Table") = "Territories"
rs.Delete
This generates the following SQL:


DELETE FROM "Territories" WHERE "TerritoryID"=?

In other words, the Recordset Delete operation now understands that it should delete only from the unique table. All other tables in the underlying join should be ignored. Correcting the recordset AddNew problem that we identified earlier also makes use of the Unique table setting, but requires some additional work. None of us likes extra work, so you need to consider whether having empty spaces in the recordset is enough of a problem to make this extra work worthwhile.

Creating a custom ReSync command
It would be nice to think that you could supply the required value for the ID and RegionDescription fields and expect that ADO won't generate an update for the Region table because Region isn't the unique table. However, it doesn't work like this. What you need to do is to perform the update without setting RegionDescription, then perform a Resync to fill in the missing values based on the actual data in the database.

By default, when you Resync a recordset based on a join, ADO constructs a SQL query for each table. However, when you specify a unique table, you can provide your own "Resync Command" to override the default Resync mechanism. This command must return the same columns that the recordset contains, and have a parameter marker for each primary key in the unique table. After adding a new record, you can then call this custom Resync command to fill in the missing values in the record. The following code shows a program that creates a recordset, sets the "Resync Command" (yet another dynamic recordset property), inserts a record, and then performs a Resync using the custom Resync Command. On either side of the Resync it prints the value of the ID and RegionDescription field, enabling you to see the effect of the Resync.


Dim rs As New Recordset
Dim cn As New Connection
cn.Open "File Name=c:\udls\nwind.udl"
rs.CursorLocation = adUseClient
Dim sSQL As String
sSQL = "select R.RegionID As ID, " & _
       "R.RegionDescription, T.TerritoryID, " & _
       "T.TerritoryDescription, T.RegionID " & _
       "from Region R, Territories T Where " & _
       "R.RegionID = 4 And R.RegionID = T.RegionID"
rs.Open sSQL, cn, , adLockOptimistic
rs.Properties("Unique Catalog") = "NorthWind"
rs.Properties("Unique Table") = "Territories"
rs.Properties("Resync Command") = _
     "select R.RegionID As ID, " & _
     "R.RegionDescription, T.TerritoryID, " & _
     "T.TerritoryDescription, T.RegionID " & _
     "from Region R, Territories T Where " & _
     "T.TerritoryID = ? And R.RegionID = T.RegionID"
rs.AddNew
rs!TerritoryID = 1000
rs!TerritoryDescription = "Houston"
rs!RegionID = 4
rs.Update
Print "Before(", rs!ID, ",", rs!RegionDescription & ")"
rs.Resync adAffectCurrent
Print "After(", rs!ID, ",", rs!RegionDescription & ")"
The printed output from this program is:


Before(  0    ,   )
After(   4    ,   South  )

This shows the effect of the Resync operation.

Conclusion
Many developers spend long hours writing code that generates SQL to perform updates, not realizing that this is exactly what the ADO client cursor engine will do for you. While your own handcrafted code may shave an extra few milliseconds off of the time that a generalized mechanism such as ADO's will take, it's unlikely that you'll ever find the time to add all of the sophisticated features that Microsoft has added into ADO. Understanding how this mechanism works not only exposes an elegant, scaleable solution to a complex problem, but it is essential knowledge for avoiding a wide set of common data-access problems. While it's obviously easier to use client-side modifications with "SELECT * FROM?" queries, ADO does provide the tools required to perform UPDATEs, DELETEs and INSERTs on real-world recordsets based on joins. More and more developers are using client-side cursors because of their role in n-tier and workflow applications, and because they smooth out differences between different providers. While SQL Server allows us to exploit its Hidden Columns feature should we wish to, it's equally possible to write applications that work with any provider using standard SQL support.

Download ADOUPDAT.ZIP

Rob Macdonald is an independent software specialist based in London. In addition to consulting a nd training in Windows, client/server, VB, COM, and systems design and management, he also runs the UK ODBC User Group and authored RDO and ODBC: Client Server Database Programming with Visual Basic, published by Pinnacle, and a forthcoming book on ADO from APress. rob@salterton.com.

To find out more about Visual Basic Developer and Pinnacle Publishing, visit their website at http://www.pinpub.com/

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the May 2000 issue of Visual Basic Developer. Copyright 2000, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Visual Basic Developer is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.

0
 

Author Comment

by:MHorner
ID: 6386603
I have only had time to skim through the article you attached but it looks like it conatins a wealth of information. Thanks for that.
0
 
LVL 1

Expert Comment

by:kodiakbear
ID: 6765533
Accepting wsh2's Long Comment :)
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now