How do I compare two images in SQL select statement

Question

0.00/5 (No votes)

See more:

i have used two picture box. one for retriving image from my pc and another picture box to compare image in database and retrive that image if it matches with picturebox1 but it doesnt work.it shows "the data types image and varchar are incompatible in equal to operator. can anyone tell the code for comparing that.

What I have tried:

select image from table1 where image='"+picturebox1.image"' ;

Posted 7-Aug-17 0:27am

Member 13349523

Updated 7-Aug-17 1:41am

Add a Solution

Comments

Richard MacCutchan 7-Aug-17 6:45am

You cannot compare different object types, as there is no way the system can identify which parts are different. You must convert both to images or byte arrays and compare them byte for byte.

F-ES Sitecore 7-Aug-17 7:16am

If you need to be able to select on an "image" then you might want to look at how you can turn the image into a hash then store the image and hash in your table. To select on an image convert it to a hash and "select * where ImageHash = @hash"

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2017-08-07T00:45:00

Solution 1

You need to convert to Byte array before you can compare, see this article: Store or Save images in SQL Server[^]
And also here: Inserting & Retrieving Images from SQL Server Database without using Stored Procedures[^]
Note that the Image field type is deprecated, instead use varbinary(MAX)

Posted 7-Aug-17 0:45am

RickZeeland

Updated 7-Aug-17 0:53am

v2

OriginalGriff · Answer 2 · 2017-08-07T01:41:00

Solution 3

Firstly, when you do this in C#:

C#

... select image from table1 where image='"+picturebox1.image"' ;

It doesn't append the image to the SELECT. What you get is the string:

... select image from table1 where image='System.Drawing.Bitmap'

See here for why: Why do I get a "Parameter is not valid." exception when I read an image from my database?[^]
If you have used similar code to INSERT your images to the DB, then I have bad news for you: they are all useless. The link shows how you need to do that properly.

It also implies that you commonly build SQL commands like this: don't. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

I'd also suggest that it's a bad idea to compare images directly in SQL - that's going to be horrible from a performance point of view.
Instead, generate hashes and compare those instead - only the same hash value images need to be directly compared for equality, saving a lot of time-and-memory consuming comparisons.

Posted 7-Aug-17 1:41am

OriginalGriff

Comments

Member 13349523 7-Aug-17 10:58am

can u plz give me an exact code for comparing two images. one image from picture box and other from database because i want to take image as input and compare it stored image im db and print some statement.. i hardly struck in doing that.. i cant able to proceed.. plz can u help me bro

OriginalGriff 7-Aug-17 11:21am

Don't do it like that.
The problem is that images are quite large, and comparing many of them means you either take up a lot of SQL Server time - if you do it in SQL - or a lot of bandwidth - if you do it in C# - because you have to pull every image from the DB to your computer to compare them. That's really inefficient, and if you have multiple users doing it (and multiple users is the whole point of SQL Server after all) there quickly comes a point when the whole thing grinds to a halt for everyone - you are effectively designing your own personal DDOS attack on your own systems!

Instead of doing that, store an extra field with your image (or two might be even better) which store one or two hashes: and MD5 and an SHA512 for example. You calculate the hash value of the image when you INSERT it to the DB, and save the hash(es) at the same time. Then, when you want to find one, the calculate the hash(es) for the "new" image, and use SQL to find only those with an identical hash. That's quick, because the hashes are (by definition) a lot smaller than the image itself but near unique enough that most of teh time you will return only one match. A quick compare on the few (or one) image with a matching hash and you know for sure it is the same image.

Member 13349523 7-Aug-17 11:54am

thanks for your suggestions bro... i try to work on that..

Member 13349523 7-Aug-17 11:56am

can u tell me how to calculate hash for an image..

OriginalGriff 7-Aug-17 11:57am

Google is not working today?
https://www.google.co.uk/search?q=how+to+calculate+hash+for+an+image+c%23&oq=how+to+calculate+hash+for+an+image+c%23&aqs=chrome..69i57.5988j0j7&sourceid=chrome&ie=UTF-8