VB Script can be used to establish db connection. This is very useful in QTP for writing db test cases. DB connection can be established for both SQL Server Authentication as well as Windows Authentication -
#########################################
Function Func_dbConnect
On Error Resume Next
'Creating the Connection Object
set cn = CreateObject("ADODB.Connection")
'Setting the Data Source
strCS = "Driver={SQL Server}; Server=1.1.1.1; Database=GSBREG_021010; UID=us; PWD=us;"
'Open Connection
cn.Open strCS
If (Err.Number<>0) Then
MsgBox("Login Failed")
End If
Set Func_dbConnect = cn
End Function
Sub ExecuteSQLQuery
Set cn = Func_dbConnect()
'Creating RecordSet Object
Set rs = CreateObject("ADODB.recordset")
rs.CursorType = 2
'SQL query
FetchAcademicLevels = "Select even_id from allocation_event"_
&" where allocation_name = 'myname'"
Set rs = cn.Execute(FetchAcademicLevels)
If(rs.EOF) Then
MsgBox "No Records"
Else
For Each x in rs.Fields
MsgBox x.name
MsgBox x.value
Next
rs.MoveNext
End If
rs.Close
cn.Close
End Sub
ExecuteSQLQuery()
#########################################
For Windows Authentication Connection String should be -
#########################################
strCS = "Driver={SQL Server}; Server=171.64.216.197; Database=copernicus_gsbreg_b2b_new; Trusted_Connection=Yes"
#########################################
#########################################
Function Func_dbConnect
On Error Resume Next
'Creating the Connection Object
set cn = CreateObject("ADODB.Connection")
'Setting the Data Source
strCS = "Driver={SQL Server}; Server=1.1.1.1; Database=GSBREG_021010; UID=us; PWD=us;"
'Open Connection
cn.Open strCS
If (Err.Number<>0) Then
MsgBox("Login Failed")
End If
Set Func_dbConnect = cn
End Function
Sub ExecuteSQLQuery
Set cn = Func_dbConnect()
'Creating RecordSet Object
Set rs = CreateObject("ADODB.recordset")
rs.CursorType = 2
'SQL query
FetchAcademicLevels = "Select even_id from allocation_event"_
&" where allocation_name = 'myname'"
Set rs = cn.Execute(FetchAcademicLevels)
If(rs.EOF) Then
MsgBox "No Records"
Else
For Each x in rs.Fields
MsgBox x.name
MsgBox x.value
Next
rs.MoveNext
End If
rs.Close
cn.Close
End Sub
ExecuteSQLQuery()
#########################################
For Windows Authentication Connection String should be -
#########################################
strCS = "Driver={SQL Server}; Server=171.64.216.197; Database=copernicus_gsbreg_b2b_new; Trusted_Connection=Yes"
#########################################
Comments
Post a Comment
No spam only genuine comments :)