Database (Legacy)

Automate CRUD operations in Kognitos with the Database Book.

The Database book allows you to connect to a database in Kognitos and automate creating, retrieving, updating, and deleting records.

This book currently only supports Microsoft SQL Server (MSSQL). Other database types are not currently supported.

Adding the Legacy Database Book

Before using the Legacy Database book, you must first install it on your agent, which will require configuration. You will need the following credentials:

#	Field	Description	Example
1	Database Username	The database username used for authentication.	db_user
2	Database Password	The password for the database user.	secureP@ss123
3	Database Host	The database server host.	dummy-host.database.net
4	Database Name	The name of the database to connect to.	my_database
5	Database Engine	The type of database engine being used.	mssql

After successfully learning the Book, you can use its procedures in your automations.

Retrieving Database Records

These procedures allow you to get and retrieve records from a database. You can automate tasks like fetching individual records, retrieving all records from a table, or applying conditions to return specific results. These capabilities support data-driven workflows, allowing you to retrieve and use database records to automate tasks and keep information in sync across systems.

Retrieving All Records

To retrieve all the records from a specific table in your database, use the following syntax, replacing object with the name of the object you want to retrieve.

Syntax

get a database's {object}

Examples

get a database's Users

get a database's emails

get a database's Orders

Specifying Table Names

When retrieving all the records from a database table in Kognitos, the system tries to infer the table name from the sentence. This works when the table name is plural, such as employees, orders, or products.

However, if your table name is singular (like employee), and you're retrieving multiple records from the database, you should explicitly specify the table name using this syntax:

get a database's {object}
    the {object} table is "{table name}"

Example

For example, suppose your table name is employee (singular), and you want to retrieve a list of employees:

get a database's employees
    the employees table is "employee"

This tells the system:

• You're asking for multiple employees (using the plural noun, employees).

• You're explicitly pointing to the correct table name (employee).

Note: If the database table name is plural and matches the object in your sentence, you don't need to explicitly specify it.

Retrieving Specific Records

To retrieve specific records from a database table, you can specify filter conditions. There are two ways to do this.

1. Using whose

You can use the whose keyword to add filter conditions when retrieving specific records. The following filter operations are supported:

Operator	Meaning
is, matches	Equals (=)
is not, is not equal	Not equal (!=)
is greater than	Greater than (>)
is less than	Less than (<)
is greater than or equal	Greater than or equal (>=)
is less than or equal	Less than or equal (<=)
is not less	Greater than or equal (>=)
is not greater	Less than or equal (<=)

Syntax

get a database's {object} whose {field} {operator} {value}

You can combine up to two filters conditions using the and keyword:

get a database's {object} whose {field} {operator} {value} and whose {field} {operator} {value}

Examples

get a database's WorkOrders whose ID is greater than 3

get a database's users whose email matches "[email protected]"
    the users table is "user"

get a database's employees whose FirstName is "Jane" and whose LastName is "Doe"

2. Using query filter

A query filter uses SQL syntax to define filter conditions and offers greater flexibility compared to whose filters. This method supports any valid SQL condition, including:

• Complex expressions

• Multiple conditions with AND/OR

• Wildcards with LIKE

• Advanced comparisons and functions

Syntax

get the database's {object} where
    the query filter is "{filter}"

Examples

get the database's person where
    the query filter is "department='Engineering' and first_name='John'"

get the database's person where
    the query filter is "department='{the department}' and first_name='{the first name}'"

Handling Empty Tables

By default, retrieving records from an empty database table will raise an exception in Kognitos. However, you can explicitly tell the system to permit empty results without raising an exception by writing the following line:

the department's database null result is "allowed"

Examples

the department's database null result is "allowed"
get a database's employees

In the example below, the table name is singular (employee).

the department's database null result is "allowed"
get some database's employees whose employee_id is 0
    the employees table is "employee"

Storing Retrieved Records

After retrieving records from the database, you can store them in an automation for later reference. To do this, use the following phrase, replacing plural data name with any meaningful plural term.

use the above as the {plural data name}

To reference a specific record within the retrieved database records, write an additional line using positional keywords like first, second, etc.

use the {position} record as the {singular data name}

Example

In the following example:

• All the retrieved database records from the query are stored as the records.

• Only the first database record is stored as the employee.

get a database's person whose FirstName is "Nancy"
use the above as the records
use the first record as the employee

Updating Database Records

To update a singular database record, first retrieve the record you want to update, then use set to modify its field values.

Syntax

set <object>'s <field> to "<value>"

Examples

Example 1: Updating a person's name

get a person from database whose ID is 3
set the person's name to "Jane Doe"

Example 2: Updating an employee's designation

get an employee from database whose employee_id is 918756423
set the employee's designation to "LEAD"

Example 3: Updating an incident record

get a incident from database whose ID is 12345
set the incident's FirstName to "Abby"

Creating Database Records

To create a new database record, use the following procedure:

Syntax

create a {table} in database with
  {field} is {value}

Components

• table - The name of the database table where the new record will be added.

• field - A specific field in the table (e.g., LastName, FirstName).

• value - The data you want to store in that field.

Example

create a person in database with
  the LastName is "Smith"
  the FirstName is "John"
  the Address is "123 Apple St"
  the DOB is "1980-04-03"

In this example, person is the name of the database table. A new record is created in that table with the information provided after with. Each indented line represents a column (LastName, FirstName, etc.) and its corresponding value.

Deleting Database Records

To delete a record from the database, use the following procedure:

Syntax

In the following syntax, record is the entry in the database to be deleted. The entry needs to be previously referenced in your automation before you can delete it.

delete {record} from the database

Example

get a database's person whose FirstName is "John"
use the above as the records
use the first record as the person
delete the person from the database

In this example:

• Line 1: Retrieves a database record.

• Line 2: Stores the retrieved records as the records.

• Line 3: Stores only the first record from the records as the person.

• Line 4: Deletes the person.