SQL Command Set

Exec SQL

This command is not part of the Default Command Set. You have to register the SQL Command Set first (see Configuration).

Executes the given SQL against the configured database and commits.

Parameters

sql
Specifies the SQL to execute (required).

Examples

Config (wetator.properties)
wetator.db.connections=[db name]
wetator.db.[db name].driver=org.hsqldb.jdbcDriver (the jdbc driver of your db)
wetator.db.[db name].url=[jdbc:...] (the jdbc url of your db)
wetator.db.[db name].user=[user]
wetator.db.[db name].password=[password]

Create a new table and insert some values

Command Parameter Optional Parameter
exec-sql DROP TABLE IF EXISTS test_tbl
exec-sql CREATE TABLE test_tbl (col1 CHAR(20), col2 NUMERIC)
exec-sql INSERT INTO test_tbl (col1, col2) VALUES ('First', 1)
exec-sql INSERT INTO test_tbl (col1, col2) VALUES ('Second', 2)

Call a stored procedure (ORACLE)

Command Parameter Optional Parameter
exec-sql BEGIN MYSCHEMA.MYPROC; COMMIT; END;
Assert SQL

This command is not part of the Default Command Set. You have to register the SQL Command Set first (see Configuration).

Executes the given SQL query against the configured database and asserts that the result matches the expected value.
The result is built by concatenating all column values of all rows, separated by spaces.

Parameters

sql-query
The SQL SELECT statement to execute (required).
To target a specific database connection, prefix the SQL with '@[db-name] ', e.g. '@mydb SELECT ...'. If no prefix is given, the default connection is used.
expected-result
The expected result as a content pattern (required).
The result is checked using the same pattern syntax as assert-content. It is possible to use '*' and '?' as wildcards.

Examples

Config (wetator.config)
wetator.commandSets=org.wetator.commandset.SqlCommandSet
wetator.db.connections=mydb
wetator.db.mydb.driver=org.hsqldb.jdbcDriver
wetator.db.mydb.url=jdbc:hsqldb:mem:testdb
wetator.db.mydb.user=sa
wetator.db.mydb.password=

Assert the count of rows in a table

Command Parameter Optional Parameter
assert-sql SELECT COUNT(*) FROM users 5

Assert the value of a specific column

Command Parameter Optional Parameter
assert-sql SELECT name FROM users WHERE id = 1 dobby
Assert SQL in Content

This command is not part of the Default Command Set. You have to register the SQL Command Set first (see Configuration).

Executes the given SQL query against the configured database and asserts that all returned values appear in the current page content.
This is useful to verify that data from the database is correctly displayed in the application's UI. Null column values are ignored.

Parameters

sql-query
The SQL SELECT statement to execute (required).
To target a specific database connection, prefix the SQL with '@[db-name] ', e.g. '@mydb SELECT ...'. If no prefix is given, the default connection is used.
timeout
An optional timeout in seconds.
If specified, Wetator will wait up to this number of seconds for the page content to match. Useful for pages that load data asynchronously.

Examples

Config (wetator.config)
wetator.commandSets=org.wetator.commandset.SqlCommandSet
wetator.db.connections=mydb
wetator.db.mydb.driver=org.hsqldb.jdbcDriver
wetator.db.mydb.url=jdbc:hsqldb:mem:testdb
wetator.db.mydb.user=sa
wetator.db.mydb.password=

Assert that all user names from the database are shown on the current page

Command Parameter Optional Parameter
open-url users/list
assert-sql-in-content SELECT name FROM users ORDER BY id