如何在SQL select语句中比较两个图像 [英] How do I compare two images in SQL select statement

问题描述

我用了两个图片框。一个用于从我的电脑和另一个图片框中恢复图像以比较数据库中的图像并检索该图像是否与picturebox1匹配但它不起作用。它显示数据类型图像和varchar与操作符不相容。任何人都可以告诉用于比较的代码。



我的尝试：



select来自table1的图像，其中image ='+ picturebox1.image';

解决方案

您需要转换为字节数组才能进行比较，请参阅此文章：在SQL Server中存储或保存图像 [ ^ ]

此处也是：插入& ;不使用存储过程从SQL Server数据库中检索图像 [ ^ ]

请注意图片字段类型，而是使用 varbinary（MAX）




首先，当你在C＃中执行此操作时：

 ... 选择图像来自 table1 其中 image = ' + picturebox1.image'; 

它不会将图像附加到SELECT。你得到的是字符串：

 ...从table1中选择图像，其中image ='System.Drawing.Bitmap'

请参阅此处了解原因：为什么我得到参数无效。我从数据库中读取图像时出现异常？ [ ^ ]

如果你使用类似的代码将图像插入数据库，那我就坏了给你的消息：他们都没用。该链接显示了您需要如何正确地执行此操作。



它还意味着您通常构建如下的SQL命令：不要。永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。



我还建议直接在SQL中比较图像是一个坏主意 - 从性能的角度来看，这将是可怕的。

相反，生成哈希并比较它们 - 只需要相同的哈希值图像需要直接进行比较以获得相等性，从而节省了大量的时间和内存消耗比较。




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"' ;

解决方案

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)




Firstly, when you do this in 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.




这篇关于如何在SQL select语句中比较两个图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！