Automation of Testing SQL queries - QTP? Other?

Posted on 2009-12-27
Last Modified: 2013-12-13
I've got a large buch of SQL queries just saved as text files. They are generally record counts, compating tables, looking for data anomolies, validating transformations that kind of thing. Been tasked with finding out if its possible to automate these scripts easily and if so what kind of tools are available to use. I've used QTP before and know that its possible to use database checkpoints and write some vb code to pass arguments etc, but I think this is mainly designed a UI tool rather than being able to run a batch of SQL scripts. Also there may be a licencing contstraint, so if I do suggest this tool, then has to be sold argument. Are there other easier/free tools out there? Is QTP gonna be the best, if so, why? cheers
Question by:chuchilla
    LVL 57

    Accepted Solution

    Yes.. You need to have a VB application or some application calling these set of queries and including your queries to this application to test it out..

    And if you are looking for free tools then this might be of interest to you..

    Else you can look at some other cheaper alternatives here:
    LVL 5

    Assisted Solution

    We have our queries externalized as well.  The queries however are stored in XML files and we have a custom application we wrote to exercise them.  For your situation it would be easy.  

    1.  Create a console application (or forms application)
    2.  Add a reader class to the application to read the text files in (you can set this to recurse using the .net Get Directories section
    3.  Create a custom Test object that has a query, bool pass / fail, and a string error result
    4  Once you have all the paths to the files - then read the files and parse each query into a test object and then add the test object to a collection
    5.  Create a test manager class to manage the execution of the class
    6.  Run a foreach loop to execute each test object and set the values in the test object based on the result (pass /fail, note any errors, etc)
    7.  Once complete you could export the test object collection to an xml file or send the results to the screen.

    Hope this helps.  

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
    Introduction to GIMP:  GNU Image Manipulation Program. It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now