Query UM entries using raw SQL

The following example illustrates how to generate and modify an SQL query statement to iterate over a set of UM entries without the overhead of having CSE instantiate an Entry instance for each entry:

const results = [];
const script = new SqlScript();

// We start with a UM query expression:
const fragment = UM.query('defined(firstname) and isSet(cmsbs.isadmin)').toSqlFragment();

// As this would yield OIDs only, we add some columns we are interested in:
fragment.addColumn('firstname');
fragment.addColumn('lastname');

// We can also SELECT raw SQL expressions:
fragment.addRawColumn('LENGTH(firstname)');

const sqlQuery = fragment.toQuery();

// See the actual SQL query statement:
console.log(sqlQuery.sql);

// Iterate all results without pagination:
sqlQuery.resultCallback = function(rs) {
    while (rs.next()) {
        results.push({
            oid: rs.getLong(1),
            firstname: Strings.nn(rs.getString(2)),
            lastname: Strings.nn(rs.getString(3)),
            length: rs.getInt(4)
        });
    }
};
script.add(sqlQuery);

// Start execution:
script.execute();

UM Developer Documentation

Query UM entries using raw SQL

Analytics

Related content