Querying Vaulted File paths in the SolidWorks PDM Database

Problem

When working with the SolidWorks PDM database, it can be useful to determine the physical path of a file stored in the vault. This article provides an example SQL query as a reliable way to construct the full vaulted file path using data from the relevant database tables.

Solution

Required Tables

This method uses the following tables from the PDM database:

• Documents – contains the metadata for each document. We will be joining with DocumentID and ExtensionID, and searching for Documents using Filename.
• Revisions – stores file revision information, we will be using RevNr and joining with DocumentID.
• FileExtension – maps file type IDs to their extension value, we will be joining with ExtensionID to find the Extension.

Database Query

SELECT 
    revs.Root + '\' + 
    RIGHT(CONVERT(VARCHAR(8), CONVERT(VARBINARY(4), docs.DocumentID), 2), 1) + '\' + 
    CONVERT(VARCHAR(8), CONVERT(VARBINARY(4), docs.DocumentID), 2) + '\' +
    FORMAT(revs.RevNr, '00000000') + '.' + 
    fileExtension.Extension AS [Vaulted File Path]
FROM 
    Documents docs
JOIN 
    Revisions revs ON revs.DocumentID = docs.DocumentID
JOIN 
    FileExtension fileExtension ON fileExtension.ExtensionID = docs.ExtensionID;

This query builds the vaulted folder path by:

• Combining the root vault path with folder and file identifiers
• Converts the SolidWorks PDM DocumentID to hexadecimal and retrieves the last character value, as the top-level folder ID.
• Convers the SolidWorks PDM DocumentID to hexadecimal and uses this value as the sub-folder ID.

<root>\<Top-Level Folder>\<Sub-Folder> --> C:\Vault\3\00000003\

And builds the vaulted filename by

• Padding the revision number to eight digits
• Appending the correct file extension

The full result will look something like:

C:\Vault\3\00000003\00000001.sldprt

Example with Search

The previous query example will return all vaulted file paths. To make the query more Document-specific, add a WHERE Clause. The example query below searches for Document(s) with a specific filename.

SELECT 
    revs.Root + '\' + 
    RIGHT(CONVERT(VARCHAR(8), CONVERT(VARBINARY(4), docs.DocumentID), 2), 1) + '\' + 
    CONVERT(VARCHAR(8), CONVERT(VARBINARY(4), docs.DocumentID), 2) + '\' +
    FORMAT(revs.RevNr, '00000000') + '.' + 
    fileExtension.Extension AS [Vaulted File Path]
FROM 
    Documents docs
JOIN 
    Revisions revs ON revs.DocumentID = docs.DocumentID
JOIN 
    FileExtension fileExtension ON fileExtension.ExtensionID = docs.ExtensionID
WHERE 
    (docs.Filename) = 'MyFileName.sldprt';

WHERE UPPER(docs.Filename) = UPPER('MyFileName.sldprt');

WHERE docs.Filename COLLATE Latin1_General_CI_AS = 'MyFileName.sldprt';

Conclusion

If you’re unsure about running the database query, please contact your PDM administrator. If you need further assistance or would like a chat, feel free to contact the EKODA Help Desk.