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.

  1. Enable the PDXpert.db database file on the client workstation.

    1. Open the PDXpert client application.

    2. From the Tools menu, select the Preferences... command.

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

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

  3. Click on the Windows Start menu and select the Control Panel, and then select Administrative Tools. Open the Data Sources (ODBC) tool.

  4. On the ODBC Data Source Administrator window, select the User DSN tab, and click the Add... button. The Create New Data Source window opens.

  5. From the list of drivers, select the SQLite3 ODBC Driver. Click the Finish button.

  6. On the SQLite3 ODBC DSN Configuration window, do the following:

    • Enter a Data Source Name: PDXpertSQLite3.

    • Click the Browse... button, navigate to the %LocalAppData%\PDXpert folder (similar to C:\Users\user\AppData\Local\PDXpert), and select the PDXpert.db file.

    • Enter a Lock Timeout [ms]: 10000 (10 seconds).

    • Select from Sync.Mode: NORMAL.

    Click the OK button to close the DSN configuration window.

    Click the OK button to close the ODBC Data Source Administrator window.

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's Change Database Server button 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.

  1. Click on the Windows Start menu and select the Control Panel, and then select Administrative Tools. Open the Data Sources (ODBC) tool.

  2. On the ODBC Data Source Administrator window, select the File DSN tab, and click the Add... button. The Create New Data Source window opens.

  3. From the list of drivers, select the most recent version of ODBC Driver for SQL Server driver. Click the Next button.

  4. Provide a new file name, such as PDXpertSQLViewer, and ensure that you know where the file will be saved. Click the Next button.

  5. After you confirm the summary, click the Finish button. The basic file is saved, and a new panel appears to accept more details.

  6. 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 Next button.

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

  8. Mark the checkbox to set the default database to PDXpertDB . Click the Next button and then the Finish button.

  9. Click the Test Data Source... button to verify your connectivity settings. You should see the message TESTS COMPLETED SUCCESSFULLY!

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.

[ODBC]

DRIVER=ODBC Driver 13 for SQL Server

UID=PDXpertViewer

PWD=By2Go4Me8

SERVER=PLMSERVER\PDXPERT

DATABASE=PDXpertDB

WSID=PLMCLIENT

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):

ADDRESS=10.1.1.1,16361

ADDRESS=PLMSERVER,16361

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.

1172

Learn More
Help Guide Contents [PDF]