DatabaseLibrary - Documentation

Version: v2.0
Scope: global
Named arguments: not supported

Introduction

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.
Database Driver Name Sample Connection String Download Driver
MySql com.mysql.jdbc.Driver jdbc:mysql://servername/dbname http://dev.mysql.com/downloads/connector/j/
Oracle oracle.jdbc.driver.OracleDriver jdbc:oracle:thin:@servername:port:dbname http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.html
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.

Shortcuts

Check Content For Row Identified By Rownum  ·  Check Content For Row Identified By Where Clause  ·  Check Primary Key Columns For Table  ·  Compare Query Result To File  ·  Connect To Database  ·  Delete All Rows From Table  ·  Disconnect From Database  ·  Execute Sql  ·  Execute Sql From File  ·  Execute Sql From File Ignore Errors  ·  Get Primary Key Columns For Table  ·  Get Transaction Isolation Level  ·  Read Single Value From Table  ·  Row Should Not Exist In Table  ·  Store Query Result To File  ·  Table Must Be Empty  ·  Table Must Contain Less Than Number Of Rows  ·  Table Must Contain More Than Number Of Rows  ·  Table Must Contain Number Of Rows  ·  Table Must Exist  ·  Tables Must Contain Same Amount Of Rows  ·  Transaction Isolation Level Must Be  ·  Verify Number Of Rows Matching Where

Keywords

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:
Disconnect from Database
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