Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

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

Introduction

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',
'D:\YourProject\Pictures.mdb';'admin';'',
'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: apphex.com
    ///Blog: http://esupersun.blogspot.com
    ///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;
            
            try
            {
                OleDbConnection conn = new OleDbConnection(_connStr);
                conn.Open();
                
                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";
                        }
                        else
                        {
                            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";
                                    break;
                                }
                                else if (bArray[nIndex] == (byte)0x25 &&
                                         bArray[nIndex + 1] == (byte)0x50) //PDF FORMAT
                                {
                                    sFlag = 3;
                                    nOffset = nIndex;                                    
                                    szImgType = "application/pdf";
                                    break;
                                }
                                else if (bArray[nIndex] == (byte)0xD0 && 
                                         bArray[nIndex + 1] == (byte)0xCF) //MSWORD FORMAT
                                {
                                    sFlag = 4;
                                    nOffset = nIndex;                                    
                                    szImgType = "application/msword";
                                    break;
                                }
                            }
                        }
                        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;
                            cmdAdd.Parameters.Add(paraImg);
                            cmdAdd.ExecuteNonQuery();
                        }
                        memStream.Close();
                    }
                }
                
                conn.Close();
                
            }
            catch (Exception ex)
            {
                //Error handle here.
                //BLL.ErrorManager.LogException(ex.Message);
                return false;
            }
            return true;
        }
    }
}

Conclusion

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

History

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

License

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

Share

About the Author

esupersun
Team Leader MICBOTS
Canada Canada
Fred Yang is the lead developer at Micbots (micbots.com), a Vancouver based game studio that produces fun and attractive mobile games on market for all to enjoy. So far the released titles include Sokoban Deluxe, Lost Warrior, Crocodile Island, Ozman Quest, Temple of Arceus, and Nezeh Series.
As an active member of the game industry, he has contributed numerous brilliant articles to Apphex Forums (apphex.com), a top mobile game developers forum, covering comprehensive technical discussions and cutting-edge programming tutorials on Android/iOS game development.
Fred holds a Ph.D. in Applied Mathematics and is also an inspiring mentor for aspiring programmers and artists trying to tap into the game industry.
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermanoj kumar choubey9-Feb-12 22:07 
GeneralMy vote of 5 Pinmemberstellagoodwyn13-Jul-10 5:04 
QuestionSQLCE question? PinmemberMrFrle11-Jul-10 9:57 
QuestionAny footer data added by access? PinmemberYoungProg7-Jul-10 6:39 
AnswerRe: Any footer data added by access? Pinmemberesupersun7-Jul-10 9:01 
GeneralMy vote of 5 Pinmemberheunguy865-Jul-10 4:56 
GeneralWOW! Just what i need, any ideas with TIF? thx. Pinmemberheunguy865-Jul-10 4:53 
GeneralRe: WOW! Just what i need, any ideas with TIF? thx. Pinmemberesupersun7-Jul-10 4:03 
QuestionDatatype image versus varbinary(max); OleDbConnection versus SqlConnection; other stuff Pinmemberhaindl2-Jul-10 5:12 
AnswerRe: Datatype image versus varbinary(max); OleDbConnection versus SqlConnection; other stuff [modified] Pinmemberesupersun2-Jul-10 9:53 

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

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

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