Store image in Oracle 8i using VB .NET as front-end
Posted on 2004-10-22
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:
Public Class frmMain
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\"
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)
What I use PL/SQL to query the table, I get the following values:
The above fields are setup in Oracle 8i as:
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!