AnsweredAssumed Answered

SQL Query for variable values in a file with version

Question asked by Sascha Smolny on Feb 17, 2020
Latest reply on Feb 26, 2020 by Sascha Smolny

Like • Show 0 Likes0
Comment • 4

Hello,

i can get variables with the api ("IEdmEnumeratorVariable10"). But this is very slow. I need about 500ms to get a variable value.

So I want to use an sql query. I have found an Example here:

SQL query for multiple epdm variable values in a file

I have changed the Query a little. This is working fine and fast for the latest Version of the File:

with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)  
select *
from  
(select filename, v.variablename, vv.valuetext 
from  
documents d  
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID  
inner join projects p on p.ProjectID = dp.ProjectID  
inner join variablevalue vv on vv.DocumentID = d.DocumentID  
inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID  
  and mv.VariableID = vv.VariableID and mv.revisionno = vv.RevisionNo 
inner join Variable v on v.VariableID = vv.VariableID  
inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID  
where filename LIKE 'TEST.SLDASM' and d.Deleted = 0 and dc.ConfigurationName = '@'
) a  
PIVOT  
(MAX(ValueText)  
for variablename in ([Zeichnungsnummer], [Artikelnummer], [Bezeichnung1], [Bezeichnung2])  
)pivot_table

Now I want to read the variables for an earlier version of the File. But how do I have to change the SQL query?

I can get all Versions with:

with maxvar as (select documentid, variableid, configurationid, max(revisionno) revisionno from variablevalue group by documentid, variableid, configurationid)  
select *
from  
(select filename, v.variablename, vv.valuetext, vv.RevisionNo  --add vv.RevisionNo
from  
documents d  
inner join DocumentsInProjects dp on d.DocumentID = dp.DocumentID  
inner join projects p on p.ProjectID = dp.ProjectID  
inner join variablevalue vv on vv.DocumentID = d.DocumentID  
inner join maxvar mv on mv.DocumentID = vv.DocumentID and mv.ConfigurationID = vv.ConfigurationID  
  and mv.VariableID = vv.VariableID -- and mv.revisionno = vv.RevisionNo 
inner join Variable v on v.VariableID = vv.VariableID  
inner join DocumentConfiguration dc on dc.ConfigurationID = vv.ConfigurationID  
where filename LIKE 'TEST.SLDASM' and d.Deleted = 0 and dc.ConfigurationName = '@'
) a  
PIVOT  
(MAX(ValueText)  
for variablename in ([Zeichnungsnummer], [Artikelnummer], [Bezeichnung1], [Bezeichnung2])  
)pivot_table

I get a Table with (Example):

filename	RevisionNo	Zeichnungsnummer	Artikelnummer	Bezeichnung1	Bezeichnung2
TEST.SLDASM	1	TEST	Test	Test	Test
TEST.SLDASM	2
TEST.SLDASM	3	NULL	NULL	NULL	NULL
TEST.SLDASM	4	NULL	NULL	Bez1	Bez2
TEST.SLDASM	5	NULL	NULL	NULL
TEST.SLDASM	6	NULL	NULL	NULL	NULL

If i only filter by RevisionNo i don´t get the right values.

See S-014197:

Be aware that only "unique" values are stored in the ‘VariableValue’ table . In other words, if there are no changes to a variable value over several file versions, only the most recent value is stored in the table.

But how can i get the right values for a specific Version?

Regards

Sascha

Sandy Scott Feb 25, 2020 11:39 AM

Correct Answer

I see that I misunderstood what you were trying to do.

This query removes some of the extra complexity of my original query and finds what you were looking for.

The way it works is:

Look for every possible revision of a Document, and links every one of those that to every to every entry in the Variable value table, excluding those where the specific document revision we're considering is newer than the variable value revision.
Group by Document, Document Revision, Configuration and VariableID, taking the maximum value of the Variable Revision.
Link back to the VariableValue table to get the actual value, and to any other tables you feel like to get additional metadata
Pivot, choosing the columns you want. This is where you'd insert the dynamic SQL bits I showed in the last query to fetch all the possible columns, but in this case I've just got two - add more in line 51

Notes:

If you only want this for 1 file, specify it in line 44, it'll run in under a second.
For the whole vault, or a large portion (e.g. find everything in a folder by matching the beginning of the Path as line 46), remove that condition, and it will take much longer to complete.

SELECT
     *
FROM (
     SELECT P.[Path]
          , D.[Filename]
          , DC.[ConfigurationName]
          , T.[RevNr]
          , VV.[ValueText]
          , V.[VariableName]
     FROM (
          SELECT R.[DocumentID]
               , R.[RevNr]
               , VV.[ConfigurationID]
               , Max(VV.RevisionNo) AS 'MaxRev'
               , VV.VariableID
          FROM [Revisions] AS R
          INNER JOIN [VariableValue] AS VV
               ON VV.[DocumentID] = R.DocumentID
                    AND VV.RevisionNo <= R.RevNr
          GROUP BY R.[DocumentID]
               , R.[RevNr]
               , VV.ConfigurationID
               , VV.VariableID
          ) AS T
     INNER JOIN [VariableValue] AS VV
          ON VV.DocumentID = T.DocumentID
               AND VV.ConfigurationID = T.ConfigurationID
               AND VV.RevisionNo = T.MaxRev
               AND VV.variableid = T.VariableID
     INNER JOIN [Variable] AS V
          ON V.VariableID = T.VariableID
     INNER JOIN [Documents] AS D
          ON D.DocumentID = T.DocumentID
     INNER JOIN [DocumentConfiguration] AS DC
          ON DC.ConfigurationID = T.ConfigurationID
     INNER JOIN [DocumentsInProjects] AS DiP
          ON DiP.DocumentID = T.DocumentID
     INNER JOIN [Projects] AS P
          ON P.ProjectID = DiP.ProjectID
     
     WHERE
          D.[Deleted] = 0
     -- Select files here
          AND D.[Filename] LIKE 'C0011990%'
     -- or Select a whole folder
          --AND P.[Path] LIKE '\General\%'
     ) AS T2
PIVOT(max(T2.valueText) FOR T2.VariableName IN (
               [Number]
               , [Description]
               -- ADD more columns here - must match VariableName in Variables Table
               )) AS PVT
ORDER BY Path
     , Filename
     , ConfigurationName
     , RevNr

See the reply in context

No one else had this question

Outcomes

Sandy Scott Feb 19, 2020 6:43 AM

Hi Sascha,

The way I've achieved this is to find the maximum value of the revision field for each combination of VariableID, DocumentID & ConfigurationID in subquery. You then link this subquery back to the VariableValue table to get the actual value.

I have a query that does most of this, but a few warnings first:

This uses dynamic SQL to generate the columns list.
It does it for every configuration - all your Solidworks documents will have at least 2 configurations the "@" configuration in PDM and the actual default configuration. some also pick up some rogue properties with ConfigurationID 1 before initial check in, this line
```
AND (VV.ConfigurationID <> 1 OR D.ExtensionID NOT in (3,4,5))
```
is supposed to eliminate those. Please check that extensionIDs 3, 4, 5 correspond to .slddrw .sldasm and .sldprt in your database.
It's pretty slow - this takes about 20s to run in our vault with 20,000 ish files.
Put your database name in the USE line.

/* Find every variable for every configuration of every file */

USE [XerosPDM]

-- Generate list of columns for pivoting
DECLARE 
    @columns NVARCHAR(MAX) = '', 
    @sql     NVARCHAR(MAX) = '';
 
-- select the category names
SELECT 
    @columns+=QUOTENAME(VariableName) + ','
FROM 
    [Variable]
WHERE VariableID > 44
ORDER BY 
    VariableName ASC;
 
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
 

SET @sql ='
/* Find every variable for every configuration of every file */
SELECT *
FROM (
     SELECT V.VariableName
          , D.Filename
          , P.Path
          , DC.ConfigurationName
          , VV2.ValueText
     FROM (
          /* Find the latest version of each variable within a configuration/part */
          SELECT VariableID
               , DocumentID
               , ConfigurationID
               , MAX(RevisionNo) AS ''MaxRev''
          FROM [VariableValue]
          GROUP BY VariableID
               , DocumentID
               , ConfigurationID
          ) AS VV
     INNER JOIN [Variable] AS V
          ON VV.VariableID = V.VariableID
     INNER JOIN [VariableValue] AS VV2
          ON VV.DocumentID = VV2.DocumentID
               AND VV.ConfigurationID = VV2.ConfigurationID
               AND VV.VariableID = VV2.VariableID
               AND VV.MaxRev = VV2.RevisionNo
     INNER JOIN [Documents] AS D
          ON VV.DocumentID = D.DocumentID
     INNER JOIN [DocumentConfiguration] AS DC
          ON VV.ConfigurationID = DC.ConfigurationID
     INNER JOIN [DocumentsInProjects] AS DiP
          ON VV.DocumentID = DiP.DocumentID
     INNER JOIN [Projects] AS P
          ON DiP.ProjectID = P.ProjectID
     WHERE D.Deleted = 0 AND D.ObjectTypeID = 1
          AND (VV.ConfigurationID <> 1 OR D.ExtensionID NOT in (3,4,5))
     ) AS T
PIVOT(MAX(T.ValueText) FOR T.VariableName IN (
               ' + @columns + '
               )) PVT
order by Path, Filename, ConfigurationName asc
;
';
-- execute the dynamic SQL
EXECUTE sp_executesql @sql;

I hope that's useful!

Actions

Sascha Smolny Feb 25, 2020 9:50 AM

Hello!

Many thanks for this example. But this is also using only the latest Version.

Sorry, but i did not find out, how to change the SQL code for a specific version.

I found another Solution in the API. "GetVersionVars" ( 2019 SOLIDWORKS API Help - GetVersionVars Method (IEdmEnumeratorVariable7) ). That is much faster:

"In previous versions of the API, you had to call IEdmFile5::GetFileCopy get a local copy of a file and then call IEdmFile5::GetEnumeratorVariable to read variables. This method is more efficient, because it retrieves the variable values directly from the database, removing the need to retrieve the file first."

I think that will be enough in my case. A Sql query would also be interesting!

Many Thanks!

Regards

Sascha

Actions

Sandy Scott @ Sascha Smolny on Feb 25, 2020 11:39 AM

I see that I misunderstood what you were trying to do.

This query removes some of the extra complexity of my original query and finds what you were looking for.

The way it works is:

Look for every possible revision of a Document, and links every one of those that to every to every entry in the Variable value table, excluding those where the specific document revision we're considering is newer than the variable value revision.
Group by Document, Document Revision, Configuration and VariableID, taking the maximum value of the Variable Revision.
Link back to the VariableValue table to get the actual value, and to any other tables you feel like to get additional metadata
Pivot, choosing the columns you want. This is where you'd insert the dynamic SQL bits I showed in the last query to fetch all the possible columns, but in this case I've just got two - add more in line 51

Notes:

If you only want this for 1 file, specify it in line 44, it'll run in under a second.
For the whole vault, or a large portion (e.g. find everything in a folder by matching the beginning of the Path as line 46), remove that condition, and it will take much longer to complete.

SELECT
     *
FROM (
     SELECT P.[Path]
          , D.[Filename]
          , DC.[ConfigurationName]
          , T.[RevNr]
          , VV.[ValueText]
          , V.[VariableName]
     FROM (
          SELECT R.[DocumentID]
               , R.[RevNr]
               , VV.[ConfigurationID]
               , Max(VV.RevisionNo) AS 'MaxRev'
               , VV.VariableID
          FROM [Revisions] AS R
          INNER JOIN [VariableValue] AS VV
               ON VV.[DocumentID] = R.DocumentID
                    AND VV.RevisionNo <= R.RevNr
          GROUP BY R.[DocumentID]
               , R.[RevNr]
               , VV.ConfigurationID
               , VV.VariableID
          ) AS T
     INNER JOIN [VariableValue] AS VV
          ON VV.DocumentID = T.DocumentID
               AND VV.ConfigurationID = T.ConfigurationID
               AND VV.RevisionNo = T.MaxRev
               AND VV.variableid = T.VariableID
     INNER JOIN [Variable] AS V
          ON V.VariableID = T.VariableID
     INNER JOIN [Documents] AS D
          ON D.DocumentID = T.DocumentID
     INNER JOIN [DocumentConfiguration] AS DC
          ON DC.ConfigurationID = T.ConfigurationID
     INNER JOIN [DocumentsInProjects] AS DiP
          ON DiP.DocumentID = T.DocumentID
     INNER JOIN [Projects] AS P
          ON P.ProjectID = DiP.ProjectID
     
     WHERE
          D.[Deleted] = 0
     -- Select files here
          AND D.[Filename] LIKE 'C0011990%'
     -- or Select a whole folder
          --AND P.[Path] LIKE '\General\%'
     ) AS T2
PIVOT(max(T2.valueText) FOR T2.VariableName IN (
               [Number]
               , [Description]
               -- ADD more columns here - must match VariableName in Variables Table
               )) AS PVT
ORDER BY Path
     , Filename
     , ConfigurationName
     , RevNr

Actions

Sascha Smolny @ Sandy Scott on Feb 26, 2020 1:53 AM

Hello!

Many many many Thanks for this! This is working great!

I need only 0.0625 seconds for a Query. If i filter with

WHERE [RevNr] = 5 and [ConfigurationName] like '@'

before the ORDER BY statement, than the Query need only 0.03 seconds. This is exact what i need!

This is really awesome! Thank you very much!

Regards

Sascha

Actions

SQL Query for variable values in a file with version

Outcomes

Related Content

Recommended Content