DevOps, CI/CD and Automation

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 (is incorrect)
Long: True
MayDefer: False
Chachedeferred: False

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

Most important question 1. Question 2 is irrelevant as long as I don't have a solution for 1.

1. What do I do wrong and how to get it running? When I copy the SQL-statement in TOAD I get the desired result and see (part of) the CLOB-string displayed.

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