Working with DataBase in QTP - Understanding Connection and Recordset object

QTP Interacts with database creating an instance of ADODB object using ADO. ADO is a Microsoft technology and stands for ActiveX Data Objects and is a programming interface to interact and access data from a database.

Connection Object

Before a database can be accessed by QTP, An object for database connection has to be established.

Set adocon = createobject(“Adodb.connection”)

Once the object is created, we need to set connection string to connect to the database. We can define a connection string with details of database including database provider, database, and user credentials for access.

Some useful methods and properties of connection object are as follows:

Properties:

adocon.connectionstring – This property sets or returns the details used to create a connection to a data source. Includes details of driver, database,username and password.

Strconnection = "Driver=… "Server=svrnme;uid=username;pwd=password;"

For e.g to connect to a database for excel the strConnection would be like:

 strcon = "Driver={Microsoft Excel Driver (*.xls)};Dbqls="+xfilename +";ReadOnly=0;"

For details of connection strings, see www.connectionstrings.com

adocon.ConnectionTimeout – this defines the time  to wait for a connection to be established.

adocon.provider – This sets or gets connection provider name.

adocon.state – gives status whether connection is on or off.

Methods

adocon.open – opens a database connection based on the connection string provided. 

adocon.Open connectionstring,userID,password,options

adocon.Execute – execute the sql statement provided

adocon.execute “Select * from table”

adocon.close – This closes the adodb connection.

RecordSet Object:

 Once a connection has been established, we can create recordset object to hold a set of record from database. A recorset consists of records and column

Set rs = createobject(“”Adodb.recordset”)

Some useful methods and properties of RecordSet Objects are as follows:

Properties:

BOF property returns True  if the current record position is before the first record in the Recordset,

EOF property returns True if the current record position is after the last record in the Recordset, otherwise it returns False. For a empty recordset,i.e no records in the recordset or empty recordset, value of BOF and EOF is false. So the property can be used in a loop to validate RecordSet does not return any records.

MaxCount Property returns the maximum value of records to be returned in a recordset.
rs.MaxCount = 20 will return 20 rows of data in recordset.

Methods:

rs.cancel – cancels an existing execution.

rs.clone – returns a clone of existing recorset and assigns to an object

set rsclone = rs.clone

rs.Close - closes instance of recordset

rs.open – opens a recordset based on query specified.

rs.open sqlquery, adocon

where sqlquery is query executed and adocon is connection object.

rs.move – moves the pointer in a recordset by specified count as defined in numrec
rs.move numrec, start.

Also  movenext,moveprevious, movefirst, movelast can be used to move to specified location in recordset.

rs.fields.count gives number of items in the fields collection.

rs.field.item(i) returns specified item from the collection.