Re: How to get CLOB with VB

The following works for me using: ADO 2.0 and the ODBC driver from Oracle version 8.1.5

However, Using Oracle Objects for Ole (OO4O) from VB to do this is MUCH faser and cleaner.

'CREATE TABLE empclob (
' empno number(4),
' clob1 clob);

'Visual Basic Code

Dim objCon As ADODB.Connection
Dim objRst As ADODB.Recordset

Set objCon = New ADODB.Connection
Set objRst = New ADODB.Recordset

  objCon.ConnectionString = "DSN=V815;Data Source=V815;User ID=scott;password=tiger;"
  objCon.Open ConnectionString

  objRst.Open "empclob", objCon, adOpenStatic, adLockOptimistic, adCmdTable

  'One way to insert... using insert
  objRst.AddNew Array("empno", "clob1"), Array(9000, "Testing 9000")

  'Another way to insert... using update   objRst.AddNew
  objRst!EMPNO = 9001
  objRst!CLOB1 = "Testing 9001"
  objRst.Update

  'Third way to insert... using stream
  objRst.AddNew
  objRst!EMPNO = 9002

  objRst.Fields("CLOB1").AppendChunk "First stream..."
  objRst.Fields("CLOB1").AppendChunk "Second stream..."
  objRst.Fields("CLOB1").AppendChunk "Third and last stream..."
  objRst.Update

'Read from the clob

Dim objCon As ADODB.Connection
Dim objRst As ADODB.Recordset

Set objCon = New ADODB.Connection
Set objRst = New ADODB.Recordset

  objCon.ConnectionString = "DSN=dsn815;Data Source=V81;User ID=scott;password=tiger;"
  objCon.Open ConnectionString
  MsgBox ("connected")
  sql = "select * from empclob"
  Set rsTabel = New ADODB.Recordset
  rsTabel.ActiveConnection = objCon
  rsTabel.CursorLocation = adUseServer

rsTabel.LockType = adLockReadOnly
rsTabel.Source = sql
rsTabel.Open

MsgBox (rsTabel(0))
MsgBox (rsTabel(1)) Received on Tue Feb 22 2000 - 15:10:11 CST