Skip to main content

Making Database Queries

When dealing with SAP systems and underlying databases, powerful checks and automations can be built by accessing database tables. The database global variable is available when running a check/automation against these system types.

Code Walkthrough​

In this walkthrough, we will be showing how you can create a JavaScript based custom check that uses the database global variable to get information about the database.

We will be using a PostgreSQL database for the example code so you may need to adapt the SQL syntax to work on the database you have.

Querying the database​

Structure Query Language (SQL) queries can be simply passed to the execute method of the database global object as text strings. This makes it really easy to create SQL queries but be careful to ensure that names of columns (and in some databases, capitalization of columns) are correct to ensure that these queries work.

const sql = "SELECT table_schema, table_name"
            + " FROM information_schema.tables";

const result = database.execute(sql);

The information_schema.tables table read above is built into PostgreSQL and contains the list of tables available in the database.

The variable result above could either contains an array of DataTable's or only one DataTable. We know that this SQL statement only returns a single result table and so this is a single DataTable object returned and we can process the rows.

const rows = result.rows;

for (let i = 0; i < rows.length; i++) {
    const row = rows[i];
    const schema = row.getValue("table_schema");
    const table = row.getValue("table_name");

    console.log(`${schema} - ${table}`);
}

Here we are using the rows property on the result table to loop around the returned records and the getValue(..) method on the row to retrieve both the table and schema name.

Check Result Table​

When writing JavaScript based custom checks (RUN_JS) a common use case is to make a query on a database and then display the results in a table display for the check output. You can also process those results somehow to determine the status (CRITICAL, WARNING, OK etc.) of the check to make it easier for your users.

The check results can be generated manually (using a loop like above) or there is a convenience method on the check global object that will allow you to pass the DataTable directly from the SQL query and Avantra will work out the display and formatting for you.

For example:

const sql = "SELECT table_schema, table_name"
            + " FROM information_schema.tables";

const result = database.execute(sql);

check.result = result;

This code will output a formatted table, with two columns table_schema and table_name in the check result. However, this output should only be used if you wish to dynamically create the SQL, based on parameters, or perform more advanced SQL queries since the built in custom check type RUN_SQL for Custom SQL based checks may be easier to maintain and deploy.

Updating the Database​

With the database execute method it is also possible to perform changes to the database. This is useful in automations to perform complex tasks. For SAP systems it is not recommended and we advise using ABAP Remote Function calls (RFCs) instead to perform changes.

note

Database changes should only be made in automation steps and not as part of custom JavaScript based checks as checks are executed periodically and are meant to observe and not change.

warning

Extreme caution should be taken when updating the database manually. As this could be a dangerous action, it is disabled globally by default. To change this, set the Security.RUNJSAllowDBWrite setting in Administration → Settings menu.

In this short example, we will insert a single record into a custom table my_custom_table on a PostgreSQL database. If you are using a different database type type, please check the corresponding reference guide for your version.

// We have 2 columns in our table "my_custom_table"
// -- column_1 is an auto-incrementing number used as a primary key
// -- column_2 is a free text field
const sql = "INSERT INTO public.my_custom_table"
            + " (column_1, column_2)"
            + " VALUES (DEFAULT, 'Test'::text)";

let result = 0;

try {
    // Perform the insert
    result = database.execute(sql);
    // the result contains the number of records inserted/changed
    action.message = `${result} record(s) created!`;
    action.success = true;
} catch(e) {
    // Catch an exception and change the action to false
    result = -1;
    action.message = e.getMessage();
    action.success = false;
}

It's difficult to get this to throw the exception normally but editing the SQL to set the column_1 key to a used value will violate the unique constraint and allow you to test the automation step.

SQL Reference Guides​

Each database vendor and type may have subtly different syntax for the SQL query you wish to run for your use case. We recommend keeping the SQL reference guide handy for the database you are writing for to check the format required.

Some sample guides:

Examples​

Example: Simple SELECT from a database

This example is the full source code from the walkthrough which is to output the list of tables in a PostgreSQL database.

const sql = "SELECT table_schema, table_name FROM information_schema.tables";

// Perform the query
const result = database.execute(sql);

// we could output the data using console.log
// uncomment the lines below to output this manually:

// const rows = result.rows;

// for(let i = 0; i < rows.length; i++) {
//     const row = rows[i];
//     const schema = row.getValue("table_schema");
//     const table = row.getValue("table_name");

//     console.log(`${schema} - ${table}`);
// }

// But we prefer to use the built in mechanism to format the results
check.result = result;

// Set the check status based on number of tables present
if (result.rows.length > 100) {
    // We want to alert our users when we have more than 100 tables:
    check.status = CRITICAL;
    // If a check is CRITICAL, always give a reason why so users can fix
    check.message = `${result.rows.length} tables found! Please reduce to under 100`;
} else {
    // This is not needed but here for completeness
    // - the default status is OK
    check.status = OK;
}

API Reference​

Global Variable database​

The global variable database is available when a JavaScript custom check or automation is run on a database or SAP system monitored object and provides methods to run SQL queries on the database.

Note that these queries are executed with the credentials defined in Avantra and permissions or Avantra settings may prevent access or force read only.

execute​

execute(sqlQuery: string): any

Executes an SQL query and returns the result set.

Parameters

  • sqlQuery (string) - the SQL query to execute

Returns: any - an object containing the result set. If the query returns more than one result set, a list of result set is returned

executeAsync​

executeAsync(sqlQuery: string): void

Executes an SQL query in the background. This can be useful for long-running SQL queries such as back-ups and restores that can be monitored with another SQL query on the database.

This method will throw an exception if the SQL is malformed or the query cannot be started. Otherwise, it will return nothing.

Since: 21.11.4

Parameters

  • sqlQuery (string) - the SQL query to execute

openConnection​

openConnection(credentials: any): ConnectionDatabaseHost

Opens a new database connection which can be used to execute a SQL query. You have the option to pass different credentials for the new connection. Use this in case you need a connection with different permissions than the default database user maintained in Avantra. You can also pass null to create a new connection to the database using the default Avantra credentials.

Example:

// Use with automation credentials:
var conn2 = database.openConnection(action.input.db_user);

// Use with ad hoc credentials:
var conn2 = database.openConnection({"user":"johnsmith", "password": "secret"});

Since: 21.11.6

Parameters

  • credentials (any) - the credentials used to open the new connection

Returns: ConnectionDatabaseHost - the database connection for given credentials

Class AbstractDatabaseHost​

canWrite​

canWrite(): boolean

Test if write queries can be executed or not

Since: 25.1.0

Returns: boolean - true when write queries can be executed, false otherwise

Class ConnectionDatabaseHost​

This class allows interaction with a database using custom credentials.

This class can be created by calling database.openConnection(credentials) and provides the same execute and executeAsync methods but these will be executed with the new credentials.

execute​

execute(sqlQuery: string): any

Executes an SQL query (with custom credentials) and returns the result set.

Since: 21.11.6

Parameters

  • sqlQuery (string) - the SQL query to execute

Returns: any - an object containing the result set. If the query returns more than one result set, a list of result set is returned

executeAsync​

executeAsync(sqlQuery: string): void

Executes an SQL query in the background. This can be useful for long-running SQL queries such as back-ups and restores that can be monitored with another SQL query on the database.

This method will throw an exception if the SQL is malformed or the query cannot be started. Otherwise, it will return nothing.

Since: 21.11.6

Parameters

  • sqlQuery (string) - the SQL query to execute

Class DataTable​

This object represents a table of data that is read from the database. You can think of this object as an internal table. The property rows (and also the linked method) return an array of data that represent the rows of this table.

getExecutionTime​

getExecutionTime(): number

Returns the time taken in ms to retrieve the data

See Also: executionTime

getRows​

getRows(): Row[]

Return the row data for this table.

Returns: Row[] - the rows of the data table

See Also: rows

executionTime​

readonly executionTime: number

Returns the time taken in ms to retrieve the data

Accessors

Type: number

Modifiers: readonly

rows​

readonly rows: Row[]

Return the row data for this table.

Accessors

Type: Row[]

Modifiers: readonly

Class Row​

This object represents a single row in the result set. You should use the method getValue(..) and pass in the name of the field to retrieve the value for this row.

getValue​

getValue(key: string): string

Retrieve the specified field name's value in this row.

Parameters

  • key (string) - the field name to retrieve

Returns: string - a string with the field value