Progress
DataServer
for ODBC Guide


Modifying Tables to Support the ROWID Function

If you want to use the ROWID function with an ODBC data source, you must make certain changes to your data-source table. The following example procedure for a Sybase and SQL Server 6 illustrates how to do this.

To use the ROWID function with a stored procedure using a similar syntax, first check your data source to see if it supports stored procedures. With a Sybase database, make the following changes to your data-source table:

  1. Add a column of the integer data type named PROGRESS_RECID. The new column must be able to contain null:
  2. alter table table
        add PROGRESS_RECID integer null 
    

  3. Add a column with identity characteristics named PROGRESS_RECID_IDENT_. The new column must have the numeric data type:
  4. alter table table
        add PROGRESS_RECID_IDENT_ numeric(10,0) identity 
    

  5. Create a trigger to maintain the PROGRESS_RECID column:
  6. create trigger _TI_table on table for insert as
    begin
        if (select max(inserted.PROGRESS_RECID) from inserted) is NULL
        begin
            update table set PROGRESS_RECID = @@identify
                where PROGRESS_RECID is null
            select convert (int, @@identity)
        end
    end 
    

  7. Change the nonunique indexes so that they include a PROGRESS_RECID column as the last component:
  8. create index table##index on table (column, PROGRESS_RECID) 
    

  9. If you have already created your schema holder, update it to reflect your changes to the data-source table.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095