Click here to Skip to main content
13,045,379 members (117,825 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as


23 bookmarked
Posted 30 Jun 2010

Saving OLE Object as Image Datatype in SQL

, 8 Sep 2014
Rate this:
Please Sign up or sign in to vote.
This article shows how to extract OLE pictures with specific formats like BMP, JPEG, PDF or Microsoft WORD from Access, convert and save them in SQL


Recently, I got a project related to the OLE images conversion from Access to SQL Server. As the current system uses image as datatype and there is no way to make any change for its datatype (e.g. varbinary(max)), I have no problem in reading the array of bytes from the OLE field where the image is stored as an OLE object. But this array of bytes that I read is not the actual image due to an OLE Object wrapping header added by Access. This header seems to be of variable length and further more since the definition of OLE object storage is not documented (contains some header information before actual data), simply extracting the field contents as a byte array using GetChunk method and saving it to database does not work.

There’re a few articles discussing this problem on the internet, but I could not find a convincing solution, which reads an OLE Object from Access table column, saves it in database in such a way that the original OLE Object data can be retrieved from this without any data loss (see this link).

This article shows by steps how to extract OLE pictures with specific formats like BMP, JPEG, PDF or Microsoft WORD from the Microsoft Access MDB database, convert and save them in SQL Server database. An OLE to Image class OLE2IMG that strips the OLE headers from the OLE field is introduced.

OLE Header

Any OLE Object file has some standard signature, e.g., BMP image has signature as "42 4D" & files with extensions DOC, DOT, PPS, PPT, XLA, XLS, WIZ Microsoft Office applications (Word, Excel, Powerpoint) will have signature as " D0 CF 11 E0 A1 B1 1A E1". This article will discuss the following 4 formats:

  • 0x424D---BMP (The ole header has a fixed length 78)
  • 0xFFD8---JPEG (Variable header length)
  • 0x2550---PDF (Variable header length)
  • 0xD0CF---MSWORD 1997/2003 (Variable header length)

It’s clear that all we have to do is to read the OLE Object data into a byte array. This byte array will be compared for the above file extensions and whenever a match is found, the corresponding byte offset will be saved. All the data before this byte offset will be stripped (the OLE Object Header part of byte array) and the data starting from this offset will be stored as the actual data. In order to accomplish this, I will create two tables in SQL Server database in the next step, one table is for temporarily storing the OLE Object imported from Access, another one works as the destination table which stores actual data, data length and its type.

Create Tables in SQL Server

The sample is demonstrated in SQL Server 2008, but it’s also applicable to SQL 2000 and 2005. Now open the Microsoft SQL Server 2008 Management Studio, create two tables (example, 9K_OLEIMG and 9K_RAWIMG) in the current database. The OLE table 9K_OLEIMG has an identity column ImgId and an image column OleData that will store the OLE data imported from MDB source, while the destination table 9K_RAWIMG has four columns ImgId, ImgLen, ImgType and ImgData. The data structure of the two tables is shown in Figure 1.

Figure 1

In order to import the source ole data, just place your source picture MDB file under your project folder. For example, D:\YourProject\Pictures.mdb. Suppose the picture MDB contains a table tblPic with a column PicId and an OLE column ColPic.

Make sure the “OPENROWSET and OPENDATASOURCE” support is enabled. Go to SQL Server Surface Area Configuration->Surface Area Configuration for Features, check the checkbox as shown in Figure 2.

Figure 2

Next, create a new query window for the given database, run the script below:

Insert into dbo.[9K_OLEIMG](OleData)
select ColPic from OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'select ColPic from [tblPic] Order by PicId')

The OLE data from the source MDB should be inserted into the table 9K_OLEIMG if no error messages are generated while running the script.

Convert and Save OLE Object

The snippet below includes an OLE to Image conversion class OLE2IMG which retrieves an OLE image from the source table 9K_OLEIMG, then converts to raw image datatype and saves the image, type and length in the destination table 9K_RAWIMG.

using System;
using System.Text;
using System.IO;
using System.Data.OleDb;
namespace NINEK
    /// <summary> 
    ///Date: Jun-30-2010
    ///Author: esupersun
    ///Powered by:
    ///The method Ole2Img accepts an integer Image ID and a string Connection
    ///String as Input parameters, while returning a boolean result indicating whether
    ///the conversion successes or fails.
    ///SQL Server Database Connection String example:
    ///connStr = "Provider=SQLOLEDB;Data Source=APPHEX.COM\\SQL2008;
    ///Initial Catalog=APPHEX;Integrated Security=SSPI;";
    /// <summary> 
    class OLE2IMG    {
        public OLE2IMG()
        public bool Ole2Img(string _connStr, int _imgId)
            short sFlag = 0;
            int nIndex = 0;
            int nCount = 0;  
            int nOffset = 0;
            int nImgLen = 0;
            int nReadbyte = 14400;
            string szImgType = string.Empty;
                OleDbConnection conn = new OleDbConnection(_connStr);
                String strQuery = String.Format
                                ("SELECT OleData FROM [9K_OLEIMG] WHERE ImgId =" + _imgId);
                OleDbCommand cmdQuery = new OleDbCommand(strQuery, conn);
                if (Convert.DBNull != cmdQuery.ExecuteScalar())
                    byte[] bData = (byte[])cmdQuery.ExecuteScalar();
                    if (bData.Length > 0)
                        MemoryStream memStream = new MemoryStream(bData, true);
                        byte[] bArray = new byte[nReadbyte];
                        nCount = memStream.Read(bArray, 0, nReadbyte);
                        if (bArray[78] == (byte)0x42 &&
                            bArray[79] == (byte)0x4D) //BMP FORMAT
                            sFlag = 1;
                            nOffset = 78;                            
                            szImgType = "image/bmp";
                            for (nIndex = 78; nIndex < nReadbyte - 2; nIndex++)
                                if (bArray[nIndex] == (byte)0xFF &&
                                    bArray[nIndex + 1] == (byte)0xD8) //JPG FORMAT
                                    sFlag = 2;
                                    nOffset = nIndex;                                    
                                    szImgType = "image/pjpeg";
                                else if (bArray[nIndex] == (byte)0x25 &&
                                         bArray[nIndex + 1] == (byte)0x50) //PDF FORMAT
                                    sFlag = 3;
                                    nOffset = nIndex;                                    
                                    szImgType = "application/pdf";
                                else if (bArray[nIndex] == (byte)0xD0 && 
                                         bArray[nIndex + 1] == (byte)0xCF) //MSWORD FORMAT
                                    sFlag = 4;
                                    nOffset = nIndex;                                    
                                    szImgType = "application/msword";
                        if (sFlag > 0)
                            nImgLen = bData.Length - nOffset;
                            memStream.Position = 0;
                            memStream.Write(bData, nOffset, nImgLen);
                            memStream.Position = 0;
                            byte[] bImgData = new byte[nImgLen];
                            nCount = memStream.Read(bImgData, 0, nImgLen);
                            String strAdd = String.Format("Insert into [9K_RAWIMG](ImgId, ImgLen,
                                                           ImgType, ImgData) values(" + _imgId + ","
                                                           + nImgLen + ", '" + szImgType + "',?) ");
                            OleDbCommand cmdAdd = new OleDbCommand(strAdd, conn);
                            OleDbParameter paraImg = new OleDbParameter
                                                     ("@Image", OleDbType.LongVarBinary, nImgLen);
                            paraImg.Value = bImgData;
            catch (Exception ex)
                //Error handle here.
                return false;
            return true;


This class can save OLE object for a bitmap, JPEG, Word doc 1997/2003 and PDF without any data loss but doesn’t apply to all circumstances. The source OLE Object should be of one of the formats below:

  • BMP
  • JPEG
  • PDF
  • MSWORD 1997/2003


  • 30th June, 2010: Initial post
  • 2nd July, 2010: Updated OLE2IMG.cs code


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Canada Canada
Fred Yang is a mentor for aspiring programmers and artists who are interested in breaking into the game industry.

You may also be interested in...

Comments and Discussions

QuestionThanks! Pin
Theotronix22-Nov-14 13:30
memberTheotronix22-Nov-14 13:30 
GeneralMy vote of 5 Pin
manoj kumar choubey9-Feb-12 22:07
membermanoj kumar choubey9-Feb-12 22:07 
GeneralMy vote of 5 Pin
stellagoodwyn13-Jul-10 5:04
memberstellagoodwyn13-Jul-10 5:04 
QuestionSQLCE question? Pin
MrFrle11-Jul-10 9:57
memberMrFrle11-Jul-10 9:57 
QuestionAny footer data added by access? Pin
YoungProg7-Jul-10 6:39
memberYoungProg7-Jul-10 6:39 
AnswerRe: Any footer data added by access? Pin
esupersun7-Jul-10 9:01
memberesupersun7-Jul-10 9:01 
GeneralMy vote of 5 Pin
heunguy865-Jul-10 4:56
memberheunguy865-Jul-10 4:56 
GeneralWOW! Just what i need, any ideas with TIF? thx. Pin
heunguy865-Jul-10 4:53
memberheunguy865-Jul-10 4:53 
GeneralRe: WOW! Just what i need, any ideas with TIF? thx. Pin
esupersun7-Jul-10 4:03
memberesupersun7-Jul-10 4:03 
QuestionDatatype image versus varbinary(max); OleDbConnection versus SqlConnection; other stuff Pin
haindl2-Jul-10 5:12
memberhaindl2-Jul-10 5:12 
AnswerRe: Datatype image versus varbinary(max); OleDbConnection versus SqlConnection; other stuff [modified] Pin
esupersun2-Jul-10 9:53
memberesupersun2-Jul-10 9:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170713.1 | Last Updated 8 Sep 2014
Article Copyright 2010 by esupersun
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid