Store image in Oracle 8i using VB .NET as front-end

Hi Experts!

I know this question may have been asked before but I can't seem to find anything with real value for the application I need.

Here's the situation:
1. I need to store an image in an Oracle 8i table (not a pointer to a picture file--I know it will make the table size large, but my supervisor WANTS the image stored in the table for easier retrieval, etc.)

2. I've got some code from the MSDN site, but it strictly deals with SQLServer and not Oracle. But, with the code I am able to write to the table.

Here's the code that I have so far:
Imports System
Imports System.Data
Imports System.Data.OracleClient
Imports System.IO

Public Class frmMain
    Inherits System.Windows.Forms.Form

Public Shared Sub cmdUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpload.Click
        Dim photo() As Byte = GetPhoto("m:\kanban\001748-01.jpg")

        Dim oracleConn As OracleConnection = New OracleConnection("user id=IFSAPP;data source=tst2;password=***")
        Dim addKBImg As OracleCommand = New OracleCommand("INSERT INTO UTC_KANBAN_IMG (KB_IMG, KB_PART_NUM, KB_IMG_PATH) " & "Values(:KBImg, :KBPartNum, :KBImgPath)", oracleConn)

        addKBImg.Parameters.Add(":KBImg", OracleType.Blob, photo.Length).Value = photo
        addKBImg.Parameters.Add(":KBPartNum", OracleType.VarChar, 25).Value = "001748-01"
        addKBImg.Parameters.Add(":KBImgPath", OracleType.VarChar, 150).Value = "M:\Kanban\"
        addKBImg.Connection.Open()
        addKBImg.ExecuteNonQuery()
        oracleConn.Close()
        MsgBox("Done")
    End Sub

    Public Shared Function GetPhoto(ByVal filePath As String) As Byte()
        Dim fs As FileStream = New FileStream(filePath, FileMode.Open, FileAccess.Read)
        Dim br As BinaryReader = New BinaryReader(fs)
        Dim photo() As Byte = br.ReadBytes(fs.Length)

        br.Close()
        fs.Close()

        Return photo
    End Function
End Class

What I use PL/SQL to query the table, I get the following values:
KB_IMG: <BLOB>
KB_PART_NUM: 001748-01
KB_IMG_PATH: M:\Kanban\

The above fields are setup in Oracle 8i as:
KB_IMG: BLOB
KB_PART_NUM: VARCHAR2(25)
KB_IMG_PATH: VARCHAR2(150)

When I use Crystal Reports (which we use to create the report that has a picture of the part) and import the Oracle table, we can see the KB_PART_NUM and KB_IMG_PATH with no problems (they're just strings anyway), but when Crystal 8.5 tries to read the image file, I get the error: ORA-00932: inconsistent datatypes

Can an expert please help me get through this problem. I've looked on the net, but can't find anything of substance that I can use and unfortunately this project needs to be completed ASAP. Please provide code samples.

I appreciate your efforts!

Also, I'm use VB .NET 2003, WinXP SP-2, Oracle 8i, Crystal Reports 8.5...What I'm ultimately trying to accomplish is to read the contents of a directory for all the jpeg files, loop through and add each image to the Oracle table, and then in Crystal create a report for each part. If you can also help with the reading of the directory to loop through each of the jpeg files, that would be greatly appreciated!
vsshahAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jacobhooverCommented:
Vsshah,
  you are going down the right path as far as I can see, however there is one slight flaw in your logic.  Crystal reports is NOT going to be able to read the blob back intrinsically.  You will probably have to write a wrapper class that retreives the blob data and either writes it back to disk as a temp file (the ugly way) or to generate the picture file in memory.

Jake
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vsshahAuthor Commented:
Hi Jake,

Thanks for your comments. Any ideas on how to write the wrapper class or generate the pic in memory? Or, is there some third party software you know of that would help out?

Thanks...
0
jacobhooverCommented:
How are you setting the picture property?  As you may have to cache the images to disk before you can set them.  I'm not 100% certain if the Crystal Reports "Picture/Image" control would take an in memory object.
0
vsshahAuthor Commented:
I'm not really setting any picture property, just reading it from a folder on the network and trying to filestream it to a database record in Oracle--so I guess I'm not really using any temporary cache to hold the pictures. I'm not sure about Crystal being able to read the BLOB datatype either.

I've tried just creating a quick db in Access to read the Oracle file and see if I can see the picture on a form, but had no luck there--I was able to read the other fields with no problem, but get a blank when I try to see the picture file; although, Access did seem to import the correct datatype for it--as an OLE object. I tried, while in Access, to set the class of the OLE object to "MSPhotoEd.3", but still no luck. (I previously had created the same database I'm trying to replicate with VB.NET/Oracle in Access, and haven't had a big problem with it, except it is extremely slow to access all the pictures over our network, even though we're set to 100Mbps. So, what we have right now is a hybrid Access/Oracle/Crystal Solution that works for the most part, except when we start to introduce our Citrix users to access the Crystal Report that's based on BOTH Oracle and Access. It's a big mess for the most part! We think Access is the main culprit here, so that's why we're trodding down the path of a strictly Oracle db (which is where all of our manufacturing/engineering data is stored) and a VB .NET frontend for the user to enter the picture and its associated data.

I did read something about Oracle's interMedia module that our DBA needs to possibly install, so I'll be checking on that too with him. It seems this interMedia module allows you to specify the datatype of a column as an image; so, our problem may be solved right there--assuming Crystal can then read that datatype correctly too! :)  But, we're not opposed to using VB .NET as a way to create/print a form as long as it can display the picture contained within Oracle.

Thanks for your help Jake!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.