vsshah
asked on
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
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(":
addKBImg.Parameters.Add(":
addKBImg.Parameters.Add(":
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!
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
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(":
addKBImg.Parameters.Add(":
addKBImg.Parameters.Add(":
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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!
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!
ASKER
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...