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. 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.

    There are two types of user-specified views:

    • A server UDV selects data from public views using standard Microsoft SQL Server syntax.

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

  • Private views are written for 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 mirrored in a 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.

Connecting to server-side SQL Server§

This is the recommended connection to the PDXpert database instance.

ODBC data flow for SQL Server on server machine

Advantages of using SQL Server on the server computer:

  • SQL Server can manage concurrent queries.

  • Data obtained from the server is immediately available, and has no refresh delay.

  • Any public view (not just the SQLite-compatible subset) can be used in queries.

  • Queries can use SQL Server's full range of built-in data manipulation functions.

  • Table column names are somewhat more flexible for Custom Attribute names.

  • The local workstation doesn't require the PDXpert client to update its data.

  • SQL Server offers powerful development tools with better documentation, support and complementary products.

  • The PDXpert client starts faster because data in client-side views must be downloaded.

If your PDXpert system uses SQL Server LocalDB, then you must upgrade to SQL Server Express (or higher) to access the database from client workstations.

Connecting to client-side SQLite or SQL LocalDB§

ODBC data flow for SQLite on client

ODBC data flow for SQL Server LocalDB on client

Advantages of using SQLite or SQL LocalDB on the client workstation:

  • Some data is materialized locally to increase query performance.
  • Data obtained from local views has no network delay.
  • Even when the workstation is disconnected, the local database provides static snapshot of previous part data.
  • The server-side SQL Server can be completely firewalled from the network.

When you enable local views, the SQLite database is automatically installed. However, SQL Server LocalDB is recommended because queries are compatible with, and can be tested on, the server instance. If you prefer to use SQL Server LocalDB, then you must install it as given in the PDXpert Installation Guide on-line: search the web for PDXpert LocalDB client

Effect of PDXpert upgrades§

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

Help Guide Contents [as PDF]