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.
Create 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 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.
Using client-side local views with SQLite ODBC driver §
SQL Server LocalDB is another 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-defined views for use with CAD or other local applications. These local views are contained in a SQLite database that's created when the Enable local views: Using SQLite database user preference 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.
Unlock the Preferences... window (F2 key), mark Enable local views with the Using SQLite database option. Lock the window (F2 key) to save your preference.
The Enable local views option is available only when the current user has been assigned a Roles collection member that includes the Allow local views on user computer permission. If local views are not permitted or enabled, then the client machine's local views database is not created or, if previously created, is no longer refreshed.
Install the 32-bit or 64-bit version of the SQLite3 ODBC driver. It's available at http://www.ch-werner.de/sqliteodbc/.
You can ignore the installer options for SQLite 2 Drivers and SQLite+TCC components.
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-defined 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 only SQLite views with SQLite-compatible syntax.
See view naming tips in the Collections reference > General > Views collection help topic.
- Private system tables are prefixed as _Private. Do not query these tables.
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.
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.
SQL Server ODBC driver: Initial setup §
This example creates a database source name (.dsn) file. Details may change depending on your Windows; refer to Microsoft's instructions for exact information.
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 panel appears to accept 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.
Indicate that SQL Server should verify the log-in ID using the With SQL Server authentication option. 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 created 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 and then the button.
Click the TESTS COMPLETED SUCCESSFULLY!button to verify your connectivity settings. You should see the message
If you have errors creating your connection, verify your Microsoft SQL Server configuration, firewall and antivirus settings, and ODBC client application's connection procedure. ODBC clients must account for differences in 32b/64b applications and operating systems. Microsoft offers extensive help here: "Using ODBC with Microsoft SQL Server" http://msdn.microsoft.com/en-us/library/ms811006.aspx.
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 13 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:
09/15/2015 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):
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.
- Verify 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 a Windows Firewall for Database Engine Access" at http://msdn.microsoft.com/en-us/library/ms175043.aspx.
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.
Never update or delete data in the PDXpert PLM database. Use only SELECT commands, and only objects within the viewer database schema.
Do not add your own table or view directly into the PDXpertDB database. A PDXpert system upgrade deletes all views, updates private views, and then rebuilds public views and members of the Views collection. Create a separate database to hold your own objects.
PDXpert PLM database objects may change after an upgrade. Where possible, base your queries on public views and user-defined 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 create a different account or change the password. You can manage log-in accounts using Microsoft's SQL Server Management Studio.
Help Guide Contents [PDF]
- 0001. Welcome!
- 0002. Help conventions
- 0100. PDXpert Application Server
- 0101. Server overview
- 0200. How to...
- 0300. Console reference
- 0400. How to start the PDXpert client
- 0500. Program concepts
- 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. Manage documents
- 1000. How to start a document
- 1003. Fill in the new document
- 1004. Add or remove references
- 1005. Save your document
- 1006. Remove your document
- 1007. Release your document
- 1008. Manage a released document
- 1009. Revise a released document
- 1010. Cancel a released document
- 1100. How to work with parts
- 1101. Manage 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. Save your part
- 1209. Remove your part
- 1210. Release your part
- 1211. Revise a released part
- 1212. Manage a released part
- 1213. Cancel a released part
- 1300. How to revise multiple markups
- 1400. How to work with change forms
- 1401. Processing a change
- 1402. Originate a new change form
- 1403. Analyze a submitted change
- 1404. Fix change processing errors
- 1405. Remove your change form
- 1406. Review a routed change
- 1407. Resolve an on-hold change
- 1408. Analyze an accepted change
- 1409. Use a released change
- 1410. View a completed change
- 1411. Analyze a stopped change
- 1412. View a rejected change
- 1413. Remove a canceled change
- 1414. Return a submitted change
- 1500. How to work with file attachments
- 1600. How to report, import & export
- 1601. Create a report
- 1602. Export a PDX package
- 1603. Use the Report/Export Wizard
- 1700. Import & update items
- 1701. Initialize your PLM database
- 1702. Use the External Data Importer
- 1703. Item Master import
- 1704. Iteration-level relational imports
- 1705. Bill of materials import
- 1706. References import
- 1707. Revision files import
- 1708. Sources import
- 1709. Item-level relational imports
- 1710. Item files & links import
- 1711. Materials import
- 1712. Groups collection import
- 1713. Materials collection import
- 1714. Organizations collection import
- 1715. Persons collection import
- 1716. Custom collection import
- 1800. View & export via ODBC
- 1801. View database objects
- 1802. Create an ODBC connection
- 1803. ItemViews reference
- 1804. ReferencePairViews reference
- 1805. SourcePairViews reference
- 1806. SourceItemMasterView reference
- 1807. StructurePairViews reference
- 1808. ChangeViews reference
- 1809. ChangePairViews reference
- 1810. FilePairMasterView reference
- 1900. How to perform other tasks
- 2000. Menu reference
- 2100. Item Explorer reference
- 2200. Document window reference
- 2300. Part window reference
- 2400. Change Form window reference
- 2500. System Rules reference
- 2501. System Rules window
- 2502. General: Copy files to snapshot
- 2503. General: Item uniqueness definition
- 2504. General: Reviewer's comment required
- 2505. Default File Access
- 2506. Password Policy
- 2507. References Tabs
- 2508. BOM: Allow duplicate parts
- 2509. BOM: Lock part default unit of measure
- 2510. 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