The MSSQLUtils object

In request filters for SQL Server, there is always an object available as context.mssqlutils, which contains the executeQuery method.

executeQuery

This method sends a SQL query to the database and returns the result set.

This is useful when you need to retrieve some data from the database before the current request is sent to the server.

Important: this can only be invoked from request filters. Invoking it from a response filter may cause a deadlock.

Keep in mind that this is a synchronous call, and therefore will delay the forwarding of the current request until the result set is fully received.

This should typically be used only for quick queries that return small result sets.

The signature is:

MSSQLResultSet executeQuery(string sql)

The object returned is of type MSSQLResultSet and has the following properties:

• metadata (ColMetadata): the metadata for the result set

• rows (List<Row>): the rows in the result set

• error (Error): if this is not null, then the query failed and the other properties are null

Example

Before allowing a request, check whether the current database user has a given role:

let rs = context.mssqlutils.executeQuery("select is_member('db_owner') as res");

let isDbOwner = rs.rows[0].res;

if ( ! isDbOwner) {

    context.result.success = false;
    context.result.errorMessage = "Request rejected - your permissions are insufficient";

    return;

}

Note that it may be desirable to cache this type of information in (for instance) the filter context to avoid retrieving it every time, but that's not required.

Example

Generic query:

let rs = context.mssqlutils.executeQuery("select * from gallium_demo.customers");

log.info("Result set: \n" + rs);

let row3 = rs.rows[3];

log.info("row3: " + row3);

log.info("row3 last_name: " + row3.last_name);

When this executes, the following will be in the log:

[INFO   uselog] 15:13:34.103 [forwardFromClientThread-39] - Result set: 

---------------------------------------------

| id | first_name | last_name     | country |

---------------------------------------------

| 1  | Andrea     | Albinioni     | AR      |

| 2  | Bruno      | Botticelli    | BE      |

| 3  | Charles    | Corleone      | CA      |

| 4  | Daniella   | Durango       | DK      |

| 5  | Eric       | Edmunds       | ES      |

etc...

[INFO   uselog] 15:13:34.103 [forwardFromClientThread-39] - row3: Row id=4, first_name=Daniella, last_name=Durango, country=DK, 

[INFO   uselog] 15:13:34.103 [forwardFromClientThread-39] - row3 last_name: Durango