0 Replies Latest reply on Sep 2, 2005 8:52 AM by 282491

    RAW datatype in EXCEL using OO4O

    282491
      Hi all.

      I would like to display a result of "select saddr from v$session where rownum < 2"
      to EXCEL sheet and I want to use OO4O.

      The data type of saddr in v$session is RAW.

      Is there any good solution to display oracle "RAW datatype"?

      To solve this question, I have tried two method - OLEDB provider and OO4O.

      When I used OLEDB, the result is nice,but the other is not.

      Please help me.

      Thank in advance.

      Please see below.

      1) method #1 : using OLEDB provider
      Dim sConn As String
      Dim oConn As ADODB.Connection
      Dim oRs As ADODB.Recordset
      Dim cntCol, cntRow As Long
      Dim resData() As Variant
      Dim i, j As Long
      Dim startTime, endTime As Date
      startTime = timer
      sConn = "Provider=MSDAORA;Data Source=" & gTargetServer & ";User ID=" & gUserID & "; Password=" & gPasswd
      Set oConn = New ADODB.Connection
      oConn.Open sConn
      Set oRs = New ADODB.Recordset

      oRs.Open gSqlString, oConn, adOpenStatic, adCmdText

      endTime = timer

      If Not (oRs.BOF Or oRs.EOF) Then
      Do While Not (oRs.BOF Or oRs.EOF)
      i = i + 1
      oRs.MoveNext
      Loop
      oRs.MoveFirst
      cntCol = oRs.Fields.Count
      cntRow = i

      ReDim resData(cntRow, cntCol - 1)

      '======== Column Title ==========

      For j = 0 To oRs.Fields.Count - 1
      resData(0, j) = oRs.Fields(j).Name
      'Debug.Print oRs.Fields(j).Name, oRs.Fields(j).Type
      If oRs.Fields(j).Type = 204 Then
      'Debug.Print "Attribute=" & oRs.Fields(j).Attributes
      End If
      Next j
      i = 1
      Dim varLogo As Variant
      Dim varChunk As Variant

      Dim by() As Byte
      Dim k As Integer
      Dim hexaString As String

      Do While Not (oRs.BOF Or oRs.EOF)
      For j = 0 To cntCol - 1
      If oRs.Fields(j).Type = 204 Then
      If oRs.Fields(j).Value & "" = "" Then
      resData(i, j) = oRs.Fields(j).Value
      Else
      hexaString = ""
      by = oRs.Fields(j).Value
      For k = 0 To UBound(by)
      hexaString = hexaString & Right("0" & Hex(by(k)), 2)
      Next k
      'resData(i, j) = "'" & hexaString
      resData(i, j) = hexaString
      End If
      Else
      resData(i, j) = oRs.Fields(j).Value
      End If
      Next j
      oRs.MoveNext
      i = i + 1
      Loop
      Else
      ReDim resData(2, oRs.Fields.Count - 1)
      For j = 0 To oRs.Fields.Count - 1
      resData(0, j) = oRs.Fields(j).Name
      Next j
      resData(1, 0) = "There is no record"
      End If

      oRs.Close
      oConn.Close
      Set oRs = Nothing
      Set oConn = Nothing
      endTime = timer
      getResultSet = resData

      The result is right.
      SADDR
      C0000000614ADDF0


      2) method #1 : using OO4O

      Dim oSession As Object
      Dim oDatabase As Object
      Dim oRs, tRs As Object

      Dim cntCol, cntRow As Long
      Dim rowNum, colNum As Long

      Dim startTime, endTime As Date

      Dim sraw() As Byte
      Dim k As Integer
      Dim hexaString As String


      startTime = timer


      Set oSession = CreateObject("OracleInProcServer.XOraSession")
      Set oDatabase = oSession.DbOpenDatabase(gTargetServer, gUserID & "/" & gPasswd, 0&)
      Set oRs = oDatabase.DbCreateDynaset(gSqlString, 0&)

      endTime = timer

      cntCol = oRs.Fields.Count
      cntRow = oRs.RecordCount

      ReDim resData(cntRow, cntCol)

      For colNum = 0 To cntCol - 1
      resData(0, colNum) = oRs.Fields(colNum).Name
      Next colNum

      For rowNum = 1 To cntRow
      For colNum = 0 To cntCol - 1
      Select Case oRs.Fields(colNum).Type
      Case 11
      hexaString = ""
      sraw = oRs.Fields(colNum).Value
      'Debug.Print sraw
      For k = 0 To UBound(sraw)
      hexaString = hexaString & Right("0" & Hex(sraw(k)), 2)
      Next k
      resData(rowNum, colNum) = hexaString

      Case Else
      resData(rowNum, colNum) = oRs.Fields(colNum).Value
      End Select

      Next colNum
      oRs.MoveNext
      Next rowNum

      Set oRs = Nothing
      Set oDatabase = Nothing
      Set oSession = Nothing

      getResultSet = resData

      ==> The result is wrong:
      SADDR
      FDFF0000000061004A00D66B