View database objects

The PDXpert database includes a set of read-only views that contain part, document and change form records. You can use these views as the basis for Transforms and, via an Open Database Connectivity ("ODBC") client, extract data for part lookup, reporting and other applications.

There are 3 kinds of read-only views within PDXpert:

• Public views are built into the PDXpert database. They can provide data to ODBC clients, SQL Server client applications, Transforms, or user-specified views.

  Changes to the PDXpert database attempt to keep public views as stable as practical, minimizing rework to your queries after an upgrade.

  The viewer schema contains the most recent pending and released items. If both pending and released relationships exist, the pending relationship is shown. These views are used primarily for item-level look-up data for CAD and for client-side local database queries.

  Public views always end with the characters …View (such as ItemView). Within the view's name, …Pair… is a parent-child (for example, assembly-component) view. …Master… views include each item's custom attributes.

  An ODBC client using a public view directly must

  1. handle all data types/sizes in the table; or

  2. SELECT only those columns that contain data types that it can handle.

  Otherwise, the ODBC client application must extract compatible data from a public view via a user-specified view.

• User-specified views ("UDV") can be added to the PDXpert database using the Views collection, in the Collection Explorer's General group. UDVs select data from public views and other user-specified views to support CAD applications on the PDXpert client computer. A UDV can exclude columns, filter data, perform calculations, reformat data and CAST() data types to other more convenient types. User-specified views are always contained in the viewer schema, and begin with the characters My…; for example, viewer.MyReleasedParts.

  A UDV selects data from a limited set of public views (viewer.ItemView, viewer.ItemMasterView, viewer.SourceItemMasterView, and viewer.FilePairMasterView) that are created on the client. The UDV query SELECT statement must conform to SQLite or SQL Server syntax, depending on the option chosen in the User Settings: Enable local views option.

• Private views are written to support internal requirements of the PDXpert application, and normally used only for advanced queries. These views may be modified or deleted when upgrading to a new PDXpert release; queries using these should be tested and updated after an upgrade. Private views are always contained in SQL Server's default dbo schema, and end with the characters …_View; for instance, dbo.Item_View or just Item_View.

Client-side and server-side connections §

Product data is contained in the server machine's Microsoft SQL Server database, and a subset can be copied to the SQLite or SQL LocalDB database on the client workstation. Client-side views are designed for fast and secure CAD part lookup. More powerful and flexible server-side views are useful for general reporting and data extraction to downstream systems, such as ERP/MRP. See the Make an ODBC connection help topic.

Effect of PDXpert upgrades §

Do not add your own views, tables, functions or other objects to the PDXpert (PDXpertDB) database. Views are often deleted and rebuilt during an upgrade, and PDXpert will not know how to rebuild your views. Unknown objects may interfere with normal operations or damage your data. Use a separate database to contain your own database objects.

Public views typically remain stable from one release to the next. Revisions to public views are given in the upgrade's release notes.

User-specified views contained in the PDXpert Views collection are retained during upgrades. After upgrading to a new PDXpert release, test (and, if needed, update) user-specified views.

1171