Database Handling in QTP

When we talk about Database handling in QTP, there are few standard actions/operations on which we are actually focusing on and these operations are generally defined only after analyzing multiple factors like type of validations, project scope, framework approach etc.

As per a very generic approach the very first thing is to hit the database, there after fire the query, hence fetch the query results and finally disconnect from the database.

Below mentioned are the basic steps to be followed in the QTP script to access the data:

1. Create an object of ADODB.
2. Define the connection string.
3. Open the connection with the database.
4. Execute the query.
5. Access the data using the recordset object.
6. Close the database connection.
7. Release the memory occupied by the objects.

Now we will be taking each of the above mentioned steps in script level details.

1. Create an object of ADODB.

      ----------------------------------------------------------------------------

                          Set objDatabase = CreateObject("ADODB.Connection")

----------------------------------------------------------------------------

ActiveX Data Objects for Data Base (ADODB) is a collection of methods to access the data from the database for Applications. A Connection object represents a physical connection to a data store. 

2. Define the connection string.

Connection strings are simply string variables which contain database connection information.  Connection String related to specific connection can be set either with or without the DSN (Data Source Name).

In Case of DSN –

You create the DSN depending upon what you want to fetch the data from like SQL Server, CSV, Excel, MSAccess etc. and it also depends upon the drivers present in your workstation. Say for example DSN for Ms-Access is created with name as “MyAccessDSN” for a pre specified database selected at a known location on the same workstation. You will write the command as:

       -----------------------------------------------------------------

                     objDatabase.ConnectionString = "DSN = MyAccessDSN"

  -----------------------------------------------------------------            

In case you don’t want to create a DSN --

Connection String will now contain the complete information of what is contained in DSN. It has a benefit over DSN, that the connection since it can contain the complete network path of the database, so can work on all systems. But in case of DSN, that has to be present in the machine when you are using it. Again taking that database is on MS-Access – 

----------------------------------------------------------------

objDatabase.ConnectionString = "Driver={Microsoft Access 

                                                              Driver(*.mdb)};
                        Dbq=C:\MyDatabase.mdb;Uid=Admin;Pwd=;"

----------------------------------------------------------------

3. Open the connection with the database.

Once the connection string has been set, next step goes towards the opening of the connection. The Open method is used to establish (open) a physical connection to a data source. Once the connection is live, you can execute commands against the data source. 

      -------------------------------------------

                            objDatabase.Open

      -------------------------------------------

      

4.  Execute the query.

Write your query in a string variable and execute that particular query. It will return a recordset object as shown below.

------------------------------------------------------------------------

        strQuery = " SELECT Field1, Field2 FROM  Table1 "

                         

        Set objRecordSet = objDatabase.Execute(strQuery) 

-------------------------------------------------------------------------

5. Access the data using the recordset object.

Recordset object created in the last step represents the entire set of records from a base table or the results of an executed command. One important thing not to miss here is, at any point of time, the Recordset object refers to only a single row of the record within the set as the current record. 

In general for looping down till the end through the recordset we could use the following snippet :

   ----------------------------------------------------

           Do While objRecordSet.EOF <> True 

               sValFetched = objRecordSet.Fields(Field1).Value
               'Perform operation on the Records

           Loop    
    ----------------------------------------------------------

Where, EOF (End Of Fields) will Return true if the current record position is after the last record,
otherwise false.

and, sValFetched will have the data picked from the database record.

6. Close the database connection.

Once all the activities have been carried out on the connection object, we should close the open connection with the database.

                          ----------------------------------------

              objDatabase.Close

                          -----------------------------------------

7. Release the memory occupied by the objects.

The final should be step is to free up the memory allocated to the connection and the recordset variables.

                                 --------------------------------------------

               Set objDatabase = Nothing
                                 ---------------------------------------------

Keep Blogging . . .   :)

--

# Hims