1

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

3 Answers3

1

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 Weinstein
  • 9,823
  • 9
  • 42
  • 59
0

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
  • 99,783
  • 25
  • 219
  • 289
  • 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 –  Jul 29 '09 at 21:31
  • 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 Jul 29 '09 at 21:53
  • 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. –  Jul 29 '09 at 23:03
  • 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 Jul 29 '09 at 23:23
  • 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 –  Jul 30 '09 at 01:08
  • 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 Jul 30 '09 at 02:44
0

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
  • 407
  • 3
  • 8