Skip to main content

Database Connections

Database Connections


What is adodb connection ?

The ADO(ActiveX Data Objects) Connection object is used to create a connection to a data source. Through this connection, you can access and manipulate a database.
What is adodb recordset?
 The ADO Recordset object is used to hold a set of records from a database table.To be able to read database data, the data should be loaded into a recordset.
⇒QTP Scripts for connecting to MS Access:
Option Explicit
Dim con,rs
 
Set con=createobject("adodb.connection") 
Set rs=createobject("adodb.recordset")

con.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=C:\mydatabase.mdb;Uid=Admin;Pwd=;"

rs.open "select * from emp",con

Do while not rs.eof
VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
VbWindow("Form1").VbButton("ADD").Click
rs.movenext
Loop

'Release objects'Release objects
Set rs= nothing
Set con= nothing

Note: The database we are using here is MS Access.Before running this script create a table in MS Acess.In the above script I used table called "emp" and column 'names as "v1" and "v2". "d:testdata.mdb" is path of the table which we created. The main use of this script is to use testdata of table(which is in  ' database) in the application. In the above script we are passing values from database to Textboxes in Windows Application.
⇒QTP Script for connecting to sqlserver: 
Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open"Driver={SQL Server};server=MySqlServer;uid=MyUserName;pwd=MyPassword;database=pubs"
rs.open "select * from emp",con

Do while not rs.eof
VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
VbWindow("Form1").VbButton("ADD").Click
rs.movenext
Loop

'Release objects'Release objects
Set rs= nothing
Set con= nothing
⇒QTP Script for connecting to oracle:
Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open "Driver={Microsoft ODBC for Oracle};Server=QTPWorld; Uid=your_username;Pwd=your_password;"
rs.open "select * from emp",con

Do while not rs.eof
VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
VbWindow("Form1").VbButton("ADD").Click
rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing
⇒QTP Script for connecting to MySQL:
Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open"Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=myDB;User=Uname;Password=Pwd;Option=3;"
rs.open "select * from emp",con

Do while not rs.eof
VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
VbWindow("Form1").VbButton("ADD").Click
rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing
⇒QTP Script for connecting to Excel:
Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

con.open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=C:\TestStatus.xls;Readonly=True"
rs.open "SELECT count(*) FROM [Status$] where Status = 'Failed' ",con

Msgbox rs(0)

'Release objects
Set rs= nothing
Set con= nothing


⇒QTP Script for connecting to Sybase: 
  
Option Explicit
Dim con,rs

Set con=createobject("adodb.connection")
Set rs=createobject("adodb.recordset")

' Open a session to the database
con.open"Driver={SYBASE SYSTEM 11};Srvr=myServerAddress;Uid=Uname;Pwd=Pwd;Database=myDataBase;"
rs.open "select * from emp",con

Do while not rs.eof
VbWindow("Form1").VbEdit("val1").Set rs.fields("v1")
VbWindow("Form1").VbEdit("val2").Set rs.fields("v2")
VbWindow("Form1").VbButton("ADD").Click
rs.movenext
Loop

'Release objects
Set rs= nothing
Set con= nothing
Labels:

Comments

Post a Comment

Popular posts from this blog

SSO with SAML login scenario in JMeter

SAML(Security Assertion Markup Language) is increasingly being used to perform single sign-on(SSO) operations. As WikiPedia puts it, SAML is an XML-based open standard data format for exchanging authentication and authorization data between parties, in particular, between an identity provider and a service provider. With the rise in use of SAML in web applications, we may need to handle this in JMeter. This step-by-step tutorial shows SAML JMeter scenario to perform login operation. First request from JMeter is a GET request to fetch Login page. We need to fetch two values ‘SAMLRequest’ and ‘RelayState’ from the Login page response data. We can do this by using  Regular Expression Extractor . These two values need to be sent in POST request to service provider. Refer below image to see how to do this. We will get an HTML login page as a response to the request sent in 1st step. We need to fetch values of some hidden elements to pass it in the next request. We can do this b
Post a Comment
Read more

Reading data from text file in SoapUI using groovy script

How to read data from text file in SoapUI. SoapUI Pro has some advance feature which is not in SaopUI as data fetching from external sources so in SoapUI we use Groovy script for that. Following are the peace of groovy script code for reading data from text file. 1. Reading all data from text file. //reading all txt file at once File file = new File("E://TestData.txt") fileContent = file.getText()                  log.info fileContent 2. Reading data line by line from text file. //reading text line by line File file1 = new File(" E://TestData.txt ") List textLine = file1.readLines() log.info textLine 3. Reading data randomly of any line from text file. //reading text randon line number File file2 = new File(" E://TestData.txt ") List textLine2 = file2.readLines() rowIndex  =  Math.abs(new Random().nextInt() % 4 + 1) log.info textLine2[rowIndex]
Post a Comment
Read more

VBScript Code - Function to convert CSV file into excel and viceversa in QTP using VBScript

We at times are required to convert excel files into csv to read as flat files and sometime require to convert a csv file into excel file to use excel features on the data.   Below function shows how to convert an csv file into excel file and vice versa. We can also convert to other formats based on constants Here constant value 23 is used to create a csv file and constant -4143 to save a file as xls file. Once the destination file is created, we can delete the source file as shown below.  In case of any issue in understanding the code, please add in comment section Call func_ConversionCSVExcel("E:\Test.csv", "E:\Test_converted.xls", "csvtoexcel") Public Function func_ConversionCSVExcel(strSrcFile, strDestFile, Conversion) on error resume next Set objExcel = CreateObject("Excel.application") set objExcelBook = objExcel.Workbooks.Open(strSrcFile) objExcel.application.visible=false objExcel.application.displayalerts=
Post a Comment
Read more