SQL Select Launcher (sql_select.jar)

The SQL Select launcher allows to connect to a database, optionally remotely run a script (i.e. to prepare some data or run a consolidation job or wathever...) and check the SQL script does not return any data.

Configuration

The sql_select.xml file is just a template and must NOT be edited. It's used by the system to build dynamically the form that the user will be able to fill in from the GUI when creating a custom execution configuration.

Parameter	Description
SQL
sql script root path	This must indicate where are located all the SQL scripts. This is a root path. Each test in XStudio has a canonical path that will be appended to this path. This path MUST not include an ending slash. Default value is: `C:/test_repository/tests/sql_select`
Connection
connection url	This must indicate the connection URL to connnect to the first database. Default value is: `jdbc:mysql://192.168.10.1/test_db`
username	This must indicate the username to connect to the database. Default value is: `my_username`
password	This must indicate the password to connect to the database. Default value is: `my_password`
driver jar	This must indicate the driver to use to connect to the database (depending on the database server this may be a MySQL, PostgreSQL, Oracle or any other JDBC driver). Default value is: `Y:/mysql-connector-java-3.1.12/mysql-connector-java-3.1.12-bin.jar`
driver classpath	This must indicate the driver class path to instantiate. Default value is: `com.mysql.jdbc.Driver`
Scripts
run command remotely before running test	This must indicate if you need to run remotely a script on a server using plink before running the test cases. Default value is: `0` (false)
plink executable	This must indicate the path to the plink executable. Default value is: `C:/progra~1/Putty/plink.exe -batch`
remote host	This must indicate the on which remote host to run the command. Default value is: `10.1.1.1`
username	This must indicate the username to connect to the remote host. Default value is: `my_username`
password	This must indicate the password to connect to the remote host. Default value is: `my_password`
command	This must indicate the command to execute remotely. Default value is: `grep /~eric/ /var/log/httpd/access.log > fredlog`

These values can be changed while creating the campaign session from XStudio.

Note about file path parameters:
Any parameter referring to a file or folder path (for instance Test root path) can be provided either using \ separator (if the tests are going to be executed on a Windows agent) or / separator (if the tests are going to be executed on a linux or MacOSX agent).

On windows, if you provide a path containing an OS-localizable folder such as C:\Program Files, always prefer the English version (i.e. NOT C:\Programmes if you're using a french-localized Windows) or the corresponding native environment variable (i.e. %PROGRAMFILES%).

Process

The tests are executed by the launcher using the following procedure:

If necessary, connect to a remote host using plink with specific credentials (in the configuration) and execute the command specified (as well in the configuration)
connect to the databases specified in the configuration
Execute the sql script <sqlScriptRootPath>/<testPath>/<testName>_<testcaseName>.sql on the database
Check the resultset returned

If the result set is empty, a success is logged. If the SQL script returns some results or a failure is detected, a failure is logged. The log of the scripts are also attached to the testcase execution in XStudio.

If some parameters are set on your test cases, the launcher will search for the parameter name in the queries and will replace them with the value of the parameter if it finds some. This allows to customize some query templates based on the parameterization of each test case.

Tutorial: Creating and executing SQL Select tests

In this tutorial, we will learn to run some SQL Select tests.

Prerequisites

Create a MySQL database and run the following script to populate it with example data:

CREATE TABLE table_stock_1 (
  article_id INTEGER,
  article_name TEXT,
  article_color TEXT,
  article_nb INTEGER,
  PRIMARY KEY(article_id)
);

INSERT INTO table_stock_1 VALUES
(1, 'Hat', 'Red', 10),
(2, 'Hat', 'Blue', 4),
(3, 'Hat', 'Green', 17),
(4, 'Sweat-shirt', 'Red', 5);

CREATE TABLE table_stock_2 (
  article_id INTEGER,
  article_name TEXT,
  article_color TEXT,
  article_nb INTEGER,
  PRIMARY KEY(article_id)
);

Then create 2 test cases scripts:
Test case 1:

Create the text file C:\\test_repository\\tests\\sql_select\\folder1\\test1_testcase1.sql and write in it:
SELECT * FROM table_stock_1;

Test case 2:

Create the text file C:\\test_repository\\tests\\sql_select\\folder1\\test1_testcase2.sql and write in it:
SELECT * FROM table_stock_2;

The test case at index 1 of Test 1 is going to fail as it will return some data (table_stock_1 is not empty) and the test case at index 2 is going to succeed (table_stock_2 is empty).

Create a dedicated category for SQL Select tests and create a test

create a category SQL Select associated to the launcher sql_select.jar
under this category, create (somewhere in the tree) a test with name test1 and with a canonical path set to folder1.
create 2 test cases under this test named testcase1 and testcase2 (this is what is used to find the right script on disk).

Creating a test campaign

create a campaign including only the test test1
create a campaign session specifying in the configuration:
- sql scripts root path: C:/test_repository/tests/sql_select
- the same driver, classpath and credential to connect to the test database

Run a campaign session

Run the campaign session

Permissions

WARNING: if you're running your tests on Windows, it may be required to run the tests as administrator.
Having an account with Administrators permissions may even not be enough in some cases (especially if you're using Windows 10) and you may need to disable completely the UAC (User Access Control) on your computer.
To do so:

Press the Windows + R key combination
Type in regedit
Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System
In the right-side pane, look for EnableLUA and set the value 0
Close the registry editor
Restart your computer

Debug

If your tests are not executed correctly or are reporting only failures, this is very likely because your configuration is incorrect or because you used a wrong naming convention for your tests and test cases.

The best way to quickly find out what's wrong is to look at the traces generated by XStudio (or XAgent).
The traces always include the detailed description of what the launcher performs (command line execution, script execution, API calling etc.) to run a test case. So, if you experiment some problems, the first thing to do is to activate the traces and look at what's happening when you run your tests.

Then, try to execute manually in a cmd box the exact same commands.
This will normally fail the same way.
At this point, you needs to figure out what has to be changed in these commands in order to have them run properly.

When you have something working, compare these commands to what's described in the Process chapter above. This will tell you exactly what you need to change.

Most of the time, this is related to:

some incorrect values in some parameters of your configuration,
the name of your tests,
the name of your test cases,
the canonical path of your tests

Launchers / Automated tests