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 article 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.

You should find the following article useful. For added clarity, the scripts that get created in this article are available for your perusal. You can download them from the table at the end of this article.

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

Part 1 of this series showed you how to configure Rational Functional Tester for these scripting functions. This article will show you how to create and bundle scripts. This article assumes that you have completed the steps detailed in Part 1.

Back to top

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.

Back to top

Database test automation: Scripting against SQuirreL SQL

The following steps will take you through creating four scripts. The first three scripts are focused on specific SQuirreL SQL tasks. The last script is the shell script, aggregating the three scripts to execute in sequence.

First script

Record your first script against SQuirreL SQL: the SQuirreL_SQL_Connect script.

1. Click the Record button.
2. Provide the name of the script and click the Finish button to begin recording, as shown in Figure 1.

3. Select the Start Application button, select squirrel-sql – executable as your application, and click the OK button, as shown in Figure 2.

4. Double-Click the Classics Database alias (in the SQuirreL SQL window), as shown in Figure 3.

5. Click the Connect button (again, no User name or Password is needed, as shown in Figure 4).

6. Click Stop Recording button in Rational Functional Tester.

You should now have a script that looks similar to that shown in Listing 1.

                
import resources.SQuirrel_SQL_ConnectHelper;

/**
 *  Description		: XDE Tester Script
 *  @author	IBM Rational
 */
public class SQuirrel_SQL_Connect extends SQuirrel_SQL_ConnectHelper
{
/**
 *  Script Name		: SQuirrel_SQL_Connect
 *  Generated			: Jun 20, 2007 9:51:08 AM
 *  Description		: XDE Tester Script
 *  Original Host		: WinNT Version 5.1 Build 2600 (S)
 *
 *  @since 	2007/06/20
 *  @author	IBM Rational
 */
public void testMain (Object[] args)
{
		startApp(“squirrel-sql”);
	
			// Frame :  SQuirrel SQL Client Version 2.5
		listOfDatabaseAliasesThatCanBe().doubleClick(atText(“Classics Database”));
		connect().click();
}
}

Second script

Record your next script against SQuirreL SQL: the SQuirreL_SQL_Simple_Query script.

1. Provide the name of the script and click the Finish button to begin recording it, as shown in Figure 5.

2. Select Session > New SQL Worksheet, as shown in Figure 6.

3. Click in the SQL edit field and enter SELECT * FROM ORDERS, as shown in Figure 7.

Note: You may need to maximize your window and resize the RESULTS pane.

4. Click the Run SQL button (also shown in Figure 7).
5. Initiate the Verification Point and Action wizard, as shown in Figure 8.

6. Use the Object Finder tool to select the result-set in the SQuirreL SQL window, as shown in Figure 9.

7. Select Table Contents for the Data Value, provide VerifyOrdersResultSet for the Verification Point Name, and then click the Next button (as shown in Figure 10).

8. Select the first 5 columns (for data to test) and click the Finish button, as shown in Figure 11.

Note: Make sure that you have chosen Column Selection in the drop-down list. You can then select the first column (that is, click the first cell of data) and drag to the right.

9. Click the Stop Recording button in Rational Functional Tester.

You should now have a script that looks similar to that shown in Listing 2.

                
public class SQuirrel_SQL_Simple_Query extends SQuirrel_SQL_Simple_QueryHelper
{
/**
 *  Script Name		: SQuirrel_SQL_Simple_Query
 *  Generated			: Jun 20, 2007 9:54:46 AM
 *  Description		: XDE Tester Script
 *  Original Host		: WinNT Version 5.1 Build 2600 (S)
 *
 *  @since 	2007/06/20
 *  @author	IBM Rational
 */
public void testMain (Object[] args)
{

			// Frame :  SQuirrel SQL Client Version 2.5
		mainFrameMenuBar().click(atPath(“Session”));
		mainFrameMenuBar().click(atPath(“Session->New SQL Worksheet”));
		justToMakeGetToolTiptextToBeCa().click(atPoint(30,9));
		mainFrame().inputKeys(“{CAPSLOCK}SELECT * FROM ORDERS”);
		runSQLControlENTER().click();
		VerifyOrdersResultSetVP().performTest();

}
}

Note: Depending on the version of Rational Functional Tester that you are using, your verification point line may look like the following:
dataSetViewerTablePanelMyJTabl().performTest(VerifyOrderResultSetVP());

Third script

Record your final script against SQuirreL SQL: the SQuirreL_SQL_Closescript.

1. Provide the location and name of the script, then click the Finish button to begin recording, as shown in Figure 12.

2. In SQuirreL SQL, select File > Exit, as shown in Figure 13.

3. Click the Yes button in the confirmation dialog, as shown in Figure 14.

4. If you receive the "unsaved changes" dialog shown in Figure 15, just click No.

5. Click the Stop Recording button in Rational Functional Tester.

You should now have a script that looks similar to that in Listing 3.

                
public class SQuirrel_SQL_Close extends SQuirrel_SQL_CloseHelper
{
/**
 *  Script Name		: SQuirrel_SQL_Close
 *  Generated			: Jun 20, 2007 10:01:03 AM
 *  Description		: XDE Tester Script
 *  Original Host		: WinNT Version 5.1 Build 2600 (S)
 *
 *  @since 	2007/06/20
 *  @author	IBM Rational
 */
public void testMain (Object[] args)
{

			// Frame :  SQuirrel SQL Client Version 2.5
		mainFrameMenuBar().click(atPath(“File”));
		mainFrameMenuBar().click(atPath(“File->Exit”));
		
		
			//
		yes().click();

			//
		no().click();

}
}

Fourth script

Tying it all together, create the shell script: SQuirreL_SQL_Shell.

1. Right-Click your project in the Functional Test Project view in Rational Functional Tester, and then select Add Empty Script as shown in Figure 16.

2. Provide a location and name for the script, and then click Finish, as shown in Figure 17.

3. Create a new line after // TODO Insert code here // and click the empty line to place the cursor there.
4. Right-Click each of your SQuirreL SQL scripts in turn and choose Insert As callScript to add them to your new shell script, as shown in Figure 18.

Note: Be careful to add the scripts in the desired order.

Note: Be careful to add the scripts in the desired order.

You should now have a shell script that looks similar to that shown in Listing 4.

                
import resources.SQuirrel_SQL_ShellHelper;

/**
 *  Description		: XDE Tester Script
 *  @author	IBM Rational
 */
public class SQuirrel_SQL_Shell extends SQuirrel_SQL_ShellHelper
{
/**
 *  Script Name		: SQuirrel_SQL_Shell
 *  Generated			: Jun 20, 2007 10:09:19 AM
 *  Description		: XDE Tester Script
 *  Original Host		: WinNT Version 5.1 Build 2600 (S)
 *
 *  @since 	2007/06/20
 *  @author	IBM Rational
 */
public void testMain (Object[] args)
{
		callScript(“SQuirrel_SQL_Connect”);
		callScript(“SQuirrel_SQL_Simple_Query”);
		callScript(“SQuirrel_SQL_Close”);
	
}
}

Back to top

Showtime! Playing back the scripts

Now that you have created your SQuirreL Scripts, it is simply a matter of playing them back. A couple of things to note:

• When playing back scripts, you may notice some timing issues. Simply get around these by adding waitForExistence() method calls to certain objects. For instance, in the first script, add it to the database alias object. The line of code that I use looks like the following:
  
  listOfDatabaseAliasesThatCanBe().waitForExistence();
  
• You may need to add synchronization into your other two scripts. Basically, you want to make sure that if the first script leaves SQuirreL SQL processing data, the second script will wait until it is finished.

Back to top

Closing Thoughts: Where To Go From Here

It will benefit you to explore the extensive capabilities that SQuirreL SQL offers you. If the simple functionality you looked at today will suffice for you, all the better. However, you will probably find that there are some great things that you can do with this tool. What makes things nicer is that you will be able to automate the things you find using Rational Functional Tester.

As an automation engineer, you do not create test projects focused solely on database testing (unless, of course, that is all that you are doing). Instead, you wrap this kind of automation into your application scripting. There are a few ways that you can do this.

• One way is to make these scripts generic methods, meaning that they can pass in a variety parameters (such as SQL query strings) and dynamically verify the result set. You can then create custom Super Helper Classes and put these generic methods in them. This allows you to simply call the necessary method (for example to query on a table) from your test scripts.
• Another, perhaps easier, way is to simply keep these as scripts. You should still make them generic (for instance, accepting different parameters, and so on), but they are called from shell scripts (similar to the shell script you created in this exercise).

How you do these things is entirely up to you. The end result in any case is that you now know how to successfully automate your database testing with Rational Functional Tester and SQuirreL SQL.

Back to top

Download

Information about download methods

Resources

Learn

• 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.
  
  

Discuss

• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the author

Rate this article

Comments

Back to top

Table of contents

• Database testing
• First things first: the prerequisites
• Database test automation: Scripting against SQuirreL SQL
• Showtime! Playing back the scripts
• Closing Thoughts: Where To Go From Here
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: Rational Functional Tester
• Briefing: Quality driven software delivery
• Buy: Rational Functional Tester

Dig deeper into Rational on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers