HOWTO: Use ADO GetChunk/AppendChunk with Oracle for TEXT 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 TEXT data to a LONG datatype column in an Oracle 7.3 database using the ActiveX Data Objects (ADO) methods GetChunk and AppendChunk.

For an example of using Binary Large Object (BLOB) data to a LONG RAW datatype column please see the article listed in the REFERENCES section of this article.

MORE INFORMATION

The following project has a RichTextBox and a CommonDialog control, and three command buttons on the Start-up Form. Results and status display in the Debug window of 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 the REFERENCES section for information on installing MDAC contains ADO 2.x and the Microsoft ODBC Driver for Oracle version 2.5.

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

Sample Code

CREATE TABLE LARGTEXT (

    MYID              NUMBER(2) NOT NULL PRIMARY KEY,

   BIGTEXT                 LONG
   );
   / 
   INSERT INTO LARGTEXT (MYID) VALUES (1);
   / 
   Commit; 

Application Description

1. Open a new project in Visual Basic. Form1 is created by default.



2. Place a RichTextBox and CommonDialog control along with three command buttons on the new form. You may need to add RichTextBox control and the CommonDialog control to your project. To do this, from the Project menu select Components and then select the Microsoft Common Dialog Control as well as Microsoft Rich TextBox Control.



3. From the Projects menu, select References and then select Microsoft ActiveX Data Objects. The version will depend on your setup. For Visual Basic 6.0, select Microsoft ActiveX Data Objects 2.0



4. Place the following code in the General Declarations section of Form1:

   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 = 10000 '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 Cursor type.
            strConn = "UID=MyUID;PWD=MyPassword;" & _
                    "driver={Microsoft ODBC for Oracle};" & _
                    "SERVER=MyServer;"
            Set Cn = New ADODB.Connection
            Cn.CursorLocation = adUseClient
            Cn.Open strConn
            Debug.Print Cn.ConnectionString
   
         End Sub
   
         Public Sub Command1_Click()
            ' AppendChunk button.
            ' This procedure prompts for a .txt file,
            ' converts that file to a String Variable,
            ' and saves the String Variable to the table
            ' using the Appendchunk method.
            '
            Dim TextFile As String
            Dim strData As String   'String for LongVarChar data.
            Dim SourceFile As Integer
   
            ' Open the LARGTEXT table.
            strSQL = "Select MyID, BigText from LARGTEXT WHERE MyID = 1"
            Set Rs = New ADODB.Recordset
            Rs.CursorType = adOpenStatic
            Rs.LockType = adLockOptimistic
            Rs.Open strSQL, Cn
   
            'Retrieve the text file and update the record.
            CommonDialog1.Filter = "(*.txt)|*.txt"
            CommonDialog1.ShowOpen
            TextFile = CommonDialog1.filename
            Me.MousePointer = vbHourglass
            Me.Caption = "Retrieving the Text File"
   
            ' Save text file to the table column.
            SourceFile = FreeFile
            Open TextFile 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 TextFile & " empty or not found."
                Exit Sub
            Else
   
                Numblocks = FileLength / BlockSize
                LeftOver = FileLength Mod BlockSize
   
                    Rs(1).AppendChunk Null
                    strData = String(LeftOver, " ")
                    Get SourceFile, 1, strData
                    Rs(1).AppendChunk strData
   
                    strData = String(BlockSize, " ")
   
                    For i = 1 To Numblocks
                      Get SourceFile, , strData
                      Rs(1).AppendChunk strData
                    Next i
   
                    Close SourceFile
   
                Rs.Update   'Commit the new data.
   
                Close SourceFile
            End If
   
            Me.Caption = "Text File Retrieved"
            Command2.Enabled = True
            Me.MousePointer = vbNormal
         End Sub
   
         Private Sub Command2_Click()
            ' GetChunk Button.
            ' This procedure retrieves the text file
            ' from the table using the GetChunk method,
            ' converts the data to a file and
            ' displays that file in the RichTextBox.
            '
            Dim strData As String   'String for LongVarChar data
            Dim DestFileNum As Integer
            Dim DiskFile As String
            Dim txtOutFile As String
            Dim FileSize As Long
   
            Me.MousePointer = vbHourglass
            Me.Caption = "Getting the Text File"
   
            ' Remove any existing destination file.
            DiskFile = App.Path & "\BigText.txt"
            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
            FileSize = Rs(1).ActualSize
            Debug.Print "The file size is " & FileSize
   
            strData = String(FileSize, 32)
   
            For i = 1 To Numblocks
                strData = String(BlockSize, 32)
                strData = Rs(1).GetChunk(BlockSize)
                Put DestFileNum, , strData
            Next i
            strData = String(LeftOver, 32)
            strData = Rs(1).GetChunk(LeftOver)
   
            Put DestFileNum, , strData
            Close DestFileNum
   
            RichTextBox1.LoadFile DiskFile, rtfText
   
            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
    

© Microsoft Corporation 1999, All Rights Reserved.
Contributions by Ron Nelson, Microsoft Corporation

REFERENCES

For additional information about using ADO GetChunk/AppendChunk with Oracle for BLOB Data, please see the following article in the Microsoft Knowledge Base:

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

Q175018 HOWTO: Acquire and Install the Microsoft Oracle ODBC Driver

Additional query words:

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