Solved

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

Posted on 2004-10-22
641 Views
Last Modified: 2009-12-16
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!
0
Question by:vsshah
    4 Comments
     
    LVL 7

    Accepted Solution

    by:
    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
     

    Author Comment

    by:vsshah
    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
     
    LVL 7

    Expert Comment

    by:jacobhoover
    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
     

    Author Comment

    by:vsshah
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Product Review - Android Remix

    Come along for the ride with our Senior Product Manager, Brian Matis, as he reviews the Android Remix.

    Suggested Solutions

    Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
    Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now