Progress
DataServer
for ODBC Guide


ODBC Data-source Views

ODBC data-source schema objects include views. A view is a presentation of data in one or more tables. (All supported ODBC data sources, except Microsoft Access, support views.) Views appear as tables in the Data Dictionary’s table list for the schema holder, not as views. In addition, the schema holder contains no unique index information for views. Because views do not have unique indexes, you cannot modify any of the data that a view contains; however, you can access a view with the FOR EACH, FIND NEXT, and GET NEXT Progress statements. Further, because views do not have index definitions, the DataServer cannot reposition the cursor to retrieve individual records, thus you must be sure to get all of the data that you need in a single database request.

The following Progress code example retrieves data from a view:

OPEN QUERY query-name FOR EACH view-name.
REPEAT:
  GET NEXT query-name.
  DISPLAY view-name.
.
.
.
END. 

If a view has a unique combination of columns, you can simulate a unique index using the Progress Data Dictionary. You can then access a view that has a simulated unique index just as you do a table; that is, you can scroll backward and forward, and update, create, and delete data, if the data source supports it. See the "Modifying Field-level Information" section in Chapter 5, “The DataServer Tutorial,” for information on how to do this.

Some views are the results of joins and contain data from more than one table. You can also provide unique index information for these views if they have a unique combination of columns. You can then scroll backward and forward, but the ODBC data source does not allow you to create or delete data in a multi-table view. You can, however, update data in some views.

The DataServer does not support access to columns in views that are the results of aggregates or computations unless the calculated column has a name associated with it. You assign a specific name to a calculated column when you define a data source view. For example, the following SQL statement names a computed column in a view definition:

create view view-name as select cc = count(cust_num) from customer 

If your data source supports views, you can also access those views by using the RUN STORED–PROC send–sql–statement option to send a SQL statement to select the data from the view. In this case, you can access the view without adding index definitions for the view to the schema holder.

Although the schema holder contains your ODBC data-source views, the Data Dictionary’s SQL View Report does not list them, nor can you access them through the PRO/SQL menu functions.


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