x404.co.uk
http://www.x404.co.uk/forum/

File exists in SQL
http://www.x404.co.uk/forum/viewtopic.php?f=4&t=9331		 Page 1 of 1
Author:  rykard [ Wed Jul 07, 2010 11:34 am ]
Post subject:  File exists in SQL
HI,
can anyone help me? I am a trying to write a simple SQL script to check whether a file exists of not. I have tracked down xp_fileexist but this only seems to work in a root directory :( and all my files wil be in subdirectories... is there another command I am missing or is it not possible?
Author:  EddArmitage [ Wed Jul 07, 2010 1:00 pm ]
Post subject:  Re: File exists in SQL
I don't understand. Can you post what you've got so far? SQL is for querying and modifying databases - is this what you mean and if so can you outline your db structure?
Author:  rykard [ Wed Jul 07, 2010 1:06 pm ]
Post subject:  Re: File exists in SQL
What I have is a PDF path in a db record, for some reason a PDF hasn't been created and I am trying to track down easily a list of the missing PDFs,

so basically what I am trying to do is below in 'english'..

select * from table T1
where T1.PDF_Path doesn't exist
Author:  jonlumb [ Wed Jul 07, 2010 1:11 pm ]
Post subject:  Re: File exists in SQL
My understanding is that the functionality isn't there. Yes you need the SQL to read the file names / paths out of the database, but there's no way to get SQL to loko through the file/folder structure. Any other programming language should be fine though.
Author:  forquare1 [ Wed Jul 07, 2010 1:14 pm ]
Post subject:  Re: File exists in SQL
You could try the xp_cmdshell command. You could then have this fire off a script which would allow you to use all the usual UNIX/Windows command line tools to search for files...
Author:  rykard [ Wed Jul 07, 2010 1:38 pm ]
Post subject:  Re: File exists in SQL
thanks guys, I was kinda figuring I was asking the impossible... guess i wil have to write a program to do it...
Author:  EddArmitage [ Wed Jul 07, 2010 2:27 pm ]
Post subject:  Re: File exists in SQL
It sounds perfectly scriptable, as Ben says.

You need a script that:

  1. Executes the SQL to get the file paths
  2. Grabs the results from the SQL
  3. For each file path
    1. Browses to see if the file exists
    2. If it doesn't add it to an output file

I don't have much experience of Windows scripting, but it'd be doable in Bash for example.
Author:  finlay666 [ Wed Jul 07, 2010 11:00 pm ]
Post subject:  Re: File exists in SQL
EddArmitage wrote:
It sounds perfectly scriptable, as Ben says.

You need a script that:

  1. Executes the SQL to get the file paths
  2. Grabs the results from the SQL
  3. For each file path
    1. Browses to see if the file exists
    2. If it doesn't add it to an output file

I don't have much experience of Windows scripting, but it'd be doable in Bash for example.


bash, perl, a quick c# command line tool

sql wont help find the file, its a db language, you will just get the file name (possibly path)
Author:  EddArmitage [ Thu Jul 08, 2010 7:54 am ]
Post subject:  Re: File exists in SQL
finlay666 wrote:
sql wont help find the file, its a db language, you will just get the file name (possibly path)

Yup. My understanding was there's a DB with file paths in it somewhere, that needs querying first.
Author:  rykard [ Thu Jul 08, 2010 8:23 am ]
Post subject:  Re: File exists in SQL
that is correct, xp_fileexists only works to a single level. I am, going to write a program in the app, it wil take a lot longer but that's the only solution with my limited sql knowledge.

thanks for the help everyone, I may revisit this when I have sometime to look into it properly...
Author:  Nick [ Tue Aug 10, 2010 6:12 pm ]
Post subject:  Re: File exists in SQL
Image

That is all.
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/