SQL Data Bindings for Gwen

SQL Data Bindings have been introduced to Gwen v2.1.0 to enable easy access to data stored in databases. This is handy for cases where you need to fetch data from a database and do some work with that data. For example, checking that the value of a field in a database contains some expected value or assigning a value stored in (or derived from) a database to an attribute and doing something with it.

The DSL for binding the result of an SQL query to an attribute is defined as follows:

<attribute> <is|will be> defined by sql "<selectStmt>" in the <dbName> database

Where:

• attribute = the name of the attribute to bind the fetched data to
• selectStmt = the SQL select statement used to fetch the data
• dbName = a unique name assigned to the database connection

DB Connection Setup

Setting up a database connection is a two step once-off process.

1. Configure the database connection by defining the following Gwen settings. Replace dbName  with the name you want to assign to your connection.
   • gwen.db.dbName.driver = the name of the JDBC driver class
   • gwen.db.dbName.url = the URL to the database containing all connection properties including credentials
2. Point your GWEN_CLASSPATH environment variable to the location of the JDBC driver JAR. You could choose to set this in a wrapper script (bat or shell) or in the environment settings of your local machine.

For more details, see SQL Data Bindings on our wiki.

Any number of database connections can be configured. You just need to assign a unique dbName to each connection and configure the driver and url settings using that name and add any required JDBC driver JARs to your GWEN_CLASSPATH variable (multiple JAR entires can be delimited using the standard Java classpath separator for the platform you are on).

Simple Usage

Assuming you have a database named ‘feedback‘ configured as per the MySQL example here, you can then bind an SQL query that fetches an email address (for example) to an attributed named ‘my email‘ as shown below. Ideally you should define this binding in a Gwen Meta file.

Given my email is defined by sql "select email from subscribers where name='gweninterpreter'" in the feedback database

In this example:

• attribute = my email
• selectStmt = select email from subscribers where name=’gweninterpreter’
• dbName = feedback

With this binding in place, you can then reference the ‘my email‘ attribute anywhere in your features and Gwen will execute the SQL query to fetch the email value from the database and bind it in place.

For example, if you perform an assertion as follows, Gwen will fetch and bind the ‘my email‘ value before doing the comparison.

# compare email in database with expected literal
Then my email should be "gweninterpreter@gmail.com"

As another example, you can also enter the fetched email value into a field on a web page:

# web page field binding (in Meta)
Given the email field can be located by name "email"

# enter email value fetched from database into field on web page
When I enter my email in the email field

Advanced Usage

You can also use string interpolation to soft code the ‘name‘ parameter in the above query to another attribute called ‘my name‘ by redefining the SQL in your binding as follows instead:

select email from subscribers where name='${my name}'

Now whatever value is bound to the ‘my name‘ attribute in memory will be substituted for the ‘${my name}‘ placeholder in the SQL string before Gwen executes it. This value could be the captured value of a field on a web page, a property value, an assigned literal, or any other type of Gwen binding (another SQL binding even).

Advertisement