VBS访问、操作oracle数据库

 

Sub FetchDataCount()
    Dim sheetName As String
    sheetName = "test"

    Dim dbServiceName As String
    dbServiceName = Sheets(sheetName).Cells(1, 1)
    If dbServiceName = "" Then
        MsgBox "服务名不能为空"
        Return: FetchDataCount
    End If

    Dim tableName As String
    tableName = Sheets(sheetName).Cells(1, 2)
    Dim sqlCommand As String
    sqlCommand = Sheets(sheetName).Cells(1, 3)

    Set con = CreateObject("adodb.connection")
    con.connectionstring = "provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=sde;password=sde;Data Source=" & dbServiceName
    con.Open

    If Err <> 0 Then
        MsgBox "连接数据库失败"
        WScript.Quit
    End If

   ' Dim Rs As ADODB.Recordset
    Dim strSQL
    Dim rs

    If sqlCommand = "" And tableName = "" Then
        MsgBox "既未输入查询语句 ，也没输入表名"
        Return: FetchDataCount
    End If

    If sqlCommand = "" Then

        strSQL = "select * from " & tableName + " where 1=1 "
    Else
        strSQL = sqlCommand
    End If
    '定义查询命令对象

    Set rs = con.Execute(strSQL)

    Dim flag As Boolean
    flag = False

    Dim index As Integer

    index = 2

    Dim iColIndex As Integer
    'iColIndex = 1

    Do Until rs.EOF
          Set fld = rs.Fields
          If flag = False Then
             For i = 0 To fld.Count - 1 Step 1
                Sheets(sheetName).Cells(index, i + 1) = rs.Fields(i).Name
             Next i
             flag = True

             index = index + 2
          End If

        For m = 0 To fld.Count - 1 Step 1
            Sheets(sheetName).Cells(index, m + 1) = rs.Fields(m).Value
        Next m
        index = index + 1
        rs.movenext
    Loop

    con.Close
    Set cmd = Nothing
    Set con = Nothing

End Sub