# Database (Legacy) The **Database** book allows you to connect to a database in Kognitos and automate creating, retrieving, updating, and deleting records. {% hint style="info" %} This book currently only supports **Microsoft SQL Server (MSSQL)**. Other database types are not currently supported. {% endhint %} ## 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:
#FieldDescriptionExample
1Database UsernameThe database username used for authentication.db_user
2Database PasswordThe password for the database user.secureP@ss123
3Database HostThe database server host.dummy-host.database.net
4Database NameThe name of the database to connect to.my_database
5Database EngineThe 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`). {% hint style="success" %} **Note:** If the database table name is plural and matches the object in your sentence, you don't need to explicitly specify it. {% endhint %} ### 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 "user@gmail.com" 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 's to "" ``` ### 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`.