Collection Explorer General group Views collection

Purpose §

Defines an administrator-specified SQL Server or SQLite database view.

Where used §

ODBC database connections.

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.

  • When the ODBC client connects to the server-side SQL Server PDXpertDB database, or the optional client-side SQL Server LocalDB, the SELECT statement must conform to SQL Server syntax.
  • For ODBC clients connecting to the client-side SQLite database pdxpert.db, the SELECT statement must (a) conform to SQLite syntax, and (b) select data only from the set of SQLite-compatible public 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.

Active: users can select §
Default member of collection §
Permanent member of collection §
See the Managing collections: Common attributes help topic.

Setup §

A SQL Server administrator-specified view must always be based on PDXpert's public views.

For important information about writing views, refer to the Viewing & exporting via ODBC 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.


Help Guide Contents [as PDF]