---
title: 'UFT GUItest - Connect to DB'
disqus: hackmd
---

## UFT GUItest - Connect to DB

* 讀取資料庫內容
* Micro Focus UFT One 15.0
* ADODB has 4 properties or methods with which we can work with the databases
1. ADODB.**Connection** − Used to establish a connection to the Database

2. ADODB.**Command** − Used to execute a SQL command(Queries or Stored Procedures)

3. ADODB.**Fields** − Used to fetch a particular column from a record set after executing a query/stored proc

4. ADODB.**Recordset** − Used to fetch data from a database
* REF：[QTP-Accessing Databases](https://www.tutorialspoint.com/qtp/qtp_accessing_databases.htm) , [UFT-connection to oracle](https://community.microfocus.com/t5/UFT-One-User-Discussions/UFT-Connection-to-Oracle-Database/td-p/242131)

## :rocket:UFT output value
step1:

step2:

step3:

step4:建立資料來源，與資料庫連線

## :rocket:ADODB.Command
* delete特定資料

Query = "DELETE FROM xxx WHERE xxx"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = "Provider=sqloledb; Data Source=127.0.0.1; Initial Catalog=DB_Name; user id=admin; password='123456';"
cmd.CommandText = Query
cmd.Execute RecordsAffected
Set conn = Nothing
If Err.Number > 0 Then
	Print "erroe msg -- " & Query
End If
Print "Records Affected: " & RecordsAffected

## :rocket:ADODB.RecordSet
* 方法一：select特定資料

Dim objConnection 
Dim objRecordSet 

Set objConnection = CreateObject("ADODB.Connection")   
Set objRecordSet = CreateObject("ADODB.Recordset")     
 
Dim DBQuery 
DBQuery = "Select NAME from dbo.EMPLOYEE where AGE = 29"
 
objConnection.Open "Provider = sqloledb.1;Server =.\SQLEXPRESS;
User Id = sa;Password=Password123;Database = Trial"
 
objRecordSet.Open DBQuery,objConnection
 
Value = objRecordSet.fields.item(0)				
msgbox Value

objRecordSet.Close        
objConnection.Close		
 
Set objConnection = Nothing
Set objRecordSet = Nothing

* 方法二：select特定資料

Query = "SELECT xxx,xxx,xxx FROM xxx"
connection = "Provider=sqloledb;Data Source=127.0.0.1;Initial Catalog=DB_Name;user id=admin;password='123456';"
Set rs = CreateObject("ADODB.RecordSet")
rs.Open Query, connection, adOpenForwardOnly
On Error Resume Next
rs.MoveFirst
Do While Not rs.EOF
	Print "output data -- " & rs(0) & "," & rs(1) & "," & rs(2)
	rs.MoveNext
Loop
rs.Close
Set rs = Nothing

## Appendix and FAQ

:::info
**Find this document incomplete?** Leave a comment!
:::

###### tags: `UFT`,`GUItest`