top mobile game developers forum
Mobile Game Developer Forums

Saving OLE Object as Image Datatype in SQL

Saving OLE Object as Image Datatype in SQL
cstcoder
Posted : Thu, 13 Apr 2017 22:35:57 - [#1]


Group:  Moderator  
Rank:   Bronze Member
Joined:  6/29/2016
Posts:   23
Rubies:  993 
Location:  Vancouver
By cstcoder 2017-04-13

Introduction
Recently I worked on a project which needs to save OLE object as image datatype in SQL. I have no problem in reading the array of bytes from the OLE field where the image 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 the 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 the link http://support.microsoft.com/kb/175261)
This article shows by steps how to extract OLE pictures with specific formats like BMP, JPEG, PDF or MS 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. eg. BMP image has signature as "42 4D" & files with extensions DOC, DOT, PPS, PPT, XLA, XLS, WIZ MS 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:
o 0x424D---BMP (The ole header has a fixed length 78)
o 0xFFD8–-JPEG (Variable header length)
o 0x2550---PDF (Variable header length)
o 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 above file extensions and whenever the 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 (ex. 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:
Code:
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 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.
Code:
///////////////////
///OLE2IMG.cs///
///////////////////
using System;
using System.Text;
using System.IO;
using System.Data.OleDb;

namespace CSTCODE
{
    /// <summary>
    ///The method Ole2Img accepts an integer Image ID and a string Connection
    ///String as Input parameters, while returning a boolean result indicating whether
    ///the convertion 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:
o BMP
o JPEG
o PDF
o MSWORD 1997/2003

You never know till you have tried
Zambie
Posted : Fri, 14 Apr 2017 09:54:59 - [#2]


Rank:    New Member
Joined:  11/5/2016
Posts:   19
Rubies:  30 
Really helpful!!!
siley
Posted : Fri, 11 Aug 2017 19:26:31 - [#3]


Rank:    New Member
Joined:  8/24/2016
Posts:   13
Rubies:  17 
Location:  Toronto
Boo hoo! Angel Bookmarked. +1 :D :X :PApplause


Forum Jump
You may not post new threads in this forum.
You may not reply to threads in this forum.
You may not delete your posts in this forum.
You may not edit your posts in this forum.
You may not create polls in this forum.
You may not vote in polls in this forum.
Main Forum RSS : RSS
This page was generated in 0.736 seconds
Home | Top Mobile Games | Developer Resource | Forum Rules | Sitemap | Downloads | Contact Us | About Us
Copyright ©2017 - Apphex Forums  Powered by: cstcode v1.0.5