Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

Querying the File System - From ColdFusion to SQL Server

DZone's Guide to

Querying the File System - From ColdFusion to SQL Server

· Database Zone ·
Free Resource

Running out of memory? Learn how Redis Enterprise enables large dataset analysis with the highest throughput and lowest latency while reducing costs over 75%! 

While looking for a solution of a different problem, I realized that SQL Server has a feature called extended stored procedures. Extended stored procedures, at least in SQL Server 2000, have to be written in C++ and compiled to a DLL. A good example of such a procedure is my previous article on Using Regular Expression in SQL Server. I am not going to get into how to write extended stored procedures in this article since my C++ skills are rusty at best. Instead, I will talk about using an existing stored procedure to query the file system and how to move from using ColdFusion to using SQL Server.

Using ColdFusion

Getting a list of files is pretty trivial in ColdFusion. All you have to do is use the "<cfdirectory>" tag like so:

<cfset localDirectoryPath = "C:\Temp" />

<cfdirectory
name="textFiles"
action="list"
filter="*.txt"
directory="#localDirectoryPath#" />


That will get a list of all files with the ".txt" extension in the "C:\Temp" directory.

Getting a list of files from a network drive is not any different except for specifying the networks path as UNC path (and a little service configuration):

<cfset networkDirectoryPath = "\\larry\share" />

<cfdirectory
name="textFiles"
action="list"
filter="*.txt"
directory="#networkDirectoryPath#" />


As stated above, this will not work if you do not have the ColdFusion service configured to use a specific account that has access to your network path.

The typical ColdFusion service setup looks like the following screen shot when accessed through the "Services" configuration in Windows:

coldFusionServiceNoUser

To be able to query a network path, you need to specify a user that has access to that network path:

coldFusionServiceUser

Using SQL Server

The setup for using SQL Server for querying network paths is the same as the one for ColdFusion:

Default Setup sqlServerServiceNoUser But should be something like sqlServerServiceUser

If you do not setup the SQL Server to run as the appropriate user, trying to query a network path with the method provided here, you will an access denied error:

sqlServerAccessDenied

With that aside, I have written a custom stored procedures based on the built-in extended stored procedure "xp_cmdshell". This procedure will return a query with the following:

  • fileID - unique auto incremented integer value
  • fileName - the name of the file
  • lastModifiedOn - the date the file was last modified
  • fileSize - the size of the file in bytes

Here are the results of querying the local path "C:\Temp" without a file extensions filter:

exec dbo.getDirectoryFileList 'c:\temp', null

queryingFileSystemNoFilter

Once configured the procedure can also work on network drives like so to return only files with the ".btw" extension:

exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'

queryingFileSystemWithFilter

Show Me the Code

/*
Type: Stored Procedure
Name: dbo.getDirectoryFileList
Author: Boyan Kostadinov
Created: 03.17.2008
Dependencies: master.dbo.xp_cmdshell
Usage: exec dbo.getDirectoryFileList 'c:\temp', null
exec dbo.getDirectoryFileList '\\larry\indium\Production\labelGeneration', '*.btw'
Parameters: @directoryPath varchar(255)
- The path of the local or network directory

@fileExtensionFilter varchar(10) - Optional
- The file extension to filter the file list by
Returns: A list of files found on the file system
*/
create procedure dbo.getDirectoryFileList
@directoryPath varchar(255),
@fileExtensionFilter varchar(10) = null
as

set nocount on

-- Declare and initialize local variables
declare @dosCommand varchar(5000)
set @dosCommand = ''

-- If the file extension fileter was empty, set it to all files
if @fileExtensionFilter is null or ltrim(rtrim(@fileExtensionFilter)) = ''
set @fileExtensionFilter = '*.*'

-- If the directory path does not have an ending '\', append one
if substring(@directoryPath, len(@directoryPath), 1) <> '\'
set @directoryPath = @directoryPath + '\'

-- Build the dos command to get a list of files
select @dosCommand =
'insert into #tempFileList(fileListRow) ' +
'exec master.dbo.xp_cmdshell ''dir ' + @directoryPath + + @fileExtensionFilter + ''''

-- Create a temporary table to store the file list
create table #tempFileList (
fileListRow varchar(1000) null
)

-- Create the #fileList temporary table to store the file list
create table #fileList (
fileID int primary key identity(1,1) not null,
[fileName] varchar(255) not null,
lastModifiedOn datetime not null,
fileSize bigint not null,
)

exec(@dosCommand)

-- 8 - Delete unneeded data from the #OriginalFileList
delete from #tempFileList
where fileListRow is null

delete from #tempFileList
where fileListRow like '%Volume%'

delete from #tempFileList
where fileListRow like '%Directory%'

delete from #tempFileList
where fileListRow like '%<DIR>%'

delete from #tempFileList
where fileListRow like '%bytes%'

if not exists (select * from #tempFileList where fileListRow like '%access is denied%')
begin
-- Populate the #fileList table with the final data
insert into #fileList(lastModifiedOn, fileSize, [fileName])
select ltrim(substring(fileListRow, 1, 10))
+
' '
+
rtrim(ltrim(substring(fileListRow, 11, 15)))
+
'm'
as 'lastModifiedOn',
replace(ltrim(substring(fileListRow, 21, 18)), ',', '') as 'fileSize',
ltrim(substring(fileListRow, 40, 1000)) as 'fileName'
from #tempFileList

select * from #fileList
end
else
select fileListRow as errorMessage from [#tempFileList] as e

-- Drop the temporary tables
drop table #tempFileList
drop table #fileList

set nocount off
go

Download

You can download the stored procedure from http://tech-cats.net/blog/downloads/sql/getDirectoryFileList.txt

References

Accessing the Windows File System from SQL Server

Running out of memory? Never run out of memory with Redis Enterprise databaseStart your free trial today.

Topics:

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}