Retrieving images from SQL Server

[VBAHole22]

I am trying to write a windows tool that retrieves images from a SQL Server database. The images are in an image field in SQL.

The issue I'm having is that the success of getting one of these images out and displaying it seems to be based on how that image got in the db in the first place. I have code that stores the image using .NET. I then pull those out using this

 

cc = new SqlConnection(connString);
com = new SqlCommand(cmd,cc);
cc.Open();
byte[] b = (byte [])com.ExecuteScalar();

MemoryStream mem = new MemoryStream(b);
pictureBox1.Image = Image.FromStream(mem);

 

and that works. But a majority of my user base is using Access to load images into this blob field. They link to the sql database using ODBC and then they right click on the field and insert object and browse to a file.

When I double click on the field in access I get the image. When I run the above code I get

 

Parameter is not valid. :: at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)

at System.Drawing.Image.FromStream(Stream stream)

at Tool1.Viewer.PullImage(XMLSettings xmlSet, String ImageID) in c:\projects\tool1\viewer.cs:line 110

 

I can see that my MemoryStream has a length so it appears valid. It's as if I need to do something else because this image was loaded with Access but I don't know what to do?

Any suggestion?

[Mister E]

It seems that when you write the binary data from Access over ODBC you are somehow messing the data up. What method are you using to do this? The data is being saved but the process is breaking when trying to read the underlying picture's bytes.

[VBAHole22]

I have a field in my sql db that is type image. I create an odbc connection in acess link to the table, open it in access and then right click on the image field and Load Object... I then browse to the file of the image and it loads to the database.

 

Seems like a valid way to go about it. I don't understand why it comes out the wrong way. Maybe there is another way for me to extract them from the db? Maybe I should try to do it in some Access kind of way.

[Joe Mamma]

I have a field in my sql db that is type image. I create an odbc connection in acess link to the table, open it in access and then right click on the image field and Load Object... I then browse to the file of the image and it loads to the database.

 

Seems like a valid way to go about it. I don't understand why it comes out the wrong way. Maybe there is another way for me to extract them from the db? Maybe I should try to do it in some Access kind of way.

Im thinking you might want to convert your access mdb to an adp and connect via ado to the sql server

 

there are lots of bugs with the ODBC sql server driver. . . I know that text/ntext fields can get truncated

[VBAHole22]

Unfortunately that aspect of this project is out of my control. The images have already been loaded into Sql Server via Access. My task is to design a .NET windows app to pull them out and display them on a form.

 

Perhaps I can use something other than a SqlConnection object to pull them back out of the database somehow?

[Joe Mamma]

Unfortunately that aspect of this project is out of my control. The images have already been loaded into Sql Server via Access. My task is to design a .NET windows app to pull them out and display them on a form.

 

Perhaps I can use something other than a SqlConnection object to pull them back out of the database somehow?

the question is. . . are you sure you hacve images in the database and that access didnt corrupt them???

[Joe Mamma]

the question is. . . are you sure you hacve images in the database and that access didnt corrupt them???

I see the problem. . . you arent saving an image into sql server, you are saving an embedded OLE object.

 

note when you link a table to an access mdf, images are mapped as OLE Objects.

 

we use DBPix for our VB6/VBA apps. In delphi this isn't a problem as it ships with native bound image controls. :( sorry.

[VBAHole22]

So .NET has no native support for extracting this OLE object?

Optionally I can do this in VB6 if that has something different.

[Joe Mamma]

So .NET has no native support for extracting this OLE object?

Optionally I can do this in VB6 if that has something different.

I dont know if .NET has an ActiveX container control. . . but I dont think the problem is .net. . . the problem is access. you need to not use the bound container to slurp in your file. you need to build an access interface to open the file, read the bytes to an array, and push the bytes into the field.

 

if you already have an array of bytes in the sql server that is a valid image, notice how access doesnt display it correctly? it only can display the embedded OLE object it loaded.

 

and by the way, it doesnt work using an adp either. . . I tried that.

 

You are going to have to rewrite the access so the image isnt bound, but has a picture box on it. in your move events, grab the byte array from the recordset, and coax it into the unbound image. . . also give a method for opening a file and pushing the bytes into the recordest field (and into the unbound image at the same time)

 

again. . . check out DBPix. for 99 bux, it might be the best way to go :( sorry

[VBAHole22]

Ouch.

Thanks for the help joe.

What a can of worms I opened with this one. Who would have thought this would be so difficult. I googled and 4 pages buried in there I saw where someone had said that some images can get a header attached when they get jammed into ole. The northwind got a header of 78 bytes so you have to offset this when reading it out. But they didn't explain how they knew that.

 

Arrggg! I'll look into that software you recommend. Wonder how they do it? Geez you would think I was working with software from 2 different companies here but i ain't.

Sql Server doesn't seem to have any kind of support at all for this image type natively. I mean if you just have sql server and a pile of images how would you get them in there anyhow? I can see why folks turn to access to load images into sql. It appears to be a simple easy way but what a pain to get out.

 

Wonder what would happen if I got really creative and tried to query the linked access table.......

[Joe Mamma]

Ouch.

Thanks for the help joe.

What a can of worms I opened with this one. Who would have thought this would be so difficult. I googled and 4 pages buried in there I saw where someone had said that some images can get a header attached when they get jammed into ole. The northwind got a header of 78 bytes so you have to offset this when reading it out. But they didn't explain how they knew that.

 

Arrggg! I'll look into that software you recommend. Wonder how they do it? Geez you would think I was working with software from 2 different companies here but i ain't.

Sql Server doesn't seem to have any kind of support at all for this image type natively. I mean if you just have sql server and a pile of images how would you get them in there anyhow? I can see why folks turn to access to load images into sql. It appears to be a simple easy way but what a pain to get out.

 

Wonder what would happen if I got really creative and tried to query the linked access table.......

give me the weekend and I will scour the delphi source to see how they bind images in their dbimage control

[penfold69]

The OLE Header is 78 bytes and, (from memory) it starts with a static string.

 

The basic premise is to read the image data from the database, check for this static string at the beginning of it, and advance forward 78 bytes if it is present. Then decode the "remaining" bytes as an image.

 

I have some code somewhere that does it in VB.Net. I'll see if I can grab it and post it.

 

P.

[Joe Mamma]

The OLE Header is 78 bytes and, (from memory) it starts with a static string.

 

The basic premise is to read the image data from the database, check for this static string at the beginning of it, and advance forward 78 bytes if it is present. Then decode the "remaining" bytes as an image.

 

I have some code somewhere that does it in VB.Net. I'll see if I can grab it and post it.

 

P.

is there a way to tell from the bytes that it is OLE? I guess you can pad 78 bytes on any other insert.

[VBAHole22]

I tried the 78 pad but no love. Same error message. The link I found about the 78 said that the pad is variable in length and it determined by the file's name. Not sure how to interpret that.

 

MemoryStream mem = new MemoryStream(b);
int offset = 78;
mem.Write(b, offset,b.Length-offset);
Bitmap bmp = null;
bmp = new Bitmap(mem);
cc.Close();
pictureBox1.Image = bmp;

[penfold69]

http://support.microsoft.com/default.aspx/kb/175261 is worth a read.

 

Its in VB6, but the basic premise is there to convert.

 

Also, a little testing by this guy gives some more info.

 

P.

[Joe Mamma]

I tried the 78 pad but no love. Same error message. The link I found about the 78 said that the pad is variable in length and it determined by the file's name. Not sure how to interpret that.

Is that when the object is linked? I dont think a file is relevent if the object is embedded.

 

What you want is embedded objects.

[VBAHole22]

Maybe I pull the thing out into a word doc.

I don't care how I get it out, i just now that is how they got them in and I wish they didn't.

 

More 78 testing