Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Location
    California
    Posts
    2

    Unanswered: Using ADO to find column defined as BLOB in Oracle

    I've written some code that takes in an Oracle table and a column name and determines the column definition according to DataTypeEnum. I'm mostly concerned with verifying the definition of LongRaw and BLOB columns. The code below works fine for LongRaw ... but for BLOB columns it fails on rs.Open with an Unknown error. Any suggestions on why this does not work for a blob column? Here's the code:

    Function VerifyColType2(serviceName As String, user As String, passwd As String, layerName As String, colName As String, expectedColType As String) As Boolean
    On Error GoTo ErrorHandler

    VerifyColType2 = False

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim fld As ADODB.Field
    Dim FieldType As String

    cn.CursorLocation = adUseClient
    cn.Open "Driver={Microsoft ODBC for Oracle}; " & "CONNECTSTRING=" & serviceName & ";uid=" & user & _
    ";pwd=" & passwd & ";"

    rs.Open layerName, cn, 0, 1, adCmdTable



    ' Enumerate Fields collection of table
    For Each fld In rs.Fields
    If fld.Name = colName Then
    ' translate field-type code to text
    ' for definitions of each type see http://msdn.microsoft.com/library/de...tatypeenum.asp
    Select Case fld.Type
    Case 20
    FieldType = "adBigInt"
    Case 128
    FieldType = "adBinary"
    Case 11
    FieldType = "adBoolean"
    Case 8
    FieldType = "adBSTR"
    Case 136
    FieldType = "adChapter"
    Case 129
    FieldType = "adChar"
    Case 6
    FieldType = "adCurrency"
    Case 7
    FieldType = "adDate"
    Case 133
    FieldType = "adDBDate"
    Case 134
    FieldType = "adDBTime"
    Case 135
    FieldType = "adDBTimeStamp"
    Case 14
    FieldType = "adDecimal"
    Case 5
    FieldType = "adDouble"
    Case 0
    FieldType = "adEmpty"
    Case 10
    FieldType = "adError"
    Case 64
    FieldType = "adFileTime"
    Case 72
    FieldType = "adGUID"
    Case 9
    FieldType = "adIDispatch"
    Case 3
    FieldType = "adInteger"
    Case 13
    FieldType = "adIUnknown"
    Case 205
    FieldType = "adLongVarBinary"
    Case 201
    FieldType = "adLongVarChar"
    Case 203
    FieldType = "adLongVarWChar"
    Case 131
    FieldType = "Numeric"
    Case 138
    FieldType = "adPropVariant"
    Case 4
    FieldType = "adSingle"
    Case 2
    FieldType = "adSmallInt"
    Case 16
    FieldType = "adTinyInt"
    Case 21
    FieldType = "adUnsignedBigInt"
    Case 19
    FieldType = "adUnsignedInt"
    Case 18
    FieldType = "adUnsignedSmallInt"
    Case 17
    FieldType = "adUnsignedTinyInt"
    Case 132
    FieldType = "adUserDefined"
    Case 204
    FieldType = "adVarBinary"
    Case 200
    FieldType = "adVarChar"
    Case 12
    FieldType = "adVariant"
    Case 139
    FieldType = "adVarNumeric"
    Case 202
    FieldType = "adVarWChar"
    Case 130
    FieldType = "adWChar"
    Case Else
    FieldType = "UNKNOWN"
    End Select
    ' show results
    Debug.Print " Name: " & fld.Name & vbCr & " Type: " & FieldType & vbCr
    If expectedColType = FieldType Then
    MsgBox "Expected column type to be " & expectedColType & " and found the actual column to be " & FieldType & ".", vbInformation, "PASSED column verification"
    Else
    MsgBox "Expected column type to be " & expectedColType & " and found the actual column to be " & FieldType & ".", vbExclamation, "FAILED column verification"
    End If
    VerifyColType2 = True

    End If
    Next fld

    rs.Close
    cn.Close
    Exit Function
    ErrorHandler:
    ' clean up

    If Not rs Is Nothing Then
    If rs.State = adStateOpen Then rs.Close
    End If
    Set rs = Nothing

    If Not cn Is Nothing Then
    If cn.State = adStateOpen Then cn.Close
    End If
    Set cn = Nothing

    Set fld = Nothing


    If Err <> 0 Then
    MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
    End Function


    Thanks,
    Ken

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It looks like ADO doesn't support BLOB, and assuming it was created after the existence of it, I woulnd't expect it to support it.

  3. #3
    Join Date
    Aug 2004
    Location
    California
    Posts
    2
    There are lots of examples of using ADO to read and write blobs so they must be supported to some degree. I can read them, write them ... but when I perform the code in the original question, it blows.

    I'm hoping someone has a workaround.

    Thanks,
    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •