Views

Collection Explorer ◢ General group ◢ Views collection

Purpose §

Defines an administrator-specified SQL Server or SQLite database view for replicating CAD data to the client's local database.

Where used §

ODBC database connections for CAD and other local applications.

Data fields §

Name §

This is the view name that's used by ODBC or SQL Server clients. PDXpert view names always begin with the letters My… as in, say, MyReleasedParts. The name must conform to the executing database engine's (Microsoft SQL Server or SQLite) guidelines.

To prevent errors in SQL Server and SQLite views and in ODBC client queries, only Basic Latin alphanumeric and underscore (_) characters are allowed. Do not use space or symbol characters (for example, %~'/\*<>([{+–=}])?). An invalid name requires deleting (not renaming) the Views member, and may require manually dropping the view in the database. Invalid characters are automatically replaced with an underscore.

The member Name defines the order that each view is added to the PDXpertDB.viewer. database schema:

• Views members are added to the collection in Name alphabetical order.

• If a member of the Views collection refers to another Views member, that other member must be added first.

• Each time you save a Views collection member, all Views members are dropped and created again in alphabetical order.

Thus, a new Views collection member must be named to alphabetically follow all members that it uses in its FROM clause.

It's helpful to apply a suffix to a view that's compatible with only one engine, such as MyItems_SQLite or MyItems_MSSQL. A view compatible with both databases wouldn't have a suffix: MyItems.

Description §

This gives the view and its purpose.

SQL SELECT statement §

The SQL SELECT statement is a required value that makes the view.

• For an ODBC client connecting to a SQL Server database, the SELECT statement must conform to SQL Server syntax.

• For an ODBC client connecting to the SQLite database, the SELECT statement must (a) conform to SQLite syntax, and (b) select data only from the set of SQLite-compatible public and user-specified views.

  Each time the PDXpert client starts, the client-side views are updated from the server. Minimizing the number of views, and custom attributes within each view, reduces the time required for the client to start.

A set of four part-related public views are copied into the client-side local database: FilePairMasterView, ItemMasterView and ItemView, and SourceItemMasterView. Private views used for local CAD views are executed by the PDXpert client against the local database, and can select data from only these four views. If your view refers to any other table or view, then it will always return zero rows.

The SQL Server public views are copied from the server viewer schema into the local views default schema. Do not include the viewer prefix in local queries: use SELECT ... FROM ItemView WHERE ... and not SELECT ... FROM viewer.ItemView WHERE ...  If you include the viewer schema in your query, the local database will not show the query in the views list.

See also User Settings: Enable local views help topic.

Active: users can select §

Default member of collection §

Permanent member of collection §

See the Managing collections: Common attributes help topic.

Setup §

For information about connecting to views, refer to the Make an ODBC connection help topic.

Any change to a view, or to custom attributes in a view, can affect the local views database. Before you change a view and before you add, remove or rename custom attributes used within a view, all other users should exit PDXpert and all ODBC applications that use local views. After making your changes, users should start the PDXpert client before resuming work with the ODBC application.

After upgrading to a new PDXpert release, always test your views for proper operation.

A view's SQL is not visible to PDXpert's upgrade procedure. A view may require changes to maintain compatibility with PDXpert upgrades.

Views included with your PDXpert system are provided for demonstration purposes, and may not be compatible with future PDXpert releases.

2008