Reference › Test Case Reference › Test Step Descriptions › Other Transaction Steps › SQL Database Execution (JDBC)

SQL Database Execution (JDBC)

The SQL Database Execution step lets you connect to a database using JDBC (Java Database Connectivity) and make SQL queries on the database.

Full SQL syntax is supported, but your SQL is not validated. The SQL is passed through to the database where it is validated. If you get an SQL error, it is captured in the response. You can assert on the error. Verify that the SQL is valid for the database manager you are using.

Prerequisites: The JDBC driver appropriate for your database must be on the DevTest classpath. You can place the driver JAR file in the hot deploy directory. The DevTest classpath includes the Derby client driver, so you do not need to re-add it.

Parameter Requirements: Have the name of the JDBC driver class, the JDBC URL for your database, and a user ID and password for the database. You also must know the schemas for the tables in the database to construct your SQL queries.

1. Enter the following parameters in the SQL Database step editor:

    

   Connection Info:

   JDBC Driver

   Enter or select the full package name of the appropriate driver class. Standard driver classes are available in the drop-down list. You can also use the Browse button to browse the DevTest class path for the driver class.

   Connect String

   The connect string is the standard JDBC URL for your database. Enter or select the URL. The JDBC URL templates for common database managers are available in the drop-down list.

   Max Rows to Fetch

   Enter the maximum number of rows you want returned in the result set. This field is required. Enter -1 for unlimited rows.

    

   Execution Info:

   User ID

   Enter a user ID (if the database requires it).

   Password

   Enter a password (if the database requires it).

   Keep Connection Open

   If this option is selected, the database connection that is opened the first time that the step executes is cached. That database connection is then closed when garbage collection happens for the step. If Keep Connection Open is not selected, the connection is closed each time that the step executes.

   Returns Result Set

   Select this check box if your query results in a Result Set being returned; that is, a SELECT type query. Leave cleared for an UPDATE, INSERT, or DELETE. If this check box is set incorrectly, your query causes an error.

   If SQL error

   Select the step to redirect to if an error occurs.

    

   To communicate with the local demo server, use:

   JDBC Driver

   org.apache.derby.jdbc.ClientDriver

   Connect String

   jdbc:derby://localhost:1527/reports/lisa-reports.db

   User ID

   sa

   Password

   sa

2. After you have entered the database connection information, including the user ID and password (if necessary), click Test Connection to test your connection.

   If the information is correct, a success message in a window appears. Otherwise an error message appears.

3. You are now ready to enter your SQL statement in the lower window.

   Properties can be used in your SQL. DevTest substitutes the parameter before passing the SQL string to the database.

   The JDBC step supports stored procedure calls. Basic data types (strings, numbers, dates, Boolean) are supported as arguments that a stored procedure uses as input and returns. Click Add to add a parameter. You cannot edit the numbers in the Parameter column. As you add, delete, and move rows, the numbers in the Parameter column are automatically renumbered.

   The JDBC step can also use JDBC prepared statements. You can use question marks in a SQL statement and can add named {{properties}}. This ability allows you to avoid being concerned about the type of the argument or escaping single quotation marks in parameter values. A statement "insert into MYTABLE(COL1,COL2) values (?, ?)" with a reference to {{col1}} and {{col2}} is easier to understand. The type and escape characters are automatically converted.

   To include a null in your statement, use this syntax: {{<<NULL>>}}.

4. After you have created the SQL query, click Test/Execute SQL to execute the query.

   A message indicates the result status.

5. Click OK.

   Your results display in the Result Set tab.

6. You are now ready to create filters and assertions on the result set.

   The icons on the bottom of the Result Set tab provide easy access to the following filters and assertions:

   Get value for another value in a Result Set Row

   You select a search field cell, a value field filter, and enter a property name. If the cell value in the search field is found, the value in the Value field in that row is set as the value of the property that is entered.

   Parse Result for Value filter

   The value in the selected cell is set as the value of the property that is entered.

   Result Set Contents Assertion

   The value in the chosen field (column) is compared to the regular expression that is entered.

   For more information about these and other filters and assertions appropriate for result sets, see Filter Descriptions and Assertion Descriptions.

The SQL Database Execution step has a default name using this convention: JDBC SQLfunction tablename. The supported functions in step name defaults are select, insert, delete, update, and perform. You can change step names at any time.