July 12, 2011 at 10:25 pm
Hi,
While adding new image in table. how to check image is already exists or not.
regards,
SUNDARA MURTHY S
July 13, 2011 at 3:34 am
Use SELECT statement. If the column value is NULL, image does not exist.
July 13, 2011 at 4:41 am
Are you using the IMAGE data type?
A breakdown of your table DDL would be helpful.
July 13, 2011 at 4:48 am
how i can use DDL. how to check the uploading image is exist or not in table
July 13, 2011 at 5:12 am
You don't use DDL to check that an image exists. You give us the DDL (the CREATE TABLE statement) to show us how your table is designed so we can answer your question correctly. Otherwise, we just assume based on how we would design such a table and our assumptions may be wrong.
July 13, 2011 at 5:22 am
Table Structure
Create Table ##ItemMaster(Sys_Item_Num numeric (18,2), ImgFile image)
While modification, i what to check the uploading image is already in table or not.
July 13, 2011 at 5:38 am
First, I recommend changing that Image data type to a VarBinary(MAX) data type. Image is deprecated in SQL Server (has been since 2005) and will go away in a future version without much warning. So if you can fix it now, do so.
My advice would be to load the image into a staging table and do a compare by converting the Image to binary in both the staging table and the destination table. I'm guessing at the syntax because I've never converted an image before.
INSERT INTO dbo.##ItemMaster (ImgFile)
SELECT st.ImgFile
FROM dbo.##StagingItemMaster st
LEFT OUTER JOIN dbo.##ItemMaster im
ON Convert(varbinary(MAX),st.ImgFile) = Convert(varbinary(MAX),im.ImgFile)
WHERE im.ImgFile IS NULL;
This is untested code. Make sure to verify it in a Dev environment before implementing it in Production. Please let me know if you have any problems with it.
If you change the table to use varbinary instead of image, you should be able to do the compare without the CONVERT() function. I think...
July 13, 2011 at 6:04 am
As it can become quite a long binary string, you can also try converting the image to a hash value, which allows easier comparison:
WHERE HASHBYTES('MD5', cast(ImageColumn1 as varbinary(MAX)))
= HASHBYTES('MD5', cast(ImageColumn2 as varbinary(MAX)))
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2011 at 6:07 am
Koen Verbeeck (7/13/2011)
As it can become quite a long binary string, you can also try converting the image to a hash value, which allows easier comparison:
WHERE HASHBYTES('MD5', cast(ImageColumn1 as varbinary(MAX)))
= HASHBYTES('MD5', cast(ImageColumn2 as varbinary(MAX)))
Thank you, Koen. I didn't even think about hashbytes.
July 14, 2011 at 9:44 am
Brandie Tarvin (7/13/2011)
Koen Verbeeck (7/13/2011)
As it can become quite a long binary string, you can also try converting the image to a hash value, which allows easier comparison:
WHERE HASHBYTES('MD5', cast(ImageColumn1 as varbinary(MAX)))
= HASHBYTES('MD5', cast(ImageColumn2 as varbinary(MAX)))
Thank you, Koen. I didn't even think about hashbytes.
You were right not to. It has some unpleasant limitations.
In SQL 2008, 2008 R2, and Denali the input size is limited to 8000 bytes, so using it on a varbinary(MAX) value which may be a larger and just produce an error will not be useful. (In SQL 2005 the documentation doesn't specify this limit - but either be design or as a bug an error "the data would be trucnated" is produced if teh input is more than 8000 bytes.)
Even when it works (that is when the image is small enough), it will sometimes return a false positive - you will get the same hash for two different values - so comparing hashbytes is not a reliable check; using SHA2 (in denali only) or SHA1 (in 2005 onwards) will deliver fewer false positives than MD5 (SHA2 delivers fewer than SHA1), so even for small images SHA2 should be used rather than MD5, and SHA1 should be used where SHA2 is not available. The length can be compared as well as the hash to reduce the frequency of false positives, but to eliminate them you still need to do a proper comparison (preferably not in T-SQL, as the language isn't really suited to such a job) any time you get a "match"; and if the images may be longer than 8000 bytes, do the hashing outside of SQL too in order to eliminate the limit on image size.
Tom
July 14, 2011 at 9:55 am
i've done something like this with putting icons in a database, and i get a CRC value from vb.NET,and store it along with the varbinary(max) in the row;
it seemed a little easier for me to get the application to calculate the CRC value when inserting, but that logic might not apply to your case. i would then test length of the image and the CRC for duplicates.
Lowell
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply