vbs Excel Oracle,QTP中VBS调用数据库的方式【mysql,sqlserver,oracle,excel】

Option Explicit

Dim oDataBase

Set oDataBase = new DataBase

Dim sDBName, sSQL, RS

'''To Read Mysql Data

'sName = "Mysql.ProductDB"

'sSQL = "select * from Products_Core limit 0,10;"

'print oDataBase.StartEngine("Mysql",sName, "ProductDB")

'Set RS = oDataBase.DB_Execute_Query(sName, sSQL)

'print oDataBase.Get_Field_Value(RS, 8, "product_id")

'

'''To Read SqlServer Data

'sName = "SQLServer.Account"

'sSQL = "select top 10 * from dangdang_money"

'print oDataBase.StartEngine("SQLServer",sName, "Account")

'Set RS = oDataBase.DB_Execute_Query(sName, sSQL)

'print oDataBase.Get_Field_Value(RS, 8, "money_id")

'

'''To Read Excel Data

'sName = "Excel.TD.xls"

'sSQL = "select * from publish_comm_data"

'print oDataBase.StartEngine("Excel",sName, "TD.xls")

'Set RS = oDataBase.DB_Execute_Query(sName, sSQL)

'print oDataBase.Get_Field_Value(RS, 2, "PID")

'

'''To Read OracleData

'sName = "Oracle.DB"

'sSQL = "select * from Table"

'print oDataBase.StartEngine("Oracle",sName, "DB")

'Set RS = oDataBase.DB_Execute_Query(sName, sSQL)

'print oDataBase.Get_Field_Value(RS, 2, "Field")

'

class DataBase

Private oDBConns

Private oCurrentConn

Public Property Get Version

Version = "0.1"

End Property

Public Function StartEngine(sType, sName, sDBName)

Dim oDBEngine

Dim bResult

Dim i

bResult = True

On Error Resume Next

Execute "Set oDBEngine = New " & sType & "Engine"

If Err.Number <> 0 Then

bResult = False

Reporter.ReportEvent micFail, "DataEngine StartEngine", "Failed to create instance : " & sType

End If

On Error Goto 0

If bResult = True Then

On Error Resume Next

Set oDBEngine.Enginer = Me

Err.Clear

On Error Goto 0

oDBEngine.StartEngine(sDBName)

if not oCurrentConn is Nothing then

If not oDBConns.Exists(sName) Then

oDBConns.Add sName, oCurrentConn

Set oCurrentConn = Nothing

End If

else

bResult = False

end if

End If

If bResult = False Then

Reporter.ReportEvent micFail, "DataBase StartEngine", "Failed to start engine " & sName & " Of Type " & sType

On Error Resume Next

oDBEngine.StopEngine

On Error Goto 0

End If

Set oDBEngine = Nothing

StartEngine = bResult

End Function

Public Function DB_Connect(connection_string)

On error Resume next

set oCurrentConn = CreateObject("ADODB.Connection")

If Err.Number <> 0 then

Set oCurrentConn = Nothing

Exit Function

End If

oCurrentConn.Open connection_string

If Err.Number <> 0 then

Set oCurrentConn = Nothing

Exit Function

End If

On Error Goto 0

End Function

Public Function DB_Disconnect(sName)

if oDBConns.Exists(sName) then

oDBConns(sName).close

Set oDBConns(sName) = Nothing

oDBConns.Delete sName

end if

End Function

Public Function DB_Execute_Query(sName, SQL)

if oDBConns.Exists(sName) then

Set DB_Execute_Query = oDBConns(sName).Execute(SQL)

end if

End Function

Public Function Get_Field_Value(RS, int_col, str_Field)

index_col = int(int_col)

If (not RS.eof) then

Rs.MoveFirst

While index_col > 1

RS.MoveNext

index_col = index_col - 1

Wend

Get_Field_Value = RS.Fields.Item(str_Field)

If Not IsNull(Get_Field_Value) Then

Get_DB_Field_Value=CDbl(Get_Field_Value)

End If

Else

Reporter.ReportEvent micFail,"未在数据库中查询到值，请检查数据的正确性"

Rs.close

Set Rs = Nothing

End If

End Function

Public Function Get_Collection_Count(rs)

Dim i

For each coll in rs

i = i + 1

Next

Get_Collection_Count = i

End Function

Private Sub Class_Initialize

Set oDBConns = CreateObject("Scripting.Dictionary")

End Sub

Private Sub Class_Terminate

Set oDBConns = Nothing

End Sub

End class

Class SQLServerEngine

Public Enginer

Public Property Get Version

Version = "0.1"

End Property

Public Property Get EngineType

EngineType = "SQLServer"

End Property

Public Function StartEngine(str_db_name)

Sql_Con_Str = "driver={SQL Server};Server=10.255.254.207;DATABASE=" & str_db_name & ";UID=writeuser;PWD=ddbackend;"

StartEngine = Enginer.DB_Connect(Sql_Con_Str)

End Function

Public Sub StopEngine

Set Enginer = Nothing

End Sub

End Class

Class MysqlEngine

Public Enginer

Public Property Get Version

Version = "0.1"

End Property

Public Property Get EngineType

EngineType = "MysqlServer"

End Property

Public Function StartEngine(str_db_name)

Sql_Con_Str = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=10.255.254.208;PORT=3306;DATABASE=" & str_db_name & ";USER=writeuser;PASSWORD=ddbackend;OPTION=3;"

StartEngine = Enginer.DB_Connect(Sql_Con_Str)

End Function

Public Sub StopEngine

Set Enginer = Nothing

End Sub

End Class

Class OracleEngine

Public Enginer

Public Property Get Version

Version = "0.1"

End Property

Public Property Get EngineType

EngineType = "OracleServer"

End Property

Public Function StartEngine(str_db_name)

Sql_Con_Str = "driver={Microsoft ODBC for Oracle};Server=" & str_db_name & ";UID=dangdang;PWD=dangdang;"

StartEngine = Enginer.DB_Connect(Sql_Con_Str)

End Function

Public Sub StopEngine

Set Enginer = Nothing

End Sub

End Class

Class ExcelEngine

Public Enginer

Public Property Get Version

Version = "0.1"

End Property

Public Property Get EngineType

EngineType = "OracleServer"

End Property

Public Function StartEngine(str_db_name)

Sql_Con_Str = "DBQ=D:\QTP\ProductPage_Project\Test_Data\" & str_db_name & ";Driver=Driver do Microsoft Excel(*.xls)"

StartEngine = Enginer.DB_Connect(Sql_Con_Str)

End Function

Public Sub StopEngine

Set Enginer = Nothing

End Sub

End Class

使用说明：

Dim oDataBase

Set oDataBase = new DataBase

Dim sDBName, sSQL, RS

'''To Read Mysql Data

'sName = "Mysql.ProductDB"

'sSQL = "select * from Products_Core limit 0,10;"

'print oDataBase.StartEngine("Mysql",sName, "ProductDB")

'Set RS = oDataBase.DB_Execute_Query(sName, sSQL)

'print oDataBase.Get_Field_Value(RS, 8, "product_id")格式如上，但是具体的机器上链接时需要配置相应的数据库连接串，可以修改各engine里的Sql_Con_Str，其实主要修改驱动的名称，因为不同的驱动的名字不一样。