Displaying an Image from a SQL Server "Image" field

Targets Web
1

I have an existing SQL Server database that has JPG images held in a table within an field of type = Image.

I need to display these images on a Xojo web page.

I can get the data into a recordset but getting the Image data into a WebImageView is eluding me.

I’ve tried:

imgLogo.Picture = rs.Field(“Image”).PictureValue

Where imgLogo is my WebImageView control. This gives me an “Unsupported Format” exception as apparently this isn’t JPEG data.

I’ve also tried using the WebPicture constructor and passing the image data as a string but this gives me an “Out of Bounds” exception.

Does anyone have any suggestions?

Thanks in advance.

Jim

2

Perhaps try using DatabaseField.NativeValue to get the picture data in its native (possibly BLOB) format.

Then you might try putting the image data from the DB into a MemoryBlock and then using the WebPicture constructor to create it.

Or you can try writing the image data from the DB to a file and then loading the file.

3

HI Paul,

I’ve tried using NativeValue but Xojo thinks it’s a string. I think this is the problem. What is Xojo doing with an “Image” datatype from SQL? Why is it a string?

If it is a string, how do I get to the Native BLOB data? Can I decode it somehow?

Thanks

4

What code did you try? NativeValue should be returning a Nil-encoded String, which is essentially just a collection of bytes. Does that not work with the WebPicture constructor? Does it work with the constructor if you assign it to a MemoryBlock first? Is it a valid JPEG if you save the NativeFile to a file on the drive?

5

Hi Paul,

The recordset is not bringing back the data from the database.

If I try:
Dim m As MemoryBlock
m = rs.Field(“Logo”).NativeValue

or

Dim s As String
s = rs.Field(“Logo”).NativeValue

Then m and s are only 26 bytes long.

My table definition looks like this: -
CREATE TABLE [dbo].[AccountImage](
[AccountId] [int] NOT NULL,
[Image] [image] NULL
)

My SQL to retrieve the info looks like this: -
SELECT
AccountId,
[Image] Logo
FROM [dbo].[AccountImage]
WHERE
[AccountId] = @AccountId

And there is 43K of data in the record I’m retrieving.

Any suggestions?

Thanks

Jim

6

I just found this post in the database section:
https://forum.xojo.com/6805-show-image-stored-retrieving-in-mssql/0#p47448

So is this a bug? Could you confirm please?

7

the recommendation for storing images in SQL 2008+ would be Varbinary(max) as data type image is being depreciated. With this said, based on the forum thread you provide, this would indicate that it maybe related.

a few thoughts here. does the image have to be stored in the database or can you store it on the File System and have a database file pointer to the image to load into the web app?

Another thought is, if you try ODBC… do you get the same results?

8

Hi Rich,

I’ve tried converting the Image datatype to Varbinary(max) in my stored procedure but to no avail. I’ve also tried converting the Varbinary(max) value to varchar(max) but that still only returns 26 bytes!

The database is an existing one with a ASP.NET website populating the data which I can’t modify. So I have to read the data from the database.

I’ll try ODBC and see what happens.

Thanks for your suggestions.

9

Success! Connecting using ODBC correctly retrieves the Image data!