File exist question

[KSiva]

Hi all,

I have a scenario here. I have table column called filenames. I want to write a query that will return all the rows of that table where the file name exists in the system.

Something like:

SELECT * from tab_files where filename EXISTS.

I know I can use xp_fileexist but I dont know how to use it with sql.

Can someone help me please?

Siva

Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.

 

[Glyndwr]

Sivakumar

is this more what you're looking for ?
it is a little more involved but gives you extra information.

drop table #FileExistList
drop table #tab_files
go
create table #tab_files (tabFile varchar(30)) 
insert into #tab_files(tabFile) values ('c:\autoexec.bat')
insert into #tab_files(tabFile) values ('c:\config.sys')
insert into #tab_files(tabFile) values ('c:\Idonotexist.doc')
go
create table #FileExistList (tabFile varchar(30) ,file_exists int , is_directory int , parent_exists int)

go
DECLARE FileExistCursor CURSOR
READ_ONLY
FOR SELECT tabFile FROM #tab_files

DECLARE @name varchar(300)
OPEN FileExistCursor

FETCH NEXT FROM FileExistCursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
--		PRINT 'add user defined code here'
--		eg.
		
		insert into #FileExistList(file_exists , is_directory , parent_exists ) 
			exec master.dbo.xp_fileexist @name

		update #FileExistList set tabfile = @name where tabfile is null
	END
	FETCH NEXT FROM FileExistCursor INTO @name
END

CLOSE FileExistCursor
DEALLOCATE FileExistCursor

select * from #FileExistList

output :
tabFile                        file_exists is_directory parent_exists 
------------------------------ ----------- ------------ ------------- 
c:\autoexec.bat                          1            0             1 
c:\config.sys                            1            0             1 
c:\Idonotexist.doc                       0            0             1 

(3 row(s) affected)

 

[Glyndwr]

Sivakumar

is this more what you're looking for ?

drop table #FileExistList
drop table #tab_files
go
create table #tab_files (tabFile varchar(30)) 
insert into #tab_files(tabFile) values ('c:\autoexec.bat')
insert into #tab_files(tabFile) values ('c:\config.sys')
insert into #tab_files(tabFile) values ('c:\Idonotexist.doc')
go
create table #FileExistList (tabFile varchar(30) ,file_exists int , is_directory int , parent_exists int)

go
DECLARE FileExistCursor CURSOR
READ_ONLY
FOR SELECT tabFile FROM #tab_files

DECLARE @name varchar(300)
OPEN FileExistCursor

FETCH NEXT FROM FileExistCursor INTO @name
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
--		PRINT 'add user defined code here'
--		eg.
		
		insert into #FileExistList(file_exists , is_directory , parent_exists ) 
			exec master.dbo.xp_fileexist @name

		update #FileExistList set tabfile = @name where tabfile is null
	END
	FETCH NEXT FROM FileExistCursor INTO @name
END

CLOSE FileExistCursor
DEALLOCATE FileExistCursor

select * from #FileExistList

output :
tabFile                        file_exists is_directory parent_exists 
------------------------------ ----------- ------------ ------------- 
c:\autoexec.bat                          1            0             1 
c:\config.sys                            1            0             1 
c:\Idonotexist.doc                       0            0             1 

(3 row(s) affected)

 

[KSiva]

Hi thanks for writing for my question.

But I want more like getting the result straight away, rather than storing into a table. Because this query will be called thousands times in an hour and I dont want to create and drop the table all the time.

Is there anyway I can get just the row if the file exists and return the row of the records?

Siva

Sivakumar Kandaraj
System Administrator,Web Programmer
Melbourne
Australia.