HOWTO: Use ADO GetChunk/AppendChunk with Oracle for BLOB Data

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
• Microsoft Visual Basic Enterprise Edition for Windows, versions 5.0, 6.0

SUMMARY

The purpose of this article is to demonstrate how to save and retrieve Binary Large Object (BLOB) data to a LONG RAW datatype column in an Oracle 7.3 database using the ActiveX Data Objects (ADO) methods GetChunk and AppendChunk.

LONG RAW datatypes are used to store binary data of variable size up to 2 Gb in length. Only one LONG RAW column may be defined per table. LONG RAW columns may not be used in subqueries, functions, expressions, WHERE clauses, or indexes. A table containing a LONG RAW column may not be clustered. Only one LONG RAW column may be defined per table and you cannot have both a LONG and a LONG RAW column define in the same table.

It should be noted that it is not recommended to store BLOB data or LONG text data in a table. A more efficient way is to store file pointers in the table that locates the actual files containing the data.

MORE INFORMATION

The following project has a Picture box, CommonDialog control, and three Command buttons on the start up form. Results and status are displayed in the Debug window or the Form's Caption. You must modify the connection string to match the settings of your Oracle installation.

NOTE: For Visual Basic 5.0 users, you need to acquire and install the Microsoft Data Access Components (MDAC) for the sample in this article. Please refer to the article listed in REFERENCES section for information on installing MDAC 2.0. MDAC 2.0 contains ActiveX Data Objects (ADO) version 2.0 and the Microsoft ODBC Driver for Oracle version 2.5.

For Visual Basic 6.0 users, ADO 2.0 and the Microsoft ODBC for Oracle driver version 2.5 installs with Visual Basic 6.0.

This project uses a table called BlobTable. Following is the script used to create the table and add one row to the table:

   CREATE TABLE BLOBTABLE (

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

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 

REFERENCES

For more information, please see the following articles in the Microsoft Knowledge Base:

Q192743 HOWTO: Use ADO GetChunk/AppendChunk with Oracle for TEXT Data

Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Additional query words:

Keywords : kbADO150 kbADO200 kbDatabase kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2,5.0,6.0
Platform : WINDOWS
Issue type : kbhowto