Transact-SQL User's Guide

Home

Join cursor processing and
data modifications

Transact-SQL User's Guide

Chapter 17 Cursors: Accessing Data Row by Row

Using browse mode instead of cursors

Browse mode lets you search through a table and update its values one row at a time. It is used in front-end applications that use DB-Library and a host programming language. Browse mode is useful because it provides compatibility with Open Server(TM) applications and older Open Client libraries. However, its use in more recent Client-Library(TM) applications (release 10.0.x and later) is discouraged, because cursors provide the same functionality in a more portable and flexible manner. Additionally, because browse mode is Sybase-specific, it is not suited to heterogeneous environments.

Normally, you should use cursors to update data when you want to change table values row by row. Client-Library applications can use Client-Library cursors to implement some browse-mode features, such as updating a table while fetching rows from it. However, cursors may cause locking contention in the tables being selected.

For more information on browse mode, see the dbqual function in the Open Client/Server(TM) documentation.

Browsing a table

To browse a table in a front-end application, append the for browse keywords to the end of the select statement sent to Adaptive Server.

For example:

Start of select statement
in an Open Client application

...

for browse

Completion of the Open Client application
routine

A table can be browsed in a front-end application if its rows have been timestamped.

Browse-mode restrictions

You cannot use the for browse clause in statements involving the union operator, or in cursor declarations.

The keyword holdlock is forbidden in a select statement that includes the for browse option.

The keyword distinct in the select statement is ignored in browse mode.

Timestamping a new table for browsing

When creating a new table for browsing, include a column named timestamp in the table definition. This column is automatically assigned the timestamp datatype; you do not have to specify its datatype. For example:

create table newtable(col1 int, timestamp,
    col3 char(7))

Whenever you insert or update a row, Adaptive Server timestamps it by automatically assigning a unique varbinary value to the timestamp column.

Timestamping an existing table

To prepare an existing table for browsing, add a column named timestamp with alter table. For example:

alter table oldtable add timestamp

A timestamp column with a null value is added to each existing row. To generate a timestamp, update each row without specifying new column values.

For example:

update oldtable
set col1 = col1

Comparing timestamp values

Use the tsequal system function to compare timestamps when you are using browse mode in a front-end application. For example, the following statement updates a row in publishers that has been browsed. It compares the timestamp column in the browsed version of the row with the hexadecimal timestamp in the stored version. If the two timestamps are not equal, you receive an error message, and the row is not updated.

update publishers
set city = "Springfield"
where pub_id = "0736"
and tsequal(timestamp,0x0001000000002ea8)

Do not use the tsequal function in the where clause as a search argument. When you use tsequal, the rest of the where clause should match a single row uniquely. Use the tsequal function only in insert and update statements. If a timestamp column is used as a search clause, it should be compared like a regular varbinary column, that is, timestamp1 = timepstamp2.


	Expand Search