Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
issam
Starting Member
30 Posts |
Posted - 2008-09-10 : 15:08:00
|
Hi,how to Compare two IMAGE fields, i try the following:-SELECT itemphoto.item_no, itemphoto.photoFROM [2560].[2560].itemphoto itemphoto ,[erpinv41].[ims].itemphoto MAINPHOTO where itemphoto.photo <> MAINPHOTO.photobut i get the follwoing error:-Msg 402, Level 16, State 1, Line 1The data types image and image are incompatible in the not equal to operator.best regards |
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-10 : 17:19:52
|
I may be wrong, but i have never heard about comparing image fields.In the previous versions of MS SQL images are stored as BLOBs, which is a bad design. As its better to store the images in a folder and put the relative path in your db and present your image in the presentation layer. Dont know if this has been changed in 2008, but i doubt it.Secondly, if you compare two image fields, what do you get in return ? Yes they are the same or a value of true ??? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 17:27:59
|
You can try to use DATALENGTH as first line of check for the two images.If the DATALENGTH is different, then you have a "different" picture".You can also use SUBSTRING(photocolumn, 1, 8000) to check first 8000 bytes.And also SUBSTRING(photocolumn, DATALENGTH(photocolumn) - 7999, 8000) to check last 8000 bytes. E 12°55'05.63"N 56°04'39.26" |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 17:30:00
|
[code]SELECT itemphoto.item_no, itemphoto.photoFROM [2560].[2560].itemphoto AS itemphotoCROSS JOIN [erpinv41].[ims].itemphoto AS MAINPHOTO where DATALENGTH(itemphoto.photo) <> DATALENGTH(MAINPHOTO.photo) or SUBSTRING(itemphoto.photo, 1, 8000) <> SUBSTRING(MAINPHOTO.photo, 1, 8000)[/code] E 12°55'05.63"N 56°04'39.26" |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-10 : 18:21:03
|
Definitely a hot guy.Hey Peter, it should past midnight in sweden. And you are still on fire ??? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-10 : 18:28:16
|
Yes, the time is now 12:23am. E 12°55'05.63"N 56°04'39.26" |
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-09-10 : 19:11:07
|
its 0:10am here, Great job.Cut back on the coffee |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-10 : 20:26:04
|
quote: Originally posted by Peso
SELECT itemphoto.item_no, itemphoto.photoFROM [2560].[2560].itemphoto AS itemphotoCROSS JOIN [erpinv41].[ims].itemphoto AS MAINPHOTO where DATALENGTH(itemphoto.photo) <> DATALENGTH(MAINPHOTO.photo) or SUBSTRING(itemphoto.photo, 1, 8000) <> SUBSTRING(MAINPHOTO.photo, 1, 8000) E 12°55'05.63"N 56°04'39.26"
I believe you can also do these: or itemphoto.photo not like MAINPHOTO.photo or checksum(itemphoto.photo) <> checksum(MAINPHOTO.photo) or binary_checksum(itemphoto.photo) <> binary_checksum(MAINPHOTO.photo) or SUBSTRING(itemphoto.photo, 8001, 16000) <> SUBSTRING(MAINPHOTO.photo, 8001, 16000) or SUBSTRING(itemphoto.photo, 16001, 24000) <> SUBSTRING(MAINPHOTO.photo, 16001, 24000) or SUBSTRING(itemphoto.photo, 24001, 32000) <> SUBSTRING(MAINPHOTO.photo, 24001, 32000) or SUBSTRING(itemphoto.photo, 32001, 40000) <> SUBSTRING(MAINPHOTO.photo, 32001, 40000) ... and so on ... If they compare equal in the first cut of tests with checksum, binary_checksum, like, and substring compares of fisrt and last 8000 characters, you could use the datalength of the photos to generate a series of substrings to do a complete test in a dynamic SQL Statement.If you are using SQL 2005, convert the column over to varbinary(max) to make things simple:itemphoto.photo <> MAINPHOTO.photo CODO ERGO SUM |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-09-11 : 01:13:21
|
I don't think checksum work over image columns.That's the reason I wrote the checksum replacement. E 12°55'05.63"N 56°04'39.26" |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-09-11 : 12:28:17
|
You're right about checksum.I also tried the LIKE with image and that doesn't work either. LIKE does work with text or ntext, so I guess I just assumed it would work with image. CODO ERGO SUM |
|
|
rboehme
Starting Member
1 Post |
Posted - 2008-10-30 : 20:15:44
|
Install the xp_md5 extended system procedure (you can download the md5.dll from the Internet), and run a statement like this:select * from tablename where dbo.fn_md5x(oldimage, datalength(oldimage)) <> dbo.fn_md5x(newimage, datalength(newimage))For improving performance, create a separate column named md5x, populate it with the values returned by the function, and change the select statement to look like this:select imagecolumn from tablename where md5x = @md5xYou can create a local function out of the extended procedure, like follows:CREATE FUNCTION [dbo].[fn_md5x] (@data IMAGE, @len INT = -1) RETURNS CHAR(32) ASBEGIN DECLARE @hash CHAR(32) EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT RETURN @hashENDGO |
|
|
|
|
|
|
|