QTP 数据库相关

 QTP访问SQL数据库

Dim conn,res

Set conn = createobject("adodb.connection")
connstr =  "Provider=SQLOLEDB.1;Password=Password01!;Persist Security Info=True;User ID=sa;Initial Catalog=PCRM2;Data Source=ws-gisgtssts-03\paypaltestserver"
conn.Open connstr
Set res = createobject("adodb.recordset")

res.CursorLocation = 3 'adUserClient
res.CursorType = 2  'adOpenDynamic

strSQL = "select count(CreatedOn) as Expr1 from TaskBase"
res.Open strSQL,conn

While not res.EOF
    msgbox "val1: " & res("Expr1")     ' res.Fields("Expr1").Value 
    res.MoveNext
Wend

res.Close
Set res = nothing
conn.Close
Set conn = nothing

 

Dim conn,rowsAffected

Set conn = createobject("adodb.connection")

connstr =  "Provider=SQLOLEDB.1;Password=Password01!;Persist Security Info=True;User ID=sa;Initial Catalog=student;Data Source=HRC-OV9RO6JPTEH"
conn.Open connstr

delStr = "delete from stuScore where stuNum = '1001'"

conn.Execute delStr,rowsAffected

If rowsAffected = 0 Then
    msgbox "no rows were deleted"
else
    msgbox  rowsAffected & " row(s) were deleted"
End If

conn.Close
Set conn = nothing

 利用ADO自动化组件连接Excel，并运行查询语句

'强制变量声明
 Option Explicit
 '声明conn数据库链接对象和rs结果集对象
 Dim conn,rs
'声明数据库链接字符串
 Dim connectionString 
 connectionString ="DSN=Excel Files;DBQ=C:\readXML\Book1.xls;"
'声明sql执行语句
 Dim sqlString 
 sqlString = "select * from [Sheet1$]"
'创建ado组件对象
 Set conn = CreateObject("adodb.connection")
 '打开数据库
 conn.Open connectionString
 '执行sql语句并获得结果集
 Set rs = conn.Execute(sqlString)
'遍历结果集中的username字段的数据
 While Not rs.EOF
     MsgBox(rs.Fields("username").Value)
 rs.MoveNext
 
Wend
'关闭结果集
 rs.Close
 '关闭数据库
 conn.Close
 
'释放对象
 Set sqlString = Nothing
 Set connectionString = Nothing
 Set rs = Nothing
 Set conn =Nothing

 QTP访问Oracle数据库

Dim rs,sq,pkey
set conn=createobject("adodb.connection")
set rs=createobject("adodb.recordset")'需要安装Oracle客户端connStr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=bpo30;Data Source=bpo30;Password=bpo30;Host=172.17.5.6;Port=1521;" 
conn.Open connStr sql="select  *  from staff"
rs.Open sql,conn
rs.MoveFirst
Do While rs.Eof<>true
Msgbox rs.Fields(0)
rs.MoveNext
Loop
rs.close
set rs=nothing
conn.close
set conn=nothing

 

相关：

Dim res,cmd,sql
Set res = createobject("adodb.recordset")
Set cmd = createobject("adodb.command")
Set conn = createobject("adodb.connection")

connStr = "Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=bpo30;Data Source=bpo30;Password=bpo30;Host=172.17.5.6;Port=1521;"

cmd.ActiveConnection = connStr
conn.Open connStr
cmd.CommandType = 1
sql = "select count(*) from staff"
cmd.CommandText = 1
Set res = conn.Execute(sql)
msgbox res(0)

res.Close
Set res = nothing
Set cmd.ActiveConnection = nothing
Set cmd = nothing