Aaquagua!: Querying the File System - From ColdFusion to SQL Server

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:

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

Using SQL Server

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

Default Setup But should be something like

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:

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'

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