QTP中连接数据库的vbs脚本

在利用QTP进行自动化测试的过程中，不可避免地，需要牵涉到对数据库的操作。

本文对在QTP中进行数据库操作做一下总结

 

1.      数据库连接

采用ADO连接数据库的方式，建立应用程序和数据库的连接。具体代码如下：

 

Set Cnn = CreateObject("ADODB.Connection")

Public Sub DBConnect(ByVal iniFile)

       On error resume Next

       Dim StrCon,rel

       Dim DBType,DBServer,DBUser,DBPass,DBName

       '从配置文件中获取数据库类型，Server名，用户名，用户密码，数据库名

     DBType = ZxINI_ReadNodeValue(iniFile, "Database", "DBType")

       DBServer = ZxINI_ReadNodeValue(iniFile, "Database", "DBServer")

       DBUser = ZxINI_ReadNodeValue(iniFile, "Database", "DBUser")

       DBPass = ZxINI_ReadNodeValue(iniFile, "Database", "DBPass")

       DBName = ZxINI_ReadNodeValue(iniFile, "Database", "DBName")

      

       Select Case DBType

              Case 0                   'Sql Server

                     StrCon = "Provider=MSDASQL;Driver={SQL Server};Server="&DBServer&";Database="&DBName&";Uid="&DBUser&";Pwd="&DBPass&";"

              Case 1                   'Oracle

                     StrCon = "Provider=MSDAORA.1;Data Source="&DBServer&";Password="&DBPass&";User ID="&DBUser&";Persist Security Info=True"

              Case 2                   'Sybase

                     StrCon = "Driver={SYBASE SYSTEM 11};Srvr="&DBServer&";Uid="&DBUser&";Pwd="&DBPass&";Database="&DBName&";"

              Case 3                   'Access

                     StrCon = "Driver={Microsoft Access Driver (*.mdb)};Dbq="&DBName&";Uid="&DBUser&";Pwd="&DBPass&";"

       End Select    

 

       Cnn.Open StrCon

       ' 获取连接结果信息

       rel = chkDBError()

       If rel = true Then

              MsgBox "数据库连接失败，测试中止！"

              On Error GoTo 0

       Exit Test

       Else

              GE_loginfo "数据库已建立连接！ StrCon = " &strCon ,micPass

       End If

End Sub

 

2.      检查数据操作失败

Public Function chkDBError()

       Dim ObjError

       If Cnn.Errors.Count > 0 Then

              For Each ObjError In Cnn.Errors

                     If objError.Number <> 0 Then

                            GE_logError "数据库操作失败：" &objError.description ,micFail

                            chkDBError = true

                     End If

              Next

       Else

              chkDBError = false

       End If

End Function

 

3.      执行数据库操作语句

Public Function ExecuteSql(ByVal sqlstr)

      On error resume next

       Dim rel,Rst

       Dim RstCol

       Dim i,j,n

       ' 设置结果集

       Set Rst =CreateObject("ADODB.Recordset")

       sqlstr = LTrim(sqlstr)

      

       ' 判断是查询语句还是执行语句

       If Left(sqlstr,6)="select" Then

             Rst.open sqlstr, Cnn

 

              If chkDBError() =  True Then

                     rel = -1

                     GE_logError "查询语句 "&sqlstr& "  发生错误！",micFail

              Else

                 IF Rst.EOF THEN

                            rel = 0

                            GE_loginfo "查询语句 "&sqlstr& "  无返回值！",micWarning

                     Else

                            i = 0

                            ' 将查询结果保存到动态datatable中

For each RstCol in Rst.Fields

                                   i = i+1

                               datatable.AddSheet("SQL RecordSet").AddParameter RstCol.name,""

                Next

                            n=0

                            While not Rst.EOF

                                   n=n+1

                                   For j = 0  to i-1

                                          datatable.SetCurrentRow n

                        datatable.Value( Rst.Fields(j).name,"SQL RecordSet") =  Rst.Fields(j).value

                                   Next

                                   Rst.MoveNext

                            Wend

                           

                            rel =datatable.GetSheet("SQL RecordSet").GetRowCount

                            GE_loginfo "查询语句 "&sqlstr& "  返回"&rel&"条结果，保存于datatable <SQL RecordSet>",micPass

                     End if

             End If          

      Else

              '执行非查询语句

              Rst.open sqlstr, Cnn

             If chkDBError() =  True Then          

                     rel = -1

                     GE_logError "执行语句 "&sqlstr& "  发生错误！",micFail

             Else       

                     rel = 1

                     GE_loginfo "执行语句 "&sqlstr& "  成功！",micPass

             End If

       End If

      

       '返回值： -1，sql语句执行错误

'               0，查询sql没有有结果返回

'               >0，查询sql有结果时为结果集记录数,

'                                  非查询sql执行成功 ,值为1

    ExecuteSql=CInt(rel)

       Rst.close

End Function

 

 

4.      执行数据库脚本、

Public Sub ExecuteSqlScript(ByVal Dbtype,ByVal sqlFile)

Dim oShell

Dim runScript

Dim rtn

 

Set oShell = CreateObject ("WSCript.shell")

       If  Dbtype=1 Then   ‘Oracle

               runScript="sqlplus /nolog"  & " @"& sqlFile

       ElseIf Dbtype=2 Then  ‘SqlServer，此处用户名和密码，数据库名写死，根据需要可采用从

配置文件读取的方式

          runScript="isql -Uzxin_smap -Pzxin_smap -S "& SCPdbservername &" -i "& sqlFile

       End If

       ‘依次执行语句

       rtn=oShell.run(runScript,1,true)

       'wait 1

       'wshshell.sendkeys "exit~"      ‘此处根据脚本是否包含退出语句决定是否需要

       Set oShell = Nothing

       if rtn = 0 then

          GE_loginfo runscript,micPass  

       else

          GE_logError runscript & "got Error :" & rtn,micFail

       end if

End Sub