Retrieving Database Records
Automate database record retrieval with the Database Book.
Overview
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
Examples
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:
Example
For example, suppose your table name is employee
(singular), and you want to retrieve a list of employees:
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
whose
You can use the whose
keyword to add filter conditions when retrieving specific records. The following filter operations are supported:
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
You can combine up to two filters conditions using the and
keyword:
Examples
2. Using query filter
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
Examples
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:
Examples
In the example below, the table name is singular (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.
To reference a specific record within the retrieved database records, write an additional line using positional keywords like first
, second
, etc.
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
.
Last updated
Was this helpful?