How to check if the image already exists or not in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

I Stored Image in sql table using sql server 2005.Now I need to check Whether the image already exists or not.
My Code is Below

What I have tried:

SQL

CREATE PROCEDURE sptblEmployeeProofDetail
(
@SID numeric(18),@EmpSid numeric(18),@ProofTypeSID numeric(18),@ProofNo nvarchar(50),
@ProofPath nvarchar(100),@ProofImage image,@RevisionNo numeric(18),@CreatedBy numeric(18,3))
AS 
BEGIN

IF ((Select Count(*) from tblEmployeeProofDetail where EmpSid=@EmpSid and ProofTypeSID=@ProofTypeSID
     and ProofPath=@ProofPath and ProofImage=@ProofImage)>0)
BEGIN
 Select Top 1  @RevisionNo = RevisionNo +1 from tblEmployeeProofDetail order by RevisionNo Desc
END
INSERT INTO tblEmployeeProofDetail
(EmpSid,ProofTypeSID,ProofNo,ProofPath,ProofImage,RevisionNo,CreatedBy,CreatedOn)
VALUES
(@EmpSid,@ProofTypeSID,@ProofNo,@ProofPath,@ProofImage,@RevisionNo,@CreatedBy,GETDATE())
END

The Error Message is

Msg 402, Level 16, State 1, Procedure sptblEmployeeProofDetail, Line 9
The data types image and image are incompatible in the equal to operator.

Posted 16-Sep-18 23:00pm

Flower@12

Updated 17-Sep-18 0:43am

Maciej Los

v3

Add a Solution

Comments

Richard MacCutchan 17-Sep-18 6:04am

The message is clear, there is an incompatibility between the type in your stored procedure and the one in your database table.

phil.o 17-Sep-18 6:13am

Not exactly :) It tells that you cannot compare image datatype using equal to operator.

[no name] 17-Sep-18 6:16am

Yes.Now How can i Check whether image is exists are not in table using sql storedprocedure

Maciej Los 17-Sep-18 6:28am

Good point!

Richard MacCutchan 17-Sep-18 6:46am

So what is the answer?

phil.o 17-Sep-18 7:28am

Apparently, varbinary datatype would be eligible to comparisons, but it still has to be checked on SQL Server 2005.

Richard MacCutchan 17-Sep-18 9:03am

Yes, I read up on it a bit more following your comment. But I think that error message needs some work.

[no name] 17-Sep-18 6:15am

In my table ProofImage Field has Image Data type and the Stored Procedure also Declared as image..but it Shows an error.My Table Format
SID numeric(18, 0)
EmpSid numeric(18, 0)
ProofTypeSID numeric(18, 0)
ProofNo nvarchar(50)
ProofPath nvarchar(100)
ProofImage image
RevisionNo numeric(18, 0)
CreatedBy numeric(18, 3)
CreatedOn datetime

[no name] 17-Sep-18 6:28am

a.) If you have the Chance, replace IMAGE by VARBINARY(MAX). IMAGE will disapear sooner or later.

b.) VARBINARY(MAX) seems to work fine in the WHERE clause

Maciej Los 17-Sep-18 6:29am

Good point!

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)

OriginalGriff · Accepted Answer · 2018-09-17T00:42:00

In addition to the change to VARBINARY suggested by 0x01AA, I'd suggest that you don't use it in the WHERE clause, although it will work. Instead, add an extra column to you table containing a Hash value (MD5 or a basic SHA will do for this, there is no need to be secure) and compare the hash values instead of the image itself.

Since images tend to be pretty large, once your DB gets populated, that SELECT will start to run slower and slower if you continue to compare images. Comparing hashes is a lot quicker, as they are a defined - much smaller - size. If you are feeling paranoid, you can compare the images if you get hash matches, but it probably isn't needed.

Maciej Los · Accepted Answer · 2018-09-17T00:43:00

Well, the only way to compare images on SQL server side is to CONVERT[^] them into VARBINARY data type:

SQL

...
WHERE CONVERT(VARBINARY(MAX), ProofImage) = CONVERT(VARBINARY(MAX), @ProofImage)

Note: i'm not sure that CONVERT fuction is available on MS SQL Server 2005, because of MSDN documentation...

For further details, please see: SQL Server 2005 Data Types Reference - ConnectionStrings.com[^]

How to check if the image already exists or not in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0