ADO and LOBs Example 1: Inserting LOB Data From a File

The following is an ADO sample that demonstrates the insertion of a new row with a LOB column. A file called "c:\myfile.txt" will need to be created on your machine for this sample to work. It can be created using your favorite editor to contain any character data such as "This is only a test". This character data will then be used by the program to populate the CLOB column in the MULTIMEDIA_TAB table.

The program then retrieves the newly inserted data from the database and validates the inserted data. The inserted row is then deleted before the program exits.

The example covers the following ADO methods that can be used for LOBs, namely:

• GetChunk method

• AppendChunk method

• ActualSize property Sub Main()

  
    Dim con As New ADODB.Connection 
    Dim cmd As New ADODB.Command 
  
    Dim rst As New ADODB.Recordset 
  
    Dim LogFileName As String 
    Dim LogFileNum As Integer 
  
    Dim sql As String        ' SELECT statement 
    Dim clob_data As Variant ' data from a text file 
    Dim vardata As Variant   ' data retrieved from clob data in chunks 
    Dim vardata_len As Long  ' length of the data retrieved from the CLOB column 
    Dim done As Boolean      ' done = True if finished retrieving all the data 
    Dim Data As Variant      ' the entire data retrieved from the CLOB column 
  
    On Error GoTo ErrorHandler 
  
    ' open a text file 
    LogFileName = "c:\myfile.txt" 
    LogFileNum = FreeFile 
    Open LogFileName For Input As LogFileNum 
  
    ' load text from file to a local variable 
    clob_data = Input$(LOF(LogFileNum), LogFileNum) 
    Close #LogFileNum 
  
    ' connect as adldemo/adldemo 
    con.CursorLocation = adUseServer 
    con.Open "Provider=OraOLEDB.Oracle;Data Source=db9i;" & _ 
             "User Id=adldemo;Password=adldemo;" 
  
    ' open a recordset 
    sql = "select clip_id, story from MULTIMEDIA_TAB" 
    rst.Open sql, con, adOpenStatic, adLockOptimistic, adCmdText 
  
    ' add a new record 
    rst.AddNew 
    rst!clip_id = 1234 
    rst!story.AppendChunk (clob_data) 
    rst.Update 
  
    ' fetch entire CLOB data 
    Do While (Not (done)) 
      vardata = rst!story.GetChunk(4096) 
      If Not (IsNull(vardata)) Then 
        Data = Data & vardata 
      Else 
        done = True 
      End If 
    Loop 
  
    ' validate fetched data 
    If Data = clob_data And Len(clob_data) = rst!story.ActualSize Then 
      MsgBox "The CLOB data (of " & Len(clob_data) & " bytes) " & _ 
             "was inserted and retrieved properly!" 
    End If 
  
    ' cleanup 
    con.Execute "delete from multimedia_tab where clip_id = 1234" 
    rst.Close 
    con.Close 
  
    Exit Sub 
  ErrorHandler: 
    MsgBox "Error: " & Err.Description 
  End Sub