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.photo
FROM [2560].[2560].itemphoto itemphoto ,[erpinv41].[ims].itemphoto MAINPHOTO
where itemphoto.photo <> MAINPHOTO.photo
but i get the follwoing error:-
Msg 402, Level 16, State 1, Line 1
The 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.photo
FROM [2560].[2560].itemphoto AS itemphoto
CROSS 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.photo
FROM [2560].[2560].itemphoto AS itemphoto
CROSS 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 = @md5x
You 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) AS
BEGIN
DECLARE @hash CHAR(32)
EXEC master.dbo.xp_md5 @data, @len, @hash OUTPUT
RETURN @hash
END
GO
|
|
|
|
|
|
|
|
|
Compare two IMAGE fields