This library supports database-related testing using the Robot Framework. It allows to establish a connection to a certain database to perform tests on the content of certain tables and/or views in that database. A possible scenario for its usage is a Web-Application that is storing data to the database based on some user actions (probably a quite common scenario). The actions in the Web-Application could be triggered using some tests based on Selenium and in the same test it will then be possible to check if the proper data has ended up in the database as expected. Of course there are various other scenarios where this library might be used.
As this library is written in Java support for a lot of different database systems is possible. This only requires the corresponding driver-classes (usually in the form of a JAR from the database provider) and the knowledge of a proper JDBC connection-string.
The following table lists some examples of drivers and connection strings for some popular databases.
The examples in the description of the keywords is based on a database table named "MySampleTable" that has the following layout:
MySampleTable:
COLUMN |
TYPE |
Id |
Number |
Name |
String |
EMail |
String |
Postings |
Number |
State |
Number |
LastPosting |
Timestamp |
NOTE: A lot of keywords that are targeted for Tables will work equally with Views as this is often no difference if Select-statements are performed.
Remote Library Support Sine release v2.0 Database Library supports the Remote Server Interface of the Robot Framework. This means you can start the Library as an own Server and use the provided keywords remotely. Especially for libraries written in Java (like this one) this has the big advantage that you can still use pybot to start the Robot Framework (no Jython required) and still use these keywords provided as a Java Library.
Please take a look at the sample project here (
https://github.com/ThomasJaspers/robotframework-dblibrary/wiki/Database-Library-Sample) to see how the Remote Server functionality can be utilized in your testsuites.
Keyword |
Arguments |
Documentation |
Check Content For Row Identified By Rownum |
columnNames, expectedValues, tableName, rowNumValue |
This keyword can be used to check for proper content inside a specific row in a database table. For this it is possible to give a comma-separated list of column names in the first parameter and a pipe-separated list of values in the second parameter. Then the name of the table and the rownum to check must be passed to this keyword. The corresponding values are then read from that row in the given table and compared to the expected values. If all values match the teststep will pass, otherwise it will fail.
Example:
Check Content for Row Identified by Rownum |
Name,EMail |
John Doe|john.doe@x-files |
MySampleTable |
4 |
@throws SQLException @throws DatabaseLibraryException |
Check Content For Row Identified By Where Clause |
columnNames, expectedValues, tableName, whereClause |
This keyword can be used to check for proper content inside a specific row in a database table. For this it is possible to give a comma-separated list of column names in the first parameter and a pipe-separated list of values in the second parameter. Then the name of the table and a statement used in the where-clause to identify a concrete row. The corresponding values are then read from the row identified this way and compared to the expected values. If all values match the teststep will pass, otherwise it will fail.
If the where-clause will select more or less than exactly one row the test will fail.
Example:
Check Content for Row Identified by WhereClause |
Name,EMail |
John Doe|john.doe@x-files |
MySampleTable |
Postings=14 |
@throws SQLException @throws DatabaseLibraryException |
Check Primary Key Columns For Table |
tableName, columnList |
Checks that the primary key columns of a given table match the columns given as a comma-separated list. Note that the given list must be ordered by the name of the columns. Upper and lower case for the columns as such is ignored by comparing the values after converting both to lower case.
NOTE: Some database expect the table names to be written all in upper case letters to be found.
Example:
Check Primary Key Columns For Table |
MySampleTable |
Id,Name |
@throws SQLException @throws DatabaseLibraryException @throws DatabaseLibraryException |
Compare Query Result To File |
sqlString, fileName |
Executes the given SQL compares the result to expected results stored in a file. Results are stored as strings separated with pipes ('|') with a pipe following the last column. Rows are separated with a newline.
To ensure compares work correctly The SQL query should a) specify an order b) convert non-string fields (especially dates) to a specific format
storeQueryResultToFile can be used to generate expected result files
Example:
Compare Query Result To File |
Select phone, email from addresses where last_name = 'Johnson' |
query_result.txt |
@throws SQLException @throws DatabaseLibraryException @throws FileNotFoundException |
Connect To Database |
driverClassName, connectString, dbUser, dbPassword |
Establish the connection to the database. This is mandatory before any of the other keywords can be used and should be ideally done during the suite setup phase. To avoid problems ensure to close the connection again using the disconnect-keyword.
It must be ensured that the JAR-file containing the given driver can be found from the CLASSPATH when starting robot. Furthermore it must be noted that the connection string is database-specific and must be valid of course.
Example:
Connect To Database |
com.mysql.jdbc.Driver |
jdbc:mysql://my.host.name/myinstance |
UserName |
ThePassword |
@throws ClassNotFoundException @throws IllegalAccessException @throws InstantiationException
|
Delete All Rows From Table |
tableName |
Deletes the entire content of the given database table. This keyword is useful to start tests in a clean state. Use this keyword with care as accidently execution of this keyword in a productive system will cause heavy loss of data. There will be no rollback possible.
Example:
Delete All Rows From Table |
MySampleTable |
@throws SQLException |
Disconnect From Database |
|
Releases the existing connection to the database. In addition this keyword will log any SQLWarnings that might have been occurred on the connection.
Example:
|
Execute Sql |
sqlString |
Executes the given SQL without any further modifications. The given SQL must be valid for the database that is used. The main purpose of this keyword is building some contents in the database used for later testing.
NOTE: Use this method with care as you might cause damage to your database, especially when using this in a productive environment.
Example:
Execute SQL |
CREATE TABLE MyTable (Num INTEGER) |
@throws SQLException @throws DatabaseLibraryException |
Execute Sql From File |
fileName |
Executes the SQL statements contained in the given file without any further modifications. The given SQL must be valid for the database that is used. Any lines prefixed with "REM" or "#" are ignored. This keyword can for example be used to setup database tables from some SQL install script.
Single SQL statements in the file can be spread over multiple lines, but must be terminated with a semicolon ";". A new statement must always start in a new line and not in the same line where the previous statement was terminated by a ";".
In case there is a problem in executing any of the SQL statements from the file the execution is terminated and the operation is rolled back.
NOTE: Use this method with care as you might cause damage to your database, especially when using this in a productive environment.
Example:
Execute SQL from File |
myFile.sql |
@throws IOExcetion @throws SQLException @throws DatabaseLibraryException |
Execute Sql From File Ignore Errors |
fileName |
Executes the SQL statements contained in the given file without any further modifications. The given SQL must be valid for the database that is used. Any lines prefixed with "REM" or "#" are ignored. This keyword can for example be used to setup database tables from some SQL install script.
Single SQL statements in the file can be spread over multiple lines, but must be terminated with a semicolon ";". A new statement must always start in a new line and not in the same line where the previous statement was terminated by a ";".
Any errors that might happen during execution of SQL statements are logged to the Robot Log-file, but otherwise ignored.
NOTE: Use this method with care as you might cause damage to your database, especially when using this in a productive environment.
Example:
Execute SQL from File |
myFile.sql |
@throws IOExcetion @throws SQLException @throws DatabaseLibraryException |
Get Primary Key Columns For Table |
tableName |
Returns a comma-separated list of the primary keys defined for the given table. The list if ordered by the name of the columns.
NOTE: Some database expect the table names to be written all in upper case letters to be found.
Example:
${KEYS}= |
Get Primary Key Columns For Table |
MySampleTable |
@throws SQLException @throws DatabaseLibraryException |
Get Transaction Isolation Level |
|
Returns a String value that contains the name of the transaction isolation level of the connection that is used for executing the tests. Possible return values are: TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE or TRANSACTION_NONE.
Example:
${TI_LEVEL}= |
Get Transaction Isolation Level |
@throws SQLException |
Read Single Value From Table |
tableName, columnName, whereClause |
Reads a single value from the given table and column based on the where-clause passed to the test. If the where-clause identifies more or less than exactly one row in that table this will result in an error for this teststep. Otherwise the selected value will be returned.
Example:
${VALUE}= |
Read single Value from Table |
MySampleTable |
EMail |
Name='John Doe' |
@throws DatabaseLibraryException
|
Row Should Not Exist In Table |
tableName, whereClause |
This keyword can be used to check the inexistence of content inside a specific row in a database table defined by a where-clause. This can be used to validate an exclusion of specific data from a table.
Example:
Row Should Not Exist In Table |
MySampleTable |
Name='John Doe' |
This keyword was introduced in version 1.1.
@throws SQLException @throws DatabaseLibraryException |
Store Query Result To File |
sqlString, fileName |
Executes the given SQL without any further modifications and stores the result in a file. The SQL query must be valid for the database that is used. The main purpose of this keyword is to generate expected result sets for use with keyword compareQueryResultToFile
Example:
Store Query Result To File |
Select phone, email from addresses where last_name = 'Johnson' |
query_result.txt |
@throws SQLException @throws IOException @throws DatabaseLibraryException |
Table Must Be Empty |
tableName |
Checks that the given table has no rows. It is a convenience way of using the "Table Must Contain Number Of Rows" with zero for the amount of rows.
Example:
Table Must Be Empty |
MySampleTable |
@throws DatabaseLibraryException @throws SQLException |
Table Must Contain Less Than Number Of Rows |
tableName, rowNumValue |
This keyword checks that a given table contains less than the given amount of rows. For the example this means that the table "MySampleTable" must contain anything between 0 and 1000 rows, otherwise the teststep will fail.
Example:
Table Must Contain Less Than Number Of Rows |
MySampleTable |
1001 |
@throws SQLException @throws DatabaseLibraryException |
Table Must Contain More Than Number Of Rows |
tableName, rowNumValue |
This keyword checks that a given table contains more than the given amount of rows. For the example this means that the table "MySampleTable" must contain 100 or more rows, otherwise the teststep will fail.
Example:
Table Must Contain More Than Number Of Rows |
MySampleTable |
99 |
@throws SQLException @throws DatabaseLibraryException |
Table Must Contain Number Of Rows |
tableName, rowNumValue |
This keyword checks that a given table contains a given amount of rows. For the example this means that the table "MySampleTable" must contain exactly 14 rows, otherwise the teststep will fail.
Example:
Table Must Contain Number Of Rows |
MySampleTable |
14 |
@throws SQLException @throws DatabaseLibraryException |
Table Must Exist |
tableName |
Checks that a table with the given name exists. If the table does not exist the test will fail.
NOTE: Some database expect the table names to be written all in upper case letters to be found.
Example:
Table Must Exist |
MySampleTable |
@throws SQLException @throws DatabaseLibraryException |
Tables Must Contain Same Amount Of Rows |
firstTableName, secondTableName |
This keyword checks that two given database tables have the same amount of rows.
Example:
Tables Must Contain Same Amount Of Rows |
MySampleTable |
MyCompareTable |
@throws SQLException @throws DatabaseLibraryException |
Transaction Isolation Level Must Be |
levelName |
Can be used to check that the database connection used for executing tests has the proper transaction isolation level. The string parameter accepts the following values in a case-insensitive manner: TRANSACTION_READ_UNCOMMITTED, TRANSACTION_READ_COMMITTED, TRANSACTION_REPEATABLE_READ, TRANSACTION_SERIALIZABLE or TRANSACTION_NONE.
Example:
Transaction Isolation Level Must Be |
TRANSACTION_READ_COMMITTED |
@throws SQLException @throws DatabaseLibraryException |
Verify Number Of Rows Matching Where |
tableName, where, rowNumValue |
This keyword checks that a given table contains a given amount of rows matching a given WHERE clause.
For the example this means that the table "MySampleTable" must contain exactly 2 rows matching the given WHERE, otherwise the teststep will fail.
Example:
Verify Number Of Rows Matching Where |
MySampleTable |
email=x@y.net |
2 |
@throws SQLException @throws DatabaseLibraryException |