Microsoft KB Archive/192743

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

Article ID: 192743

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7
 * Microsoft Visual Basic 5.0 Enterprise Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition

-



This article was previously published under Q192743



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 or Oracle 8.x 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
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:

 Open a new project in Visual Basic. Form1 is created by default. 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. 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.  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.

