how to store the images using VB COM dll into Oracle database ...let mwknow

how to store Jpeg Images into oracle database using VB COM DLL or ASP script ..
thsi is urgent please reply me with Step by step process..
sekhar_vaAsked:
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.

mdouganCommented:
You should be able to use the following program to do this.  Your Oracle database has to define the Photo field as a BLOB, and you'll need to set the proper Oracle Connect String in the Declarations Section.  I've only got samples of SQL Server and Access there currently.  Obviously, change the names of the tables, and the SQL in the queries to match your DB, but the basic syntax should work.

'***************************************************************************************
' Project references the Microsoft Active Data Objects Library v 2.5
' Project component references the Common Dialog Control (named cdlg)
'
' 1 Form - frmMain
' 2 Lables and Textboxes - lblFirstName, txtFirstName, lblLastName, txtLastName
' 1 Picturebox - picPhoto
' 6 Command Buttons - cmdFind, cmdFirst, cmdPrevious, cmdNext, cmdLast, cmdUpdateImage
'
'***************************************************************************************
'***************************************************************************************

' ADO Objects
Private Conn As ADODB.Connection
Private RS As ADODB.Recordset

' Saves the project path for reading/writing temp files
Private sPath As String

' A couple of sample Connect Strings
Const SQL_SERVER_DATABASE = "Provider=SQLOLEDB;Data Source=MYSERVER; Initial Catalog=MYDATABASE; User ID=MYUSERID;Password=MYPASSWORD;"
Const ACCESS_DATABASE = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=H:\Code\ImageDB\nwind.mdb"

Private Sub cmdFind_Click()
Dim sCriteria As String
Dim vSavePlace As Variant

' Save the currently active record
   vSavePlace = RS.Bookmark
   
' Build the Search Criteria
   If txtFirstName.Text <> "" Then
       sCriteria = "FIRSTNAME LIKE '" & txtFirstName.Text & "%'"
   End If
   
   If txtLastName.Text <> "" Then
       If Len(sCriteria) = 0 Then
           sCriteria = "LASTNAME LIKE '" & txtLastName.Text & "%'"
       Else
           sCriteria = sCriteria & " AND LASTNAME LIKE '" & txtLastName.Text & "%'"
       End If
   End If
   
' Search for the record starting from the beginning of the recordset
   RS.Find sCriteria, , adSearchForward, 1
' If not found then reposition on the previously active record
   If RS.BOF Or RS.EOF Then
       MsgBox "Record Not Found"
       RS.Bookmark = vSavePlace
   End If
   
' Now, display the record
   DisplayRecord
   
End Sub

Private Sub cmdFirst_Click()
' Move to the last record.
   RS.MoveFirst
' Now, display the record
   DisplayRecord
End Sub

Private Sub cmdLast_Click()
' Move to the last record.
   RS.MoveLast
' Now, display the record
   DisplayRecord
End Sub

Private Sub cmdNext_Click()
' Move to the next record.  If you moved past the end of the recordset then reposition
   RS.MoveNext
   If RS.EOF Then
       RS.MoveLast
   End If
' Now, display the record
   DisplayRecord
End Sub

Private Sub cmdPrevious_Click()
' Move to the previoius record.  If you moved past the start of the recordset then reposition
   RS.MovePrevious
   If RS.BOF Then
       RS.MoveFirst
   End If
' Now, display the record
   DisplayRecord
End Sub

Private Sub cmdUpdateImage_Click()
   
' Prep the common dialog control
   cdlg.DialogTitle = "Update Image"
   cdlg.Filter = "JPG Files *.jpg|*.jpg|GIF Files *.gif|*.gif|BMP Files *.bmp|*.bmp|All Files *.*|*.*"
   cdlg.ShowOpen
   If cdlg.FileName <> "" Then
' If a file was choosen then try to update the record
       AddImage RS, cdlg.FileName
       RS.Update
' Now, redisplay the current record with the updated image
       DisplayRecord
   End If
 
End Sub

Private Sub AddImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Tell it the source of the stream
oStream.LoadFromFile FileName

'Load the binary object into the field value
RS.Fields("Photo").Value = oStream.Read

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub

Private Sub ReadImage(RS As ADODB.Recordset, ByVal FileName As String)
' Declare a stream object
Dim oStream As ADODB.Stream

' Create a new instance
Set oStream = New ADODB.Stream
oStream.Type = adTypeBinary
oStream.Open

' Read the field value into the stream object
oStream.Write RS.Fields("Photo").Value

' Save out to a local file
oStream.SaveToFile FileName, adSaveCreateOverWrite

' Close the stream and clean house
oStream.Close
Set oStream = Nothing

End Sub

Private Sub DisplayRecord()
On Error Resume Next

' Load the textboxes on the screen from the recordset
   txtFirstName.Text = RS("FIRSTNAME").Value
   txtLastName.Text = RS("LASTNAME").Value

' Save the Photo to a local file
   ReadImage RS, sPath & "temp.dat"

' Load the local file into a picturebox
   picPhoto.Picture = LoadPicture()
   picPhoto.Picture = LoadPicture(sPath & "temp.dat")
   

End Sub

Private Sub Form_Load()
' Open your database connection
Set Conn = New ADODB.Connection
With Conn
'    .ConnectionString = SQL_SERVER_DATABASE
   .ConnectionString = ACCESS_DATABASE
   .CursorLocation = adUseClient
   .Open
End With

' Always check to make sure the Connection is valid
If Conn.State <> adStateOpen Then
   MsgBox "Failed to Open Connection", vbExclamation, "Open Connection"
   End
End If

' Open your recordset
Set RS = New ADODB.Recordset
With RS
 .Source = "Select * From Employees"
 .ActiveConnection = Conn
 .CursorType = adOpenDynamic
 .LockType = adLockOptimistic
 .Open
End With

' Always check to make sure the Recordset is valid
If RS.State <> adStateOpen Then
   MsgBox "Failed to Open Recordset", vbExclamation, "Open Recordset"
   End
End If

' if your EXE happens to be in a root directory, the path wont have a "\"
' otherwise, it will.  In either case, make sure it has one.
If Right(App.Path, 1) = "\" Then
   sPath = App.Path
Else
   sPath = App.Path & "\"
End If

' Display the first record
DisplayRecord

End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next

'Close your Recordset
RS.Close
Set RS = Nothing

'Close your database connection
Conn.Close
Set Conn = Nothing

End Sub

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
VB-ExpertCommented:
The code I have included focuses around SQL Server 2000 and Access.  With the methods using a stream, the code can be applied anywhere as it is generic, and uses the parameters passed to it.
Appendchunk & Getchunk both require extra lines of coding, and also make the code less user friendly.  A lot of documenting is used when useing both Appendchunk & Getchunk functions.

'If you're using ADO 2.5 or better, use the Stream object instead of Getchunk/Appendchunk:

'There are 2 methods by which this can be achieved,
'Getchunk(),Appendchunk() or a Stream.

'Using a stream make the code cleaner & easier to manage.

'Included are both methods:

'AppendChunk() Write to the file

'The database can store the picture as an object using the following code

Public Function SavePictureToDB(PictControl As Object, rs As Object, FieldName As String) As Boolean

  'PURPOSE: SAVES PICTURE IN IMAGEBOX, PICTUREBOX, OR SIMILAR
  'CONTROL TO RECORDSET RS IN FIELD NAME FIELDNAME
 
   'FIELD TYPE MUST BE BINARY (OLE OBJECT IN ACCESS)
     
  Dim oPict As StdPicture
 
   Dim sDir As String
  Dim sTempFile As String
  Dim iFileNum As Integer
  Dim lFileLength As Long
 
   Dim abBytes() As Byte
  Dim iCtr As Integer
 
   On Error GoTo ErrorHandler
  If Not TypeOf rs Is ADODB.Recordset Then Exit Function
  Set oPict = PictControl.Picture
  If oPict Is Nothing Then Exit Function
 
   'Save picture to temp file
  sDir = GetTempDir
  If sDir = "" Then sDir = "C:\"
  sTempFile = sDir & "0X2341KLZX.dat"
  SavePicture oPict, sTempFile
 
   'read file contents to byte array
  iFileNum = FreeFile
  Open sTempFile For Binary Access Read As #iFileNum
  lFileLength = LOF(iFileNum)
  ReDim abBytes(lFileLength)
  Get #iFileNum, , abBytes()
  'put byte array contents into db field
  rs.Fields(FieldName).AppendChunk abBytes()
  Close #iFileNum
 
   'Don't return false if file can't be deleted
  On Error Resume Next
  Kill sTempFile
  SavePictureToDB = True
ErrorHandler:
  Close #iFileNum
  'MsgBox Err.Description
End Function


'The code below uses the Getchunk() to write this to a file.

'GetChunk() Read from the file

Private Sub List1_Click()
  Dim chunk()             As Byte
  datafile = 1
  Const ChunkSize = 16384
  sSql = "SELECT * FROM cars WHERE ID = " & List1.Text
  oRs.Open sSql, oCOn, adOpenDynamic, adLockOptimistic
  Open "pictemp" For Binary Access Write As datafile
  FL = oRs!Photo.ActualSize
  chunks = FL \ ChunkSize
  Fragment = FL Mod ChunkSize
  ReDim chunk(Fragment)
  chunk() = adoPrimaryRS!Picture.GetChunk(Fragment)
  Put datafile, , chunk()
  For i = 1 To chunks
     ReDim Buffer(ChunkSize)
     chunk() = oRs!Photo.GetChunk(ChunkSize)
     Put datafile, , chunk()
  Next i
  Close datafile
  FileName = "pictemp"
  Picture1.Picture = LoadPicture(FileName)
End Sub

OR

The coding below is generic and can be used in any situation.

Public Sub SaveFileToField(FieldName As String, FileName As String, ConnectionString As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim mstream As ADODB.Stream

  Set cn = New Connection
  cn.open ConnectionString

  Set mstream = New Stream
  mstream.Type = adTypeBinary
  mstream.open

  mstream.LoadFromFile FileName
  rs.fields(FieldName).Value = mstream.read
  rs.Update

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
End Sub

Public Sub SaveFieldToFile(FieldName As String, FileName As String, ConnectionString As String)
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim mstream As ADODB.Stream

  Set cn = New Connection
  cn.open ConnectionString

  Set mstream = New Stream
  mstream.Type = adTypeBinary
  mstream.open

  mstream.write rs.fields(FieldName).Value
  mstream.SaveToFile FileName, adSaveCreateOverwrite

  rs.Close
  cn.Close
  Set rs = Nothing
  Set cn = Nothing
End Sub


OR


''The code below uses a stream to read the picture from the database and then write this to a file

'Write the picture stored in the Access Database to a temporary file

'Streams - Read from the file

Dim mstream As ADODB.Stream

Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.Write adoAccRS.Fields("picture").Value
mstream.SaveToFile "C:\<Filename>.jpg",  adSaveCreateOverWrite
mstream.Close
       
'Read the picture stored in from the Temporary file  & Write to the SQL Database
strFilename = "C:\<Filename>.jpg"
Set mstream = New ADODB.Stream
mstream.Type = adTypeBinary
mstream.Open
mstream.LoadFromFile "C:\<Filename>.jpg"
adoSQLRS.Fields("Picture").Value = mstream.Read    'Write the value held in the stream to the SQL Picture field
adoSQLRS.Update
mstream.Close
       
'Using a Stream makes the code a lot cleaner and more efficient.

http://www.betav.com/Files/Content/Whitepapers/Using%20the%20ADO%20Stream%20Object%20to%20Manage%20BLOBs.htm
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
Visual Basic Classic

From novice to tech pro — start learning today.