Welcome to the Botium Knowledge Center

Botium can query SQL databases when testing conversation flow and assert the results and content of the database.

Currently supported SQL database connectors are:

• MySQL

• PostgresQL

• Microsoft SQL Server

• Oracle (experimental)

The mentioned database connectors are part of Botium Box distribution. Navigate to menu Global Settings, section Components to see the list of available asserters.

Clicking on one the asserters, for example the MySQL asserter, will show the configuration view.

The component configuration is handed over to the mysql driver. See Connection Options in the documentation page.

The component configuration is handed over to the pg-promise driver. See Configuration Object in the documentation page.

The component configuration is handed over to the node-mssql driver. See Configuration in the documentation page.

In order to use multiple database connections you have to register multiple components. Copy the values of one of the existing SQL components into a new one.

!!! note If you have multiple database connections for multiple environments (dev, test, prod):

-   you should have three registered components

-   The ref code should be the same, as this is the code used in the
    convo files for doing the assertions

-   For the different test projects, you have to choose the asserter
    appropriate for the environment to test is running on (see below)

In the Test Project configuration, head over to Execution and add the SQL asserter to the Involved Registered Components field.

The asserters can by used by using the Ref Code as “magic word” in convo files, followed by an SQL statement.

hello

#begin
MYSQL create table if not exists example.test(firstname TEXT, lastname TEXT)

#me
Hi, my name is Kurt Wagner

#bot
You said: Hi, my name is $firstname $lastname
MYSQL INSERT INTO example.test (firstname, lastname) VALUES ("$firstname", "$lastname")

#me
Give me some links

#bot
MYSQL select * from example.test where firstname="Kurt"
MYSQL select * from example.test | Kurt | firstname
MYSQL select * from example.test | [ "Kurt" ] | $[0].firstname

#end
MYSQL DELETE from example.test

The first parameter is always an SQL statement to execute. Usually this will be a SELECT statement, but you can use all supported SQL statements.

You can construct a dynamic SQL statement by using Scripting Memory variables:

#bot
MYSQL select * from orders where ordernum="$ordernum"

If no more parameter is given, the asserter will only verify that for a SELECT statement at least one row is returned from the statement - otherwise the test case is failed.

The second parameter is the value which is expected to be returned from the statement, the third parameter is used for selecting a portion of the returned result set.

• If 3rd parameter is not given, the 2nd parameter should be a JSON structure to directly compare with the returned result set.

  #bot
  MYSQL select ordernum, status from orders where ordernum="$ordernum" | [ { "ordernum": "$ordernum", "status": "PENDING" } ]

• If 3rd parameter is a JSONPath expression (starting with $), then the assertion is done on the output of this JSONPath expression.

  #bot
  MYSQL select ordernum, status from orders where ordernum="$ordernum" | [ "PENDING" } ] | $[0].status

• Otherwise the 3rd parameter is considered as column name - the value of this column of the first row of the result set is used for assertion.

  #bot
  MYSQL select ordernum, status from orders where ordernum="$ordernum" | PENDING | status