scan path of image - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

scan path of image

Author

Topic

hai
Yak Posting Veteran

84 Posts

Posted - 2008-02-25 : 16:59:08

Hi all,
I have a scanner that dump the images into a directory. Because of thousand images dump per day, I want to moved the images into another directory each day as nightly schedule. However I like the path of the images moved stored into the database, to retrieve later. Have anyone design something like this? Right now, I get the file name from the text files, import to the database and update the path directory. I was hoping to get away from this...and do this as a jobs.

thanks

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-27 : 09:20:00

-- This represents our permanent storage table
create table #filepath (
myfilename varchar(255),
path varchar(255),
primary key (myfilename, path)
)

-- Temporary table to get filenames into
create table #names (
myfilename varchar(255)
)
declare @command NVarchar(4000)
declare @NewPath varchar(255)

set NOCOUNT on

-- Check to see if we have anything to archive
set @command = 'dir /b c:\Temp\Test\*.wav'
insert into #names
exec master.dbo.xp_cmdshell @command

if exists (select * from #names where IsNull(replace(myfilename, 'File Not Found', ''), '') <> '')
BEGIN
print 'Archive Files'
-- We are going to remove our test list and re-create it after we move the files
-- in case a new one shows up while we are working
truncate table #names

-- Lets create an archive directory for todays files using the date and time
set @NewPath= 'c:\Temp\Test\' + replace(replace(convert(varchar(25), getdate(),13), ' ', ''), ':', '')
set @command = 'mkdir ' + @NewPath
exec master.dbo.xp_cmdshell @command, NO_OUTPUT

--Now lets move our files into the archive directory
set @command = 'move c:\Temp\Test\*.wav ' + @NewPath
exec master.dbo.xp_cmdshell @command, NO_OUTPUT

-- 'Get a list of the files from the archive directory '
set @command = 'dir /b ' + @NewPath
insert into #names
exec master.dbo.xp_cmdshell @command

-- Show the list of files
-- select * from #names -- Comment this out in the working code

-- insert the filename and path to our table
insert into #filepath
select myfilename, @NewPath from #names where myfilename is not null

-- Show that this worked
select * from #filepath
END
else
BEGIN
print 'Nothing to Archive'
END
-- Clean up
drop table #names
drop table #filepath
set NOCOUNT off

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-27 : 09:21:25

Remember that the Path (in my example c:\Temp\Test) is on the database server.

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources