# 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:

- SAP HANA SQL Reference (2.0)

  https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.02/en-US/20ff532c751910148657c32fe3431a9f.html

- Sybase ASE Transact-SQL Users Guide

  https://help.sap.com/viewer/d24f1d4e5efc460a84141cf4d25a7974/16.0.2.0/en-US

- Mircosoft SQL Server Transact-SQL Reference

  https://docs.microsoft.com/en-us/sql/t-sql/language-reference?view=sql-server-ver15

- Oracle PL/SQL Language Reference

  https://docs.oracle.com/en/database/oracle/oracle-database/21/lnpls/index.html

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

#### rows

    readonly rows: DataTable.Row[]

Return the row data for this table.

See Also

  - getRows()

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