Working with SQL databases in NeoLoad

In Tricentis NeoLoad, SQL database support is built-in out of the box. Its configuration is straightforward and implementing in projects is extremely easy. Manipulating an SQL database is typically made up of queries, and as with any database, the first thing you must do is create your connection. In NeoLoad you do so by creating an Advanced action under Database (SQL Connection).

Here you provide the essential connecting values such as the connection’s name, which will later be the value string you call to send commands to your database, the URL, username, and password to the database. Many savvy users will save time setting this up, as there is a way to test the connection settings to confirm whether the actual connection is live. There’s a convenient test button available only when creating an SQL variable which can be used to verify the link to the URL, together with the username and password. Click on Edit -> Variables, then the function icon to create a new variable. Select SQL from the list, which will bring up an SQL variable form in which you can test your connection settings. Whether you need a new SQL variable or not, it’s important to note that this is where you can test your connection settings. The SQL variable creation form looks like this:

Fill in your Host, Database, Username (login) and Password and click Test. If the connection is valid, the command in the Query box will run. Note you need to provide a TABLE name from your database after the FROM for the SELECT query to complete. If all of your connection settings are correct, you will have solid confirmation via return result. Also, this is a smart and straightforward place to test your queries to confirm that they will run as expected from NeoLoad.

So far, we’ve covered connecting NeoLoad to SQL and the ease of connection testing. Additionally, there are SQL Statement disconnection objects added to the User Path. SQL Statements, which are actual database queries, run directly within the User Path. Once you’ve defined the statements connection (using connection Name), it executes the SQL Statement query and variable against the database.

The function of the Advanced action known as SQL Disconnection does just that. Closing up the database connection is essential and a best practice when you have completed your transaction(s). At this time, you probably also want to insert the function into the End Container to close up the SQL connection following the User Path. There may be reasons why you should do this. For example, if you are using multiple databases where the opening and closing of each needs to occur within their iteration.

Here’s an example of working with SQL together with NeoLoad. Let’s say you want to bring up a row as a record from a table and increment the record by one row per iteration. To do this, you might want to create a variable that first identifies the number of rows your table holds with a SQL variable holding:

SELECT COUNT(*) from db_table; (let’s say we name it TotalRecords)

Then, create a count variable (we’ll call this “CountVar”) that counts up to this max number. To do so, set it to increment +1 per use and create your statement as follows:

SELECT * FROM db_table LIMIT ${CountVar}, 1

This query will precisely pull the single row from the number provided for each iteration. To reset the counter when you’ve reached the end of the table, add the appropriate If/Then/Else argument that looks for congruency between TotalRecords and CountVar, resetting the CountVar using a variable modifier. For Else, drag in a “go” to the next iteration command. Although this example requires some SQL knowledge as well as using some of the other advanced functions in NeoLoad, the entire process is primarily drag-and-drop-based.

Note that NeoLoad is capable of supporting MySQL, Oracle, DB2, PostgreSQL, Microsoft SQL, and custom types as available database options.

This post was originally published in 2018 and was most recently updated in July 2021.