Drive data with databases

Learn how to use any database as part of your automation flow.

What you learn in this lesson

  • How to use the database building block
  • How to include dynamic data from your custom queries, directly in your automation flowchart

Lesson Overview

Learn how to use any database as part of your automation flow.

What you learn in this lesson

  • How to use the database building block
  • How to include dynamic data from your custom queries, directly in your automation flowchart

In this video I’ll demonstrate the Database building block.

This building block allows you to execute queries against all kinds of databases which gives you the opportunity to prepare applications that you want to use automation for or simply just get data to use in the case.

I’m gonna use a Microsoft SQL server in this video.

First I’m gonna add a Database building block.

  • Add Database block

It’s says Experimental block, but this only means that you can expect some minor bug fixes in the upcoming releases. You can still use the building block and when it has matured the sign will go away. So it might be that when you see this video the sign is not in the latest release.

The first field in the Database building block is the Connection field. The database building block is based on ODBC, so it can be used with any database that has an ODBC driver – meaning literally all databases on the market.

Instead of entering a full ODBC connection string in this field, I prefer to use the ODBC Data Source administrator, to create a DSN – Data Source Name – and then just referrer to this in the Connection field.

To create a DSN, just type ODBC in the start menu, and select the ODBC Administrator.

  • Open ODBC from the Windows Start menu.

I have already set up an ODBC connection named ‘LocalSQL’, which points to a Leaptest database on a local SQL server.

  • Show SQL server Admin

I have a table named “customers” in the database with 5 fields.

  • Focus back in Leaptest

With the ODBC connection in place we can move back into Leaptest.

In the connection field I will enter ‘LocalSQL’ which points to the ODBC connection I just showed you. In the ‘query’ field I will query all customers.

  • Add “SELECT * FROM Customers”

When I click “Refresh” the building block will execute the query against the database using the ODBC connection. The columns returned from the query are now added as properties on the building block.

Remark that when you run the case in production, the machine where the Agent is running, should have the same ODBC connection setup, and of course also have access to the database.

I’ll add a Log message building block to investigate the result from the query.

  • Add Log Message

I’ll wire up the database columns to some fields in the Log Message and compose a message.

  • Wire up properties in Database with Log Message fields.
  • Compose message in Log Message
  • Add Pass

So let’s try to run it.

  • Run case

As we can see, only the first data row returned from the query is written to the log. This is because the default value for the Method field is set to “First row”. If we want other rows than the first we can change the Method setting to either “Row index” – which allows us to specify the exact row number in the returned data set or “Iterate” that will iterate all data rows returned.

Let’s set it to “Iterate”. This means the green output connector is triggered for each data row, and when all data rows have been iterated the “Completed” output connector will be triggered.

I’ll just move the Pass building block to the Completed connector and run the case.

  • Move Pass
  • Run case.

As we can see all data rows are now iterated and written to the log.

If we are interested in how long the query took, we can get this information from the Response time property. We also have the total number of rows returned as a property.

I’ll just combine these 2 properties into the Pass block.

  • Wire up properties with fields
  • Compose message
  • Run case

As you can see executing a query against a local database is very fast.

As mentioned in the start of the video you can use this with any database that has an ODBC driver, so it’s possible to integrate Leaptest with literally any database on the market.

This ends this video.