Extending IBM Rational Functional Tester with Open Source Tools: Part 1: Using SQuirreL SQL for Database Testing

Setting up Rational Functional Tester

The typical scenario to test the database side of your application is to perform some sort of insert or delete action within your application, and then verify that the database was properly updated. Because IBM® Rational® Functional Tester does not provide a native means to work with databases, SQuirreL SQL is a great tool to satisfy your database testing needs. It is an open-source tool written in Java™, which makes it an ideal candidate for Rational Functional Tester’s record and playback automation capabilities. Part 1 of this article shows you how to set up Rational Functional Tester, and Part 2 helps you create scripts.

Daniel Gouveia (dgouveia@us.ibm.com), Software Technical Sales Specialist, IBM 

Dan Gouveia is a sales engineer for IBM, primarily focusing is on Rational’s functional and performance testing tools. He works with customers on implementing these tools in the most effective manner, often dealing with topics such as keyword-driven testing, developing automation frameworks, etc.



26 December 2007

Also available in Chinese Vietnamese

Database testing

Many testers are interested in testing the database-side of their application. The typical scenario is that you perform some sort of insert or delete action within your application, and then verify that the database was properly updated. As test groups turn towards automation, this database testing becomes a natural desire. Unfortunately, IBM® Rational® Functional Tester does not provide a native means to work with databases.

SQuirreL SQL is a great tool to satisfy your database testing needs. Upon hearing this, you may develop a sudden case of "Yet Another Tool Syndrome". After all, it is in fact another tool that you do need. The good news is that it is an open-source tool. This gives you the ability to download and use this tool for free. Further, it does provide a level of support, in case you run into issues with the tool. Lastly, it is written in Java™. This makes it an ideal candidate for Rational Functional Tester’s record and playback automation capabilities.

This article represents an adaptation of a "getting started" tutorial that has helped customers adopt the use of SQuirreL SQL into their Rational Functional Tester automation. SQuirreL SQL provides a plethora of functionality for the aspiring database-guru. Nonetheless, the intent of this series is to keep things simple while ensuring that it conveys certain key concepts, such as running a query and verifying the result-set. It does not address the numerous remaining capabilities, leaving them for the exploration of inquiring minds like yours.

Please note that you should have Rational Functional Tester experience (at least record and playback) before performing the exercises in this article.


First things first: the prerequisites

Before getting started, make sure that you have Rational Functional Tester installed, licensed, and ready to run! This is pretty obvious, but quite critical for you to successfully complete the tasks in this article.

Rational Functional Tester

Launch your ODBC Administrator to confirm that you have a System DSN called ClassicsAccess. This should get set up when you install Rational Functional Tester, including IBM® Rational® Manual Tester. If you do not see it there, you can follow the steps below to acquire the Access database (ClassicsData.mdb) and set up an ODBC DSN for it. The following steps will allow you to acquire the ClassicsDatabase.mdb file and set up a System DSN, using your ODBC Administrator.

  1. Download ClassicsData.mdb here.
  2. Save it in C:\Temp\ClassicsDatabase.
  3. Launch the ODBC Administrator on your computer. (On Microsoft® Windows® XP, select Start > Control Panel > Administrative Tools > Data Sources (ODBC).)
  4. Click the System DSN tab, as shown in Figure 1.
Figure 1. System data sources
window with tabs
  1. Click the Add button.
  2. Select Microsoft Access Driver (*.mdb), as shown in Figure 2.
Figure 2. Selecting the driver
window with list
  1. Click the Finish button.
  2. Enter ClassicsAccess in the Data Source Name field, as shown in figure 3.
Figure 3. Setting up Microsoft Access
provide information in setup window
  1. Click the Select button.
  2. Browse to C:\Temp\ClassicsDatabase and select ClassicsData.mdb, as shown in Figure 4.
Figure 4. Selecting the database
window with navigation tree
  1. Click the OK button.
  2. Click the OK button (on the ODBC Microsoft Access Setup window)

    You should now see your ClassicsAccess System DSN in the list, as shown in Figure 5.
Figure 5. A list of system data sources
window with tabs
  1. You can click the OK button to close out of the ODBC Data Source Administrator.

Java and SQuirreL SQL

You will need to install Java V1.5. You can try Java V1.6, but it may produce inconsistent results. This tutorial was developed with V1.5. Prior versions of Java will not work with SQuirreL SQL.

Once you have installed and configured the proper version of Java, you will need to install SQuirreL SQL. The following steps will allow you obtain this software:

  1. Browse to http://squirrel-sql.sourceforge.net/
  2. Select Download and Installation.
  3. Select Install jars (and source) of SQuirreL <version> for Windows/Linux/others.
  4. Select squirrel-sql-<version>-install.jar
  5. Save to desired location (for example, C:\Temp\Installs\SQuirreL SQL).

To install SQuirreL SQL, run the following command:

  1. java -jar squirrel-sql-<version>-install.jar
  2. This will take you through a wizard to get things set up. You can simply take the defaults on each page.
  3. If you do have certain databases (for instance, DB2, Oracle, MySQL, and so on), there will be a section that asks if you want to install plugins to support those databases. Feel free to choose the ones that you want. The wizard will add extra support, specific to your databases, into the SQuirreL SQL install. This will be functionality that you can explore at a later time.

SQuirreL SQL configuration: Configuring Database Access

Once you have SQuirreL SQL installed, go ahead and launch it. You should configure it for the ClassicsData database. If the Help window appears (Figure 6), close it (or feel free to read through it).

Figure 6. SQuirreL Help window
window with topics and help text

You need to configure an alias, prior to accessing a database. Since this tutorial is using an Access database (.mdb) file (which has an associated ODBC DSN), you will want to set up the driver using the JDBC-ODBC Bridge driver, as shown in Figure 7.

Note: A blue check mark will appear next to drivers that were successfully loaded.

Figure 7. List of drivers
window with list

Configuring a database driver

The following steps will take you through configuring a database driver in SQuirreL SQL:

  1. Click the Add (+) button in the Aliases window, as shown in Figure 8.
Figure 8. Aliases window
blank window with buttons
  1. Fill in the pertinent alias information, as shown in Figure 9.
    Figure 9. Supply Name, Driver, and so on
    provide alias information in window
    Note: You only need to supply the following for your JDBC ODBC Bridge:
    1. The Name (call it Classics Database)
    2. The correct Driver (select the JDBC ODBC Bridge driver)
    3. The correct URL (add ClassicsAccess in place of <alias>, where <alias> = ODBC System DSN (Data Source Name).

      You do not need to provide a username and password for this ODBC System DSN.
  2. Click the Test button.
  3. Click the Connect button, as shown in Figure 10 (don’t worry about the User name and Password).
Figure 10. Connecting to the Classics Database
window with connection fields
  1. Click the OK button in the Connection Successful dialog, as shown in Figure 11.
Figure 11. Connection successful
window with light bulb icon
  1. Click the OK button in the Alias window.
  2. You should now have an alias called: Classics Database, as shown in Figure 12.
Figure 12. Aliases
blank window with icons

You are going to want to test out the Classics Database alias to verify that you can see the contents of the Access database (ClassicsData.mdb). To access your database:

  1. Double-Click Classics Database in Aliases list.
  2. Click the Connect button in the connection window, as shown in Figure 13.
Figure 13. Connecting to the database
window with connection fields
  1. Verify that you are now connected to the database (you should see information about your database similar to that shown in Figure 14).
Figure 14. Database connection information
window with navigation tree

Note: You may see a Session Startup dialog that discusses startup performance (Figure 15). You can simply close this. Feel free to explore the Alias Schema Properties on your own at a later point in time. Make sure to select the Don't show this message again checkbox.

Figure 15. Startup dialog
performance warning window
  1. After you verify your connection to the database (Figure 16), close the session (click the X in the upper right of the database session window).
Figure 16. Closing the database session window
red arrow pointing to the close icon

A perfect combination: Configuring Rational Functional Tester to use SQuirreL SQL

It is now time to configure Rational Functional Tester for use with SQuirreL SQL. To do so, follow the steps below:

  1. Launch Rational Functional Tester.
  2. Select Configure > Configure Applications for Testing. This will open the Application Configuration Tool, as shown in Figure 17.
Figure 17. Edit the application information
window showing application details
  1. Click the Add button.
  2. Select the Executable or Batch File radio button, as shown in Figure 18.
Figure 18. Add the application
window to select application type
  1. Click the Next button.
  2. Browse to the squirrel-sql.bat file, as shown in Figure 19. This launches SQuirreL SQL.
Figure 19. Preparing to launch SQuirreL SQL
window to select application or file
  1. Click the Finish button.
  2. Click the Finish button (in the Application Configuration Tool window, as shown in Figure 20).
Figure 20. Edit the configuration information
window with application details

Next Steps

This concludes the first article of this two-part series. The next article shows you how to create four scripts against SQL SQuirreL: three specific tasks and one shell to aggregate those tasks.

Download ClassicsData.mdb from the downloads section below.


Download

DescriptionNameSize
DownloadClassicsData.zip15 KB

Resources

Learn

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=276846
ArticleTitle=Extending IBM Rational Functional Tester with Open Source Tools: Part 1: Using SQuirreL SQL for Database Testing
publish-date=12262007