OLEDB Provider does not return CLOB data

From: Hans Troost <Hans.troost_at_solvay.com>
Date: 13 May 2002 00:42:49 -0700
Message-ID: <80ac6502.0205122342.725be56c_at_posting.google.com>

[Quoted] Hi all,

I hope someone can help me out, since I've a severe problem and a question.

I try to retrieve an Oracle CLOB field to use it for several purposes, but I fail in doing it.

Environment: VB6 SP5, MDAC 2.5x (can be upgraded to 2.7 if necessary) and ORACLE OLEDB Provider 8.1.7

I wrote a simple and small program that on load retrieves 10 records and tries to use the CLOB-field. To help myself, the program generates a messagebox about the CLOBfield that now says:

Name: ClobFld

Type: 201	(which seems to be OK)
TypeName: Null	(???)

Defined size: 2147483647
Actual size: 0
Long: True
MayDefer: False
Chachedeferred: False

The program has a form with an ADO Data Control named Adodc1 on it

1. What do I do wrong and how to get it running? When I copy the SQL-statement in TOAD or SQLPLUS I get the desired result. I think it has to do with the mapping of the datatypes.
2. I want to avoid retrieving the CLOB-field in a loop, using the GetChunk-method. As far as I know there are ways to do so, something like using an ADODB-Command object and setting some parameters before executing the query, but I have no idea how to do this. (Please don't respond with the usage of a stored procedure: I can not use that solution, since we now have more than 1 Oracle Instance/Schema-combinations with this type of data and expect more of them. The final program will allow the user to choose an Instance/Schema combination and I don't want to write/maintain stored procedures for each Instance/Schema combination.)

Here is my code, please take a look at it:

Private dbCon As ADODB.Connection
Private Rst As ADODB.Recordset

Private Sub Form_Initialize()

    Set dbCon = New ADODB.Connection

    With dbCon

.ConnectionString =

"Provider=OraOLEDB.Oracle.1;OSAuthent=1;Data Source=DatabaseName"

.Open

    End With

    Set Rst = New ADODB.Recordset
    Rst.Open "Select REGNO, ClobFld from Schema.Table where REGNO between 91 and 100", dbCon, adOpenStatic, adLockReadOnly End Sub

Private Sub Form_Load()

    Set Adodc1.Recordset = Rst
End Sub

Private Sub Form_Terminate()

    Rst.Close
    dbCon.Close
    Set dbCon = Nothing
    Set Rst = Nothing
End Sub

Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

    Dim MsgStr As String

    Dim sResult As String
    Const lChunkSize as Long = 100
    Dim lFieldLength as Long
    Dim lCharsRead as Long

    If Not Rst.BOF And Not Rst.EOF Then

        With Rst.Fields("ClobFld")
            MsgStr = "Name: " & .Name & Chr(13)
            MsgStr = MsgStr & "Type: " & .Type & Chr(13)
            MsgStr = MsgStr & "Type: " & .Type & Chr(13)
            MsgStr = MsgStr & "Typename: " & TypeName(.Value) &
Chr(13)
            MsgStr = MsgStr & "Defined size: " & .DefinedSize &
Chr(13)
            MsgStr = MsgStr & "Actual size: " & .ActualSize & Chr(13)
            MsgStr = MsgStr & "Long: " & CBool(.Attributes And
adFldLong) & Chr(13)
            MsgStr = MsgStr & "Maydefer: " & CBool(.Attributes And
adFldMayDefer) & Chr(13)
            MsgStr = MsgStr & "Cachedeferred: " & CBool(.Attributes
And adFldCacheDeferred) & Chr(13)
        End With
        MsgBox MsgStr

        lFieldLength = Rst.Fields("ClobFld").ActualSize
        lCharsRead = 0
        While lCharsRead < lFieldLength
        	sResult = sResult &
Rst.Fields("ClobFld").GetChunk(lChunkSize)
        	lCharsRead = lCharsRead + lChunkSize
        Wend

    End If

End Sub

Thanks in advance,

Hans Troost Received on Mon May 13 2002 - 09:42:49 CEST