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.
- Download ClassicsData.mdb here.
- Save it in C:\Temp\ClassicsDatabase.
- Launch the ODBC Administrator on your computer. (On Microsoft® Windows® XP, select Start > Control Panel > Administrative Tools > Data Sources (ODBC).)
- Click the System DSN tab, as shown in Figure 1.
Figure 1. System data sources
- Click the Add button.
- Select Microsoft Access Driver (*.mdb), as shown in Figure 2.
Figure 2. Selecting the driver
- Click the Finish button.
ClassicsAccessin the Data Source Name field, as shown in figure 3.
Figure 3. Setting up Microsoft Access
- Click the Select button.
- Browse to C:\Temp\ClassicsDatabase and select ClassicsData.mdb, as shown in Figure 4.
Figure 4. Selecting the database
- Click the OK button.
- 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
- 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:
- Browse to http://squirrel-sql.sourceforge.net/
- Select Download and Installation.
- Select Install jars (and source) of SQuirreL <version> for Windows/Linux/others.
- Select squirrel-sql-<version>-install.jar
- Save to desired location (for example, C:\Temp\Installs\SQuirreL SQL).
To install SQuirreL SQL, run the following command:
java -jar squirrel-sql-<version>-install.jar
- This will take you through a wizard to get things set up. You can simply take the defaults on each page.
- 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
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
Configuring a database driver
The following steps will take you through configuring a database driver in SQuirreL SQL:
- Click the Add (+) button in the Aliases window, as shown in Figure 8.
Figure 8. Aliases window
- Fill in the pertinent alias information, as shown in Figure 9.
Figure 9. Supply Name, Driver, and so on
Note: You only need to supply the following for your JDBC ODBC Bridge:
- The Name (call it
- The correct Driver (select the JDBC ODBC Bridge driver)
- The correct URL (add
ClassicsAccessin 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.
- The Name (call it
- Click the Test button.
- 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
- Click the OK button in the Connection Successful dialog, as shown in Figure 11.
Figure 11. Connection successful
- Click the OK button in the Alias window.
- You should now have an alias called: Classics Database, as shown in Figure 12.
Figure 12. Aliases
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:
- Double-Click Classics Database in Aliases list.
- Click the Connect button in the connection window, as shown in Figure 13.
Figure 13. Connecting to the database
- 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
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
- 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
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:
- Launch Rational Functional Tester.
- Select Configure > Configure Applications for Testing. This will open the Application Configuration Tool, as shown in Figure 17.
Figure 17. Edit the application information
- Click the Add button.
- Select the Executable or Batch File radio button, as shown in Figure 18.
Figure 18. Add the application
- Click the Next button.
- Browse to the squirrel-sql.bat file, as shown in Figure 19. This launches SQuirreL SQL.
Figure 19. Preparing to launch SQuirreL SQL
- Click the Finish button.
- Click the Finish button (in the Application Configuration Tool window, as shown in Figure 20).
Figure 20. Edit the configuration information
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.
- In the Rational Functional Tester area on developerWorks, get the resources you need to advance your skills with this tool.
- Browse the technology bookstore for books on these and other technical topics.
Get products and technologies
- Download a free trial version of Rational Functional Tester.