Database Testing using HP-UFT and CA-LISA

   It is considered always better to test the data and perform database validation. Due to the defects in the store procedures, which corrupt the data, truncate or dump the data in incorrect column of the table. When we try to view the same from GUI, it fails as the information is incorrect or sometime even not available. Many time the test team had to go to the database level to perform the database validation. This requires the tester to have knowledge and understanding of database schema and how to create and execute SQL queries. If you are doing database validation you must be aware of what kind of pain it is to create and maintain those scripts. Each time the values are to be pulled from screens, XML and manually passed to SQL queries. For few records its feasible, but imagine if you have to validate thousands of records and that to on regular basis. Recently I had an opportunity to check the features of HP UFT. UFT can be utilized to perform the database validation but the cost involved is very high, but if you already have UFT or LISA below is the brief of how their approach are to achieve this.
 HP-UFT:
Pre-requisite for UFT is to install database client. Eg. If you are using Oracle as your database, first you will have to install Oracle Client on the system where you UFT is available. This is required to connect to the database and later execute SQL queries.
Step 1. Start UFT and create sample test case. On the canvas, follow step are required to connect to database
Step 2. Database -> Open Connection
Step 3. Database -> Select Data
Step 4. Database -> Execute Command
These are the steps which are required each time. For validation there are no in-build assertions which can be utilized. You will have to rely on checkpoint and will have to parameterize the expected value.
Step 5. To pull the records from database follow steps 1 & 2. Use write to file step to dump the records to file. Here all the records are written in tags eg. '<'ROW'>''<'IDENTIFY_DATA'>''' To avoid this you will have to do some scripting. If you wish to perform validation on the values from this file again it’s difficult as the file is not an XML file and the data is not arranged properly
Problem:
Difficult to add database validation.
Mapping of external data is point to point and takes effort to update / enhance if required
When data is pulled it is in incorrect XML format which is difficult to validate or use in other test cases
 CA-LISA
Pre-requisite for LISA is to add the database .jar file in LISA lib/hot deploy folder.
Step 1. In LISA add JDBC step.
        a. Select / Add JDBC driver from the dropdown. This list is available because you have added .jar file of the database
        b. Enter the connection string in the text box
        c. Enter User name & password
        d. Enter the SQL query which you want to execute
        e. You are now ready to add filters and assertions on the result set.
To pull the records from database simple add filter 'Write Properties to File' to the same step and all the values will be available in the mention file.
If you compare both the approach of the tools you will notice that in LISA you can achieve the same in just 1 step.
Problem: Only problem observed is to get the correct .jar file for database connectivity.
    Overall compared to above mentioned two tool, database validation using LISA seems to be far easy. The same can be used as test data for other test cases. As LISA framework is built on properties it is very easy to use the captures values through properties in complete LISA project.

You are welcome to provide your thoughts on the above.