Installing LocalDB for PDXpert client-side ODBC applications

Last update 2017-06-07

The PDXpert client can maintain a "live view" of your part and supplier data for use by ODBC-compatible applications. While PDXpert's default SQLite database works well in most cases, you can install the Microsoft SQL Server LocalDB to improve ODBC capabilities.

Topic contents

PDXpert PLM client-side database

The PDXpert client maintains a client-side database that contains part, supplier and file records. You can use this local data, via an Open Database Connectivity ("ODBC") connection, for applications such as CAD part lookup.

For most ODBC applications, you can use PDXpert's SQLite database for local data caching. SQLite has a small footprint, requires only an ODBC driver installation, and has no user account restrictions.

However, you may prefer to use Microsoft SQL Server Express LocalDB on the client workstation because:

  • LocalDB queries and tools are fully compatible with SQL Server
  • Views can be developed on the local client using SQL Server Management Studio (see note about developing queries on the server)
  • Some applications (e.g., Altium Designer) lock the single-session SQLite database file, blocking periodic updates from PDXpert. LocalDB is a multi-session database, and prevents CAD file locks.

LocalDB is a reduced-footprint free edition of the SQL Server database engine, and is compatible with SQL Server language and tools.

ODBC client applications connect to LocalDB using the SQL Server Native Client for Windows, which contains the SQL Server ODBC driver and the SQL Server OLE DB provider in one dynamic link library.

Installing and enabling the LocalDB database and tools

This procedure installs SQL Server LocalDB, and the SQL Server ODBC Driver or Native Client. After this procedure is complete, PDXpert will copy ("mirror") selected data from the server's SQL Server database into the LocalDB database.

ODBC data flow for Microsoft LocalDB (ODBC Driver)

If you're a PDXpert administrator, you can install the SQL Server Management Studio ("SSMS"). Administrators use SSMS to develop new members of the Views collection.

Microsoft LocalDB, ODBC driver and SSMS

The PDXpert client can't use any SQL Server edition other than LocalDB.

Checking for an existing SQL Server LocalDB instance

If another application has installed a SQL Server LocalDB instance, you don't need to install a new instance.

To check your system, find the SqlLocalDB.exe tool on your system (user help SqlLocalDB Utility). If you don't have this tool, there's a good chance you don't have SQL Server LocalDB installed. Open a Windows command prompt in the folder that has SqlLocalDB.exe, and run this to discover the installed instances:

C:\Program Files\Microsoft SQL Server\{sqlversion}\Tools\Binn>SqlLocalDB info

MSSQLLocalDB

v11.0

The PDXpert client uses the v11.0 (SQL Server 2012) instance or, if not found, the MSSQLLocalDB (SQL Server 2014 or later) instance.

Even if you have a LocalDB instance, you may need to install the Microsoft ODBC driver as described below.

Downloading SQL Server LocalDB and its tools

  • Required — Use SQL Server 2016 LocalDB if your system supports it, otherwise use SQL Server 2014 LocalDB.

    Install one of the following:

    • SQL Server 2016 Express SP1 downloader tool. Requires PDXpert 11.2.31103.3 or later, on 64-bit Windows 8 or later.

      When asked Which package would you like to download?, select the LocalDB option to download the Windows 64-bit (x64) SqlLocalDB.msi installer.

    • SQL Server 2014 with Service Pack 2 Express LocalDB installer. Requires PDXpert 9.3.27875.554 or later, on Windows 7 or later.

      Select the correct SQL 2014 installer for your computer: Windows 64-bit x64\SqlLocalDB.msi or Windows 32-bit x86\SqlLocalDB.msi.

  • Required — Use the Microsoft ODBC Driver for SQL Server, unless your application requires SQL Server Native Client.

    Install one of the following:

  • Optional (for developing queries and checking results) — SQL Server Management Studio download.

Installing the LocalDB database, ODBC driver and SSMS

You must have Windows administrator permissions to install these components.

After you save the files to your client computer:

  1. If your system doesn't have SQL Server LocalDB, install the SqlLocalDB.msi using Microsoft's installation instructions.

  2. If your system doesn't have Microsoft ODBC Driver for SQL Server (or SQL Server Native Client), install it using Microsoft's installation instructions.

  3. If you're installing the optional SQL Server Management Studio, run SSMS-Setup-ENU.exe and follow the instructions.

Enabling the LocalDB database within PDXpert

PDXpert will automatically copy your data from the PDXpert server to the LocalDB database after you enable local views in PDXpert.

  1. Open the PDXpert client application.
  2. On the Tools menu, select Preferences....
  3. Unlock the window (F2 key), mark Enable local views and then choose Using SQL Server LocalDB.
  4. Save the setting (F2 key).

Deleting the LocalDB database

To avoid problems with your SQL Server LocalDB instance, perform these steps before deleting the %UserProfile%\PDXpertLocalDb.mdf and .ldf files.

To remove the PDXpertLocalDb database:

  1. In the PDXpert client application, select Tools menu | Preferences... command.

  2. In the  Preferences window, choose Using SQLite database and clear the Enable local views checkbox.

  3. Exit the PDXpert client. Exit/stop all other ODBC applications that are connected to the SQL Server Express LocalDB instance.

  4. Detach the database from SQL Server Express LocalDB using one of these methods:

    • Use SQL Server Management Studio or

    • Use the SQLCMD utility. If it's not installed, download and install SQLCMD.exe.

      The {sqlcmdpath} depends on how your SQLCMD utility was installed:
      • SQL Server Express or higher: C:\Program Files\Microsoft SQL Server\110\Tools\Binn
      • SQL Server Command Line Utilities: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn

      Open a Windows command prompt, and enter these commands:

      When you first run sqlcmd, there may be a delay opening the server connection. If you see an error, enter the command a second time.

      If your database uses SQL Server 2012 LocalDB, then use (localdb)\v11.0 instead of (localdb)\MsSqlLocalDb.

      Microsoft Windows [Version 10.0.14393]

      (c) 2016 Microsoft Corporation. All rights reserved.

       

      C:\{anypath}>cd "{sqlcmdpath}"

       

      {sqlcmdpath}>sqlcmd -S (localdb)\MsSqlLocalDb

       

      1> USE master;

      2> ALTER DATABASE PDXpertLocalDb

      3> SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

      4> GO

      Changed database context to 'master'.

      Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.

      Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

      1> sp_detach_db 'PDXpertLocalDb', 'true';

      2> GO

      1> QUIT

      Exit the command prompt.

After you've detached the database from (localdb)\MsSqlLocalDb, you can delete the %UserProfile%\PDXpertLocalDb.mdf and %UserProfile%\PDXpertLocalDb_log.ldf files. To create a new SQL Server PDXpertLocalDb database, open the PDXpert cient and enable the LocalDB database.

Administrator only: Working with the LocalDB database

Testing PDXpert software's local database

Use these instructions to connect SQL Server Management Studio to the PDXpert local database.

The PDXpert local database contains:

  • Public views (with a …View suffix)
  • User-defined views (with a My… prefix)
  • Private tables  (labeled _Private1, _Private2, …). The meaning and use of these tables may change from one release to the next, and their contents should not be used in queries.

Right-click the PDXpertLocalDb node and select New Query from the context menu.

This test query selects all parts with three of their sources:

SELECT *

FROM [SourceItemMasterView] -- note 1

WHERE [Class] = 1

Notes:

  1. For simplicity, develop your query on the client database. If you develop a query on the server database using a view within the viewer scheme, remove the explicit reference to viewer before saving the query in the Views collection. Views within the server's viewer scheme appear with the client's dbo scheme. If you need to extract similar data from both client and server, create a separate view tailored for each.

Adding a user-defined view to the PDXpert Views collection

The LocalDB replaces PDXpert's default SQLite database. LocalDB uses the Microsoft SQL Server language. Any queries developed for SQLite should be reviewed to ensure compatibility with LocalDB.

After creating and testing your SQL query on the LocalDB, you can save it in a Views collection member. For details, see the PDXpert help topic Adding a new collection member.

Final comments

If you make changes within the PDXpert collections, your queries will need to be reviewed and possibly revised. For instance, a SQL statement will fail if you remove or rename a custom attribute, or rename a part type. Make changes to your PDXpert collections cautiously to minimize reworking your user-defined views.

Your SQL queries must only extract data using SELECT statements. Never attempt to update or delete data within the PDXpert database.

SQL Server and Microsoft are trademarks or registered trademarks of Microsoft Corp.

Learn More
Install Guide Contents