Problem retrieving images from Access 2003 Binary Large Object (BL

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I can write and read jpeg images to/from MS Access table BLOB fields using
the ReadBlob and Write Blob functions recommended in
http://support.microsoft.com/?kbid=210486, and the retrieved image can be
displayed in an MS access form image field.

If I connect to the Access database from an ASP page and read the BLOB field
using ADO.NET functions the returned file is twice the size of the file
returned when reading the BLOB field using MS Access, and it cannot be
displayed.

The code for reading the BLOB field is listed below and copied from
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502 .

Can you suggest what may be causing this problem.



Comparing the contents of the two jpeg files returned it appears that each
byte in the original file is converted to two bytes when stored in the MS
access table. If the data is read back using the MS access WriteBLOB function
then each pair of bytes is correctly reconverted back to one byte with the
original value.

When the BLOB is read from the ASP page this does not happen. I tried
removing the most signifant byte of each pair of bytes and creating a file of
the original size but the picture could still not be displayed.

Further examination of the file contents showed that the most of the least
significant bytes of each pair contained the original value but occasionally
they did not and there is a value in the most significant byte.

<%@ Page Language="VB" Debug="true" ContentType="text/html"
ResponseEncoding="iso-8859-1" %>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.IO" %>
<%
Dim PictureCol As Integer = 0 ' the column # of the BLOB field

Dim cn As New OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inetpub\wwwroot\PaulsWebASPNet\LakeDistrictFells2000.mdb")

Dim cmd As New OleDbCommand("SELECT photo FROM tblPhoto WHERE
PhotoNumber=2", cn)

cn.Open()

Dim dr As OleDbDataReader = cmd.ExecuteReader()
dr.Read()

Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(PictureCol, 0, b, 0, b.Length)
dr.Close()
cn.Close()

‘***** start of temporary code ******
Dim fs As New
System.IO.FileStream("C:\Inetpub\wwwroot\paulsWebaspNet\test2.jpg",
IO.FileMode.Create, IO.FileAccess.Write)
fs.Write(b, 0, b.Length)
fs.Close()
‘***** end of temporary code ******

'Response.Expires = 0
'Response.Buffer = TRUE
'Response.Clear
'Response.ContentType = "image/jpg"
'Response.BinaryWrite(b)
'Response.End

%>
 
Hi, Paul.
Can you suggest what may be causing this problem.
Click to expand...

Regardless of the graphic file format that the image was originally in, Jet
stores the graphic as a bitmap image. Jet can compensate for this within the
Access environment (which is why your conversion works within Access), but
your ASP.Net function is retrieving the stored BMP image, not a JPG file.

Access experts recommend storing graphic files outside of Access, not in a
table, to avoid excessive database bloat and frequent database corruption
problems. This technique merely stores the path and file name or hyperlink
in the table, and stores the actual graphic files on the file server. I
recommend that you do the same for your ASP.Net application.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
 
Regardless of the graphic file format that the image was originally in, Jet
stores the graphic as a bitmap image.
Click to expand...

Not so - you are thinking of OLE Embedding, a purely front-end technology, which is not the case here.
The OLE Object field is simply a binary field that can be used to store any binary data without any overhead
or conversion - Jet/Access do not convert formats that you insert into it.

Provided you avoid OLE Embedding and its associated problems it is perfectly possible, and reliable, to
work with reasonable volumes of image data in tables, and the images occupy no more space than they would
in the file-system.
 
ReadBlob/WriteBlob use string functions, which causes the doubling of size. Eliminate the string functions
and you will avoid the conversion & extra storage and will be able to work with the data directly in ASP
as you require.

--
_______________________________________________________
http://www.ammara.com/
Image Handling Components, Samples, Solutions and Info
DBPix 2.0 - lossless jpeg rotation, EXIF, asynchronous
 
No. You've only fixed part of the problem. And if you store a good number
of images in Jet tables, you'll eventually discover a much, much larger
problem and why Access experts advise against doing so.

Good luck.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
 
No. You've only fixed part of the problem.
Click to expand...

So what's the other part ?
And if you store a good number
of images in Jet tables, you'll eventually discover a much, much larger
problem and why Access experts advise against doing so.
Click to expand...

The advice against doing so is usually due to the overhead of OLE embedding, reliance on OLE Servers, interop
problems etc. etc. None of these apply to raw binary storage.

I've worked on hundreds of applications that store millions of images in Access tables without any problems
whatsoever. Provided that one doesn't use OLE Embedding, and obviously don't try to store more than 2GB
of image data.
 
Did you ever find it very slow to insert data to access? say a minute per 10
mb?
I'm doing
cmd.Parameters.Add("@ImgBinary", OleDbType.VarBinary).Value =
saveStream.ToArray();
so it's binary array.
 
Never, but I've never done it this way.

=?Utf-8?B?SmFzb24gQ2h1?= said:
Did you ever find it very slow to insert data to access? say a minute per 10
mb?
I'm doing
cmd.Parameters.Add("@ImgBinary", OleDbType.VarBinary).Value =
saveStream.ToArray();
so it's binary array.
Click to expand...
 
Hi Paul!

Would you please send me the modified functions for reading and writing in
order to store the JPG files in my database without the overhead?

Thanks in advance for your help,

Julian
(e-mail address removed)
 
See:
http://www.lebans.com/loadsavejpeg.htm
Version 1.9 Updated April 23, 2004

A97LoadSaveJpeg.zip contains a database demonstrating how to use the Intel
Jpeg library with Access. Jpeg files are stored in their original compressed
state within a Binary OLE avoiding the Ole object "bloat" issue. Here is the
A2K version: A2KLoadSaveJpeg.zip

A standard Image control is used to display the contents of the field
containing the Jpeg file. The Intel DLL is used to load the contents of the
field directly into memory bypassing the need to create a temporary disk
file.

The contents of the Image control, containing any supported source Image
format, can be saved to disk as a Jpeg file.

Very large Jpeg files that could not be loaded into the standard Access
Image control directly can now be loaded through the Intel Jpeg library.

Version 1.9

Fixed Sample Report. Added A2K version.

Version 1.2

Fixed File Dialogs. Added control to resize loaded Jpegs. Added sample
Report. Starting to clean up the code!

Version 1.1

Any Image file that can be loaded into the standard Image control can now be
saved to a disk Jpeg file. Version 1.0 was restricted to Bitmap files.

Version 1.0

Initial release.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Back
Top