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.

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

   1. Open the PDXpert client application.

   2. From the Tools menu, select the User Settings... command.

   3. On the User Settings tool, mark Enable local views with the Using SQLite database option. Save the setting: Item ➔ Save, 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.

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

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-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 replace it with SQL Server Express (or higher) to allow connections from network computers. 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.

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.

SQL Server ODBC driver: Initial setup §

This example makes a database source name (.dsn) file.

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

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

9. 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 Finish button.

10. Click the Test Data Source... button to make sure that your connectivity settings are correct. You should see the message TESTS COMPLETED SUCCESSFULLY!

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.

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.

1172