Store only part of the folder/file information in DB

Hi,

My application uses a base-folder where all documents are stored.
The base-folder can have unlimited sub-folders.
The base-folder will never be changed from the moment one document is stored in DB.

The idea is to only store the part of a file's location that extents the base-folder.
F.e.:
Basefolder = C:\MyFolder\
Actual file location = C:\MyFolder\MyFolder\MyFilename.doc
FilenameExt (in DB)  = MyFolder\MyFilename.doc

When the existence of the file must be checked I would simple connect the two strings together: basefolder + FilenameExt

1. How can I easily substract the base-folder from a file location when store a single record?
2. How can I easily update existing records in DB where currently the actual file locations are stored (with SQL Server)?

Thanks,
Stef
Stef MerlijnDeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
i have a similar functionality in one of my apps
i solved this problem with
function ExtractRelativePath(const BaseName: string; const DestName: string): string;

A := ExtractRelativePath('C:\MyFolder\', 'C:\MyFolder\MyFolder\MyFilename.doc'); // --> MyFolder\MyFilename.doc

note that if you specify a non subdirectory for the DestName it will find the shortest route from dest to base
A := ExtractRelativePath('C:\MyOtherFolder\', 'C:\MyFolder\MyFolder\MyFilename.doc'); // --> ..\MyFolder\MyFilename.doc
0
Stef MerlijnDeveloperAuthor Commented:
Can you please explain this a bit more?

note that if you specify a non subdirectory for the DestName it will find the shortest route from dest to base
A := ExtractRelativePath('C:\MyOtherFolder\', 'C:\MyFolder\MyFolder\MyFilename.doc'); // --> ..\MyFolder\MyFilename.doc

Open in new window

0
Stef MerlijnDeveloperAuthor Commented:
And question 2?
0
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Geert GOracle dbaCommented:
you could use substring
i'm assuming that you have the complete filename stored in 1 column (filename) and now you want to store the basepath next to it
and substract the basepath

well first you need to fill in the basepath column
i'm assuming you have a limited list of these ?

for this i would create a query to hold all the basepath with union:

select basepath from (
select 'C:\MyFolder\' as basepath
union select 'D:\MyFolder\'
union select 'E:\MyFolder\') as A

next step would be to match this is in the fieldname and set it in basepatch

select X.fieldname, A.basepath
from table X join (select 'C:\MyFolder\' as basepath
union select 'D:\MyFolder\'
union select 'E:\MyFolder\') as A on substring(X.fieldname, 1, len(A.basepath)) = A.basepath

after matching all records you can then fill in the basepath column
update table
set basepath = A.basepath
from table X join (select 'C:\MyFolder\' as basepath
union select 'D:\MyFolder\'
union select 'E:\MyFolder\') as A on substring(X.fieldname, 1, len(A.basepath)) = A.basepath

next you can alter the fieldname subtracting the basepath
update table
set fieldname = substring(fieldname, len(basepath)+1, len(fieldname));
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
explanation ...
if the folder is not a child of the base path then the ExtractRelativePath will use .. to get to folder
that's it
0
Stef MerlijnDeveloperAuthor Commented:
The basepath is already stored in a separate table "Tsettings" and there is only one basepath.
So I only have to substract that path from the filenames in tabel "Tfiles".
0
LearnDelphiCommented:
You may also want to check the following link that provides a LOT of details on file routines with Delphi:

http://onlinedelphitraining.com/newsletters/DelphiFileRoutines.htm 
0
Stef MerlijnDeveloperAuthor Commented:
Thank you both!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.