views:

922

answers:

4

I have a table in an access table which contains Product entries, one of the columns has a jpg image stored as an OLE Object. I am trying to import this table to MySQL but nothing seems to work. I have tried the MySQL migration tool but that has a known issue with Access and OLE Objects. (The issue being it doesnt work and leaves the fields blank) I also tried the suggestion on this site and while the data is imported it seems as though the image is getting corrupted in the transfer. When i try to preview the image i just get a binary view, if i save it on disk as a jpg image and try to open it i get an error stating the image is corrupt.

The images in Access are fine and can be previewed. Access is storing the data as an OLE Object and when i import it to MySql it is saved in a MediumBlob field.

Has anyone had this issue before and how did they resolve it ?

A: 

As far as I remember, the Microsoft "SQL Server Migration Assistant for Access" will properly migrate OLE Images, but this is only for Access->SQLServer. However, what you can do is use this to migrate to SQLServer Express (free download) and then migrate from SQLServer to MySQL.

DAC
+1  A: 

Ok so in the interests of airing my dirty code in public here what i came up with.
Note : this is a hack designed to be used once and then thrown away.

This Method takes in a datarowview containing 1 row of data from the access table. The Images are wrapped in OLE serialization, im not entirely familiar with how this works but its how Microsoft apps allow any object to be embedded into something else. (eg images into Excel Cells). I needed to remove the serialization junk around the image so i loaded the entire field as a Byte array and searched through it for 3 concurrent entries (FF D8 FF) which represent the beginning of the image data within the field.

    Private Function GetImageFromRow(ByRef row As DataRowView, ByVal columnName As String) As Bitmap
    Dim oImage As Bitmap = New Bitmap("c:\default.jpg")
    Try
        If Not IsDBNull(row(columnName)) Then
            If row(columnName) IsNot Nothing Then
                Dim mStream As New System.IO.MemoryStream(CType(row(columnName), Byte()))
                If mStream.Length > 0 Then

                    Dim b(Convert.ToInt32(mStream.Length - 1)) As Byte
                    mStream.Read(b, 0, Convert.ToInt32(mStream.Length - 1))

                    Dim position As Integer = 0

                    For index As Integer = 0 To b.Length - 3
                        If b(index) = &HFF And b(index + 1) = &HD8 And b(index + 2) = &HFF Then
                            position = index
                            Exit For
                        End If
                    Next

                    If position > 0 Then
                        Dim jpgStream As New System.IO.MemoryStream(b, position, b.Length - position)
                        oImage = New Bitmap(jpgStream)
                    End If
                End If
            End If
        End If
    Catch ex As Exception
        Throw New ApplicationException(ex.Message, ex)
    End Try
    Return oImage
End Function

Then its a matter of pulling out this data into a bitmap. So for each row in the access table i extract the bitmap and then update the corresponding MySQL entry.
It worked fine but im guessing i could have removed the serialisation stuff in a better way, perhaps theres an API to do it.

Kaius
A: 

You can write an aplication to extract data from Access using free .NET library Intasphere (http://www.intasphere.ru/en/oleextract.php). It has function to extract different type of data from MS Access OLE fields.

Sergey
A: 

I use a tool to migrate database called dbload

shumaker
How would dbload (http://www.dbload.com/) be better than SSMA?
David-W-Fenton