Selecting Data Using the MLE JavaScript Driver

Data can be selected using Direct Fetches or ResultSet objects.

You can choose between arrays and objects as the output format. The default is to return data through Direct Fetch using JavaScript objects.

Topics

Parent topic: Calling PL/SQL and SQL from the MLE JavaScript SQL Driver

Direct Fetch: Arrays

Direct Fetches are the default in the MLE JavaScript driver.

Direct Fetches provide query results in result.rows. This is a multidimensional JavaScript array if you specify the outFormat as oracledb.OUT_FORMAT_ARRAY. Iterating over the rows allows you to access columns based on their position in the select statement. Changing the column order in the select statement requires modifications in the parsing of the output. Because this can lead to bugs that are hard to detect, the MLE JavaScript SQL driver returns objects by default (oracledb.OUT_FORMAT_OBJECT), rather than arrays.

Example 7-3 demonstrates Direct Fetches using the synchronous execution model.

Example 7-3 Selecting Data Using Direct Fetch: Arrays

CREATE OR REPLACE PROCEDURE dir_fetch_arr_proc
AS MLE LANGUAGE JAVASCRIPT
{{
const result = session.execute(
    `SELECT
        department_id,
        department_name
    FROM
        hr.departments
    FETCH FIRST 5 ROWS ONLY`,
    [],
    {
        outFormat: oracledb.OUT_FORMAT_ARRAY
    }
);
for (let row of result.rows) {
    const deptID = String(row[0]).padStart(3, '0');
    const deptName = row[1];
    console.log(`department ID: ${deptID} - department name: ${deptName}`);
}
}};
/

BEGIN 
    dir_fetch_arr_proc;
END;
/

Result:

department ID: 010 - department name: Administration
department ID: 020 - department name: Marketing
department ID: 030 - department name: Purchasing
department ID: 040 - department name: Human Resources
department ID: 050 - department name: Shipping

The execute() function returns a result object. Different properties are available for further processing depending on the statement type (select, insert, delete, etc.).

For information about mle-js-oracledb, see Server-Side JavaScript API Documentation.

Parent topic: Selecting Data Using the MLE JavaScript Driver

Direct Fetch: Objects

JavaScript objects are returned by default when using Direct Fetch.

To address potential problems with the ordering of columns in the select list, results are returned as JavaScript objects rather than as arrays.

Example 7-4 Selecting Data Using Direct Fetch: Objects

CREATE OR REPLACE PROCEDURE dir_fetch_obj_proc
AS MLE LANGUAGE JAVASCRIPT 
{{
const result = session.execute(
    `SELECT
        department_id,
        department_name
    FROM
        hr.departments
    FETCH FIRST 5 ROWS ONLY`,
    [],
    { outFormat: oracledb.OUT_FORMAT_OBJECT }
);

for (let row of result.rows) {
    const deptID = String(row.DEPARTMENT_ID).padStart(3, '0');
    const deptName = row.DEPARTMENT_NAME;
    console.log(`department ID: ${deptID} - department name: ${deptName}`);
}
}};
/

BEGIN
    dir_fetch_obj_proc();
END;
/

Result:

department ID: 010 - department name: Administration
department ID: 020 - department name: Marketing
department ID: 030 - department name: Purchasing
department ID: 040 - department name: Human Resources
department ID: 050 - department name: Shipping

Unlike PL/SQL, JavaScript doesn't support the concept of named parameters. The execute() method accepts the SQL statement, bindParams, and options, in that exact order. The query doesn't use bind variables, thus an empty array matches the function's signature.

See Also:

Server-Side JavaScript API Documentation for more information about the mle-js-oracledb built-in module

Parent topic: Selecting Data Using the MLE JavaScript Driver

Fetching Rows as ResultSets: Arrays

You can use ResultSet objects as an alternative to using Direct Fetches.

In addition to using Direct Fetches, it is possible to use ResultSet objects. A ResultSet is created when the option property resultSet is set to true. ResultSet rows can be fetched using getRow() or getRows().

Because rows are fetched as JavaScript objects by default instead of as arrays, outFormat must be defined as oracledb.OUT_FORMAT_ARRAY in order to fetch rows as a ResultSet.

Example 7-5 Fetching Rows Using a ResultSet

CREATE OR REPLACE PROCEDURE dir_fetch_rs_arr_proc
AS MLE LANGUAGE JAVASCRIPT
{{
const result = session.execute(
    `SELECT
        department_id,
        department_name
    FROM
        hr.departments
    FETCH FIRST 5 ROWS ONLY`,
    [],
    {
        resultSet: true,
        outFormat: oracledb.OUT_FORMAT_ARRAY
    }
);

const rs = result.resultSet;
let row;
while ((row = rs.getRow())){
    const deptID = String(row[0]).padStart(3, '0');
    const deptName = row[1];
    console.log(`department ID: ${deptID} - department name: ${deptName}`);
}
rs.close();
}};
/

Note that the fetch operation specifically requested an array rather than an object. Objects are returned by default.

EXEC dir_fetch_rs_arr_proc();

Result:

department ID: 010 - department name: Administration
department ID: 020 - department name: Marketing
department ID: 030 - department name: Purchasing
department ID: 040 - department name: Human Resources
department ID: 050 - department name: Shipping

Parent topic: Selecting Data Using the MLE JavaScript Driver

Fetching Rows as ResultSets: Iterating Over ResultSet Objects

In addition to the ResultSet.getRow() and ResultSet.getRows() functions, the MLE JavaScript driver's ResultSet implements the iterable and iterator protocols, simplifying the process for iterating over the ResultSet.

Using either the iterable or iterator protocols is possible. Both greatly simplify working with ResultSets. The iterable option is demonstrated in Example 7-6.

Note:

ResultSet objects must be closed once they are no longer needed.

Example 7-6 Using the Iterable Protocol with ResultSets

This example shows how to use the iterable protocol as an alternative to ResultSet.getRow(). Rather than providing an array of column values, the JavaScript objects are returned instead. 

CREATE OR REPLACE PROCEDURE rs_iterable_proc
AS MLE LANGUAGE JAVASCRIPT
{{
const result = session.execute(
    `SELECT
        department_id,
        department_name
    FROM
        hr.departments
    FETCH FIRST 5 ROWS ONLY`,
    [],
    {
        resultSet: true
    }
);
const rs = result.resultSet;
for (let row of rs){
    const deptID = String(row.DEPARTMENT_ID).padStart(3, '0');
    const deptName = row.DEPARTMENT_NAME;
    console.log(`department ID: ${deptID} - department name: ${deptName}`);
}
rs.close();
}};
/

BEGIN
    rs_iterable_proc();
END;
/

Result:

department ID: 010 - department name: Administration
department ID: 020 - department name: Marketing
department ID: 030 - department name: Purchasing
department ID: 040 - department name: Human Resources
department ID: 050 - department name: Shipping

Parent topic: Selecting Data Using the MLE JavaScript Driver