Jdbc Slim

JdbcSlim is the framework to easily integrate database queries and commands into Slim FitNesse testing.
The design focuses to keep configuration data, test data and SQL code separate.
This ensures that requirements are written independent of the implementation and understandable by business users.

The framework can be used by Developers, Testers and Business Users with SQL knowledge.

JdbcSlim is agnostic of database system specifics and has no code special to any database system.
Such things should be handled by the jdbc driver.
Nevertheless the jdbc code is segregated from the slim code and adding any driver specific requirements can be done by simply changing a single class.

Most Tests require three steps.
1. Preparing test data
2. Execution some business functions
3. Validating the impact on the test data

Lets assume we have a database with some information about people and a we need to test a business function that relocates a person into a new city.

1 Preparing test data

To avoid side effects we execute the test in a transaction

Script	SQLCommand	TransactionDatabase
open Connection
execute	begin transaction

To prepare test data two approaches are possible:
1.1 Inserting new data into a database
1.2 Finding suitable existing data in the database


In this examples we use the second approach and find the ID of users named Ben and Sarah which should move.
We store the Id of the users in a Slim Symbol '$TestID' for future reference.

SQLCommand	TransactionDatabase	select ID, NAME from TestData where Name ='%NAME%'
ID?	NAME
$TestID1=	Ben
$TestID2=	Sarah

Before we proceed we validate that Ben and Sarah still live in the old Cities: Denver and Paris

SQLCommand	TransactionDatabase	select City from TestData where ID ='%ID%'
ID	CITY?
$TestID1	Denver
$TestID2	Paris

Execution some business functions

Here calls to your business code will go.
A simple update statement which can do the job is given below.

SQLCommand	TransactionDatabase	update TestData set City='%NewCITY%' where ID ='%ID%'
ID	NewCITY	Count?
$TestID1	HongKong	1
$TestID2	Tokyo	1

Validating the impact on the test data

Finally we check that Ben now lives in Hong Kong and Sarah in Tokyo

SQLCommand	TransactionDatabase	select City from TestData where ID ='%ID%'
ID	CITY?
$TestID1	HongKong
$TestID2	Tokyo

Rollback the change to not impact future tests

Script	SQLCommand	TransactionDatabase
open Connection
execute	rollback
close Connection

Check that the rollback worked

SQLCommand	TransactionDatabase	select City from TestData where ID ='%ID%'
ID	CITY?
$TestID1	Denver
$TestID2	Paris

Requirements should not contain SQL code:

• Not everybody understands SQL commands. But you want that the requirements can be understood by anybody.
• The SQL code can change if a different implementation is chosen. But the requirements should not change in this case. They should be independent of the implementation.

This can be achieved easily with Jdbc Slim.

Lets rewrite the first example with Ben and Sarah but write it in less technical terms.
The SQL Code can be moved into a separate page:

1 Preparing test data

Find the ID of users named Ben and Sarah which should move.
We store the Id of the users in a Slim Symbol '$TestID' for future reference.

SQLCommand	FindTestUserIDs
ID?	NAME
$TestID=	Ben
$TestID2=	Sarah

Before we proceed we validate that Ben and Sarah still live in the old Cities: Denver and Paris

SQLCommand	CheckInitalCityOfUsers
ID	CITY?
$TestID	Denver
$TestID2	Paris

Execution some business functions

Here calls to your business code will go.
A simple update statement which can do the job is given below.

SQLCommand	RelocateUserToNewCity
ID	NewCITY	Count?
$TestID	HongKong	1
$TestID2	Tokyo	1

Validating the impact on the test data

Finally we check that Ben now lives in Hong Kong and Sarah in Tokyo

SQLCommand	CheckFinalCityOfUsers
ID	CITY?
$TestID	HongKong
$TestID2	Tokyo

Requirements should not contain SQL code:

• Not everybody understands SQL commands. But you want that the requirements can be understood by anybody.
• The SQL Code can change if a different implementation is chosen. But the requirements should not change in this case. They should be independent of the implementation.

This can be achieved easily with JdbcSlim[?].

Lets rewrite the first example with Ben and Sarah but write it in less technical terms.
The SQL Code can be moved into a seperate SetUp[?] page.

1 Preparing test data

Find the ID of users named Ben and Sarah which should move.
We store the Id of the users in a Slim Symbol '$TestID' for future reference.

find test users
ID?	NAME
$TestID=	Ben
$TestID2=	Sarah

Before we proceed we validate that Ben and Sarah still live in the old Cities: Denver and Paris

check first city of users
ID	CITY?
$TestID	Denver
$TestID2	Paris

2 Execution some business functions

Here calls to your business code will go.
A simple update statement which can do the job is given below.

relocate users
ID	NewCITY	Count?
$TestID	HongKong	1
$TestID2	Tokyo	1

3 Validating the impact on the test data

Finally we check that Ben now lives in Hong Kong and Sarah in Tokyo

Check city after relocation
ID	CITY?
$TestID	HongKong
$TestID2	Tokyo

Given

SQLCommand	TestDatabase	select ID, City from TestData where ID ='%ID%'
ID	City?
1	Berlin

Define Properties	Dummy
key	value
CMD

Script
# Your code here to create a Connection Object
start	SQLCommand	Dummy
$Cnn=	testHomeMadeConnection;	jdbc:h2:mem:h2db;DB_CLOSE_DELAY=-1	na	na
# Now add the connection symbol to be able to use it
addConnection;	ConnectionName	$Cnn

When An asynchronus sql update starts which will execute after 1 seconds

Script
Start	DelayedExecutor	$Cnn	update TestData set City ='Miami' where ID='1'	1000

Then the city value doesn't updates immediately

SQLCommand	TestDatabase	select City from TestData where ID ='%ID%'
ID	City?
1	Berlin

When we add a retry (50 times in 10 seconds)

Then we catch the update once it happens

SQLCommand	TestDatabase	select City from TestData where ID ='%ID%'	RETRY=50:10000
ID	City?
1	Miami

Revert updates done

SQLCommand	TestDatabase	update TestData set City ='%City%' where ID='%ID%'
ID	City	Count?
1	Berlin	1

Close the connection and remove it from the list of available connections

Define Properties	UseHomeMadeConnection
key	value
DBConnection	ConnectionName
CMD

Script: SQLCommand	UseHomeMadeConnection
closeConnection
removeConnection	ConnectionName

To test that code is working it is not enough to check that the code changed the expected data.
It must also be tested that no other data got changed.

JdbcSlim[?] supports this in the following way.

Going back to our initial example where Ben and Sarah moved location.
Additional we want to check that no other user got moved.

Define the query

Define Properties	SideEffectCheck
key	value
.include	TestDatabase
cmd	select * from TestData where Name not in ('Ben', 'Sarah')
#	The 'query' flag below tells SQL Command that this statement will return more than one row
query	true

Store the data of the other people before the test

Script	SQLCommand	SideEffectCheck
open connection
execute
$RSBefore=	resultSheet
close connection

Run the test

Compare the data of the other people after the test.

Script	SQLCommand	SideEffectCheck
open connection
execute
$RSAfter=	resultSheet
$RSDiff=	compareSheet	$RSBefore
close connection

Table:SheetEcho

$RSDiff

To prepare business data often more than one table must be updated or data inserted into multiple tables.
But for a business user this is often not important. Having said that the requirements should hide this technical detail.

This can be done with Jdbc Slim.

The technical implementation is moved into a Start Up and Tear Down page:

The business defines the requirements in a table

Get the name and phone number for id
id	name?	phone?
2	Ben	>0
4	Till	>100
3	Tom	>10
2	=~/Be/	>5

See also: 7UsingSqlInScriptsAndScenarios

To configure a JDBC connection 4 parameters are required:

* JDBC Class
* URL to the database
* login of a database user
* password of the database user


These 4 values can either be stored in a configuration file or on a wiki page inside a Define Properties fixture.
For the examples used in this manual the configuration looks as below:

Define Properties	DatabaseSetup
key	value
jdbcDriver	org.relique.jdbc.csv.CsvDriver
DBURL	jdbc:relique:csv:plugins\jdbcslim\TestDB\
DBUSER	na
DBPASSWORD	na

DatabaseSetup is the name of the configuration and is passed as the first parameter to the SQLCommand.
key and value are the header line. Below these lines the actual configuration starts.

The SQL code can also be stored in the configuration. The key is CMD. Below is again an example:

Define Properties	FindTestUserIDs
key	value
.include	DatabaseSetup
cmd	select ID, NAME from TestData where Name ='%NAME%'

Further make sure that the java classpath of the slim client contains the jar with the JDBC class.

Further features of the configuration are described here:

Instead of creating a new connection for each SQL command table a connection can also be reused.

Add the property DBConnection to your configuration and give the connection a unique name.
The connection stays now open and will be reused for all SQL statements which have the same DBConnection property.
IMPORTANT: Don't forget to close the connection at the end of your tests and remove it from the internal list of available connections.

Define a connection and assign it a name with the DBConnection key.

Define Properties	NamedConnection
key	value
.include	TestDatabase
DBConnection	TestConnection

The connection will be opened at the first usage

SQLCommand	NamedConnection	select ID, NAME from TestData where Name ='%NAME%'
ID?	NAME
2	Ben
5	Sarah

This will reuse the connection. Apart from the DBConnection name no further property is required.

Define Properties	ReuseConnection
key	value
DBConnection	TestConnection
CMD

SQLCommand	ReuseConnection	select ID, NAME from TestData where Name ='%NAME%'
ID?	NAME
2	Ben
5	Sarah

Close the connection and remove it from the list of available connections

Script: SQLCommand	ReuseConnection
closeConnection
removeConnection	TestConnection

Another access to the connection will fail

comment: SQLCommand	ReuseConnection	select ID, NAME from TestData where Name ='%NAME%'
ID?	NAME
	Ben
	Sarah

If your application under test already creates a database connection and you want or must use it also in FitNesse tests than this can be done.
You might also want to use this feature if your connection would require further parameters.

Just create an object from java.sql.Connection via a FitNesse Script and assign it to a FitNesse Symbol.
than call addConnection from SQLCommand in a script and give the connection a name.
Use this name in future SQL configurations as DBConnection and your connection object will be used.

Remember to close the connection once it is not required any more.

Define Properties	Dummy
key	value
CMD

Script
# Your code here to create a Connection Object
start	SQLCommand	Dummy
$Cnn=	testHomeMadeConnection;	jdbc:h2:mem:h2db;DB_CLOSE_DELAY=-1	na	na
# Now add the connection symbol to be able to use it
addConnection;	ConnectionName	$Cnn

Define Properties	UseHomeMadeConnection
key	value
DBConnection	ConnectionName
CMD

SQLCommand	UseHomeMadeConnection	select ID, NAME from TestData where Name ='%NAME%'
ID?	NAME
2	Ben
5	Sarah

Close the connection and remove it from the list of available connections

Script: SQLCommand	UseHomeMadeConnection
closeConnection
removeConnection	ConnectionName

WARNING: Any smart user of FitNesse can decryt the encrypted passwords!

The current implementation avoids that passwords are stored in clear text in files.
If you need more security implement a differnt store for your passwords.

CREDIT: The Encryption implementation is taken from the dbfit library.

To encrypt a password run:

java -cp "commons-codec-1.9.jar;dbfit-core-3.2.0.jar" dbfit.util.crypto.CryptoApp **SECRET** -keyStoreLocation plugins\jdbcslim\TestDB\

Replace **SECRET** with your password
If no keyfile exists it will create a new one named .dbfit.jks
The keyfile must always be named: .dbfit.jks


Define the location of your key file with ".keyStoreLocation"
it is possible to use multiple key files in different locations.

Define Properties	SecretDefinition
key	value
user	James
.keyStoreLocation	plugins\jdbcslim\TestDB\
password	ENC(dP8utYfMtmrWZCylvGsOGw==)
cmd	Save the world!

Check that all the content is now available
and the password is not shown in clear text

Table: SheetCommandBase	SecretDefinition		SORT
Key?<1	Value?
cmd	Save the world!
password	****
sort
user	James

Script	Define Properties
show	show	SecretDefinition

Use the "getSecretProperty" function to access encrypted data in your fixture

Tests should be focused on requirements and in business users language.
On the other hand to implement the tests often additional configuration data is required.
To keep these separate Define Properties can be used.
They are like property files used commonly in Java.
They define key value pairs of strings

Define Properties have the following advantages:
* Properties can be defined on Wiki pages
* Properties can be defined in files
* Multiple property definitions can be merged into one big definition
* Properties like passwords can be encrypted
* A summary of all definitions can be added to the test results to track how the test was performed
* Slim Symbols or Wiki Variables can be used to switch between different sets of properties




This page tests the combination of properties definition on this Fitnesse test page plus in a properties file. From the file again a definition
on the wiki page is referenced.

The definitions are then used/printed in a Sheet Fixture to confirm the correctness.

The following rules applies

• In files lines or keys starting with "#" are comments and will be ignored
• All keys are trimmed and converted to lower case
• If multiple entries for the same key exist only the value from the last one will remain
• A key ".include" will add the content of "Sheet Definition Fixture" whose name matches the value. If such a definition doesn't exist content from a file with this name will be included. If no such file can be found the test will fail with an exception.
• A key ".keyStoreLocation" will load a file with a different encryption key
• Values which must span multiple lines can be inserted in files between triple quotes """. See sample file below.

To test this page you need a file with the following content:


The file must be named as defined in the following variable:
variable defined: DefinitionFileName=${TestDbPath}definition.properties

Define Properties	TopDefinition
key	value
cmd	myCommand with %NAME%, %CITY%, %AGE%
DEBUG
COMMAND	YES
.include	plugins\jdbcslim\TestDB\definition.properties
multiLine2	Hello World

The below "SubDefinition" will be included from the above file

Define Properties	SubDefinition
key	value
SORT	1,2,3,4
format.date	YYYY-MM-DD
version	OverwrittenFromSubDefintion

Check that all the content is now available

Table: SheetCommandBase	TopDefinition		SORT
Key?<1	Value?
cmd	myCommand with %NAME%, %CITY%, %AGE%
command	YES
dbpassword	password
dburl	jdbc:sybase:Tds:localhost/master
dbuser	sa
debug
format.date	YYYY-MM-DD
jdbcdriver	com.sybase.jdbc4.jdbc.SybDriver
multiline	Line1 Line2 Line3
multiline2	Hello World
nextkey	Something Else
sort
version	OverwrittenFromSubDefintion

The definition names are case sensitive
The keys are not case sensitive. only the last defined key will be available in the result map.

Define Properties	Def1
key	value
Hello	World
Mad	Max
Cmd	select GetDate()
HELLO	Heaven

Script: Define Properties
check	show	Def1	{Hello=World, Mad=Max, Cmd=select GetDate(), HELLO=Heaven, }
check	show	def1	ERROR:No Definitions exists under this name.

Table: SheetCommandBase	Def1		SORT
Key?<1	Value?
cmd	select GetDate()
hello	Heaven
mad	Max
sort

This needs a redesign - don't use!

Script	Define Properties	ScriptDefinition
begin table
setKey	foo
setValue	bar
show	echo
execute
setKey	foooo
setValue	baaar
show	echo
execute
end table
$ScriptEcho=	echo
check	echo	$ScriptEcho
show	show	ScriptDefinition
start	Define Properties	ScriptDefinition2
show	echo
check	echo	=~/null/

Define Properties	TopDefinition3
key	value
cmd	myCommand with %NAME%, %CITY%, %AGE%
DEBUG
COMMAND	YES
multiLine2	Hello World

Script	Define Properties
show	show	ScriptDefinition
show	getDefinition	TopDefinition3
show	show	TopDefinition3
check	show	UndefinedDefinition	ERROR:No Definitions exists under this name.

Define Properties	SymbolDefinition
key	value	echo?
cmd	myCommand with %NAME%, %CITY%, %AGE%]	$E=
DEBUG	$E	DEBUG=cmd=myCommand with %NAME%, %CITY%, %AGE%]
COMMAND	$YES	COMMAND=$YES
SORT	YES	$S=

Table: SheetCommandBase	TopDefinition3		$S
Key?<1	Value?
cmd	myCommand with %NAME%, %CITY%, %AGE%
command	YES
debug
multiline2	Hello World
sort	YES

It is good practise to clean up definitions which are not required any more.

Define Properties	ObsoleteDefinition
key	value
DBUSER	na
DBPASSWORD	na

Script	Define Properties
removeDefinition	ObsoleteDefinition

In case you need to pass more parameters then user and password to open the database connection
then you can define these in a separate list and pass this list with the parameter dbProperties to the jdbc open connection function

Define Properties	DatabaseProperties
key	value
USER	na
PASSWORD	na

Define Properties	ConnectionWithProperties
key	value
jdbcDriver	org.h2.Driver
DBURL	jdbc:h2:mem:h2db;DB_CLOSE_DELAY=-1
DBProperties	DatabaseProperties
CMD

SQLCommand	ConnectionWithProperties	select * from TestData where ID = '1'	Query
ID	Name	Phone	City	Profession
1	Bill	12345	Berlin	Doctor

The Header line of each SQL Command Table contains input and output columns.
Like in a DecisionTable output columns are marked with a question mark.
All columns without a question mark are input columns.

If the table has one ore more input columns the SQL command is executed once for each line in the table and the command must return exactly one result row.

Before each execution a string replacement happens. Any occurrence of a column name between percent signs is replaced with the actual value of the column.


Lets take an example.

SQLCommand	TestDatabase	select City from TestData where ID ='%ID%'
ID	CITY?
2	Denver
5	Paris

The above table has one input column ID and one output column CITY.
For the first line the code: select City from TestData[?] where ID ='2' will be executed and in the result set a column CITY is expected and the value is compared to 'Denver'
For the first line the code: select City from TestData[?] where ID ='5' will be executed and in the result set a column CITY is expected and the value is compared to 'Paris'

If the table has no input columns the SQL command is executed only once.
The result set can have more than one row and is compared against all rows in the table.

SQLCommand	TestDatabase	select ID, City from TestData where ID in ('2','5')
ID?	CITY?
2	Denver
5	Paris

Further Reading:

Some tables have many columns.
Inserting new data would require huge tables on the Wiki page.
But for many tests only some columns are essential.
The same is true for stored procedures with many parameters. A single test should focus on one or two parameters.
To keep the tests focused defaults can be defined for input columns.

Define Properties	myDefaults
key	value
City	Berlin

Define Properties	SelectByCityAndName
key	value
.include	TestDatabase
cmd	select * from TestData where Name ='%Name%' and City ='%City%'
#	Add above defined defaults to the command definition
InputDefaults	myDefaults

Table provides City as input - default will not be used

SQLCommand	SelectByCityAndName
Name	City	ID?	Phone?	Profession?
James	London

Table doesn't provides City as input - default will be used and a matching record found

SQLCommand	SelectByCityAndName
Name	City?	ID?	Phone?	Profession?
Bill

Table doesn't provides City as input - default will be used but no matching record found

comment:TODO SQLCommand	SelectByCityAndName
Name	City?	ID?	Phone?	Profession?
Ben

Empty expected values [Profession] are flagged blue and counted as ignored.

SQLCommand	TestDatabase	select Profession from TestData where NAME= '%NAME%' and CITY= '%CITY%'
NAME	CITY	PROFESSION?
James	London
Bert	Washington
Kim	New York

The SQL Commands Insert, Update and Delete return no normal output.
Instead they return the number of rows inserted, updated or deleted in a column called: Count

SQLCommand	TestDatabase	insert into TestData values (DEFAULT, '%Name%', '%Phone%', '%City%', '%Profession%')	dbgetgeneratedkeys
SCOPE_IDENTITY()?	City	Name	Phone	Profession	Count?
$ID1=	Berlin	Boby	12345	Doctor	1
$ID2=	Denver	Bob	6789	Artist	1
$ID3=	Sydney	Bob	6789	Artist	1

SQLCommand	TestDatabase	update TestData set City='%NewCITY%' where ID ='%ID%'
ID	NewCITY	Count?
$ID1	HongKong	1
$ID2	Tokyo	1

By defining the property DbUpdateCount a different column name can be defined.

Define Properties	UpdateDatabase
key	value
.include	TestDatabase
DbUpdateCount	HowManyUpdates

SQLCommand	UpdateDatabase	update TestData set City='%NewCITY%' where NAME like'%Name%'
Name	NewCITY	HowManyUpdates?
Bob%	HongKong	3

SQLCommand	TestDatabase	delete TestData where ID >='%ID%'
ID	Count?
$ID1	3
$ID2	0

The name which is used for replacing %X% must not match a column name. It can be any string

SQLCommand	TestDatabase	select ID,Name from TestData where ID = '%X%'
X	ID?	NAME?
2
4

Number Comparators can be used

SQLCommand	TestDatabase	select ID from TestData where ID = '%X%'
X	ID?
2	= 2
4	> 3
2	1<_ <3

String Comparators can be used

SQLCommand	TestDatabase	select NAME from TestData where ID = '%ID%'
ID	NAME?
2	=~/Be/
4	=~/Til/

Calling a SP/function with select

Define Properties	StoredProcedures
key	value
.include	TransactionDatabase
CMD	select SQRT(%No%) as root
debug

SQLCommand	StoredProcedures
No	root?
16	4.0

Input Parameters

To use Input Parameters you have to define a tuple of 3-4 values for each input column.
The values are separated with a ":".
1. Flag for Input or Output Parameter: I* = input parameter, O* = output parameter, I*O* = input and output parameter
2. Position in the statement, the first parameter has number 1
3. Integer number of SQL Type see the jdk java documentation or look here
4. The scale of the data type, might be required for VARCHAR, DECIMAL, etc. See the jdbc documentation of your database system. This flag is optional

These flags are defined in a separate Properties definition.

Define Properties	inParameters
key	value
FILL	I:3:12:0
No	in:1:8:0
inStr	Input:1:12
c	i:2:4

Link this definition to the query with the dbQueryParameters key

To avoid that the input columns are flageed red (as they are not used in string command replacements) add also the flag outputFlagUnusedInputColumns=false

SQLCommand	TransactionDatabase	select SQRT(?) as root	dbQueryParameters=inParameters outputFlagUnusedInputColumns=false
No	root?
9.0	3.0
16.0	4.0

SQLCommand	TransactionDatabase	select LPAD(?, ?, ?) as myString	dbQueryParameters=inParameters outputFlagUnusedInputColumns=false
inStr	c	FILL	myString?
xYz	7	*	****xYz
ABC	5	-	--ABC

|

Script	SQLCommand	TestDatabase
open Connection
execute	create alias IF NOT EXISTS selectStar for "six42.fitnesse.jdbcslim.SampleStoredProcedure.showTestData"
show	success
show	rawResult
show	resultSheet
execute	create alias IF NOT EXISTS myQuery for "six42.fitnesse.jdbcslim.SampleStoredProcedure.myquery"
show	success
show	rawResult
show	resultSheet
close Connection

SQLCommand	TestDatabase	call myquery('select * from TestData where Profession = ''Milkman''')	QUERY
ID	Name	Phone	City	Profession
3	Tom	4567890	Seoul	Milkman

Unused Input column names like [AGE] below are flagged as fail.
In such a case either the command should be adjusted or the column should be removed.





variable defined: TestPageName=TestPage

script
given Jdbc Slim test page	TestPage
and test content is	!|SQLCommand|TestDatabase|select Profession from TestData where NAME= '%NAME%' and CITY= '%CITY%'| |NAME |CITY |AGE |PROFESSION? | |James |London |43 | | |Bert |Washington |50 | | |Kim |New York |60 | |
when page	TestPage	is tested and HTML is extracted
then	\d	assertions pass,	2	fail,	3	are ignored	0	exceptions thrown
and cell	AGE	has result	fail
show Symbol	$HTML_Input
show Symbol	$HTML_Result
get collapsed executon log for page	TestPage

The SQL Command can be called with 1, 2 or 3 constructor parameters

Constructor with one parameter

In this case the parameter must be the name of a configuration either defined in a file or a table.
In addition the configuration must contain a "CMD" key with the sql code.

Define Properties	AllInOne
key	value
.include	TestDatabase
cmd	select ID, NAME from TestData where Name ='%NAME%'

SQLCommand	AllInOne
ID?	NAME
$TestID=	Ben
$TestID2=	Sarah

Constructor with two parameter

In this case the first parameter must be the name of a configuration either defined in a file or a table.
The second parameter must be the SQL code to be executed.

SQL Query only with Header with Input and Output Columns and with expected value

SQLCommand	TestDatabase	select * from TestData where ID = '%ID%'
ID	Name?	Phone?	City?	Profession?
3	Tom

Constructor with three parameter

In this case the first parameter must be the name of a configuration either defined in a file or a table.
The second parameter must be the SQL code to be executed
The third parameter can contain a list of further configuration parameters. The elements must be separated by spaces.

SQLCommand	TestDatabase	select ID,Name from TestData where ID in('3','5')	SORT=true debug=true
ID?<	NAME?
3	Tom
5	Sarah

If your query defines an alias for a column name like below "firstname" then you must use this name in the header.

SQLCommand	TestDatabase	select ID, Name as firstname from TestData	SORT SUBQUERY
ID?>	firstname?
5	Sarah
3	Tom

If you must use the table column name then add the flag dbUseColumnName

SQLCommand	TestDatabase	select ID, Name as firstname from TestData	SORT SUBQUERY dbUseColumnName
ID?>	Name?
5	Sarah
3	Tom

The below only applies to commands without any input values. Only these can return more than one row.

The SQL code should sort the result set so that it matches the expected values. If this is not possible as it is the output of a stored procedure the fixture can sort the actual result set. Expected values are never sorted. You have to provide them already in the order you expect them.

Use the symbol < to sort ascending and > to sort descending. Add this symbol to the end of the column name in the header. Finally add the keyword SORT as third parameter to the SQL command. Or add SORT as a key into a configuration property.

SQL Query sorted ascending on ID

SQLCommand	TestDatabase	select ID,Name from TestData where ID in('3','5')	SORT
ID?<	NAME?
3	Tom
5	Sarah

SQL Query sorted descending on ID

SQLCommand	TestDatabase	select ID, Name from TestData where ID in('3','5')	SORT
ID?>	NAME?
5	Sarah
3	Tom

SQL Query sorted descending on Name

SQLCommand	TestDatabase	select ID,Name from TestData where Name in('Tom','Sarah')	SORT
ID?	NAME?>
3	Tom
5	Sarah

SQL Query sorted first descending on City and second ascending on Name
adding the QUERY tag allows to omit the question mark at each header column name.

SQLCommand	TestDatabase	select * from TestData	SORT QUERY
ID	City>	Name<	Phone	Profession
7	Washington	Bert	432	President
3	Seoul	Tom	4567890	Milkman
5	Paris	Sarah	999999	Hero
8	New York	Kim	1	Banker
6	London	James	#null#	Butler
2	Denver	Ben	6789	Artist
1	Berlin	Bill	12345	Doctor
4	Berlin	Till	332211	Senator

Given a field like 'Phone' contains numbers
Then the column must be sorted based on number logic and not string logic.

use the flag 'd' next to the sort indicator to indicate integral decimal sorting
use the flag 'f' to indicate floating point sorting

SQLCommand	TestDatabase	select * from TestData	SORT QUERY
ID	City	Name	Phone<d	Profession
6	London	James	#null#	Butler
8	New York	Kim	1	Banker
7	Washington	Bert	432	President
2	Denver	Ben	6789	Artist
1	Berlin	Bill	12345	Doctor
4	Berlin	Till	332211	Senator
5	Paris	Sarah	999999	Hero
3	Seoul	Tom	4567890	Milkman

SQLCommand	TestDatabase	select * from TestData	SORT QUERY
ID	City	Name	Phone<f	Profession
6	London	James	#null#	Butler
8	New York	Kim	1	Banker
7	Washington	Bert	432	President
2	Denver	Ben	6789	Artist
1	Berlin	Bill	12345	Doctor
4	Berlin	Till	332211	Senator
5	Paris	Sarah	999999	Hero
3	Seoul	Tom	4567890	Milkman

If the query returns many rows but you just want to check some of them and want to ignore the others use the SUBQUERY flag.

SQLCommand	TestDatabase	select ID, Name from TestData	SORT SUBQUERY
ID?>	NAME?
5	Sarah
3	Tom

If you just want to see the row count of the recordset set the flag dbOnlyRowCount to the name of the column which should show the row count.
In this case you might want to measure performance. To do so set also one or all of the following:
dbPerf - Total processing time in seconds
dbPerfRetrieval - Time to get all rows and columns from the recordset from the database server into the application server
dbPerfExecution - Time to execute the statement on the database server

SQLCommand	TestDatabase	select d1.*, d2.*, d3.*, d4.*, d5.* from TestData d1 join TestData d2 join TestData d3 join TestData d4 join TestData d5	dbOnlyRowCount=RowCount dbPerf=ProcessingTime dbPerfRetrieval=RetrievalTime dbPerfExecution=ExecutionTime
RowCount?	ProcessingTime?	RetrievalTime?	ExecutionTime?
32768	<1	<1	<1

SQLCommand	TestDatabase	%SQL%	dbOnlyRowCount=RowCount dbPerf=ProcessingTime dbPerfRetrieval=RetrievalTime dbPerfExecution=ExecutionTime
SQL	RowCount?	ProcessingTime?	RetrievalTime?	ExecutionTime?
select d1.*, d2.*, d3.* from TestData d1 join TestData d2 join TestData d3	512	<1	<1	<1
select d1.*, d2.*, d3.*, d4.* from TestData d1 join TestData d2 join TestData d3 join TestData d4	4096	<1	<1	<1
select d1.*, d2.*, d3.*, d4.*, d5.* from TestData d1 join TestData d2 join TestData d3 join TestData d4 join TestData d5	32768	<5	<5	<5

This page demonstrates the SQL Command fixture in a script and a scenario.
The scenario uses two queries.
It also shows additional methods available to analyse the returned data.

Calling the SQLCommand directly in a Script

Define Properties	ScriptDefinition
key	value
.include	TestDatabase
cmd	select * from TestData

Script	SQLCommand	ScriptDefinition
openConnection
execute
show	success
show	rawResult
show	resultSheet
$RSOne=	resultSheet
closeConnection

Using SQLCommand in a scenario

The Scenario with two execute statements

Scenario	TheId	id	isNamed	name	andHasPhone	phone
execute	select Name from TestData where ID = '@id'
show	success
show	rawResult
show	resultSheet
check	getColumnValueByName	NAME	@name
#
execute	select * from TestData where ID = '@id'
show	success
show	rawResult
show	resultSheet
check	getColumnValueByName	Phone	@phone

Start the test

Open a connection

Script	SQLCommand	ScriptDefinition
openConnection

Call the scenario in a Decision Table

The Id Is Named And Has Phone
id	name	phone
2	Ben	>0
4	Till	>100
3	Tom	>10
2	=~/Be/	>5

Call the scenario in a Script Table

script
TheId	5	IsNamed	Sarah	AndHasPhone	999999

Close the connection

Script

closeConnection

This page calls the SQL Command fixture in a scenario
Results returned are assigned to symbols and can be used in future queries or fixtures.

Assign results from a query to symbols

SQLCommand	TestDatabase	select Id, Name from TestData where ID = '%X%'
X	ID?	NAME?
2	<= 3	$Name2=
4	>= 4	$Name4=
2	1<_ <3	Ben
3	$Id3=

script
check	echo	$Name2	Ben
check	echo	$Name4	Till
check	echo	$Id3	3

Define Properties	DefinitionOne
key	value
cmd	select * from Elements where ID = '%X%'
.include	TestDatabase

Scenario with two execute statements returning the result in symbols

Scenario	TheId	id	isNamed	name	andHasPhone	phone
execute	select * from TestData where ID = '@id'
show	success
show	rawResult
show	resultSheet
$name=	getColumnValueByName	Name
#
execute	select * from TestData where ID = '@id'
show	success
show	rawResult
show	resultSheet
$phone=	getColumnValueByName	Phone

Start the test

Open a connection

Script	SQLCommand	DefinitionOne
openConnection

Call the scenario

TheIdIsNamedAndHasPhone
id	name?	phone?
2	$Name2=	>0
4	$Name44=	>100
3	Tom	$Phone3=
2	=~/Be/	>5

script
check	echo	$Name2	Ben
check	echo	$Name44	Till
check	echo	$Phone3	4567890

Close the connection

Script

closeConnection

To insert or update null values define input parameters and use "?" syntax.
Specify null as #null#

To insert leading and trailing Spaces use the !- Denver -! syntax

Define Properties	inParameters
key	value
Name	I:1:12:0

SQLCommand	TestDatabase	insert into TestData values (DEFAULT, ?, '%Phone%', '%City%', '%Profession%')	dbgetgeneratedkeys dbQueryParameters=inParameters outputFlagUnusedInputColumns=false
SCOPE_IDENTITY()?	City	Name	Phone	Profession	Count?
$ID1=	Berlin	#null#	12345		1
$ID2=	Denver	Bob	6789		1

To test null values in output specify null as #null#

To test leading and trailing spaces use the !- text -! syntax or regex =~/\A text \z/

To test for an empty string use regex =~/\A\z/

Define this pattern in a variable it will likely changes in the future to something more readable

variable defined: EmptyString==~/\A\z/

SQLCommand	TestDatabase	select City, Name, Name|| City as CityName, Profession from TestData where ID ='%ID%'
ID	City?	Name?	CityName?	Profession?
$ID1	=~/\ABerlin\z/	#null#	#null#
$ID2	=~/\A Denver\z/	Bob	Bob Denver	=~/\A\z/

if you must insert the string #null# in the database you can define a different null pattern with the property inputNullString

Define Properties	inParameters
key	value
Name	I:1:12:0
City	I:2:12:0

SQLCommand	TestDatabase	update TestData set Name = ?, City = ? where ID ='%ID%'	inputNullString=--null-- dbQueryParameters=inParameters outputFlagUnusedInputColumns=false
ID	City	Name	Count?
$ID1	--null--	#null#	1

SQLCommand	TestDatabase	select City, Name, length(City) as CityLength, length(Name) as NameLength from TestData where ID ='%ID%'
ID	City?	Name?	CityLength?	NameLength?
$ID1	#null#	#null#	#null#	6

You can also change the output format for null values with the property outputNullString

SQLCommand	TestDatabase	select City, Name, length(City) as CityLength, length(Name) as NameLength from TestData where ID ='%ID%'	outputNullString=++null++
ID	City?	Name?	CityLength?	NameLength?
$ID1	++null++	#null#	++null++	6

FitNesse pages can be secured to control access to the Wiki. See Authentication

As Jdbc Slim is used for database testing and most databases have user management
capacities it is straight forward to use the database to authenticate FitNesse users.

To do this follow these steps:

1. Add the jdbc slim jar and the jdbc driver jar to the class path of FitNesse.
2. In the plugins.properties files add the following line

Authenticator =six42.fitnesse.jdbcslim.JdbcAuthenticator

3. Add a file JdbcAuthenticator.properties in the same folder as the plugins.properties
4. The file must contain two lines which define the database driver and the database connection string
No username or password must be specified!
See example below

JDBCDRIVER=org.h2.Driver

DBURL=jdbc:h2:mem:h2db;DB_CLOSE_DELAY=-1

Important: You should only use this Authenticator when you use SSL connections!

Otherwise your database password is transfered in plain text via the network.

Fixtures defined by JDBC Slim

1. SQL Command

2. Define Properties

This fixture is used to define 4 different kinds of properties

General Properties used by SQL Command

Define Properties	Name for Reference
key	value
first key	value

JDBC Database Connection Properties

Define Properties	Name for Reference
key	value
first key	value

Default Values

Define Properties	Name for Reference
key	value
column name	default value

Input & Output Parameters

Define Properties	Name for Reference
key	value
column name	In/Out:Position:Type:Scale

3. JDBC Authenticator

List of all flags supported by SQL Command

Key	Default Value	Example values separated with /	Usage
Connection Parameters
jdbcDriver		org.h2.Driver	Class name of the jdbc driver
dbUrl		jdbc:h2:mem:h2db	database connection string
dbProperties			Reference to another properties list which contains properties for the database connection. Check the documentation of your jdbc driver for the list of all possible properties. Normally the mandatory properties are username and password See
dbUser			Name of the database user which will be used to open the database connection
dbPassword			Password of the database user which will be used to open the database connection
dbAutoCommit	true	true / false	if false an explicit commit is required to make the changes visible in the database to other programs.
dbConnection			Name of a Connection to be able to reuse it See Or to use a connection object created externally See
Command Parameters
CMD		select * from table	the SQL command to be executed. This parameter is mandatory.
QUERY	false	true / false	If the SQL command is a query statement without place holders and which returns multiple rows than set this flag. In the header row the question mark in each column can then be omitted as all columns are output columns. See
dbQueryParameters		ParameterDefinitions	Reference to another properties list which contains the definitions for input and output parameters of an SQL statement. See
inputDefaults		DefaultValues	Reference to another properties list which contains default values for placeholders in an SQL command. See
RETRY	false	50:10000	if given re-executes the command until all assertions pass or the maximum execution time has passed. The first parameter is the number of retries the second parameter the maximum execution time in milliseconds. See
Input / Output Options
SORT	false	true / false	sort the output result based on indicators in the header line. See
SUBQUERY	false	true / false	Ignore additional rows in the result set See
dbUpdateCount	Count	false / Count	If not 'false' return the no of updates / inserts /deletes done by the SQL command. A column with the header named as the value of this flag is added to the results. See
dbGetgeneratedKeys	true		add ID generated by the database to the result
outputFlagUnusedInputColumns	true	true / false	If true and the table has input columns which are not used by any place holder than these columns will be marked as fail and the test will fail. Either remove these columns or set this flag to false. See
outputFlagExtraOutputColumns	true	true / false	If true and the result set has columns which are not in the table than these columns will be marked as fail and the test will fail. Change the SQL Command to not return extra columns or set this flag to false.
outputNullString	#null#		String used to display null values. See
inputNullString	#null#		String used to insert the value null into the database. If undefined the value from outputNullString will be used. See
Performance Metrics
dbOnlyRowCount	false	RowCount/ Rows	Instead of returning the data of the result-set returns only the row count of the same. A column with the header named as the value of this flag is added to the results. See
dbPerf	false	ProcessingTime	Sum of dbPerfRetrieval and dbPerfExecution. A column with the header named as the value of this flag is added to the results. See
dbPerfRetrieval	false	RetrievalTime	Time in seconds to retrieve the result data from the DB server into the Slim Server. A column with the header named as the value of this flag is added to the results. See
dbPerfExecution	false	ExecutionTime	Time in seconds to execute the SQL command. A column with the header named as the value of this flag is added to the results. See
Specials
DEBUG	false	false / true	If true prints debug information. Should only be used by Jdbc Slim developers
dbUseColumnName	true		If false then don't use user defined SQL column names. See
outputMultipleRecordsets AsExtraColumns	true	true / false	If the SQL command returns multiple result sets than these are joined into a single row. Set this parameter to false to get the data in separate rows. This is experimental as multiple recordsets returned from one command are unusual.
jdbcMaxloops	100		An Integer value which defines the maximum numbers of record sets and update counts which can be returned by a single SQL command. This is a safety parameter to avoid endless loops in case a JDBCdriver is badly written. Don't use.