Use Connect to initialise the database connection. Alternatively, use ConnectUsingFile to initialise the connection using properties from a file.
The Connect table can be used in two ways:
Pass the server (optionally followed by the instance name), username, password, and the database name as arguments.
For example, to connect to a locally installed version of SqlServer 2005 Express:
!|Connect|LAPTOP\SQLEXPRESS|FitNesseUser|Password|TestDB|
Here is an Oracle example:
!|Connect|localhost:1521|FitNesseUser|Password|XE|
If you want to use non-standard connection properties, or initialise your connection differently, call Connect with a single argument - the full ADO.NET or JDBC connection string. Here is an example:
|Connect|jdbc:sqlserver://myhost\myinstance;user=myuser;password=mypassword;databaseName=mydbname|
You can use this feature, for example, to utilise Windows integrated authentication or to use the OCI driver for Oracle under Java.
For additional information about database connection settings, please refer to:
|Close| command.
You can also store connection properties in a file, then initialise the connection using the ConnectUsingFile table. This allows you to hide actual database usernames and passwords from FitNesse users, should you need to do so.
ConnectUsingFile has only one argument - the path of the file on the server, either absolute or relative to the folder from which you started FitNesse (the one containing startFitnesse). The connection properties file is a plain text file, containing key/value pairs separarted by the equals symbol (=). Lines starting with a hash (#) are ignored. Use the following keys (they care case-sensitive):
service - service name, eg LAPTOP\SQLEXPRESS.username - username to connect to the database.password - password to connect to the database.database - optional fourth argument, allowing you to choose the active database.connection-string - alternative to the four previous parameters, this allows you to specify the full connection string. This parameter should not be mixed with any of the four other keys. Use either the full string or specify individual properties.Here is an example:
# DBFit connection properties file
#
#1) Either specify full connection string
#connection-string=
#
#2) OR specify service, username and password as separate properties
service=localhost
username=root
password=
#optionally specify a database name
database=dbfit
|Close| command.
DbFit enables you to use Fixture symbols as global variables during test execution, to store or read intermediate results. The .NET syntax to access symbols (>>parameter to store a value and <<parameter to read the value) is supported in both .NET and Java versions. To suppress the output of the value you may use >>>parameter and <<<parameter. In addition, you can use the Set Parameter command to explicitly set a parameter value to a string.
|Set parameter|username|arthur|
DbFit is type sensitive, which means that comparing strings to numbers, even if both have the value 11, will fail the test. Most databases will allow you to pass strings into numeric arguments, but if you get an error that a value is different than expected and it looks the same, it is most likely due to a wrong type conversion. Keep that in mind when using Set parameter. A good practice to avoid type problems is to read out parameter values from a query. This will be explained in detail soon. Another way to solve that problem is to specify a custom parser:
|Set parameter|partscount|7|java.lang.Integer|
The last parameter, java.lang.Integer in the above example, is expected to be class name with a static method Object valueOf(String) or Object parse(String). DbFit will use it to convert the given text to a Java object, and that object will be set as the parameter value.
You can also use the keyword NULL to set a parameter value to NULL.
Query is similar to traditional FIT RowFixture, but uses SQL Query results. You should specify query as the first fixture parameter, after the Query command. The second table row contains column names, and all subsequent rows contain data for the expected results. You do not have to list all columns in the result set — just the ones that you are interested in testing.
!|Query|select 'test' as x|
|x |
|test |
Query ignores row order by default. In flow mode, the Ordered Query command provides order checking.
Partial key matching is supported, like in RowFixture: columns with a question mark in their name are not used to match rows, just for value comparisons. You can use this to get better error reports in case of failed tests. It is a good practice to put a question mark after all column names that are not part of the primary key.
Rows in the actual result set and FitNesse table are matched from top to bottom, looking for equal values in all cells that are not marked with a question mark. If there are no key columns, then the first row will be taken as a match (which effectively acts as the Ordered Query). All non-key columns are used for value comparisons, not for deciding whether or not a row exists in the result set.
Query will report any rows that exist in the actual result set and not in the FitNesse table (those will be marked as surplus), rows that exist in the FitNesse table but not in the actual result set (marked as missing). All matched rows are then checked for values in columns, and any differences will be reported in individual cells. You can use a special fail [expected value] syntax to invert the test, making it fail if a certain value appears in the row:
This will fail because the order is wrong
|Ordered Query|SELECT n FROM ( SELECT 1 as n union select 2 union select 3 ) x|
|n |
|fail[2] |
|fail[1] |
|3 |
This will pass because the order is correct
|Ordered Query|SELECT n FROM ( SELECT 1 as n union select 2 union select 3 ) x|
|n |
|1 |
|2 |
|3 |
You can use query parameters (DB-specific syntax is supported, eg. @paramname for SQLServer and MySQL, and :paramname for Oracle). Corresponding fixture symbol values are automatically used for named query parameters.
|Set Parameter|depth|3|
|Query|SELECT n FROM ( SELECT 1 as n union select 2 union select 3 union select
4) x where n<@depth |
|n|
|2|
|1|
You can store elements of the result set into parameters — to re-use them later in other queries and stored procedures. Use >>parameter to store a cell value into a parameter. You can also use <<parameter to read a cell value from a parameter (for comparisons, for example).
If you use the query just to read out stuff into parameters, then make sure to mark the columns with the question mark to avoid row matching. There will be nothing to match the rows with in this case, so a proper comparison would fail.
!|query|select now() as currd|
|currd?|
|>>tsevt|
To test for an empty query, you still need to specify the second row (result set structure), but don’t supply any data rows.
If you want to prevent DbFit from mapping parameters to bind variables (eg to execute a stored procedure definition that contains the @ symbol in Sql Server), disable bind symbols option before running the query.
|set option|bind symbols|false|
|execute| insert into users (name, username) values ('@hey','uuu')|
|query|select * from users|
|name|username|
|@hey|uuu|
Remember to re-enable the option after the query is executed. You can use the same trick with the Execute command.
You can use multi-line queries by enclosing them into !- and -!. This will also prevent any special character formatting. This trick can also be used with Oracle to prevent the concatenation operator || from being treated as a FitNesse cell boundary.
Some databases treat CHAR type as fixed length and fill content up to the specified length with spaces. FitNesse strips trailing spaces by default from cell contents, which makes it hard to compare CHAR types. DbFit provides a workaround for this, that must be enabled manually since it modifies standard string parsing. To enable this option, include the following table in your tests:
|set option|fixed length string parsing|true|
After that,you can enclose strings into single-quotes ('my string') and put trailing spaces before the closing quote. This allows you to ensure that the correct length of the string is used for comparisons. Here is an example (this example is for SQL Server, since MySql strips trailing spaces):
!3 use fixed string length parsing to test blank-padded chars
|Execute|Create table datatypetest (s1 char(10), s2 nchar(10))|
|set option|fixed length string parsing|true|
|insert|datatypetest|
|s1|s2|
|testch|testnch|
direct comparison will fail
|query|select * from datatypetest|
|s1?|s2?|
|fail[testch]|fail[testnch]|
use single quotes to pad to appropriate length
|query|select * from datatypetest|
|s1?|s2?|
|'testch '|'testnch '|
Insert is the database equivalent of FitLibrary SetupFixture — it builds an insert command from the parameters in a data table (and executes the insert once for each row of the table). The view or table name is given as the first fixture parameter. The second row contains column names, and all subsequent rows contain data to be inserted.
|Execute|Create table Test_DBFit(name varchar(50), luckyNumber int)|
|Insert|Test_DBFit|
|name|luckyNumber|
|pera|1|
|nuja|2|
|nnn|3|
|Query|Select * from Test_DBFit|
|name|lucky Number|
|pera|1|
|nuja|2|
|nnn|3|
|Execute|Drop table Test_DBFit|
Columns with a question mark are used as outputs. When an output column is used, it will contain the value of the column in the new record. This is especially handy for retrieving an auto-generated primary key. For Oracle, this works regardless of whether the column was actually the ID or something else populated with a trigger. For MySQL and SQL Server, only single- column actual primary keys can be returned. The only thing that makes sense to do at this point is to store values of the output cells into variables.
!3 Use ? to mark columns that should return values
!|Insert|users|
|username|name|userid?|
|pera|Petar Detlic|>>pera|
|Mika|Mitar Miric|>>mika|
|Zeka|Dusko Dugousko|>>zeka|
|DevNull|null|>>nll|
!3 Confirm that IDs are the same as in the database
!|Ordered Query|Select * from users|
|username|name|userid|
|pera|Petar Detlic|<<pera|
|Mika|Mitar Miric|<<mika|
|Zeka|Dusko Dugousko|<<zeka|
|DevNull|null|<<nll|
!3 Stored values can be used in queries directly
|Query|Select * from users where userid=@zeka|
|username|name|userid|
|Zeka|Dusko Dugousko|<<zeka|
When the test runs, you will see actual values being stored into variables.
Update allows you to quickly script data updates. It builds the update command from the parameters in a data table and executes the update once for each row of the table. Columns ending with = are used to update records (cell specifies new data value). Columns without = on the end are used to select rows (cell specifies expected column value for the select part of update command). The view or table name is given as the first fixture parameter. The second row contains column names, and all subsequent rows contain data to be updated or queried. This example updates the username column where the name matches arthur dent.
|insert|users|
|name|username|
|arthur dent|adent|
|ford prefect|fpref|
|zaphod beeblebrox|zaphod|
|update|users|
|username=|name|
|adent2|arthur dent|
|query|select * from users|
|name|username|
|arthur dent|adent2|
You can use multiple columns for both updating and selecting, and even use the same column for both operations. You can also use parameters — eg. <<paramname — in any cell.
Execute procedure executes a stored procedure or function for each row of data table, binding input/output parameters to columns of the data table, eg:
!|Execute Procedure|MyStoredProcedure|
|param1|param2|sum? |
|2 |2 |4 |
|5 |6 |11 |
First row (mandatory)
Second row (applicable only if the procedure has parameters)
you can even insert blanks and split names into several words to make the test page more readable. Blanks are removed by DbFit to get the parameter name (eg second string => secondstring):
!|Execute Procedure|ConcatenateStrings |
|first string|second string|concatenated?|
|Hello |World |Hello World |
|Ford |Prefect |Ford Prefect |
to use IN/OUT parameters, you’ll need to specify the parameter twice. Once without the question mark, when it is used as the input; and one with the question mark when it is used as output:
|Execute Procedure|Multiply|
|factor|val|val? |
|5 |10 |50 |
Subsequent rows (applicable only if the procedure has parameters)
you can store any output value into a parameter with the >> syntax or send current parameter values to procedure using << syntax:
!|Execute Procedure|ConcatenateStrings |
|first string|second string|concatenated?|
|Hello |World |>>result |
!|Execute Procedure|ConcatenateStrings |
|first string|second string|concatenated? |
|<<result |Again |Hello World Again|
For the case where neither parameters nor return values are specified, Execute Procedure should be called with just one row (without a row for column header names).
!|Execute Procedure|MakeUser|
If a function is getting called, then a column containing just the question mark is used for function results.
!3 Stored functions are treated like procs - just put ? in the result column header
!|Execute Procedure|ConcatenateF |
|first string|second string|? |
|Hello |World |Hello World |
|Ford |Prefect |Ford Prefect|
!3 ? does not have to appear on the end (although it is a good practice to put it there)
!|Execute Procedure|ConcatenateF |
|second string|? |first string|
|World |Hello World |Hello |
|Prefect |Ford Prefect|Ford |
Execute Procedure command has no effect on the result of the test - unless an error occurs during processing.Normally, the test would fail if a database exception occurs. If you want to test a boundary condition that should cause an exception, then use Execute Procedure Expect Exception variant of the Execute Procedure command, eg:
!|Execute Procedure Expect Exception|createuser|
|new name |new username |
|arthur dent|adent |
You can even specify an optional exception code as the third argument. If no exception code is specified, then the test will pass if any error occurs for each data row. If the third argument is specified, then the actual error code is also taken into consideration for failing the test.
!|Execute Procedure Expect Exception|createuser|1062|
|new name |new username |
|arthur dent|adent |
Execute Procedure Expect Exception variant is not directly available as a separate table in standalone mode. If you need this functionality in standalone mode, then extend the ExecuteProcedure fixture and call the appropriate constructor. That class has several constructors for exceptions and error codes.
For detailed exception code verifications to work with SQL Server, user message must be registered for that particular error code, or SQL Server throws a generic error code outside the database. Here is how you can declare your error code:
sp_addmessage @msgnum = 53120, @severity=1, @msgtext = 'test user defined error msg'
Execute executes any SQL statement. The statement is specified as the first fixture parameter. There are no additional rows required for this command.
You can use query parameters in the DB-specific syntax (eg. @paramname for SQLServer and MySQL, and :paramname for Oracle). Currently, all parameters are used as inputs, and there is no option to persist any statement outputs.
!3 to execute statements, use the 'execute' command
|Execute Ddl|Create table Test_DBFit(name varchar(50), luckyNumber int)|
|Execute|Insert into Test_DBFit values ('Obi Wan',80)|
|Set parameter|name|Darth Maul|
|Execute|Insert into Test_DBFit values (@name,10)|
|Query|Select * from Test_DBFit|
|Name|Lucky Number|
|Darth Maul|10|
|Obi Wan|80|
|Execute Ddl|Drop table Test_DBFit|
Execute Ddl is intended for executing DDL SQL statements (like create, alter, drop). It’s similar to Execute with the main difference that it doesn’t support bind variables, and also automatically handles required post-execute activities, if any (e.g. Teradata requires transaction to be closed after each DDL statement).
The statement is specified as the first fixture parameter. There are no additional rows required for this command.
!|Execute Ddl|create table tab_with_trigger(x int)|
!|Execute Ddl|create or replace trigger trg_double_x before insert on tab_with_trigger for each row begin :new.x := :new.x * 2; end;|
!|Insert|tab_with_trigger|
|x |
|13 |
!|Query|select x from tab_with_trigger|
|x |
|26 |
!|Execute Ddl|drop table tab_with_trigger|
Inspect is a utility fixture class used to quickly extract meta-data information from the database, and print it out in a form which can be easily converted into a test. It can work in three modes: Query, Table or Procedure. In the Query mode, it expects a full query as argument (bound variables are supported), and prints out both the result structure and result data. In the Table mode, it expects a table or view name as an argument and prints out the table or view column names (without actual data, just the structure). In Procedure mode, it expects a procedure name as an argument and prints out the procedure parameter names. These tables can be easily converted into Query, Execute Procedure, Insert or Update tables.
In flow mode, these three inspections are available as individual commands Inspect query, Inspect table and Inspect procedure. In standalone mode, you can extend the Inspect fixture and set the appropriate mode manually while calling the constructor.
!3 Inspect Procedure prints procedure arguments to be used for Execute procedure
!|Inspect Procedure|ConcatenateStrings|
!3 Inspect Table prints table/view columns to be used for Insert/Update/Query
procedure
!|Inspect Table|users|
!3 Inspect query prints columns and data
|Insert|users|
|name|username|
|david haselhoff|dhoff|
|arthur dent|adent|
!|Inspect query|select * from users|
When the test is executed, FitNesse will append meta-data and results to the test tables in gray colour. To convert the results into a new test, select the entire table in the browser, directly from the rendered results page (not from the HTML source or wiki source), and copy it. Internet Explorer allows you to get just a few rows at a time, while in some versions of Firefox you have to select the entire table in order to copy it properly. Edit the test page, delete the old table and paste the contents of the clipboard into the page editor. You should see the results table with column values separated by tabs. Click the Spreadsheet to FitNesse button below the editor text box. This turns the tab-separated results table into a FitNesse test table, converting the tabs into pipes to separate cells and even putting the exclamation mark before the first row automatically.
Store Query reads out query results and stores them into a Fixture symbol for later use. Specify the full query as the first argument and the symbol name as the second argument (without >>). You can then use this stored result set as a parameter of the Query command later:
!|Store Query|select n from ( select 1 as n union select 2 union select 3) x|firsttable|
!|query|<<firsttable|
|n |
|1 |
|2 |
|3 |
You can also directly compare two stored queries and check for differences.
Compare Stored Queries compares two previously stored query results. Specify symbol names as the first and second argument (without <<). The query structure must be listed in the second row. (Use Inspect Query to build it quickly if you do not want to type it.) Column structure is specified so that some columns can be ignored during comparison (just don’t list them), and for the partial row-key mapping to work. Put a question mark after the column names that do not belong to the primary key to make the comparisons better. The comparison will print out all matching rows in green, and list rows that are in just one query with red (and fail the test if such rows exist). If some rows are matched partially, just by primary key, differences in individual value cells will also be shown and the test will fail.
|execute|create table testtbl (n int, name varchar(100))|
!|insert|testtbl|
|n |name |
|1 |NAME1 |
|3 |NAME3 |
|2 |NAME2 |
|Store Query|select * from testtbl|fromtable|
|Store Query|!-select n, concat('NAME',n) as name from ( select 1 as n union
select 3 union select 2) x-!|fromdual|
|compare stored queries|fromtable|fromdual|
|name |n? |
|execute|drop table testtbl|
A good test should be focused and fast to execute. That helps to get a quick and precise feedback which leads the root cause of the test failure. That usually implies using very small data sets (1-3 rows) in DbFit tests. Nevertheless, in some special cases it may be desired to run DbFit on top of relatively large number of rows.
When a large number of rows is being compared, match failures can be hard to see amongst the matched rows. It is possible to hide successfully matched rows by using hide matching rows.
!|Compare Stored Queries Hide Matching Rows|fromtable|fromdual|
|name |n? |
For better performance when comparing large data sets it’s recommended to sort the inputs in ascending order of the comparison key columns.
!|Store Query|select * from big_table1 order by name|query1|
!|Store Query|select * from big_table2 order by name|query2|
!|Compare Stored Queries|query1|query2|
|name |n? |
If it’s needed to compare really huge sets: it’s most likely best to push down the heavy comparison to the backend database instead of doing it in DbFit.
By default, each individual test (FitNesse page) in flow mode is executed in a transaction that is automatically rolled back after the test. In standalone mode, you are responsible for overall transaction control.
If in flow mode, you can use the Commit and Rollback commands to control the transactions manually, but remember that a final rollback will be added at the end of the test. These commands have no additional arguments.
In standalone mode, you will probably control transactions from outside DbFit. Utility commands to commit and rollback are still provided, if you need them. For example, use this table to rollback:
|Rollback|
Or in order to commit:
|Commit|
For standaone mode only, an alternative syntax is also still supported:
!|DatabaseEnvironment|
|Rollback|
By default DbFit is using autocommit=false mode - which means that the database statements are executed in one transaction. In some special occasions, you may need to change that. It’s possible to configure database connection’s autocommit mode via:
|set option|autocommit|true|
Possible values are:
true - automatically commit after each statementfalse (this is the default) - transaction is terminated on explicit commit or rollback (for flow mode such is performed at the end of the transaction).auto - driver default (DbFit doesn’t explicitly set autocommt mode, the JDBC driver decides)