The ‘SQL’-block lets you build SQL queries to process and retrieve data from SQL Databases. This enables storing and retrieving of any data during your bot's process.

SQL Command

To do so, an SQL SELECT statement can be used to retrieve. records from a database table. The general syntax is:

SELECT column1, column2 FROM table1, table2 WHERE column2='value';

In the above SQL statement:

• The SELECT clause specifies one or more columns to be retrieved; to specify multiple columns, use a comma and a space between column names. To retrieve all columns, use an asterisk (*).

• The FROM clause specifies one or more tables to be searched. Use a comma and space between table names when specifying multiple tables.

• The WHERE clause selects only the rows in which the specified column contains the specified value. The value is enclosed in single quotes.

• The INSERT clause enables you to insert new data in your column. If you use an INSERT query, the function won't generate any output (variables).

• The semicolon (;) ends the statement. Practically, if only a single statement is sent to the back end, a statement terminator is unnecessary. However, it is recommended to include it. 

The SQL language is not case sensitive (i.e., SELECT is the same as select). For readability purposes, you may use uppercase for commands and clauses, and lowercase for everything else.

Connect a SQL database

Input your connection string in the field SQL connection field. We use the ADO.NET (SQL authentication) connection string formatting.

Data Source=XXXXXX,1433;User Id=XXXX;Password=XXXXX;Initial Catalog=XXXX;Integrated Security=True;Encrypt=True;

Data source = Connection URL
User ID = Username
Password = Password
Catalog = Database name

Use an Input in the SQL query

If you set an input in your function, you can make your query dynamic based on an input from a user or process created in the Dialog Manager.

You can use this input in your query, by inserting your input between [].

If set, the input field will be available in the SQL function block in the Dialog Manager. You can input any variable in this field based on your bot's design as input for your SQL query.

Use the SQL output

The function block returns an array of objects with your data.

{
  "PersonID": 2,
  "LastName": "Harrold",
  "FirstName": "Peter",
  "Address": "Straat 232",
  "City": "Amsterdam"
},
{
  "PersonID": 3,
  .....,
}

Use the output function to create predefined output variables.

This will return only the selected output. Use the array index number 0 in the example above) to select which item in the array should be the output. If the query only outputs one object, this is not necessary.

{
  "name": "Peter",
  "surname": "Harrold"
}

These outputs will be available in the Dialog Manager to use in your bot's design.