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 Data Transformations and, via an Open Database Connectivity ("ODBC") client, extract data for reporting and other applications.

There are 3 classes 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, data transformations, or user-defined views. Public views always end with the characters ...View (such as ItemView). Within the view's name, ...Pair... indicates 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. be able to 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-defined view.

  • User-defined 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-defined views. A UDV can exclude columns, filter data, perform calculations, reformat data and CAST() data types to other more convenient types. User-defined views always begin with the characters My... (such as MyReleasedParts).

    There are two types of user-defined 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 (ItemView, ItemMasterView, SourceItemMasterView, and FilePairMasterView) that are materialized on the client. The query SELECT statement must conform to SQLite syntax.

  • Private views are used only by the PDXpert application. These views may contain unpredictable content, and may be modified or deleted when upgrading to a new release. Private views end with the characters ..._View (such as 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 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 a static snapshot of previous part data.
  • Data transfers from the server can be encrypted by the PDXpert client connection.
  • The ODBC connection doesn't require any log-in credentials or account management.
  • The server-side SQL Server can be completely firewalled from the network.

SQLite is immediately available when you enable local views. If you prefer to use SQL Server LocalDB, you must install it as described in the PDXpert Installation Guide on-line (search the web for PDXpert LocalDB client).

Connecting to server-side SQL Server

ODBC data flow for SQL Server on server machine

Advantages of using SQL Server on the server computer:

  • All queries are processed by SQL Server, which can use multiple concurrent processes.

  • 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 an active PDXpert client to obtain current 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 updated.

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

Effect of PDXpert upgrades

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

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

1171

Learn More
Help Guide Contents [PDF]