PDXpert PLM Software
On-line Help Guide
This help topic describes the current PDXpert PLM release. Earlier releases may be different. To view your release's version of this topic, open PDXpert and press the F1 key or select the Help menu.
Make an ODBC connection
An Open Database Connectivity ("ODBC") driver enables compatible third-party software – CAD, MRP, reporting tools, office applications – to read data from PDXpert's database using industry-standard database queries.
Many ODBC client applications, such as Microsoft Access, will have their own connection instructions or wizard. Look for a "connect to external data" or similar command.
The most recent Microsoft ODBC Driver for SQL Server is preferred for all new development. Microsoft is deprecating the SQL Server OLE DB provider and the SQL Server Native Client for Windows.
ODBC connection options §
Connecting to client-side SQLite or SQL LocalDB §
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
Connecting to server-side SQL Server §
This is the recommended connection to the PDXpert database instance.
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 CAD-related basic 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 there's no client-side data to download.
If your PDXpert system uses SQL Server LocalDB, then you must upgrade to SQL Server Express (or higher) and open firewall ports to access the database from client workstations.
Using client-side local views with SQLite ODBC driver §
SQL Server LocalDB is a different client-side database option. It may be used where SQL Server compatibility, multi-session access and performance are important. LocalDB supports some CAD applications that don't access SQLite as a shared resource. See the PDXpert Installation Guide on-line (search the web for PDXpert LocalDB client).
The PDXpert client can enable a subset of public views and user-specified views for use with CAD or other local applications. These local views are contained in a SQLite database that PDXpert makes when the Enable local views: Using SQLite database user setting is marked.
SQLite ODBC driver setup §
The following example procedure uses a free open source SQLite3 driver. If you use a commercial SQLite ODBC driver, follow the supplier's installation and DSN configuration procedure instead.
Enable the PDXpert.db database file on the client workstation.
Open the PDXpert client application.
From themenu, select the command.
On the User Settings tool, mark Enable local views with the Using SQLite database option. Save the setting: , or Ctrl+S.
The Enable local views option is available only when the current user has been given a Roles collection member that includes the Allow local views on user computer permission. If local views are not enabled, then the client machine's local views database is not made or, if made before, is no longer refreshed.
The SQLite3 ODBC driver is available at http://www.ch-werner.de/sqliteodbc/. When connecting 64-bit CAD tools, install the 64-bit version of the driver. When connecting 32-bit tools, install the 32-bit version.
Click on the Windows Administrative Tools. Open the Data Sources (ODBC) tool.menu and select the , and then select
On the ODBC Data Source Administrator window, select the User DSN tab, and click the button. The Create New Data Source window opens.
From the list of drivers, select the SQLite3 ODBC Driver. Click the button.
On the SQLite3 ODBC DSN Configuration window, do the following:
Enter a Data Source Name: PDXpertSQLite3.
Click the %LocalAppData%\PDXpert folder (similar to C:\Users\user\AppData\Local\PDXpert), and select the PDXpert.db file.button, navigate to the
Enter a Lock Timeout [ms]: 10000 (10 seconds).
Select from Sync.Mode: NORMAL.
Click thebutton to close the DSN configuration window.
Click the ODBC Data Source Administrator window.button to close the
Developing a SQLite ODBC query §
The PDXpert.db database has data objects in three categories:
A set of public views that your ODBC client can query directly. These end with …View (for example, ItemView).
Client user-specified views that are saved as members of the PDXpert Views collection. These begin with My… (for example, MyReleasedParts). Although all Views collection members are shown, your ODBC client can query SQLite views only using SQLite-compatible syntax.
See view naming tips and query limitations in the Views collection help topic.
- Private views are prefixed as _Private. Do not query these tables, as their design can change.
Develop queries for your ODBC client application using a SQLite development tool. Search the web for SQLite manager or similar, and install your preferred tool.
Example: DB Browser for SQLite at http://sqlitebrowser.org
Using instructions from your installed development tool, select the PDXpertSQLite3 data source to access the PDXpert.db database. This is usually done using the Select Data Source window and clicking on the Machine Data Source list. The PDXpert.db database is located in the %LocalAppData%\PDXpert folder. It does not require user name or password.
Do not add new or delete tables or views in the PDXpert.db database. Use only SELECT statements; do not update or delete any data contained in the database.
Using a SQLite ODBC query with your ODBC client application §
Using instructions from your installed ODBC client, select the PDXpertSQLite3 data source to access the PDXpert.db database. This is usually done using the Select Data Source window and clicking on the Machine Data Source list. The PDXpert.db database is located in the %LocalAppData%\PDXpert folder. It does not require user name or password.
After the queries are developed, add a new Views collection member with the desired SQL statement. Your ODBC client obtains the desired data from the named view.
Using the server-side SQL Server with Microsoft's ODBC driver §
If your PDXpert system uses the SQL Server LocalDB database, you must install SQL Server Express (or higher) to allow remote connections. After this install, use the PDXpert Application Server'sbutton to move the database from SQL Server LocalDB into the new SQL Server instance.
Microsoft often changes the ODBC driver; for current information, search the web for Microsoft ODBC Driver for SQL Server on Windows. Some ODBC drivers will show or hide the schema (such as dbo and viewer), which may affect your client application. Your ODBC client application may have different requirements, and you should follow those instructions instead.
If SQL Server is installed on a remote computer, you may also need to:
Use the SQL Server Configuration Manager application to enable the Named Pipes and TCP/IP protocols within SQL Native Client Configuration + Client Protocols, and SQL Server Network Configuration + Protocols for PDXPERT. Microsoft SQL Server must be restarted before the changes will take effect.
Make sure that the firewalls and/or antivirus on both remote and local computers allow communication to the SQL Server TCP port. See Microsoft's help topic "Configure the Windows Firewall to Allow SQL Server Access" at https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access
SQL Server ODBC driver: Initial setup §
This example makes a database source name (.dsn) file.
Click on the Windows Administrative Tools. Open the Data Sources (ODBC) tool.menu and select the , and then select
On the ODBC Data Source Administrator window, select the File DSN tab, and click the button. The Create New Data Source window opens.
From the list of drivers, select the most recent version of ODBC Driver for SQL Server driver. Click the button.
Provide a new file name, such as PDXpertSQLViewer, and ensure that you know where the file will be saved. Click the button.
After you confirm the summary, click thebutton. The basic file is saved, and a new page accepts more details.
Enter a Description (such as PDXpert SQL viewer data source). In the Server location, enter the fully-qualified server machine name and SQL Server instance name, such as PLMSERVER\PDXPERT. Click the button.
Select the With SQL Server authentication option to make sure that SQL Server examines the log-in ID. Apply the appropriate log-in ID and Password values, and click the button.
Use the default log-in name PDXpertViewer and password By2Go4Me8 unless you've added a different account or changed the password. You can manage log-in accounts using Microsoft's SQL Server Management Studio.
Mark the checkbox to set the default database to PDXpertDB. Click the button.
If you want to encrypt the data, mark the Use strong encryption for data checkbox and, depending on your server configuration, the Trust server certificate checkbox. If you're on a trusted network, clear both checkboxes to avoid certificate errors. Click the button.
Click the TESTS COMPLETED SUCCESSFULLY!button to make sure that your connectivity settings are correct. You should see the message
If you have errors making the connection, examine your Microsoft SQL Server configuration, firewall and antivirus settings, and ODBC client application's connection procedure. (See the next section to add a port number to the DSN file.) ODBC clients must account for differences in 32b/64b applications and operating systems. Microsoft offers extensive help on the web. For example: Microsoft ODBC Driver for SQL Server https://docs.microsoft.com/en-us/sql/connect/odbc/microsoft-odbc-driver-for-sql-server. At the time of writing, Microsoft offers a wizard for Solving connectivity errors to SQL Server https://support.microsoft.com/en-us/help/4009936/solving-connectivity-errors-to-sql-server
SQL Server ODBC driver: Final settings §
The .dsn file is a simple text file containing the connection information. It can be viewed and edited by Windows Notepad or similar text editor. This example includes the PWD= argument to simplify log-in.
DRIVER=ODBC Driver XX for SQL Server
APP=Microsoft® Windows® Operating System
DESCRIPTION=PDXpert SQL viewer data source
If your SQL Server is listening on a port other than the default port, then the SQL Server log file will show the port number:
07/18/2019 12:34:56,Server,Unknown,Server is listening on [ 'any' <ipv4> 16361].
Add the address parameters to the DSN file using the server's IP address or machine name (ADDRESS=server,port):
Using SQL Server Management Studio §
SQL Server Management Studio can be used to develop views which, after testing, can be saved as members of the Views collection.
SQL Server Management Studio is both powerful and potentially dangerous to your PDXpert database. The complete set of undocumented PDXpert database tables and private views may be visible. These private objects, which use the default Database Owner (dbo) database schema, are compiler-generated and may change from one release to the next.
Use only SELECT commands, and only objects within the viewer and released database schemas. Only very experienced SQL developers should consider updating or deleting data in the PDXpert PLM database.
Do not add your own table, view, stored procedure, or other resource directly into the PDXpertDB database. A PDXpert system upgrade deletes unknown objects before rebuilding public views and members of the Views collection. Make a separate database to hold your own objects.
When writing SQL code, query views, not tables.
PDXpert PLM database objects may change after an upgrade. Where possible, base your queries on public views and user-specified views.
PDXpert's documented public views, as well as members of the Views collection, are within the safe viewer database schema, such as PDXpertDB.viewer.ItemView or PDXpertDB.viewer.MyReleasedParts.
The default public views log-in name is PDXpertViewer and password is By2Go4Me8, unless you add a different account or change the password. You can manage log-in accounts using Microsoft's SQL Server Management Studio.
Help Guide Contents [as PDF]
- 0001. Welcome!
- 0002. Help styles
- 0100. PDXpert Application Server
- 0101. Server overview
- 0200. How to...
- 0300. Console reference
- 0400. How to start the PDXpert client
- 0500. PDXpert introduction
- 0600. How to set up PDXpert
- 0700. How to use the Item Explorer
- 0800. How to use the Collection Explorer
- 0900. How to work with documents
- 0901. How to work with documents
- 1000. How to start a document
- 1003. Fill in the new document
- 1004. Add or remove references
- 1005. Start and update a task
- 1006. Save your document
- 1007. Remove your document
- 1008. Release your document
- 1009. Manage a released document
- 1010. Revise a released document
- 1011. Cancel a released document
- 1100. How to work with parts
- 1101. How to work with parts
- 1200. How to start a part
- 1203. Fill in the new part
- 1204. Add, modify or remove BOM parts
- 1205. Import a CAD BOM
- 1206. Add or remove approved sources
- 1207. Add or remove references
- 1208. Add or remove materials
- 1209. Start and update a task
- 1210. Save your part
- 1211. Remove your part
- 1212. Release your part
- 1213. Revise a released part
- 1214. Manage a released part
- 1215. Cancel a released part
- 1300. How to revise multiple markups
- 1400. How to work with change forms
- 1401. Processing a change
- 1500. How to start a change form
- 1503. Start and update a task
- 1504. Analyze a submitted change
- 1505. Fix change form routing errors
- 1506. Remove your change form
- 1507. Review a routed change
- 1508. Resolve an on-hold change
- 1509. Analyze an accepted change
- 1510. Use a released change
- 1511. View a completed change
- 1512. Analyze a stopped change
- 1513. View a rejected change
- 1514. Remove a canceled change
- 1515. Return a submitted change
- 1600. How to work with file attachments
- 1700. How to report, import & export
- 1701. Run a report
- 1702. Export a PDX package
- 1703. Use the DataGrid
- 1704. Use the Report/Export Wizard
- 1800. Import & update items
- 1801. Use the Batch Importer
- 1802. Item Master import
- 1803. Iteration-level relational imports
- 1804. Bill of materials import
- 1805. References import
- 1806. Revision files import
- 1807. Sources import
- 1808. Item-level relational imports
- 1809. Item files & links import
- 1810. Item materials import
- 1811. Item product families import
- 1812. Groups collection import
- 1813. Materials collection import
- 1814. Organizations collection import
- 1815. Product families collection import
- 1816. Persons collection import
- 1817. Custom collection import
- 1900. View & export via ODBC
- 1901. View database objects
- 1902. Create an ODBC connection
- 1903. ItemViews reference
- 1904. ReferencePairViews reference
- 1905. SourcePairViews reference
- 1906. SourceItemMasterView reference
- 1907. StructurePairViews reference
- 1908. ChangeViews reference
- 1909. ChangePairViews reference
- 1910. FilePairMasterView reference
- 2000. How to do other tasks
- 2100. Menu reference
- 2200. Document reference
- 2300. Part reference
- 2400. Change Form reference
- 2500. System Rules reference
- 2501. System Rules tool
- 2502. General: Copy files to snapshot
- 2503. General: Copy previous tasks
- 2504. General: Item uniqueness definition
- 2505. General: Reviewer comment required
- 2506. General: Unlock change form Files (rule)
- 2507. General: Unlock change form Tasks (rule)
- 2508. Password Policy
- 2509. References Tabs
- 2510. BOM: Limit part to one row
- 2511. BOM: Lock part unit of measure
- 2512. BOM: Allow partner parts
- 2600. Collections reference
- 2601. Managing collections
- 2602. Custom attributes
- 2700. Places/Organizations/Persons
- 2800. General
- 2900. Documents
- 3000. Parts
- 3100. Changes
- 3108. Custom collections
- 3200. Other reference topics
- 3300. Software licenses & legal notices