NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: BEGINNER
VERSION DEVELOPED IN: KINGSTON+
Assumes basic knowledge and/or familiarity with Scripting in ServiceNow.
____________________________________________________________________________
In this set of articles I am going to focus on some of the ins-and-outs and observastions of the GlideRecord class. I will be starting with the basics and walking all the way through to advanced concepts. I am not the first to address this, but it is my intent to present it in a way that the examples could be used to learn and understand the concepts behind using this sometimes confusing class. I will also be throwing in, where appropriate, a SQL translation of what the GlideRecord essentially does under-the-hood when it creates the actual query that is submitted to the MYSQL database.
Axioms (things you can hang your hat on):
1. ServiceNow uses the hierarchical MySQL database structure. NOT the relational structure! This has the benefits of strikingly fast reads, with the trade-off of slow deletes. If you are interested in digging further read here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
2. The GlideRecord object always does a SELECT *. While not very efficient it does have the benefit of still working pretty fast and returning ALL fields in the record.
3. You CANNOT DO Stored Procedures. Ugh! Argh! Really?! Wow! Closest you can get is via a Script, or Database View. Crying here (have been for years).
4. The GlideRecord object is sealed. What this means is that the structure is immutable. Even though it is a JavaScript object (kind of) its structure is carefully (sort of) managed under-the-hood. You CAN modify the structure in your own instance via a prototype, but only in Global. You CANNOT modify GlideRecord via prototype in the Scoped version (a huge failing in my book).
5. There is no provision for Order-of-Precedence. Period. Sigh. This is especially glaring in the encoded queries. Watch out for it. It WILL bite you eventually. Note: The structure of your GlideRecord query will imply an order-of-precendence, but it is primitive. Encoded queries DO NOT support parenthetical structuring.
6. GlideRecords DO NOT usually error out on a bad WHERE clause. Instead the line is ignored! WARNING: This can cause extreme headaches and other physical ailments! Watch out for this and test, test, test when it comes to Update and Delete GlideRecord queries!!!!!!!!!
7. There is no support of BETWEEN. You will need to code this the hard way.
8. There is no support of the WHERE clause: (this AND this) OR (this AND this). Period. Sorry. You will need to SCRIPT this if you need that functionality.
9. With testing GlideRecord Scripts - Background, and/or Fix Scripts are your friend.
10. You do not have access to System Tables. This is painful at best. Really guys?! At least let us see some things readonly anyway. Not everything is a security violation.
11. You DO have access to table and field definitions, but you have to know where to look, and how to pull it together (I will be covering that in a further-on article of this series).
12. There is a known long-standing issue with GlideRecord where a retrieved variable cannot be accessed correctly directly. For example with a GlideRecord of incidents named "incidents":
while (incidents.next()) {
var number = incidents.number;
gs.info('---> number: ' + number);
}
Will likely print the last number in the list with all records printed off! This has something to do with the java -> to javascript conversion after the query is run. I never have been given a good reason actually. The cure is to stringify the value (i.e. incidents.number + '') or to use .getValue('number').
13. There is no UNION analog. Period. You can sort-of get around this by creating a Database View and then referencing that result with a GlideRecord. Another way is to do two GlideRecord pulls from the different tables, convert those to JavaScript Object arrays, then merge the two arrays together. Now THAT is painful!
Setting up your basic query.
var incidents = new GlideRecord('incident');
incidents.query();
gs.info('---> count: ' + incidents.getRowCount());
This translates to the following MYSQL SQL:
SELECT * FROM incident
Accessing the records in the basic query.
var incidents = new GlideRecord('incident');
incidents.query();
while (incidents.next()) {
// the three ways to "freeze" the value from the GlideRecord
gs.info('---> number: ' + incidents.number + '');
gs.info('---> number: ' + incidents.number.toString());
gs.info('---> number: ' + incidents.getValue('number')); // most efficient
}
Limiting the number of records - way 1
var limitValue = 1;
var incidents = new GlideRecord('incident');
incidents.setLimit(limitValue);
incidents.query();
while (incidents.next()) {
gs.info('---> number: ' + incidents.getValue('number'));
}
The query translates to SQL:
SELECT * FROM incident LIMIT 1
Limiting the number of records - way 2 (set to a single record for return)
var problems = new GlideRecord('problem');
problems.setLimit(1);
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Limiting the number of records - way 3 (don't do this)
var problems = new GlideRecord('problem');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Limiting the number of records - way 4 (don't do this)
var limitValue = 1;
var problems = new GlideRecord('problem');
problems.query();
var count = 0;
while (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
if (count++ > limitValue) {
break;
}
}
// =
var problems = new GlideRecord('problem');
problems.addQuery('number', 'PRB0000001');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Translates to SQL:
SELECT * FROM problem WHERE number='PRB0000001'
A specific query: implied =
var problems = new GlideRecord('problem');
problems.addQuery('number', 'PRB0000001');
problems.query();
if (problems.next()) {
gs.info('---> number: ' + problems.getValue('number'));
}
Best! Note that the .get assumes an implied equals.
var problems = new GlideRecord('problem');
if (problems.get('number', 'PRB0000001')) {
gs.info('---> number: ' + problems.getValue('number'));
}
Translates to the following SQL:
SELECT * FROM problem WHERE number='PRB0000001' LIMI 1
As a side-note the get only takes two parameters. So, this does not work! It will return nothing.
var problems = new GlideRecord('problem');
if (problems.get('number', '=', 'PRB000000')) {
gs.info('---> number: ' + problems.getValue('number'));
}
// >
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '>', 2);
servers.query();
while (servers.next()) {
gs.info('---> name: ' + servers.getValue('name'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_server WHERE cpu_count > 2
// <
var servers = new GlideRecord('cmdb_ci_server');
servers.addQuery('cpu_count', '>', 2);
servers.addQuery('cpu_count', '<', 5);
servers.query();
while (servers.next()) {
gs.info('---> name: ' + servers.getValue('name'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_server WHERE cpu_count > 2 AND cpu_count < 5
OrderBy demonstration
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.setLimit(limitValue);
computers.orderBy('name');
computers.query();
while (computers.next()) {
gs.info('---> serial number: ' + computers.getValue('serial_number'));
}
Translates to the following SQL:
SELECT * FROM cmdb_ci_computer WHERE ORDER BY name LIMIT 10
OrderByDesc demonstration
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.setLimit(limitValue);
computers.orderByDesc('name');
computers.query();
while (computers.next()) {
gs.info('---> number: ' + computers.getValue('number');
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer WHERE ORDER BY DESC name LIMIT 10
var limitValue = 10;
// add form factor to the computer form.
// set the form factor on a couple of records. One for desktop, one for laptop
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'desktop')
.addOrCondition('form_factor', 'laptop');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> name: ' + computers.getValue('name') +
', serial_number: ' + computers.getValue('serial_number') +
', form_factor: ' + computers.getValue('form_factor'));
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer
WHERE form_factor = 'desktop' OR form_factor = 'laptop'
ORDER BY name
LIMIT 10
GroupBy example
// add form factor to the computer form.
// set the form factor on a couple of records. One for desktop, one for laptop
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('form_factor', 'desktop')
.addOrCondition('form_factor', 'laptop');
computers.groupBy('form_factor')
computers.query();
while (computers.next()) {
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
}
The query translates to the following SQL:
SELECT * FROM cmdb_ci_computer
WHERE form_factor = 'desktop' OR form_factor = 'laptop'
GROUP BY form_factor
LIMIT 10
Null Check example
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'Dell');
computers.addQuery('form_factor', '!=', null);
computers.addNotNullQuery('serial_number');
computers.setLimit(limitValue);
computers.orderBy('name');
computers.query();
while (computers.next()) {
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
}
The query translates to the following SQL:
SELECT cmdb_ci_computer.* FROM cmdb_ci_computer
JOIN core_company AS manufacturer ON cmdb_ci_computer.manufacturer = manufacturer.sys_id
WHERE manufacturer.name = 'Dell'
AND cmdb_ci_computer.form_factor IS NOT NULL
AND cmdb_ci_computer.serial_number IS NOT NULL
ORDER BY cmdb_ci_computer.name
LIMIT 10
// handling a null serial_number value
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'Dell');
computers.addQuery('form_factor', '!=', null);
//computers.addNotNullQuery('serial_number');
computers.setLimit(limitValue);
computers.orderBy('name');
computers.query();
while (computers.next()) {
var serialNumber = computers.getValue('serial_number');
if (gs.nil(serialNumber )) {
serialNumber = 'Unknown!';
}
gs.info('---> name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), serialNumber, computers.form_factor]);
}
// update the form factor for all apple macbook machines. dot-walk example
var limitValue = 10;
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.name', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
// we could have used .setValue here, it has the same affect, but is much more difficult to read and maintain
// no percievable performance increase for decreased readability
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.update(); // test first before making the update!!!
}
The query translates to the following SQL:
UPDATE cmdb_ci_computer
JOIN core_company AS manufacturer ON cmdb_ci_computer.manufacturer = manufacturer.sys_id
SET cmdb_ci_computer.form_factor = 'Laptop'
WHERE manufacturer.name = 'apple'
AND cmdb_ci_computer.form_factor IS NOT NULL
LIMIT 10
Bad Constraint
var limitValue = 10;
// update the form factor for all apple macbook machines. dot-walk example
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuery('manufacturer.nam', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
//computers.update(); // test first before making the update!!!
}
Bad constraint 2
var limitValue = 10;
// update the form factor for all apple macbook machines. dot-walk example
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuey('manufacturer.name', 'apple');
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
//computers.update(); // test first before making the update!!!
}
Try Catch fail
try {
var limitValue = 10;
// update the form factor for all apple macbook machines. dot-walk example
var computers = new GlideRecord('cmdb_ci_computer');
computers.addQuey('manufacturer.name', 'apple'); // this line gets ignored!
computers.addNullQuery('form_factor');
computers.setLimit(limitValue);
computers.query();
while (computers.next()) {
gs.info('---> BEFORE. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
computers.form_factor = 'Laptop'; // make sure it is capitalized!
gs.info('---> AFTER. name: {0}\t\tserial number: {1}\t\tform factor: {2}',
[computers.getValue('name'), computers.getValue('serial_number'), computers.form_factor]);
//computers.update(); // test first before making the update!!!
}
}
catch(err) {
// no boom!
gs.info('---> ERROR: {0}', [err]);
}
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and "bookmark" AND "like" it!
Also, if you are not already, I would like to encourage you to become a member of the ITOM community!
