qtp_oracle数据库连接

1. 创建一个oracle_sxfs_qtp_connect.vbs文件

'-----------------------------------------------------------------
'function：数据库连接
'call method：在调用文件中先定义ResQtp,CmdQtp
'For example：Dim  ResQtp,CmdQtp
'             DBConnect()
'-----------------------------------------------------------------
Public Sub DBConnectQtp()
      Dim StrConQtp
      Set ResQtp = CreateObject("ADODB.Recordset")
      Set CmdQtp = CreateObject("ADODB.Command")
      'Oracle
      StrConQtp ="DRIVER={Oracle in OraDb10g_home1};SERVER=ttgfmis;UID=peter_qtp_sxfs;PWD=peter_qtp_sxfs;DBQ=TTGFMIS;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;"
      CmdQtp.activeconnection=StrConQtp
      CmdQtp.CommandType =1
End Sub
'-----------------------------------------------------------------
'function：执行SQL语句
'Input parameter: StrSql
'For example：Dim sql
'             sql="select * from t_user"
'             ExecuteSql(StrSql)           
'---------------------------------------------------------
Public Sub  ExecuteSqlQtp(StrSql)
 CmdQtp.CommandText=StrSql
 Set  ResQtp = CmdQtp.Execute()
End Sub

'function：关闭数据库
'For example：DBCloseQtp()
'---------------------------------------------------------
Public Sub DBCloseQtp()
 Set ResQtp=nothing
 Set CmdQtp.activeconnection=nothing
 Set CmdQtp=nothing
End Sub

 

2.查找数据库信息,脚本为:oracle_sxfs_operation.vbs

'连接qtp数据库
Dim Res,Cmd,ResQtp,CmdQtp
Dim SqlArray,QtpParameter,rowcount
Public Function SqlQtp(sqlsentence,SqlItem)  
    ExecuteFile "../sxfs/oracle_sxfs_qtp_connect.vbs"
   DBConnectQtp()
   ExecuteSqlQtp(sqlsentence)
    QtpParameter  = ResQtp(SqlItem).value
  DBCloseQtp()
End Function

'连接sxfs数据库
Public Function SqlSxfs(op_type,sqlsentence,SqlItem)
    SqlArray = ""
    ExecuteFile "../sxfs/oracle_sxfs_qtp_connect.vbs"
    sqlqtp_connect = "select * from dbconnect where (db_name='"&op_type&"')"
    Call SqlQtp(sqlqtp_connect,"db_server")
    op_server = QtpParameter
    Call SqlQtp(sqlqtp_connect,"db_username")
    op_username = QtpParameter
    Call SqlQtp(sqlqtp_connect,"db_userpwd")
    op_userpwd = QtpParameter
    Call SqlQtp(sqlqtp_connect,"db_dbp")
    op_dbp = QtpParameter
    ExecuteFile "../sxfs/oracle_sxfs_connect.vbs"
  Call DBConnect(op_server,op_username,op_userpwd,op_dbp) 
    sqlsentence_count = "select count(*) from " & mid(sqlsentence,14,len(sqlsentence)-13)
    ExecuteSql(sqlsentence_count)
      a = Res("count(*)").value
   If a = 0 Then
    ReDim SqlArray(0)
    SqlArray(0)=""
  else
     dim row,m
    row = 0
     ExecuteSql(sqlsentence)
      Do while not Res.eof
     m=m & Res(SqlItem) & ";"
        Res.MoveNext
     row =  row + 1
      Loop
        rowcount = row
        SqlArray = Split(m, ";")
       'u=ubound(SqlArray)
        'ArrayCount = CStr(u) 'ArrayCount为SqlItem的个数
   End If    
 DBClose()
End Function

'Product：地市非税系统
'Module：更新数据库(update,insert)
'Author: Peter-Liao
Public Function SqlAlter(OracleType,SqlSentence)
   Select Case true
   Case OracleType="city"
        sqlqtp_connect = "select * from dbconnect where (op_type='city')"
           Call SqlQtp(sqlqtp_connect,"op_server")
            op_server = QtpParameter
   Call SqlQtp(sqlqtp_connect,"op_username")
   op_username = QtpParameter
   Call SqlQtp(sqlqtp_connect,"op_userpwd")
   op_userpwd = QtpParameter
   Call SqlQtp(sqlqtp_connect,"op_dbp")
   op_dbp = QtpParameter
   ExecuteFile "../sxfs/oracle_sxfs_connect.vbs"
   Call DBConnect(op_server,op_username,op_userpwd,op_dbp) 
   ExecuteSql(SqlSentence)
   DBClose()
   Case OracleType="qtp"
         ExecuteFile "../sxfs/oracle_sxfs_qtp_connect.vbs"
         DBConnectQtp()
         ExecuteSqlQtp(SqlSentence)
  DBCloseQtp()
   End Select
End Function