SQL-VIEW

Creates a Reality file that provides a view of an existing SQL table (or SQL view) on a foreign database. This command does not create a new table. The DICT section resides in the local Reality database; the Data section(s) are located on the foreign database.

Syntax

SQL-VIEW file-name database, {user}, {password}Table PkCols DataCols {(K)}

Syntax Elements

file-name The name of the SQL view file you want to create. The format of a valid file name is described in Rules for File and Data Section Names.

database The ODBC Data Source Name for the foreign database. The DSN may itself include a user-id, and password if required, for logging on to the database. If this is the case, you will not need to supply these as independent parameters. For information on how to set up DSNs, refer to the documentation for the ODBC installation on your Reality system.

user The user-id for logging on to the database.

password The password associated with this user-id.

Table The name of the SQL table or view for which you want to create the SQL view file.

PkCols The name, or names, of the Primary Key column(s) on the SQL table or view, separated by commas if necessary. Where a table has a single Primary Key column, the Reality item-id maps to this column and is used directly to identify an SQL row. Where a table has multiple Primary Key columns, the Reality item-id comprises the same number of parts and these are used to identify the matching SQL row. The delimiter used to separate the different parts of the item-id is '\' by default. This can be changed by specifying the (K) option, which causes SQL-VIEW to prompt for the Key Separator character.

DataCols The data column names in the SQL table or view, separated by commas.

Option

K Causes the command to prompt for a Key Separator character to be used in place of '\'. You can specify any character that is not a Reality system delimiter and which does not appear in the data to be viewed.

Restrictions

As the data is stored in a form compatible with the foreign database's native applications, restrictions are imposed on Reality applications writing such data. There is no support for multi-values, indexing or transaction management.

The foreign database will impose strict control over the type and size of data that may be stored in each column. A Reality application using SQL view files must be aware of the format of the external table data and must keep within these controls. SQL view files may not be updateable, depending on the view definition and the capabilities of the foreign database.

Comments

An SQL view file may be based on an SQL table, or on a view definition in the foreign database. A view can be used to filter the data available to Reality, or to combine data from more than one table into a single file view.