How To Use ADO GetChunk/AppendChunk with Oracle for BLOB Data

   CREATE TABLE BLOBTABLE (

     MYID              NUMBER(2) NOT NULL PRIMARY KEY,
     BLOBFLD                 LONG RAW
    );
    / 
    INSERT INTO BLOBTABLE (MYID) VALUES (1);
    / 
    Commit;
				

Back to the top

Application Description

1. Open a new project in Visual Basic. Form1 is created by default.
2. Place a Picture box and CommonDialog control along with three Command buttons on the new form. You may need to add the CommonDialog control to your project. On the Project menu, point to Components, and then select the Microsoft Common Dialog Control 5.0 or the 6.0 version if you are using Visual Basic 6.0.
3. On the Project menu, point to References, and then select Microsoft ActiveX Data Objects 2.x Library.
4. Place the following code in the General Declarations section of Form1:

      ' This application demonstrates using ADO with the AppendChunk
      ' and GetChunk methods against an Oracle 7.3 database.
      '
      Option Explicit
      Dim Cn As ADODB.Connection
      Dim Rs As ADODB.Recordset
      Dim strConn As String
      Dim strSQL As String
      Dim FileLength As Long  'Used in Command1 and Command2 procedures.
      Dim Numblocks As Integer
      Dim LeftOver As Long
      Dim i As Integer
      Const BlockSize = 100000 'This size can be experimented with for
                               'performance and reliability.
   
      Private Sub Form_Load()
         Command1.Caption = "AppendChunk"
         Command2.Caption = "GetChunk"
         Command3.Caption = "Exit"
         Command2.Enabled = False
   
         'Make Connection
         Set Cn = New ADODB.Connection
         strConn = "UID=MyUID;PWD=MyPassword;" & _
                   "driver={Microsoft ODBC for Oracle};" & _
                   "SERVER=MyServer;"
         Cn.Open strConn
         Debug.Print Cn.ConnectionString
   
      End Sub
   
      Public Sub Command1_Click()
         ' AppendChunk button
         ' This procedure prompts for a BMP file,
         ' converts that file to a Byte array,
         ' and saves the Byte Array to the table
         ' using the Appendchunk method.
         '
         Dim PictBmp As String
         Dim ByteData() As Byte   'Byte array for Blob data.
         Dim SourceFile As Integer
   
         ' Open the BlobTable table.
         strSQL = "Select MyID, BLOBfld from BLOBTABLE WHERE MyID = 1"
         Set Rs = New ADODB.Recordset
         Rs.CursorType = adOpenKeyset
         Rs.LockType = adLockOptimistic
         Rs.Open strSQL, Cn
   
         ' Retrieve the picture and update the record.
         CommonDialog1.Filter = "(*.bmp;*.ico)|*.bmp;*.ico"
         CommonDialog1.ShowOpen
         PictBmp = CommonDialog1.filename
         Me.MousePointer = vbHourglass
         Me.Caption = "Retrieving the picture"
   
         ' Save Picture image to the table column.
         SourceFile = FreeFile
         Open PictBmp For Binary Access Read As SourceFile
   
         FileLength = LOF(SourceFile)  ' Get the length of the file.
         Debug.Print "Filelength is " & FileLength
   
         If FileLength = 0 Then
   
             Close SourceFile
             MsgBox PictBmp & " empty or not found."
             Exit Sub
         Else
   
             Numblocks = FileLength / BlockSize
             LeftOver = FileLength Mod BlockSize
   
             ReDim ByteData(LeftOver)
             Get SourceFile, , ByteData()
             Rs(1).AppendChunk ByteData()
   
             ReDim ByteData(BlockSize)
             For i = 1 To Numblocks
                 Get SourceFile, , ByteData()
                 Rs(1).AppendChunk ByteData()
             Next i
   
             Rs.Update   'Commit the new data.
   
            Close SourceFile
         End If
   
         Me.Caption = "Picture Retrieved"
         Command2.Enabled = True
         Me.MousePointer = vbNormal
      End Sub
   
      Private Sub Command2_Click()
         ' GetChunk Button
         ' This procedure retrieves the picture image
         ' from the table using the GetChunk method,
         ' converts the data to a file and
         ' displays that file in the Picture box.
         '
         Dim ByteData() As Byte   'Byte array for picture file.
         Dim DestFileNum As Integer
         Dim DiskFile As String
   
         Me.MousePointer = vbHourglass
         Me.Caption = "Creating Picture File"
   
         ' Remove any existing destination file.
         DiskFile = App.Path & "\image1.bmp"
         If Len(Dir$(DiskFile)) > 0 Then
            Kill DiskFile
         End If
   
         DestFileNum = FreeFile
         Open DiskFile For Binary As DestFileNum
   
         Numblocks = FileLength / BlockSize
         LeftOver = FileLength Mod BlockSize
   
         ByteData() = Rs(1).GetChunk(LeftOver)
         Put DestFileNum, , ByteData()
   
         For i = 1 To Numblocks
             ByteData() = Rs(1).GetChunk(BlockSize)
             Put DestFileNum, , ByteData()
         Next i
   
         Close DestFileNum
   
         Picture1.Visible = True
         Picture1.Picture = LoadPicture(App.Path & "\image1.bmp")
         Rs.Close
         Debug.Print "Complete"
         Me.Caption = "Success!"
         Me.MousePointer = vbNormal
      End Sub
   
      Private Sub Command3_Click()
      'Exit button.
      Cn.Close
      Unload Me
      End Sub
   					

Back to the top