You are viewing limited content. For full access, please sign in.

Question

Question

SQL Table for finding electronic file location

asked on May 18, 2015 Show version history

Which SQL table will show the file location (in actual Volume not in Laserfiche) of an electronic document?  We already have the tocid, parentid and vol_id.

0 0

Answer

SELECTED ANSWER
replied on May 18, 2015

When you locate the entry in the toc table, you can use the vol_id and edoc_storeid values to locate the file in the volume. Query the vol table by the vol_id to get the path (either fixpath or rempath columns) and convert the edoc_storeid value to a zero padded eight character hex value to get the filename. The folder path in the volume is just the segmented pair values of the first three pairs of the hex name. Note that the first pair value will have an "e" in front.

Here is an SQL query that you can run to get this information if you have the given tocid (credit goes to Cliff as this is just a modified version of the one he posted here):

DECLARE @entryID INT = 98785

SELECT dbo.toc.name AS DocumentName, dbo.vol.fixpath + '\e' + 
SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),1,2) + '\' + 
SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),3,2) + '\' +
SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2),5,2) + '\' +
CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.toc.edoc_storeid),2) + '.' + dbo.toc.edoc_ext AS FullPathAndFilename
FROM dbo.toc 
LEFT JOIN dbo.vol ON dbo.toc.vol_id = dbo.vol.vol_id
WHERE dbo.toc.tocid = @entryID

With the result looking like

One additional thing to note is that for electronic documents in the volume, you may actually see the files without an extension and that's normal.

6 0

Replies

replied on May 18, 2015

Thanks, Alex, this works perfectly.  I made sure to thank Cliff too smiley

1 0
replied on March 8, 2019

This query is fantastic - thanks for posting!

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.