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.

Databse 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.

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 1. 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 the JavaScript extension is run on a database monitored object and allows access to run SQL queries on the database.

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

execute(..)

execute(sqlQuery: string): DataTable | DataTable[]

This method allows developers to execute SQL statements on the monitored database.

The return type could either a single table or in the case of stored procedures could return an array tabular results. Use the JavaScript method Array.isArray to determine if there are multiple result tables if this could vary.

const sqlResult = database.execute("select myFieldName from table");
console.log(sqlResult.rows[0].getValue("myFieldName"));


const procResult = database.execute("sp_report1");
if (Array.isArray(procResult)){
    // multiple result sets
    const result1 = procResult[0].getRows();
    // ... processing of the results

    const result2 = procResult[1].getRows();
    // ... processing of the results
}
Parameters

  • sqlQuery: string

    the SQL query to execute

Returns

DataTable or DataTable[]

the result of the query

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): database

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 adhoc 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

database

the database connection for given credentials

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.

getRows( )

getRows(): DataTable.Row[]

Return the row data for this table.

Returns

DataTable.Row[]

the rows of the data table

See Also

rows

readonly rows: DataTable.Row[]

Return the row data for this table.

See Also
Type

DataTable.Row[]

Modifiers

Readonly

Class DataTable.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