Is there a table in SQL that stores the actual document physical location in Windows? If so, which?
The full path itself is not stored in any table in the database.
Starting with the entry ID of the document, you can look up which volume it's in by querying the toc table. Then you can get the volume path from the vol table.
To then get the full path of each of the pages in the document, see Michael's response from this archived forum post.
When you look up the entry in the toc table, you'll notice that it has a parentid column value. This value is the entry ID of the folder that the document resides in. You'll need to then recursively look up the parentid back to the repository root to get the full Laserfiche path.
Daryl,
Here is a SQL script that will pull back the physical location of each page in a document for a provided entryID;
DECLARE @entryID INT = 96962 SELECT dbo.toc.name AS DocumentName, dbo.doc.pagenum + 1 AS PageNum, dbo.vol.fixpath + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),1,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),3,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),5,2) + '\' + CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2) + '.TIF' AS FullPathAndFilename FROM dbo.doc LEFT JOIN dbo.toc ON dbo.doc.tocid = dbo.toc.tocid LEFT JOIN dbo.vol ON dbo.toc.vol_id = dbo.vol.vol_id WHERE dbo.doc.tocid = @entryID ORDER BY dbo.doc.pagenum
Here is the resulting rowset from a document from my test system with three pages;
We had a client purge 112 entries and this query helped us recover them.
We made a minor change in the WHERE clause that allowed us to run the script just once for multiple entries:
WHERE dbo.doc.tocid = @entryID
became...
WHERE dbo.doc.tocid IN(EntryID1,EntryID2,EntryID2)
And we changed the sort to this:
ORDER BY DocumentName,dbo.doc.pagenum
Even though all we could hand back to them were raw TIFs they were quite grateful. Thanks!
I modified this query to work for both docs and edocs. Thank you to everyone who contributed on this thread it was very helpful. I recently had a database migration and the documents stored on the volume were ahead of the database. I used this to find out where I was off.
DECLARE @entryID INT = 8744 set @entryID = 8754 SELECT dbo.toc.name AS DocumentName, dbo.doc.pagenum + 1 AS PageNum, CASE WHEN edoc_storeid IS NULL THEN dbo.vol.fixpath + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),1,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),3,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2),5,2) + '\' + CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), dbo.doc.storeid),2) + '.TIF' ELSE dbo.vol.fixpath + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), edoc_storeid),2),1,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), edoc_storeid),2),3,2) + '\' + SUBSTRING(CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), edoc_storeid),2),5,2) + '\' + CONVERT(VARCHAR(8),CONVERT(VARBINARY(4), edoc_storeid),2) + edoc_ext END AS FullPathAndFilename FROM dbo.toc LEFT JOIN dbo.doc ON dbo.doc.tocid = dbo.toc.tocid LEFT JOIN dbo.vol ON dbo.toc.vol_id = dbo.vol.vol_id WHERE dbo.toc.tocid = @entryID ORDER BY dbo.doc.pagenum
Is the Laserfiche path stored anywhere in SQL?
When you look up the entry in the toc table, you'll notice that it has a parentid column value. This value is the entry ID of the folder that the document resides in. You'll need to then recursively look up the parentid back to the repository root to get the full Laserfiche path.
Here is a SQL script that will pull back the Laserfiche path for a provided documentID;
DECLARE @entryID INT = 12893 DECLARE @lfPath VARCHAR(255) = ''; WHILE @entryID <> 1 BEGIN SELECT @entryID = dbo.toc.parentid FROM dbo.toc WHERE dbo.toc.tocid = @entryID SET @lfPath = (SELECT dbo.toc.name FROM dbo.toc WHERE dbo.toc.tocid = @entryID) + '\' + @lfPath END PRINT @lfPath
The resulting value from my system for the query above is;
ROOT FOLDER\Demos\Demo 3 - Extended Properties And Versioning\Source Documents\
Thanks for posting this Cliff. Alex Huang helped us modify this code to be used for Electronic Documents. If anyone needs that information, you can find it here.