Searching sql image types with WHERE clause

23 Jun 2006 6:39 PM

James

Hello,

I am storing finger print records in sql server 2000 on a table with a
column type of image. I am able to store and access the records with no
problems at all. I had a late breaking requirement to search for a
duplicate image record before I add any new ones to the table. I
quickly found out that i get an error message from sql server when I
try to use a varbinary in the where clause. The message is below.

I had to use varbinary because sql won't let me use image type as a
local variable.

Server: Msg 306, Level 16, State 1, Line 7
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Here is an simplified example of what I'm doing:

The template column is of type (image).

DECLARE @imp VARBINARY(8000)

--get the image type from DB and store it as varbinary
select @imp=template
from tb_PatientFP
where pin = 1

--search for any other records
select pin
from tb_PatientFP
where template = @imp -- causes Msg 306

Thanks in advance.

23 Jun 2006 7:34 PM

Mike C#

You're not really expecting to be able to match fingerprints exactly are
you? Or are you checking to see if you're adding an already-added
fingerprint image to the database? You can CAST an IMAGE to VARBINARY and
compare, but that's only good for up to 8,000 bytes. Anything beyond that
is going to be a very painful comparison involving TEXTPTR's and READTEXT,
unless you prefer to do your comparisons client-side...

Just a suggestion, but if you want to make sure you don't accidentally
re-load the exact same print twice, why not grab a hash value of the image
before you load it and store that as well; then compare the hash values
instead at load time? If you're trying to compare actual fingerprints (like
biometrics type applications), you might want to look into how law
enforcement does it. Often they store identifying information representing
a set of points on the fingerprint, with identifying features like ridges
and curls; then they match that up to the identifying features of the
subject's fingerprint. It takes up a lot less storage space than a graphic
image of the fingerprint.

Show quote

"James" <jhoust***@gmail.com> wrote in message
news:1151087947.676668.75310@c74g2000cwc.googlegroups.com...
> Hello,
>
> I am storing finger print records in sql server 2000 on a table with a
> column type of image. I am able to store and access the records with no
> problems at all. I had a late breaking requirement to search for a
> duplicate image record before I add any new ones to the table. I
> quickly found out that i get an error message from sql server when I
> try to use a varbinary in the where clause. The message is below.
>
> I had to use varbinary because sql won't let me use image type as a
> local variable.
>
> Server: Msg 306, Level 16, State 1, Line 7
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
>
> Here is an simplified example of what I'm doing:
>
> The template column is of type (image).
>
> DECLARE @imp VARBINARY(8000)
>
> --get the image type from DB and store it as varbinary
> select @imp=template
> from tb_PatientFP
> where pin = 1
>
> --search for any other records
> select pin
> from tb_PatientFP
> where template = @imp -- causes Msg 306
>
>
> Thanks in advance.
>

23 Jun 2006 10:29 PM

Erland Sommarskog

James (jhoust***@gmail.com) writes:

> I am storing finger print records in sql server 2000 on a table with a
> column type of image. I am able to store and access the records with no
> problems at all. I had a late breaking requirement to search for a
> duplicate image record before I add any new ones to the table. I
> quickly found out that i get an error message from sql server when I
> try to use a varbinary in the where clause. The message is below.
>
> I had to use varbinary because sql won't let me use image type as a
> local variable.

Had you been on SQL 2005 you could have used the varbinary(MAX)
data type which does not have restrictions of image. Since this
appears to be a new development, it may be a good idea to investigate
whether you can move to SQL 2005.

Then again, searching for existing images by comparing many large
binary values is not going to be very effective. Mike C#'s suggestion
of computing hash values is probably a good idea even for SQL 2005.

> --search for any other records
> select pin
> from tb_PatientFP
> where template = @imp -- causes Msg 306

You could say

WHERE convert(varbinary(8000), template) = @imp

But this could give false positives that you need to examine further.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx