Reading image data

Question

Reading image data

tendo

SSC Veteran

Points: 271
More actions
June 17, 2004 at 9:04 am

#108701

This SQL statement
SELECT     MAX(DISTINCT ProductVersion) AS New, ProductType, ProductDesc, CAST(ProductLogo AS varbinary)
FROM         eProducts
GROUP BY ProductType, ProductDesc, ProductLogo

gives me this error
Server: Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
How can I retrieve the above record? ProductLogo is an image column.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dan Smith SSC Enthusiast Points: 152 More actions · Answer 1

I am making the assumption that each row that has the Version, Type, and an image. Hence you are trying to get the latest version and then return the image for it. This code should do the trick.

-----------------------------------------------

SELECT dt.New, p.ProductType, p.ProductDesc, CAST(p.ProductLogo AS varbinary)
FROM eProducts p
INNER JOIN ( SELECT MAX(DISTINCT ProductVersion) AS New, ProductType, ProductDesc FROM   eProducts GROUP BY ProductType, ProductDesc, ProductLogo
) dt
ON p.ProductVersion = dt.New AND p.ProductType = dt.ProductType AND p.ProductDesc = dt.ProductDesc

-----------------------------------------------

Does that do the trick?

tendo SSC Veteran Points: 271 More actions · Answer 2

Nope! am afraid not, I still got the same error... however I have not yet populated the table with the data in question... could that be the problem? If so, then what SQL can insert this data? I presume it will include the file location for the image file

Thx

Pascal Dobrautz SSC Enthusiast Points: 158 More actions · Answer 3

Looks like the problem is in the "Group By" clause of then "inner join". It includes the ProductLogo, which is not in the select statment nor can it be used since it's an Image data type. So try:

-----------------------------------------------
SELECT dt.New, p.ProductType, p.ProductDesc, CAST(p.ProductLogo AS varbinary)
FROM eProducts p
INNER JOIN ( SELECT MAX(DISTINCT ProductVersion) AS New, ProductType, ProductDesc FROM   eProducts GROUP BY ProductType, ProductDesc
) dt
ON p.ProductVersion = dt.New AND p.ProductType = dt.ProductType AND p.ProductDesc = dt.ProductDesc
-----------------------------------------------

----------------------------------------
Pascal Dobrautz
www.sqlassi.net