Handling Database with QTP

- 7/9/15 -- -webex training notes

 

          Handling db using QTP:

QTP uses ADO – activex Data Objects

     To connect to Database QTP can be connected to any database.

    Frequently used commands:

    ADO Object

    RecordSetObject

    ConnectionString

    Execute

    Recordsetobject. EOF

    Note: EOF --- End of File 

    Recordsetobject.Fields.Item

    

• ADO and Recordset objects are common for all DB.
• Connection String – is different for different DB.

           Note: Go to www.connectionstrings.com

• For Insert and Update Query we don’t need Recordset concept.

 

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 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

 

Source: http://www.qtpworld.com/index.php?cid=74
------------------Well this website has descripting programming and PMP stuff.



----- Without datasource creation, one cannot connect using the script. Will receive following error if you try to execute above mentioned connection commands.

get error "Data source name not found and no default driver specified"

Reason why you will get the errro is explained in the following link:

http://www.easysoft.com/support/kb/kb01039.html


But if  you following below steps(Creat data source) you could avoid the mentioned error:

Setting Up an ODBC Data Source For MS Access

	This example shows you how to set up a new ODBC connection to an MS Access database. The steps and screenshots will be similar but slightly different when you set up other supported databases. Iguana supports some databases, such as MySQL and Oracle, through their native interfaces (i.e. not through ODBC). In such cases, the Data Source name specified in the channel configuration only needs to be the name of the database itself and no ODBC data source needs to be configured.

Every channel in Iguana needs to be associated with a database. At minimum, this database is used for logging the HL7 messages that flow across the interface. In many cases, the database also acts as a source or target for data mappings. In cases where you want to use an ODBC Database API for your channel, you will need to ensure that you have configured an appropriate ODBC data source.

To set up a new ODBC data source using MS Access:

• Click Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator window appears.

ODBC Data Source Administrator Window

• Click the System DSN tab, and click Add to create a new data source. The Create New Data Source window appears.

Create New Data Source Window

• Select Microsoft Access Driver (*.mdb) and click Finish. The ODBC Microsoft Access Setup window appears.

ODBC Microsoft Access Setup Window

• Enter the Data Source Name. In this example we created a data source called TestChannel.

	Alternatively, you can choose an existing database by using the Select button. In addition you can access and configure other parameters, such as the Login name and Password fields, by clicking the Advanced button.

• (Optional) Enter a description of your data source.
• Under Database, click the Create button to create your database. The New Database window appears.

New Database Window

• Enter the Database Name (e.g. TestChannel.mdb).
• Choose where to save the database and click OK. A dialog window appears, confirming you have successfully created your database.

• Click OK to complete the data source setup. Your newly created database appears under System Data Sources as shown below.

System Data Sources

Now you can easily add and configure a new channel for Iguana. For details, see Creating a Channel if you are using Iguana 4.0 or later, or Configuring Channels if you are using an older version of Iguana.

This is just an example. Source: http://www.interfaceware.com/manual/setting_up_odbc_datasource.html


Some more on the MSAccess drivers:

http://answers.microsoft.com/en-us/office/forum/officeversion_other-access/64-bit-driver-for-dsn-for-ms-access-database/be8c0ad4-d8fd-48e0-9026-b95d84135820

How to fix the MSAccess 2007 driver unavailability issue for Win 64 bit:
http://stackoverflow.com/questions/6721702/windows-7-64-bit-odbc-drivers-for-ms-access-missing