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.



Included page: >Installation

Download the latest Jdbc Slim library from github.com\six42\jdbcslim

variable defined: JdbcSlimLib=jdbcslim.jar

Installation Path

Adujust the below path if you installed at a different location
This path is relative to the folder in which FitNesse got started
variable defined: LibInstallPath=plugins\jdbcslim\

The Jdbc Slim Library - always required
classpath: plugins\jdbcslim\jdbcslim.jar

Further dependencies

This is required to support encryption. It can be downloaded from https://github.com/dbfit/dbfit/releases/tag/v3.2.0
classpath: plugins\jdbcslim\commons-codec-1.9.jar
classpath: plugins\jdbcslim\dbfit-core-3.2.0.jar

See the SuiteSetup pages for driver specific setup.
JDBC driver used for the samples. Not required if you use a different JDBC driver
plugins\jdbcslim\h2-1.4.188.jar
plugins\jdbcslim\csvjdbc-1.0-18.jar

User Manual and Test Suite

To access the user manual and run the examples copy all Jdbc Slim Fitnesse pages from github to FitNesseRoot\PlugIns
Copy from github the folder plugins\jdbcslim\TestDB to plugins\jdbcslim\TestDB in your installation
variable defined: TestDbPath=${LibInstallPath}TestDB\

Execute the suite on .PlugIns.JdbcSlim


To build your own test pages

1. include this page on the root page of your suite
2. include the SuiteSetup page or a page with similar content in the setup of your suite


JdbcSlim.UserGuide

Top


JdbcSlim.UserGuide.1ASimpleExample

Top

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



JdbcSlim.UserGuide.1ASimpleExample.SeparatingSqlCommandsAndRequirements

Top

Requirements should not contain SQL code:


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:

Included page: >SqlSetUp

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

Define Properties CheckInitalCityOfUsers
key value
.include TransactionDatabase
cmd select City from TestData where ID ='%ID%'

Define Properties RelocateUserToNewCity
key value
.include TransactionDatabase
cmd update TestData set City='%NewCITY%' where ID ='%ID%'

Define Properties CheckFinalCityOfUsers
key value
.include TransactionDatabase
cmd select City from TestData where ID ='%ID%'

To avoid side effects we execute the test in a transaction
Script SQLCommand TransactionDatabase
open Connection
execute begin transaction

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

Included page: >SqlTearDown

Rollback the change to not impact future tests


Script SQLCommand TransactionDatabase debug
open Connection
execute rollback
close Connection

Check that the rollback worked

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

JdbcSlim.UserGuide.1ASimpleExample.WritingGenericRequirementsWithAlias

Top

Requirements should not contain SQL code:


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.

Included page: >SqlSetUp

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

Define Properties CheckInitalCityOfUsers
key value
.include TransactionDatabase
cmd select City from TestData where ID ='%ID%'

Define Properties RelocateUserToNewCity
key value
.include TransactionDatabase
cmd update TestData set City='%NewCITY%' where ID ='%ID%'

Define Properties CheckFinalCityOfUsers
key value
.include TransactionDatabase
cmd select City from TestData where ID ='%ID%'


Script SQLCommand FindTestUserIDs
$FTU= get fixture

Define alias
find test users table: $FTU

Script SQLCommand CheckInitalCityOfUsers
$CICoU= get fixture

Define alias
check first city of users table: $CICoU

Script SQLCommand RelocateUserToNewCity
$RUTNC= get fixture

Define alias
relocate users table: $RUTNC

Script SQLCommand CheckFinalCityOfUsers
$CFCoU= get fixture

Define alias
Check city after relocation table: $CFCoU



To avoid side effects we execute the test in a transaction
Script SQLCommand TransactionDatabase
open Connection
execute begin transaction


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

Included page: >SqlTearDown

Rollback the change to not impact future tests


Script SQLCommand TransactionDatabase debug
open Connection
execute rollback
close Connection

Check that the rollback worked

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


JdbcSlim.UserGuide.2KeyBenefits.1WaitingForAsynchronousResults

Top


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

JdbcSlim.UserGuide.2KeyBenefits.2TestingOnSideEffects

Top

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


Included page: .PlugIns.JdbcSlim.UserGuide.1ASimpleExample

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


Contents:



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

JdbcSlim.UserGuide.2KeyBenefits.3MultiTableStatement

Top

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:


Included page: >SqlStartUp

The Scenario with two execute statements

Scenario Get The Name name and phone number phone for id id
execute select Name 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



Define connection details and open a connection


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

Script SQLCommand ScriptDatabase
openConnection





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



Included page: >SqlTearDown

Close the connection

Script
closeConnection


See also: 7UsingSqlInScriptsAndScenarios



JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection

Top

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:

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.ConnectionReuseViaNaming

Top

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

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.CustomisingTheConnectionWithOwnImplementations

Top

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

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.EncryptingPasswords

Top


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

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.FeatureOverviewOfPropertyFilesAndTables

Top

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


Contents:


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

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

File Content

Version=v20131230

jdbcDriver=com.sybase.jdbc4.jdbc.SybDriver
DBURL=jdbc:sybase:Tds:localhost/master
DBUSER=sa
DBPASSWORD=password
.include=SubDefinition
multiLine="""
Line1
Line2
Line3
"""
nextKey=Something Else

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

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.FeatureOverviewOfPropertyFilesAndTables.CaseSensitive

Top

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


JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.FeatureOverviewOfPropertyFilesAndTables.PropertyDefinitionViaAScript

Top

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



JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.FeatureOverviewOfPropertyFilesAndTables.RemovingDefinitions

Top

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


Contents:

JdbcSlim.UserGuide.3HowtoConfigureTheDatabaseConnection.UsingProperties

Top

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands

Top

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:



JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.DefaultInputValues

Top

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.EmptyExpectedCellsAreMarkedAsIgnored

Top

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.InsertUpdateDelete

Top

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

Contents:


JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.ReplacerStringCanBeAnyName

Top

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.ResultComparatorsCanBeUsed

Top

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/

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.StoredProcedures

Top




To avoid side effects we execute the test in a transaction

Script SQLCommand TransactionDatabase
open Connection
execute begin transaction


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


Rollback the change to not impact future tests


Script SQLCommand TransactionDatabase
open Connection
execute rollback
close Connection

Check that the rollback worked

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.StoredProcedures.WithInputParamters

Top


To avoid side effects we execute the test in a transaction

Script SQLCommand TransactionDatabase
open Connection
execute begin transaction


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

|

Rollback the change to not impact future tests


Script SQLCommand TransactionDatabase
open Connection
execute rollback
close Connection

Check that the rollback worked

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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.StoredProcedures.WithRecordSetAsOutput

Top


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

JdbcSlim.UserGuide.4TheMappingBetweenTestDataAndCommands.UnusedInputColumnsAreFlagged

Top

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.

Included page: .FitNesse.SuiteAcceptanceTests.SuiteSlimTests.SetUp

Import
fitnesse.fixtures

SetUp CustomComparators=glob:fitnesse.testsystems.slim.GlobComparator

Library
page driver
echo fixture




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



JdbcSlim.UserGuide.5ParametersOfTheSQLCommand

Top

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

JdbcSlim.UserGuide.6OutputOptions

Top


JdbcSlim.UserGuide.6OutputOptions.ColumnAlias

Top


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

JdbcSlim.UserGuide.6OutputOptions.Sorting

Top

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

Contents:

JdbcSlim.UserGuide.6OutputOptions.Sorting.SortNumbers

Top


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

Contents:

JdbcSlim.UserGuide.6OutputOptions.SubQuery

Top


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

JdbcSlim.UserGuide.6OutputOptions.TotalOnly

Top


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

JdbcSlim.UserGuide.7UsingSqlInScriptsAndScenarios

Top



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

JdbcSlim.UserGuide.7UsingSqlInScriptsAndScenarios.SymbolAssignmentOfResults

Top

Contents:


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.

setup



import
fitnesse.slim.test

library
echo script




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

JdbcSlim.UserGuide.9NullTrailingSpacesAndEmptyStrings

Top


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




Contents:


JdbcSlim.UserGuide.JdbcAuthenticator

Top

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.

JdbcSlim.XIReferenceSheet

Top

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.