Images from OLE fields in Access database

[Alan]

Hi,

I'm converting a database application from Access 97 to C#/SQL Server. Old
database contains some images in OLE fields. I've figured out that there's
OLE header preceeding actual image data and dealt with some of the images
which were in standard BMP format but most of the images are in some other
format which is displayed ok in access application and can be copy/pasted to
any image editor but when doubleclicking it doesn't open any application
(like BMP images open Paint, etc.) and I can't figure out how to load it to
..NET image control. The field data for these images starts with some headers
that include two identifiable words "Picture" and "DIB". I've tried
stripping the data in many ways but nothing on my machine is able to open
these.

Does anyone has any experience with this format? Any pointers are greatly
appreciated. Otherwise I'm afraid I'm facing migration of several thousands
of images by copy/pasting from access :/

Thank you in advance,
Alan.

 

[Mary Chipman [MSFT]]

Yes, you can write code to strip off the header and just get the image
bytes from the OLE fields. This code assumes that you want to load the
photo from Northwind into a PictureBox control, which I know you don't
want to do, but this is the code I happen to have lying around from
some appdev courseware that I wrote a few years back. It should be
enough to get you started:

private void LoadEmployeePhoto(int employeeID)
{
SqlDataAdapter da = null;
DataTable dt = new DataTable();
string strCnn =
"Data Source=(local);Database=Northwind;"
+ "Integrated Security=SSPI";
string strSQL =
"SELECT Photo FROM dbo.Employees"
+ " WHERE EmployeeID = "
+ employeeID.ToString();
MemoryStream msPic;
// Signature bytes of an
// OLE container header.
const byte OLEbyte0 = 21;
const byte OLEbyte1 = 28;
// Number of bytes in
// an OLE container header.
const int OLEheaderLength = 78;

da = new SqlDataAdapter(strSQL, strCnn);
da.Fill(dt);
if (dt.Rows.Count == 0)
return;

// Move binary picture data into the byte array
byte[] abytPic = (byte[])dt.Rows[0]["Photo"];

// Test for an OLE container header
if ((abytPic[0] == OLEbyte0)
&& (abytPic[1] == OLEbyte1))
{
// Use a second array to strip off the header.
// Make it big enough to hold
// the bytes after the header.
byte[] abytStripped =
new byte[abytPic.Length - OLEheaderLength];
// Strip off the header by copying the bytes
// after the header.
System.Buffer.BlockCopy(
abytPic, OLEheaderLength, abytStripped,
0, abytPic.Length - OLEheaderLength);

// Load the new byte array
// into a MemoryStream.
msPic = new MemoryStream(abytStripped);
}
else
{
// Load the original byte array into a MemoryStream
msPic = new MemoryStream(abytPic);
}
// Set the picture box image, using the stream.
picEmployee.Image = Image.FromStream(msPic);
}

--Mary

 

[Guest]

Hi Mary,

I am having the same problem, but I still get a "parameter not valid" error
when I call FromStream, even after stripping off the first 78 bytes.

My images contain "Photo Editor Photo MSPhotoEd.3" followed shortly by
"MSPhotoEd.3"

These are images from an old Access database that we are moving to SQL. As
part of the process, I want to extract the embedded images to files and store
only links in the new version of the application. The images were probably
copied and pasted from a variety of image apps. Interestingly enough, I can
copy the contents of the binary field and paste it in Photoshop and get an
image, but with over 700 images, I'd rather do it programmatically.

Thanks,
Adam

Here is the part of my code that loops through the images:

For Each R As DataRow In dt1.Rows
FileNm = P & R("EmployeeID") & "_" & R("LName") & "_" & R("FName")
Dim b() As Byte
b = CType(R("Picture"), Byte())
L = 0
LB = b.Length
If LB > 0 Then
Try
Dim Stm As IO.MemoryStream

If b(0) = OLEb0 And b(1) = OLEb1 Then
Stm = New IO.MemoryStream(b, OLEHeaderLength, LB -
OLEHeaderLength)
Else
Stm = New IO.MemoryStream(b)
End If

Dim IMG As Drawing.Image
IMG = Image.FromStream(Stm)
IMG.Save(FileNm, IMG.RawFormat)

Stm.Close()
Stm.Dispose()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
Next

Yes, you can write code to strip off the header and just get the image
bytes from the OLE fields. This code assumes that you want to load the
photo from Northwind into a PictureBox control, which I know you don't
want to do, but this is the code I happen to have lying around from
some appdev courseware that I wrote a few years back. It should be
enough to get you started:

private void LoadEmployeePhoto(int employeeID)
{
SqlDataAdapter da = null;
DataTable dt = new DataTable();
string strCnn =
"Data Source=(local);Database=Northwind;"
+ "Integrated Security=SSPI";
string strSQL =
"SELECT Photo FROM dbo.Employees"
+ " WHERE EmployeeID = "
+ employeeID.ToString();
MemoryStream msPic;
// Signature bytes of an
// OLE container header.
const byte OLEbyte0 = 21;
const byte OLEbyte1 = 28;
// Number of bytes in
// an OLE container header.
const int OLEheaderLength = 78;

da = new SqlDataAdapter(strSQL, strCnn);
da.Fill(dt);
if (dt.Rows.Count == 0)
return;

// Move binary picture data into the byte array
byte[] abytPic = (byte[])dt.Rows[0]["Photo"];

// Test for an OLE container header
if ((abytPic[0] == OLEbyte0)
&& (abytPic[1] == OLEbyte1))
{
// Use a second array to strip off the header.
// Make it big enough to hold
// the bytes after the header.
byte[] abytStripped =
new byte[abytPic.Length - OLEheaderLength];
// Strip off the header by copying the bytes
// after the header.
System.Buffer.BlockCopy(
abytPic, OLEheaderLength, abytStripped,
0, abytPic.Length - OLEheaderLength);

// Load the new byte array
// into a MemoryStream.
msPic = new MemoryStream(abytStripped);
}
else
{
// Load the original byte array into a MemoryStream
msPic = new MemoryStream(abytPic);
}
// Set the picture box image, using the stream.
picEmployee.Image = Image.FromStream(msPic);
}

--Mary

Hi,

I'm converting a database application from Access 97 to C#/SQL Server. Old
database contains some images in OLE fields. I've figured out that there's
OLE header preceeding actual image data and dealt with some of the images
which were in standard BMP format but most of the images are in some other
format which is displayed ok in access application and can be copy/pasted to
any image editor but when doubleclicking it doesn't open any application
(like BMP images open Paint, etc.) and I can't figure out how to load it to
.NET image control. The field data for these images starts with some headers
that include two identifiable words "Picture" and "DIB". I've tried
stripping the data in many ways but nothing on my machine is able to open
these.

Does anyone has any experience with this format? Any pointers are greatly
appreciated. Otherwise I'm afraid I'm facing migration of several thousands
of images by copy/pasting from access :/

Thank you in advance,
Alan.

 

[Guest]

Hi

What if one needs a Word doc out of an access db ole container. How many
bytes is that? I heard 85, but it is not worknig for me. Any ideas here?

Thank you

Yes, you can write code to strip off the header and just get the image
bytes from the OLE fields. This code assumes that you want to load the
photo from Northwind into a PictureBox control, which I know you don't
want to do, but this is the code I happen to have lying around from
some appdev courseware that I wrote a few years back. It should be
enough to get you started:

private void LoadEmployeePhoto(int employeeID)
{
SqlDataAdapter da = null;
DataTable dt = new DataTable();
string strCnn =
"Data Source=(local);Database=Northwind;"
+ "Integrated Security=SSPI";
string strSQL =
"SELECT Photo FROM dbo.Employees"
+ " WHERE EmployeeID = "
+ employeeID.ToString();
MemoryStream msPic;
// Signature bytes of an
// OLE container header.
const byte OLEbyte0 = 21;
const byte OLEbyte1 = 28;
// Number of bytes in
// an OLE container header.
const int OLEheaderLength = 78;

da = new SqlDataAdapter(strSQL, strCnn);
da.Fill(dt);
if (dt.Rows.Count == 0)
return;

// Move binary picture data into the byte array
byte[] abytPic = (byte[])dt.Rows[0]["Photo"];

// Test for an OLE container header
if ((abytPic[0] == OLEbyte0)
&& (abytPic[1] == OLEbyte1))
{
// Use a second array to strip off the header.
// Make it big enough to hold
// the bytes after the header.
byte[] abytStripped =
new byte[abytPic.Length - OLEheaderLength];
// Strip off the header by copying the bytes
// after the header.
System.Buffer.BlockCopy(
abytPic, OLEheaderLength, abytStripped,
0, abytPic.Length - OLEheaderLength);

// Load the new byte array
// into a MemoryStream.
msPic = new MemoryStream(abytStripped);
}
else
{
// Load the original byte array into a MemoryStream
msPic = new MemoryStream(abytPic);
}
// Set the picture box image, using the stream.
picEmployee.Image = Image.FromStream(msPic);
}

--Mary

Hi,

I'm converting a database application from Access 97 to C#/SQL Server. Old
database contains some images in OLE fields. I've figured out that there's
OLE header preceeding actual image data and dealt with some of the images
which were in standard BMP format but most of the images are in some other
format which is displayed ok in access application and can be copy/pasted to
any image editor but when doubleclicking it doesn't open any application
(like BMP images open Paint, etc.) and I can't figure out how to load it to
.NET image control. The field data for these images starts with some headers
that include two identifiable words "Picture" and "DIB". I've tried
stripping the data in many ways but nothing on my machine is able to open
these.

Does anyone has any experience with this format? Any pointers are greatly
appreciated. Otherwise I'm afraid I'm facing migration of several thousands
of images by copy/pasting from access :/

Thank you in advance,
Alan.