Results 1 to 8 of 8

Thread: File exist

  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Unanswered: File exist

    We have a database called Itemphotos. In this table is a field called 'PID' picture ID. We have a photos directory with files like 2181.jpg, 2182.jpg, 2184.jpg. The number is the ID number for the picture of the part.

    If a record exist in the database based on the PID "Primary Key" , check the directory to make sure the jpg file exist. Here is the code I have so far.

    use [ItemPhotos]
    select pid from dbo.itemdata

    declare @file_path nvarchar(500)
    declare @file_exists
    int set @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' + [itemdata].[pid]
    exec master.dbo.xp_fileexist
    @file_path,
    @file_exists output
    Print 'File '+isnull(@file_path,'NULL')+' '+
    case when @file_exists = 1
    then 'exists'
    else 'does not exist' end

    I need a way to concatonate the PID field to the @file_path
    the way I have it is incorrect + [itemdata].[pid]

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what is itemdata.pid?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2012
    Posts
    12

    answer to your question

    itemdata is a database of parts that actually have pictures. PID is the ID for the picture. So in the database PID might be 2181. In our photo directory there should be a 2181.jpg file which is a picture of that part.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    int set @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' + CAST([itemdata].[pid] AS VARCHAR(20))
    PRINT '@file_path = ' + @file_path
    Before you start using "exec master.dbo.xp_fileexist", first PRINT the strings your script generates. Check if they're correct.
    Last edited by Wim; 03-06-12 at 18:53.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jan 2012
    Posts
    12

    Results from you suggestion

    When I paste the line below into the code, ([itemdata].[pid]) is underlined and it says, "multi-part indetifier itemdata.pid not found.


    int set @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' + CAST([itemdata].[pid] AS VARCHAR(20))


    Entire code block is below:

    use [ItemPhotos]
    select pid from dbo.itemdata

    declare @file_path nvarchar(500)
    declare @file_exists
    int set @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' + CAST([itemdata].[pid] AS VARCHAR(20))

    exec master.dbo.xp_fileexist
    @file_path,
    @file_exists output
    Print 'File '+isnull(@file_path,'NULL')+' '+
    case when @file_exists = 1
    then 'exists'
    else 'does not exist' end

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that there is only one row in itemdata, then:
    Code:
    USE [ItemPhotos]
    GO
    
    DECLARE
       @file_exists     INT 
    ,  @file_path       NVARCHAR(500)
    
    SELECT @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' 
    +  CAST(pid AS VARCHAR(20)) 
       FROM dbo.itemdata 
    
    EXECUTE master.dbo.xp_fileexist
       @file_path
    ,  @file_exists output
    
    SELECT 'File ' + Coalesce(@file_path, 'NULL') + ' '
    +  CASE
          WHEN @file_exists = 1 THEN 'exists' 
          ELSE 'does not exist'
       END
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    declare @pid varchar(20) <----
    select @pid = pid from itemdata

    declare @file_path nvarchar(500)
    declare @file_exists
    int set @file_path = '\\av-sql2\c$\inetpub\photos\macolaphotos\' + CAST(@pid AS VARCHAR(20))

    -- ditto what Pat said.
    Last edited by corncrowe; 03-08-12 at 12:50.

  8. #8
    Join Date
    Jan 2012
    Posts
    12
    Pat,

    Thanks for the post, I should have made this clearer. Itemdata is not 1 row. It is a database. PID is short for picture identifier. If a record exist in the database, it should have a jpg file in the directory with the same number. So if PID = 2818 for that record, then there should be a 2818.jpg. Can you help me with thowing this into some kinda loop?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •