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

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

This sample Visual Basic application has a RichTextBox control to view the selected text file, a CommonDialog control to pick a text file (it defaults to .txt files) and three command buttons to control the application flow. The AppendChunk Command button, when clicked, opens the Open File dialog box allowing you to select a .txt file. The code behind the button takes that file and stores it to the LargText LONG column using the AppendChunk method. The GetChunk button, when clicked, retrieves the text data in the LONG column using the GetChunk method, converts the Text data to a .txt file and displays that file in the RichTextBox control. The third button allows you to exit the application.

To create the application follow these steps:

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.6.
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
   
   					

Run the Project and click the AppendChunk button. Change the directory to pick a .txt file. Click the file of choice and wait for the GetChunk button to become enabled. After the GetChunk button is enabled, click it and you should see the text you selected in the RichTextBox control. The Debug window will have the size of the file you selected along with the ADO connect string.