I need to query folder structure. In other words I need to be able to access directory structure by SQL. I'm thinking of that there should be some OLE DB provider or some ODBC driver for that purpose. My friend said to google "folder monitoring" but I can't find anything Now I'm doing in command prompt dir>somefile and importing resulted text file to the DB, but it's kind of "not real-time". I need to access it real time like explorer does. Is there a way? Thanks
A:
Why do you need SQL specifically to query files and folders? What kind of query are you trying to make, and what are your performance requirements for it (for one thing, you should realize that query performance in the lack of indices will not depend on the query language).
Pavel Minaev
2009-07-29 21:24:36
Thanks for your reply,I don't need specifically SQL to do this.I have a folder in network where many people dump many files mainly images. I need to have that folder as a DB to query and know the files currently available there. Don't expect big performance since the folder is really big and even OS opens it for couple of minutes
2009-07-29 21:31:43
To clarify: do you have a single folder, or a tree of folders (so you need to scan all subfolders); and what file attributes do you need to be able to query - filename/size/date, or some metadata embedded into the files themselves (e.g. MP3 tags)?
Pavel Minaev
2009-07-29 21:53:42
it's one folder (but I'd like to not have that restriction), for now I need to query only file names, but I wish I could query metadata also such as EXIFF fields of the pictures.
2009-07-29 23:03:47
At this point a clarification is needed regarding which OS, platform and language you're using. In any case, if you want to query things such as EXIFF tags, you'll have to roll your own reader. I'm not aware of any existing solution that would allow you to query metadata like that without building an index first.
Pavel Minaev
2009-07-29 23:23:54
I'm giving up the metadata idea and willing to query only the filenames. If there is a solution for any OS, I'll just install that os and point it to that networked folder. So OS is not important could be anything. The language? I'm looking if there is a ready solution, since the problem seems so obvious. Wish there would be ODBC or OLEDB driver. Such as for accessing text or csv files or similar
2009-07-30 01:08:23
If it boils down to that, then I wonder if you really need any code in the first place. Wouldn't good old `DIR` command do what you want (find all files in directory by name or by mask?). Note that it can also do a recursive search in subdirectories with `/S` switch.
Pavel Minaev
2009-07-30 02:44:15
A:
I'm assuming you'll be storing information about the folder structure in your database as a hierarchy:
- ID
- ParentID
- ObjectName
For example, to store that fileA and fileB are under DirectoryD, you'd have three records: - 1, 1, root - 2, 1, DirectoryD - 3, 2, fileA - 4, 2, fileB
If that's the case, I recommend reading up on hierarchical db models:
Alex
2009-07-29 21:27:58
A:
You could try importing a .NET assembly into your database which contains the logic for querying the file-system, expose it as a user-defined-function and then call that function anytime you wanted to get the values from this directory in real-time.
Granted, I've only seen this done with scalar results from the .Net functions, but it should be possible to return table-values as well.
An easy C# example of doing file-system access can be found here, but if C# isn't your flavor, or you don't dabble in code at all then this won't be of any help.
TheMissingLINQ
2009-08-13 18:21:51