Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Detect existing of file by passing the path of file, possible?
- Thread starter lingyi
- Start date
(1)Do you have SQL2000? If so, I am thinking of a solution that uses a UDF (User Defined Function)
(2) Does the path&filename for the image file exist as column in the Products table?
For example, the UDF could return 1 if the file exists, 0 if it doesn't. It would work approx. like this:
Select SUM(ImgExists(ColumnWithPath)) as ExCount
From Products
Other variations of the Select statement would be possible if, for instance, you wanted to list the records that did (or did not) have the Image on disk.
But for this approach to work, you need SQL2000. Is that what you have?
bperry
- Thread starter
- #3
Okay,
Many times in SQL Server, folks won't actually store images directly in the database (although that is quite possible.) Instead, what they will do is store the path&file name of the image in an ordinary varchar column. Something like 'c:\products\pictures\widget.gif'. Then it becomes the job of the application (ASP, whatever...) to read that column to get the path&file name, and then retrieve the image from the disk.
I was assuming (maybe incorrectly) that this is what you were doing. So when I said this:
Select SUM(ImgExists(ColumnWithPath)) as ExCount
From Products
I thought you had a column in your table like I have described.
If this is not what you were looking for, then my apologies. (It's too bad, because I actually have it working!) Would you like to describe further what it is you are interested in?
bperry
Many times in SQL Server, folks won't actually store images directly in the database (although that is quite possible.) Instead, what they will do is store the path&file name of the image in an ordinary varchar column. Something like 'c:\products\pictures\widget.gif'. Then it becomes the job of the application (ASP, whatever...) to read that column to get the path&file name, and then retrieve the image from the disk.
I was assuming (maybe incorrectly) that this is what you were doing. So when I said this:
Select SUM(ImgExists(ColumnWithPath)) as ExCount
From Products
I thought you had a column in your table like I have described.
If this is not what you were looking for, then my apologies. (It's too bad, because I actually have it working!) Would you like to describe further what it is you are interested in?
bperry
- Thread starter
- #5
Hi,
I trying to pass the physical path of all my images located in server, from there i would like to do a select statement to select the item where imgExits( path name & itemfilename) = true. Or my other idea is can function pass back the whole recordset so that i can use it in my asp. I trying to filter the item which those tat hv image to be displayed on the web page. Thanks.
I trying to pass the physical path of all my images located in server, from there i would like to do a select statement to select the item where imgExits( path name & itemfilename) = true. Or my other idea is can function pass back the whole recordset so that i can use it in my asp. I trying to filter the item which those tat hv image to be displayed on the web page. Thanks.
- Thread starter
- #7
Hi bperry,
I have a file name in my product table, and i will do a select statement as this 'select 'c:\temp\products\' + Productfilename as pathName from tblproduct WHERE ImgExists(Pathname)=1', yet when i try to create function for ImgExits, using the scripting.FileSystemObject, it prompted error. May I know how you detect the existing of file in ImgExists? Thanks.
I have a file name in my product table, and i will do a select statement as this 'select 'c:\temp\products\' + Productfilename as pathName from tblproduct WHERE ImgExists(Pathname)=1', yet when i try to create function for ImgExits, using the scripting.FileSystemObject, it prompted error. May I know how you detect the existing of file in ImgExists? Thanks.
Bernadette
MIS
Hi There
The easiest way is to use the extended stored proc xp_fileexist to check for the file.
Usage:
exec master..xp_fileexist 'path of file to check'
EG:
exec master..xp_fileexist 'c:\autoexec.bat'
Hope This Helps
Bernadette
The easiest way is to use the extended stored proc xp_fileexist to check for the file.
Usage:
exec master..xp_fileexist 'path of file to check'
EG:
exec master..xp_fileexist 'c:\autoexec.bat'
Hope This Helps
Bernadette
Alrightie, hopefully this is what you need.
The solution uses a UDF to determine whether the file exists on disk. It returns 1 if exists, 0 if not.
Here is the function:
CREATE FUNCTION FileExists (
@File varchar(50)
)
Returns int AS
BEGIN
--------------------
-- Method 1
--------------------
declare @result int
exec master..xp_fileexist @File, @result out
Return @result
--------------------
-- Method 2
--------------------
--declare @result int
--exec @result =master..xp_getfiledetails @File
--return ABS(@result - 1)
END
-----------------
And here are some example of how you might use it.
-------------------------
-- List all that exist
-------------------------
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 1
-------------------------
-- List all do not exist
-------------------------
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 0
-------------------------
-- Count do not exist
-------------------------
Select Count(*) as DoesNotExist
From
(
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 0
) dt
-------------------------
-- Count do exist
-------------------------
Select Count(*) as DoesExist
From
(
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 1
) dt
or
Select
SUM(dbo.FileExists('c:\temp\products\' + ImageName))
as DoesExist
From Products
----------------------
The solution uses a UDF to determine whether the file exists on disk. It returns 1 if exists, 0 if not.
Here is the function:
CREATE FUNCTION FileExists (
@File varchar(50)
)
Returns int AS
BEGIN
--------------------
-- Method 1
--------------------
declare @result int
exec master..xp_fileexist @File, @result out
Return @result
--------------------
-- Method 2
--------------------
--declare @result int
--exec @result =master..xp_getfiledetails @File
--return ABS(@result - 1)
END
-----------------
And here are some example of how you might use it.
-------------------------
-- List all that exist
-------------------------
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 1
-------------------------
-- List all do not exist
-------------------------
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 0
-------------------------
-- Count do not exist
-------------------------
Select Count(*) as DoesNotExist
From
(
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 0
) dt
-------------------------
-- Count do exist
-------------------------
Select Count(*) as DoesExist
From
(
Select * from Products
Where dbo.FileExists('c:\temp\products\' + ImageName) = 1
) dt
or
Select
SUM(dbo.FileExists('c:\temp\products\' + ImageName))
as DoesExist
From Products
----------------------
Oh yes, I almost forgot.
This will work fine if you plan to run it as sa or other member of sys admin.
If you need an 'ordinary' user to call this function, then you will need to make a security change in the master database. I (or someone else here) can help you with that if you need it.
This will work fine if you plan to run it as sa or other member of sys admin.
If you need an 'ordinary' user to call this function, then you will need to make a security change in the master database. I (or someone else here) can help you with that if you need it.
- Status
Similar threads
