Over a million developers have joined DZone.

Querying the File System - From ColdFusion to SQL Server

· Database Zone

To stay on top of the changing nature of the data connectivity world and to help enterprises navigate these changes, download this whitepaper from Progress Data Direct that explores the results of the 2016 Data Connectivity Outlook survey.

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

Turn Data Into a Powerful Asset, Not an Obstacle with Democratize Your Data, a Progress Data Direct whitepaper that explains how to provide data access for your users anywhere, anytime and from any source.

Topics:

Published at DZone with permission of Boyan Kostadinov, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

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

{{ parent.tldr }}

{{ parent.urlSource.name }}