INF: Updating Text/Image Columns With Cursors

ID: Q110779

The information in this article applies to:

SUMMARY

While it is possible to use the Transact-SQL TXTPTR function to obtain a text pointer with a call to dbcursorfetch(), there is currently no way to retrieve the text timestamp for use with dbwritetext(). This restriction introduces important considerations when attempting to use DB-Library (DB-Lib) cursors to scroll through a result set and selectively execute positioned updates to both non-text/image and text/image columns.


MORE INFORMATION

One of the following methods can be used to retrieve the text timestamp:

Optimistic Concurrency Control By Values

To implement this method, the cursor must be opened with CUR_OPTCCVAL and an entire text/image column needs to be selected in the call to dbcursoropen(). Thus, the optimistic concurrency control by values will protect the text/image column by disallowing updates with dbcursor() if another user has modified the text/image value after it was retrieved.

It is important to note that this method will be unacceptable for large amounts of data, since dbcursor() must be called to perform updates. This effectively limits the text/image column to a usable datalength of less than 64K.

Locking Concurrency Control

To implement this method, the cursor must be opened with CUR_LOCKCC and the text column does not need to be selected in the call to dbcursoropen(). In order to insure that the value returned for the text/image column is the same as the value which was present in the table at the time the row was fetched, it is necessary to force an exclusive lock on the associated data page when each row is fetched. This lock must be held until the next row is fetched.

In order to achieve this, it is necessary to execute a BEGIN TRAN prior to calling dbcursorfetch(). Once dbcursorfetch() is called, a single select of the text/image column of the associated row in the fetch buffer is executed on the same dbproc in which the cursor was opened (this insures that select will not be blocked). In order to identify the appropriate text/image value to select, a WHERE clause is used which references a unique identifier returned by the previous call to dbcursorfetch().

Once this step is completed, the dbtxptr() and dbtxtimestamp() functions can be successfully called, and in turn, subsequent calls to dbreadtext() and dbwritetext() can be made.

It is important to note that while this method will allow the retrieval and updating of large text/image values, it will only work properly if all users access the text data using the same method to lock down the data page containing the text pointer. In other words, cursor locking concurrency control will not protect text pages from users who have obtained and saved text pointers with some other front-end application (such as an ad-hoc query program). These users could conceivably execute updates to the text page at a later time without ever re-accessing the text pointer on the data page.

However, if all users access the text data using the method described above, then the cursor locking control will prevent users (other than the one which currently holds the data page lock) from modifying the data in the text page. As a result, they will not be able to access the text pointer on the data page.

Additional query words: DB-Library dblib 


Keywords          : kbprg SSrvDB_Lib SSrvProg 
Version           : 4.2
Platform          : WINDOWS 
Issue type        :

Last Reviewed: March 18, 1999