Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-22
4
Medium Priority
?
682 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
Comment
Question by:vsshah
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 7

Accepted Solution

by:
jacobhoover earned 1000 total points
ID: 12385658
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
ID: 12399783
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
ID: 12403982
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
ID: 12410207
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

618 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