SQLUnit Tags

SQLUnit User Guide
<<< Previous		Next >>>

The sqlunit Tag

Description

The root tag for an SQLUnit test specification.

Syntax

sqlunit ::= ((connection)+, (setup)*, (test | batchtest | diff)+, (teardown)*)

Parent Elements

None. This is the top level element.

Attributes

None

Nested Elements

Table 1. Nested Elements for sqlunit

Name	Description	Required
connection	Specifies one or more database Connections that need to be instantiated for the test to run.	Yes, one or more.
setup	Specifies any setup tasks that need to happen before all the tests in the suite are run.	No
test	Specifies a test that needs to run in this suite.	Either one or more of test, batchtest or diff must be specified
batchtest	Specifies a test that uses the JDBC batching functionality.	Either one or more of test, batchtest or diff must be specified
diff	Specifies a test that compares the results generated by two SQL or stored procedure calls	Either one or more of test, batchtest or diff must be specified
teardown	Specifies any tasks that need to happen after all the tests in the suite are run	No

The setup Tag

Description

The setup tag specifies operations that need to be carried out once before all the tests defined in the XML file. Typical tasks may include dropping and recreating the test database and tables, or deleting and inserting test data into the tables. A setup operation can be specified either by zero or more nested sql tags or by a single nested include tag which specifies an external file containing the setup SQL to execute. It can also contain set tags to setup variables to be used later for the test, or foreach tags which can be used to execute a single SQL multiple times, such as insert SQL statements.

Syntax

setup ::= ((set)*, (((sql)*, (foreach)*) | (include)*))

Parent Elements

sqlunit

Attributes

None

Nested Elements

Table 2. Nested Elements for setup

Name	Description	Required
set	Sets initial values for SQLUnit variables.	No
sql	Specifies the SQL statement to be executed with or without replaceable parameters.	No
foreach	Contains bulk SQL statements, such as inserts which are executed within the foreach tag.	No
include	Specifies an external file which contains the SQL statements to execute during the setup phase.	No

Examples

A simple setup to delete rows from a table

 <setup>
   <sql><stmt>delete from foo where 1=1</stmt></sql>
 </setup>

The batchresult Tag

Description

The batchresult element specifies the expected result from running either the statements specified in a batchsql or batchcall tag

Syntax

batchresult ::= ((updatecount)*)

Parent Elements

batchtest

Attributes

Table 3. Attributes for batchresult

Name	Description	Required
expected-count	Specifies the number of rows expected to be updated as a result of executing the batchsql or batchcall element. If the expected-count is specified, then the number of updatecount elements will not be counted for determining the test's success	No
failed-at	Specifies which statement in the batch is expected to fail when running the test. The index is zero-based	No

Nested Elements

Table 4. Nested Elements for batchresult

Name	Description	Required
updatecount	Specifies in the body of the element the number of rows expected to be modified by the SQL at the corresponding position (zero-based) in the batch	Yes, unless either or both expected-count and failed-at are specified

Examples

A typical batchresult element

 <batchresult>
   <updatecount>1</updatecount>
   <updatecount>1</updatecount>
 </batchresult>

The call Tag

Description

The call tag is used to describe a call to a stored procedure or function. A stored procedure differs from a standard SQL call in that it can return multiple result sets in a single result. Not all databases support this functionality in the same way. Stored procedures are called within Java code using the standard call syntax that looks like {call [?=]stored_procedure_name([?,...])}. This is also the way it should be specified to SQLUnit. SQLUnit uses the CallableStatement to execute this call

Syntax

call ::= (stmt, (param)*)

Parent Elements

test diff foreach setup teardown

Attributes

Table 5. Attributes for call

Name	Description	Required
id	An internal id for the stored procedure call	No
connection-id	Used to specify a particular connection when multiple Connections are specified in a given test. If not specified, SQLUnit will try to use the default Connection, if one exists	No

Nested Elements

Table 6. Nested Elements for call

Name	Description	Required
stmt	Specifies the actual stored procedure call in JDBC format described above in the body of this element	Yes
param	A param element specifies a single replaceable parameter specified for the stored procedure call.	Zero or more

Examples

Call to Stored Procedure dept_id=AddDept(dept_name)

 <call>
   <stmt>{? = call AddDept(?)}</stmt>
   <param id="1" type="INTEGER" inout="out">${deptId}</param>
   <param id="2" type="VARCHAR">Information Technology</param>
 </call>

The set Tag

Description

The set tag allows declarations of variable names and associating them with values within the context of a single SQLUnit run. SQLUnit stores these variables and their values in an internal symbol table. Variables need not be pre-declared in order to be used. A variable is usually created in the symbol table by being assigned the appropriate value based on context, the first time it is referenced in the XML file. Subsequent references to the variable will return the value of the variable.

Syntax

set ::= ((sql|call),result)?|(constructoryArgs*,methodArgs*)?

Parent Elements

setup prepare

Attributes

Table 7. Attributes for set

Name	Description	Required
name	Specifies the name of the SQLUnit variable. A variable must always be specified like ${varname}. Attempts to specify variables like varname or $varname will result in syntax error.	Yes
value	Specifies the initial value of the variable. The value must be specified. In cases where you do not know the value, you can initialize it with a default value, or not invoke the set tag to declare the variable. When the variable is first encountered in SQLUnit, it will implicitly create an entry for that variable in the symbol table.	Not required if nested elements are specified.
static	Specifies whether the method call is static. Valid values are true and false.	Required if the class and method attributes are also supplied
class	Specifies the class name whose method has to be invoked to populate the variable	Only required if the method attribute is also supplied
method	Specifies the method name in the specified class to invoke.	Only required if the class attribute is supplied

Nested Elements

Table 8. Nested Elements for set

Name	Description	Required
sql	Specifies a SQL statement to invoke which will populate the variables in the result.	Only required if there is no value attribute in the set tag. A call tag can also be specified here instead.
call	Specifies a stored procedure call to invoke which will populate the variables in the result.	Only required if there is no value attribute in the set tag. A sql tag can also be specified instead.
result	Specifies a result tag with variables in the col tag body. The variables will be populated from the values returned by the SQL or stored procedure call.	Only required if there is either a sql or a call tag.
constructorArgs	Specifies arguments to a constructor of a non-static method if that is being used to populate the variable to set.	Only required if we want to invoke a non-static method
methodArgs	Specifies arguments to the method to be executed to populate the variable to be set	Required if we want to invoke a method to set the variable

Examples

Setting a variable ${var} to 14

 <set name="${var}" value="14" />

Setting variable ${myquery.col1} from SQL

 <set name="${myquery}">
   <sql><stmt>select col1 from mytable where col2=45</stmt></sql>
   <result>
     <resultset id="1">
       <row id="1">
         <col id="1" name="c1" type="INTEGER">${col1}</col>
       </row>
     </resultset>
   </result>
 </set>

Setting variable ${var} from a method call return

 <set name="${var}" class="java.lang.System" static="true"
     method="getProperty">
   <methodArgs>
     <arg name="key" type="java.lang.String"
     value="property" />
   </methodArgs>
 </set>

The jndi Tag

Description

A wrapper tag to group together JNDI properties that are needed to instantiate an InitialContext for the JNDI DataSource lookup.

Syntax

jndi ::= ((arg)*)

Parent Elements

connection dynamicsql methodinvoker

Attributes

None

Nested Elements

Table 9. Nested Elements for jndi

Name	Description	Required
arg	A tag that specifies the name and value of a single JNDI property.	One or more arg elements are required.

Examples

A typical JNDI tag for getting a DataSource from JBoss

 <connection>
   <datasource>jdbc/myDSN</datasource>
   <jndi>
     <arg name="java.naming.factory.initial"
       value="org.jnp.interfaces.NamingContextFactory" />
     <arg name="java.naming.provider.url" 
       value="jnlp://localhost:1099" />
     <arg name="java.naming.factory.url.pkgs" 
       value="org.jboss.naming" />
   </jndi>
 </connection>

The methodinvoker Tag

Description

The methodinvoker tag allows the caller to invoke methods in Java code and check to see if they complete without exception. These methods are generally, but need not be, JDBC specific. Any objects that are returned from the methods are ignored. The tag only verifies that the method ran successfully, and if not, it threw an expected exception.

Syntax

methodinvoker ::= ((constructorArgs)*, (methodArgs)*)

Parent Elements

test

Attributes

Table 10. Attributes for methodinvoker

Name	Description	Required
static	If set to true, specifies that the method being invoked is static. Valid values are true and false.	No, default is false.
class	Specifies the full class name of the class in which the method is defined.	Yes
method	Specifies the method name to invoke.	Yes

Nested Elements

Table 11. Nested Elements for methodinvoker

Name	Description	Required
constructorArgs	Wraps a set of arg elements which specify the arguments, if any, that the constructor will need.	Not required for static methods, or if the class has a default no-args constructor.
methodArgs	Wraps a set of arg elements which specify the arguments, if any, that the method will need.	Not required if the method takes no arguments.

Examples

A simple methodinvoker tag without method arguments

 <methodinvoker class="net.sourceforge.sqlunit.utils.TestArgument"
     method="toString" >
   <constructorArgs>
     <arg name="c7" type="java.lang.Double.TYPE" value="0.2" />
   </constructorArgs>
 </methodinvoker>

The teardown Tag

Description

The teardown tag specifies SQL operations that need to be carried out after all the tests in the XML file are done. If SQLUnit detects a test failure, it will skip the remaining tests and invoke the teardown operations before exiting. Typical tasks include dropping the test database or deleting data from test tables.

Syntax

teardown ::= (((sql)*, (foreach)*) | (include)*)

Parent Elements

sqlunit

Attributes

None

Nested Elements

Table 12. Nested Elements for teardown

Name	Description	Required
sql	Specifies the SQL statements to be executed, with or without replaceable parameters, that should executed on teardown.	No
foreach	Contains bulk SQL statements such as SQL DELETE statements, which will be executed from within a foreach tag.	No
include	Specifies an external file which contains the SQL statements to be executed on teardown.	No

Examples

Dropping some test tables on teardown

 <teardown>
   <sql><stmt>drop table testdata</stmt></sql>
   <sql><stmt>drop table testdata2</stmt></sql>
 </teardown>

The foreach Tag

Description

The foreach tag allows for simple bulk substitution by replacing a named parameter in a SQL statement with values from a supplied list or values generated by incrementing a counter. The syntax is similar to FOR and FOREACH statements found in most programming languages.

Syntax

foreach ::= ((sql)*)

Parent Elements

setup prepare teardown

Attributes

Table 13. Attributes for foreach

Name	Description	Required
param	The name of the parameter that will be substituted in the SQL statement. For example, if the param is id, then all occurences of ${id} will be substituted by the current value of id.	Yes
values	A comma-separated list of values for the id.	No, either this or (start,stop,(step)) or (start,count,(step)) may be specified
start	Specifies the starting value of the sequence of values for the param	No, values can be specified instead. If neither is specified, then start defaults to 0.
stop	The ending value of the sequence of values for param	No, count can be specified instead.
step	The increment if a sequence is specified with start and stop	No, defaults to 1.
count	The number of entries in the sequence specified by start and stop.	No, stop can be specified instead.

Nested Elements

Table 14. Nested Elements for foreach

Name	Description	Required
sql	Contains a single SQL statement with placeholders for param which will be executed in a loop specified by the foreach attributes. The replaceable placeholder(s) should be specified as ${id}.	Yes

Examples

A foreach used to prepare test data

 <prepare>
   <sql><stmt>delete from foreachtest where 1=1</stmt></sql>
   <foreach param="id" start="0" stop="10" step="1">
     <sql>       <stmt>insert into foreachtest (id,name) values (${id},'name${id}')</stmt>
     </sql>
   </foreach>
 </prepare>

The connection Tag

Description

Supplies information for the SQLUnit test harness to connect to the underlying database containing the stored procedures to be tested. Connections can be built using JDBC or from a DataSource object looked up through JNDI. Connections can also be specified using file URLs. Multiple connection tags can be specified if more than one Connection is needed for a single SQLUnit test suite.

Syntax

connection ::= (((driver, url, user, password, (jarfile-url)?) | (datasource, jndi))?)

Parent Elements

sqlunit

Attributes

Table 15. Attributes for connection

Name	Description	Required
connection-id	When multiple Connections are defined in a SQLUnit test, this attribute refers to a Connection to use for running this call by its connection-id. If not specified, SQLUnit will try to look up the default Connection which has no connection-id attribute defined.	No, default Connection is used if not supplied
extern	If specified, the value of this attribute points to a Java properties file which contains the information necessary to build the Connection. The file can be specified without path information if it can be found in the caller's CLASSPATH or specified as a relative path, as well as an absolute path on the file system. A sample properties file can be found in /cvs/test/postgresql/sqlunit.properties.
transaction-support	Indicates whether transaction support is provided by SQLUnit. By default this is turned on. Turn this off if your stored procedure provides its own transaction support using COMMIT and ROLLBACK.	No
reconnect-on-failure	Will destroy the current Connection and rebuild it in the event of a test failure or exception. This is needed to work around buggy database drivers which leave the Connection in an inconsistent state in such cases.	No, defaults to false
server-name	If specified, will override the database server name that is derived from the DatabaseMetaData for the connection. This is used to lookup data types supported by this database.	No, defaults to the database server name

Nested Elements

Table 16. Nested Elements for connection

Name	Description	Required
driver	Specifies the full Java class name for the JDBC driver. This is needed for building the Connection using JDBC.	Yes (JDBC)
url	Specifies the JDBC URL to the database	Yes (JDBC)
user	Specifies the database user who will connect to the database	Yes (JDBC)
password	Specifies the password for the database user	Yes (JDBC)
jarfile-url	Specifies a URL (eg. file:///tmp/mydriver.jar) for a JAR file containing a JDBC driver which is not in the CLASSPATH.	No (JDBC)
datasource	Specifies the name of a DataSource. By convention, this would look something like jdbc:/myDSN, where the DataSource object is actually stored under java:/comp/env/jdbc. This is likely to vary between JNDI servers and sites, so check with your administrator for the DataSource name to use	Yes (JNDI)
jndi	Empty tag, contains zero or more arg elements containing the name-value pairs to pass in when creating the Initial Naming Context that will be used to look up the DataSource object. These name-value pairs locate the JNDI service. Each arg element will correspond to a line in the jndi.properties file for the given JNDI server.	Yes (JNDI)

Examples

Specifying a JDBC connection for PostgreSQL

 <connection>
   <driver>org.postgresql.Driver</driver>
   <url>jdbc:postgresql://localhost:5432/demodb</url>
   <user>defaultuser</user>
   <password>d3fau1tus3r</password>
 </connection>

Specifying a JNDI connection with JBoss

 <connection>
   <datasource>jdbc/myDSN</datasource>
   <jndi>
     <arg name="java.naming.factory.initial"
       value="org.jnp.interfaces.NamingContextFactory" />
     <arg name="java.naming.provider.url" 
       value="jnlp://localhost:1099" />
     <arg name="java.naming.factory.url.pkgs" 
       value="org.jboss.naming" />
   </jndi>
 </connection>

The include Tag

Description

The include tag is used to specify an external file that contains a list of SQL statements that should be executed together. The statements can be separated from one another by a free standing slash (/) or semi-colon character (;) on its own line, or by a line terminated by a semi-colon (;) character.

Syntax

include ::= (EMPTY)

Parent Elements

setup teardown

Attributes

Table 17. Attributes for include

Name	Description	Required
file	Specifies a relative or absolute path name to the file containing the SQL statements.	Yes
connection-id	When multiple Connections are defined in a SQLUnit test, this attribute is used to select the Connection to use for running the SQL statements. If not specified, SQLUnit will use the default Connection if that is specified.	No

Nested Elements

None

Examples

Cleanup SQL being included in a teardown tag

 <teardown>
   <include file="test/sybase/teardown.sql" />
 </teardown>

The result Tag

Description

The result tag allows the test author to specify an expected result from a SQL call or stored procedure. SQLUnit executes the SQL or stored procedure and converts the results into an internal Result object. It will also convert the data supplied in the result tag to an internal Result object, and then compares the two for equality. Values specified in the col nested element of a result element can contain variable names instead of literal values. The variable names will be updated with the values from the internal symbol table. Result tags can also contain an exception element if the call is expected to throw an exception for this particular test. Note that all id attributes of the result and its nested elements start at 1, so as to be consistent with how JDBC resultsets are set up, and minimize confusion between the two representations.

Syntax

result ::= ((((outparam)*, (updatecount)?, (resultset)*)+ | ((outparam)*, exception))?)

Parent Elements

test

Attributes

Table 18. Attributes for result

Name	Description	Required
id	The id of the result	No

Nested Elements

Table 19. Nested Elements for result

Name	Description	Required
outparam	Zero or more outparam elements may be specified, each representing an output parameter from the stored procedure. An outparam element can contain a resultset element in case of Oracle CURSOR types, or text information in case of non-CURSOR types.	No
updatecount	Specifies the updatecount that is returned from the SQL or stored procedure call.	No.
resultset	The resultset tag specifies a single resultset returned from a database call (SQL or stored procedure). A result element can contain one or more resultsets, which in turn will contain one or more rows, which would contain one or more columns. If there are no rows, a resultset can also be empty.	No.
exception	The exception tag is used to test for expected failures of a stored procedure in response to specific inputs.	No. Exception tags can coexist with outparam tags.

Examples

A multi-resultset result tag

 <result>
   <resultset id="1">
     <row id="1">
       <col id="1" name="c1" type="INTEGER">1</col>
       <col id="2" name="c2" type="VARCHAR">Blah</col>
       <col id="3" name="c3" type="INTEGER">3</col>
     </row>
   </resultset>
   <resultset id="2" />
   <resultset id="3" />
   <resultset id="4">
   <row id="1">
       <col id="1" name="ac1" type="INTEGER">1</col>
       <col id="2" name="ac2" type="INTEGER">1</col>
       <col id="3" name="ac3" type="VARCHAR">Unused Blahs</col>
       <col id="4" name="ac4" type="INTEGER">1</col>
       <col id="5" name="ac5" type="VARCHAR">Unused</col>
   </row>
   <row id="2">
       <col id="1" name="ac1" type="INTEGER">1</col>
       <col id="2" name="ac2" type="INTEGER">1000</col>
       <col id="3" name="ac3" type="VARCHAR">Deprecated Blahs</col>
       <col id="4" name="ac4" type="INTEGER">1</col>
       <col id="5" name="ac5" type="VARCHAR">Deprecated</col>
   </row>
   </resultset>
 </result>

A result containing an exception

 <result>
   <exception>
     <code>1234</code>
     <message>Test Exception</message>
   </exception>
 </result>

The methodArgs Tag

Description

A wrapper tag to group together arguments to a method call.

Syntax

methodArgs ::= ((arg)*)

Parent Elements

methodinvoker dynamicsql

Attributes

None

Nested Elements

Table 20. Nested Elements for methodArgs

Name	Description	Required
arg	A tag that specifies the name, type and value of the method argument.	One or more arg elements are required.

Examples

A methodArgs tag with different argument types

 <methodArgs>
   <arg name="m3" type="java.lang.Integer" value="-2147483648" />
   <arg name="m7" type="java.lang.Double" value="2.2" />
   <arg name="m8" type="java.lang.String" value="Some text" />
 </methodArgs>

The row Tag

Description

Represents a row in a database table returned from a SQL or stored procedure call.

Syntax

row ::= (col)+

Parent Elements

resultset

Attributes

Table 21. Attributes for row

Name	Description	Required
id	The row id	Yes

Nested Elements

Table 22. Nested Elements for row

Name	Description	Required
col	A row can have one or more col elements defined within it.	Yes

Examples

A simple row tag

 <row id="1">
   <col id="1" name="col1" type="INTEGER">1</col>
 </row>

The param Tag

Description

The param tag is used to specify an argument to a SQL statement or stored procedure, or aggregated into a set of paramset tags for batch calls. The body of the tag contains the value of the parameter.

Syntax

param ::= (BODY)

Parent Elements

call sql paramset

Attributes

Table 23. Attributes for param

Name	Description	Required
id	Specifies the sequence number of the parameter to be replaced. The sequence is one-based to keep parity with the JDBC specification.	Yes
name	Specifies the name of the parameter to be replaced. This has been added for readability to help stored procedure authors/testers to spot errors quickly.	No
type	Specifies the type of the parameter to be replaced. The type is a String, and has a one to one mapping to the datatypes defined in java.sql.Types. It also allows some additional Oracle specific types specified in net.sourceforge.sqlunit.OracleExtensionTypes. The value of the type is the same as the field name of the member variables in Types or OracleExtensionTypes classes. Thus if the type is Integer, the corresponding SQL type is java.sql.Type.INTEGER, and the string value that should be used here is INTEGER.	Yes
is-null	If set to true, indicates that this parameter is an SQL NULL value. Valid values are true or false.	No, default is false.
inout	Specifies whether the parameter is an IN, OUT or INOUT parameter. Valid values are in, out and inout respectively.	No, default is in.

Nested Elements

None

Examples

A simple param tag definition

 <param id="1" name="v1" type="INTEGER">1</param>

The batchsql Tag

Description

The batchsql tag allows the client to specify a set of non-parameterized SQL statements which need to run in a batch. SQLUnit uses the JDBC batching mechanism to run the set of SQL statements.

Syntax

batchsql ::= ((stmt)+)

Parent Elements

batchtest

Attributes

Table 24. Attributes for batchsql

Name	Description	Required
connection-id	When multiple Connections are defined in a SQLUnit test, this attribute provides a mechanism for choosing one of the defined Connections to use for running this batch. If a connection-id is not specified, SQLUnit will try to look up a Connection which has no connection-id.	No

Nested Elements

Table 25. Nested Elements for batchsql

Name	Description	Required
stmt	Contains the SQL statement or stored procedure call in the body of the element	One or more stmt elements need to be specified

Examples

A typical batchsql call

 <batchsql>
   <stmt>delete from customer where custId=1</stmt>
   <stmt>insert into customer values(1,'Some One','secret',
     'en_US',null,0,now())</stmt>
   <stmt>insert into customer values(1,'Someone Else','secret',
     'en_US',null,0,now())</stmt>
   <stmt>delete from customer where 1=1</stmt>
 </batchsql>

The prepare Tag

Description

The prepare tag specifies additional SQL statements that should be run before the test in whose block it appears in. This is used for specifying additional operations that need to happen on a per-test basis, which is not covered in the setup tag.

Syntax

prepare ::= ((set)*, (sql)*, (foreach)*)

Parent Elements

test

Attributes

None

Nested Elements

Table 26. Nested Elements for prepare

Name	Description	Required
set	This tag sets values for SQLUnit test variables into the SQLUnit symbol table.	No.
sql	Identifies one or more SQL statements that should be executed as part of the per-test prepare	No.
foreach	Specifies a block within a foreach block which is executed as part of the prepare.	No.

Examples

A foreach tag inside a prepare tag

 <prepare>
   <sql><stmt>delete from foreachtest where 1=1</stmt></sql>
   <foreach param="id" start="0" stop="10" step="1">
     <sql><stmt>insert into foreachtest (id,name) values 
       (${id},'name${id}')</stmt></sql>
   </foreach>
 </prepare>

The paramset Tag

Description

Wrapper tag for one or more param elements.

Syntax

paramset ::= ((param)+)

Parent Elements

batchcall

Attributes

Table 27. Attributes for paramset

Name	Description	Required
id	Specifies the id of the paramset element.	Yes

Nested Elements

Table 28. Nested Elements for paramset

Name	Description	Required
param	Specifies argument names and values to be passed into a batchcall call.	One or more

Examples

A paramset passed into an INSERT batchcall

 <paramset id="1">
   <param id="1" type="INTEGER">1</param>
   <param id="2" type="VARCHAR">Some One</param>
 </paramset>

The col Tag

Description

Represents a column of a database table returned from a SQL or stored procedure call.

Syntax

col ::= (BODY)

Parent Elements

row

Attributes

Table 29. Attributes for col

Name	Description	Required
id	The column id	Yes
name	The column name	No
type	The XML name of the data type for the column	Yes

Nested Elements

None

Examples

A simple column tag

<col id="1" name="col1" type="INTEGER">1</col>

The batchtest Tag

Description

The batchtest tag allows specifying a batch of SQL statements or stored procedure calls that must be executed within a batch. It allows the client to specify the expected number of results, and a point where a failure may be expected. Note that only statements which update data in some way should be specified hre, since the JDBC specification does not allow the returning of resultsets from SQL statements run using the batching feature

Syntax

batchtest ::= ((batchcall | batchsql), batchresult)

Parent Elements

sqlunit

Attributes

Table 30. Attributes for batchtest

Name	Description	Required
name	The name of the batch test. THis is used by SQLUnit to print progress messages to its log output	Yes
assert	Specifies a single or comma-separated assertion that must hold true for the batch test.	No, defaults to equal if not specified.
failure-message	If specified, SQLUnit will print the user-supplied failure message if the test failed	No, default is no failure message
java-object-support	If set to true, Java Object Support is enabled for the test	No, default is false

Nested Elements

Table 31. Nested Elements for batchtest

Name	Description	Required
batchcall	Specifies the set of batch calls to be made. Either batchcall or batchsql must be specified	No, but see Description
batchsql	Specifies the set of batch calls to be made. Either batchcall or batchsql must be specified	No, but see Description
batchresult	Specifies the expected return values from the call	Yes

Examples

A typical batchtest specification

 <batchtest name="Testing basic batchtest functionality">
   <batchsql>
     <stmt>delete from customer where custId=1</stmt>
     <stmt>insert into customer values(1,'Some One','secret',
       'en_US',null,0,now())</stmt>
     <stmt>insert into customer values(1,'Someone Else','secret',
       'en_US',null,0,now())</stmt>
     <stmt>delete from customer where 1=1</stmt>
   </batchsql>
   <batchresult>
     <updatecount>1</updatecount>
     <updatecount>1</updatecount>
     <updatecount>1</updatecount>
     <updatecount>2</updatecount>
   </batchresult>
 </batchtest>

The constructorArgs Tag

Description

Collection of arg elements that need to be passed to the constructor of a specified class whose method needs to be invoked by SQLUnit

Syntax

constructorArgs ::= ((arg)*)

Parent Elements

dynamicsql methodinvoker

Attributes

None

Nested Elements

Table 32. Nested Elements for constructorArgs

Name	Description	Required
arg	Specifies an individual argument	One or more

Examples

Example of a constructorArgs tag

 <constructorArgs>
   <arg name="c1" type="java.lang.Byte" value="-128" />
   <arg name="c2" type="java.lang.Short" value="-32768" />
   <arg name="c3" type="java.lang.Integer" value="-2147483648" />
   <arg name="c5" type="java.lang.Character" value="a" />
   <arg name="c6" type="java.lang.Float" value="1.1" />
   <arg name="c7" type="java.lang.Double" value="2.2" />
   <arg name="c8" type="java.lang.String" value="Some text" />
 </constructorArgs>

The batchcall Tag

Description

The batchcall tag allows the SQLUnit client to run a single stored procedure or parameterized SQL with a set of different input arguments. It uses JDBC batching to do this.

Syntax

batchcall ::= ((stmt), (paramset)+)

Parent Elements

batchtest

Attributes

Table 33. Attributes for batchcall

Name	Description	Required
connection-id	When multiple Connections are defined for a SQLUnit test, this attribute refers to the Connection to use for running this call. If not specified, SQLUnit will try to look up a Connection which has no connection-id attribute specified.	No

Nested Elements

Table 34. Nested Elements for batchcall

Name	Description	Required
stmt	Contains the SQL statement or stored procedure to call in the body.	Yes
param	Specifies a set of param elements	At least one needs to be provided

Examples

A typical batchcall tag

 <batchcall>
   <stmt>insert into customer values(?,?,?,?,?,?,now())</stmt>
     <paramset id="1">
       <param id="1" type="INTEGER">1</param>
       <param id="2" type="VARCHAR">Some One</param>
       <param id="3" type="VARCHAR">secret</param>
       <param id="4" type="VARCHAR">en_US</param>
       <param id="5" type="VARCHAR">SQLUnit</param>
       <param id="6" type="INTEGER">0</param>
     </paramset>
     <paramset id="2">
       <param id="1" type="INTEGER">2</param>
       <param id="2" type="VARCHAR">Someone Else</param>
       <param id="3" type="VARCHAR">secret</param>
       <param id="4" type="VARCHAR">en_US</param>
       <param id="5" type="VARCHAR">SQLUnit</param>
       <param id="6" type="INTEGER">0</param>
     </paramset>
 </batchcall>

The outparam Tag

Description

The outparam tag is used to represent OUT parameters returned as a result of a Stored procedure call. The value is stored in the body text as a String or as a embedded resultset element in case of CURSOR type OUT parameters.

Syntax

outparam ::= (BODY)|(resultset)

Parent Elements

result

Attributes

Table 35. Attributes for outparam

Name	Description	Required
id	The param-id of the outparam parameter.	Yes
name	The name of the outparam parameter. This is mainly for readability to help stored procedure authors/testers to spot errors quickly.	No
type	Specifies the type name of the parameter.	Yes

Nested Elements

Table 36. Nested Elements for outparam

Name	Description	Required
resultset	This is needed only when the type of the OUT parameter is a CURSOR, and specifies the value of the CURSOR outparam.	Not if the type is not CURSOR

Examples

A outparam with a INTEGER value

 <outparam id="1" type="INTEGER">24</outparam>

A outparam tag with an embedded CURSOR

 <outparam id="1" type="oracle.CURSOR">
   <resultset id="1">
     <row id="1">
       <col id="1" type="INTEGER">7</col>
       <col id="2" type="VARCHAR">James Bond</col>
       <col id="3" type="VARCHAR">Martini</col>
     </row>
   </resultset>
 </outparam>

The test Tag

Description

The test tag specifies the SQL statement or stored procedure that needs to be tested, its input parameters, and the expected output. The tag will be run by SQLUnit to compare the expected results against the actual result.

Syntax

test ::= ((match)*, (prepare)?, ((sql | call | methodinvoker | dynamicsql), result)?)

Parent Elements

sqlunit

Attributes

Table 37. Attributes for test

Name	Description	Required
name	Specifies a human-readable name for the test, which will be printed to the log as part of SQLUnit's progress messages.	Yes
assert	Specifies a single or comma-separated list of assertions that must be true for the test.	No, defaults to equal
failure-message	Allows the caller to supply an error message which should be displayed if the test failed.	No, if not specified, no user message will be displayed.
java-object-support	If set to on, indicates that Java Object Support is turned on. Valid values are on and off.	No, default is off
expected-duration-millis	If specified, specifies that SQLUnit should time the test and fail the test if the test does not complete in the window specified +/- a percentage tolerance if specified, or 10% if not.	No
percentage-tolerance	If specified with expected-duration-millis, this specifies the percentage tolerance that SQLUnit will put on the expected-duration in order to calculate if the test should fail.	No, will default to 10 if expected-duration-millis is specified and percentage-tolerance is not specified.

Nested Elements

Table 38. Nested Elements for test

Name	Description	Required
match	Specifies zero or match elements that should be applied to match the result returned with that specified.	No
prepare	Specifies SQL setup code that must be run on a per-test basis.	No
sql	Specifies the SQL statement that must be run for this test.	Either one of sql, call, methodinvoker or dynamicsql is required.
call	Specifies a stored procedure that must be run for the test.	Either one of sql, call, methodinvoker or dynamicsql is required.
methodinvoker	Specifies a method that should be invoked for the test.	Either one of sql, call, methodinvoker or dynamicsql is required.
dynamicsql	Specifies a method which returns a String of dynamic SQL code that should be executed for this test.	Either one of sql, call, methodinvoker or dynamicsql is required.
result	Specifies the expected result from the test.	Yes

Examples

 <test name="Adding department HR">
   <sql>
     <stmt>select AddDept(?)</stmt>
     <param id="1" type="VARCHAR">Human Resources</param>
   </sql>
   <result>
     <resultset id="1">
       <row id="1">
         <col id="1" name="adddept" type="INTEGER">
           ${deptId_HR}
         </col>
       </row>
     </resultset>
   </result>
 </test>

The resultset Tag

Description

Represents a resultset returned from a SQL or stored procedure call.

Syntax

resultset ::= (row)*

Parent Elements

result

Attributes

Table 39. Attributes for resultset

Name	Description	Required
id	The resultset id	Yes
rowcount	Specifies the number of rows in the resultset. This is specified when the test client wants SQLUnit to only match the number of rows in the resultset, rather than the contents themselves.	No
order-by	A comma-separated list of column ids on which the rows in the resultset should be sorted. By default, both the resultset returned from the SQL or stored procedure call and the resultset that is specified will be sorted before comparing. The default sorting is by all the specified columns in the order in which they appear. The order-by attribute should be used only if the default sort needs to be overriden, for example, when the SQL or stored procedure returns ordered data. The sort order specified is ASCENDING, unless the column id is prefixed with a negative sign, which will cause it to be sorted in DESCENDING order.	No

Nested Elements

Table 40. Nested Elements for resultset

Name	Description	Required
row	A resultset can have zero or more row elements	No

Examples

A simple resultset tag containing a single row

 <resultset id="1">
   <row id="1">
     <col id="1" name="col1" type="INTEGER">1</col>
   </row>
 </resultset>

The match Tag

Description

The match tag specifies a user-defined matching strategy that will be applied instead of the default strategy to check for exact equality between two column values

Syntax

match ::= ((arg)*)

Parent Elements

diff

Attributes

Table 41. Attributes for match

Name	Description	Required
resultset-id	If specified, defines the resultset(s) on which the user- defined matching criteria should be applied. Wildcards may be applied to specify multiple values using a '', eg. '3' or '*'. Ranges may be specified using an expression such as '1-2', or enumerations using an expression of the form '1,2,3'.	No, defaults to all resultsets if not specified
row-id	If specified, defines the row(s) on which the user-defined matching strategy will be applied. Same wildcarding rules as defined in resultset-id apply.	No, defaults to all if not specified
col-id	If specified, defines the column(s) on which the user-defined matching strategy should be applied. Same wildcarding rules as defined in resultset-id apply.	No, defaults to all if not specified
matcher	The full class name of the Matcher class that will be used for the user-defined matching. The Matcher class must implement the IMatcher interface and be available in the caller's CLASSPATH	Yes

Nested Elements

Table 42. Nested Elements for match

Name	Description	Required
arg	Specifies the name and value of the parameters that are needed by the Matcher class, in addition to the actual column values, to compute the match.	Zero or more arg elements may need to be specified

Examples

Specifying a perccentage matcher with 20% tolerance

 <match col-id="1-2"
   matcher="net.sourceforge.sqlunitmatchers.PercentageRangeMatcher">
   <arg name="pc-tolerance" value="20" />
 </match>

The exception Tag

Description

Specifies an expected exception with expected error code and message

Syntax

exception ::= (code)?, (message)?

Parent Elements

result

Attributes

None

Nested Elements

Table 43. Nested Elements for exception

Name	Description	Required
code	The error code for the expected exception	No
message	The error message for the expected exception	No

Examples

An exception declaration

 <exception>
   <code>0</code>
   <message>ERROR: Cannot insert a duplicate key into unique index ux2_employee</message>
 </exception>

The diff Tag

Description

The diff element is used to compare the results of two SQL queries or stored procedure calls. This scenario can be used to the results of a database load or when the results are either too long or unknown at the time the test specification is being written. The two queries/calls can either use the same Connection or use their own dedicated Connection to the same or different databases. The result tag is not specified in case of diff. SQLUnit will internally generate the two Result objects and match them. The default matching is exact equality, but can be overriden by supplying one or more match elements in the diff element. More information on Matching can be found in the User-Defined Matching section

Syntax

diff ::= ((match)*, (prepare)?, (sql|call), (sql|call))

Parent Elements

test

Attributes

Table 44. Attributes for diff

Name	Description	Required
name	The name of the test used by SQLUnit to report progress messages in its log output.	Yes
assert	Specifies a single assertion or a comma-separated list of assertions which must be true for this diff.	No, defaults to equal if not specified
failure-message	If specified, SQLUnit will print the user-supplied failure message if the test failed.	No
java-object-support	If specified, Java Object Support is enabled for the test	No, default is false
multi-threaded	If set to true, runs the calls in parallel to save time	No, default is false (serial execution)

Nested Elements

Table 45. Nested Elements for diff

Name	Description	Required
match	Provides overrides for the matching strategy on a per column basis	No
prepare	Allows per test setup calls	No. Zero or one prepares can be specified.
sql	Specifies the SQL statement to execute.	Either one of sql or call needs to be specified. Two and only two needs to be specified
call	Specifies the stored procedure to execute.	Either one of sql or call needs to be specified. Two and only two needs to be specified.

Examples

Diff call with multiple match elements

 <diff name="Diffing different resultset/multiple matchers"
     failure-message="Diff test #3 failed">
   <match col-id="1" 
     matcher="net.sourceforge.sqlunitmatchers.AllOrNothingMatcher">
     <arg name="match" value="true" />
   </match>
   <match col-id="2"
     matcher="net.sourceforge.sqlunitmatchers.RangeMatcher">
     <arg name="tolerance" value="50" />
   </match>
   <match col-id="3" 
     matcher="net.sourceforge.sqlunitmatchers.PercentageRangeMatcher">
     <arg name="pc-tolerance" value="10" />
   </match>
   <sql connection-id="1">
     <stmt>select widget_name, price_per_unit, number_sold from widgets where widget_id=?</stmt>
     <param id="1" type="INTEGER">1</param>
   </sql>
   <sql connection-id="2">
     <stmt>select widget_name, price_per_unit, number_sold from widgets where widget_id=?</stmt>
     <param id="1" type="INTEGER">2</param>
   </sql>
 </diff>

The arg Tag

Description

Carries argument values and types for the containing element

Syntax

arg ::= (EMPTY)

Parent Elements

jndi match constructorArgs methodArgs

Attributes

Table 46. Attributes for arg

Name	Description	Required
name	The name of the argument to be used	Yes
value	The value of the argument	Yes
type	The type of the argument	No, but required in methodArgs and constructorArgs

Nested Elements

None

Examples

Specifying args for jndi

 <arg name="java.naming.factory.initial"
   value="org.jnp.interfaces.NamingFactory" />

Specifying args for methodArgs

 <methodArgs>
   <arg name="col1" type="java.lang.String" value="ABC" />
 </methodArgs>

The sql Tag

Description

The sql tag describes a regular SQL statement (not a stored procedure) with or without replaceable parameters. It can be used to describe a stored function call for databases that support it. It is converted internally to a JDBC PreparedStatement object. SQL specified by the sql statement can return a result with a single resultset or an updatecount.

Syntax

sql ::= (stmt, (param)*)

Parent Elements

foreach setup test diff teardown

Attributes

Table 47. Attributes for sql

Name	Description	Required
id	The name or number of the sql call	No
connection-id	When multiple Connections are defined in a SQLUnit test, this attribute refers to the Connection to use for running this call. If not specified, SQLUnit tries to look up the default Connection.	No

Nested Elements

Table 48. Nested Elements for sql

Name	Description	Required
stmt	Specifies the actual SQL statement, with or without replaceable parameters in the body of the tag.	Yes
param	Occurs once for each replaceable parameter, if specified, for the SQL string.	Yes, if there are replaceable parameters in the SQL string.

Examples

Example of a simple sql tag

 <sql>
   <stmt>select custId from customer where custId=?</stmt>
   <param id="1" type="INTEGER">1</param>
 </sql>

The dynamicsql Tag

Description

The dynamicsql tag invokes a named method in a named class in order to generate a SQL string dynamically, which is then fed to SQLUnit for testing against a known result. This was included because it is often necessary to test dynamic SQL that is built in response to known conditions, and the results are known, but the actual SQL may not be of interest.

Syntax

dynamicsql ::= ((constructorArgs)*, (methodArgs)*)

Parent Elements

test foreach setup diff teardown

Attributes

Table 49. Attributes for dynamicsql

Name	Description	Required
static	If set to true, specifies that the method is a static method	No, default is false (non-static method)
class	Specifies the full class name where the method is defined	Yes
method	Specifies the method name to invoke	Yes
id	The name or number of the sql call	No
connection-id	When multiple Connections are defined in a SQLUnit test, this attribute refers to the Connection to use for running this call. If not specified, SQLUnit tries to look up the default Connection.	No

Nested Elements

Table 50. Nested Elements for dynamicsql

Name	Description	Required
constructorArgs	Wraps a set of arg elements which specify the arguments the constructor for the class will take	Not required if this is a static method, or if the class has a default null constructor
methodArgs	Wraps a set of arg elements which specify the arguments to pass to the method	Not required if the method does not take arguments

Examples

A dynamicsql tag that invokes a static method

 <dynamicsql class="net.sourceforge.sqlunit.utils.TestArgument"
   static="true" method="getGeneratedOracleSqlString">
 </dynamicsql>

<<< Previous	Home	Next >>>
Interpreting the output		Extending SQLUnit: Writing your own tag handler

SQLUnit Tags

List of currently supported tags

The sqlunit Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

The setup Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The batchresult Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The call Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The set Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The jndi Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The methodinvoker Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The teardown Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The foreach Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The connection Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The include Tag

Description

Syntax

Parent Elements

Attributes

Nested Elements

Examples

The result Tag

Description