GlideDBFunctionBuilder - Scoped, Global

Build functions to perform SQL operations in the database.

The GlideDBFunctionBuilder methods provide a way to build Relational Database Management System (RDBMS) functions to perform SQL operations on record data. These methods can be used in both scoped and global server scripts.

To use platform functions:
  • Construct a function using the GlideDBFunctionBuilder constructor and associated methods.
  • After building a function, you can apply the function to the current record using the addFunction() method of the GlideRecord class.
  • Add the function to a query using the addQuery() method of the GlideRecord class.
  • Retrieve the results of the function using the existing GlideRecord API methods such as getValue() and getElement().

Scoped GlideDBFunctionBuilder - add()

Adds the values of two or more integer fields.

Use the field(String field) method to define fields on which the operation is performed.

Table 1. Parameters
Name Type Description
None
Table 2. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - build()

Builds the database function defined by the GlideDBFunctionBuilder object.

Table 3. Parameters
Name Type Description
None
Table 4. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();
gs.print(myAddingFunction);
Output:
*** Script: glidefunction:add(order,priority)

Scoped GlideDBFunctionBuilder - concat()

Concatenates the values of two or more fields.

Use the field(String field) method to define fields on which the operation is performed.

Table 5. Parameters
Name Type Description
None
Table 6. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myConcatFunction = functionBuilder.concat();
myConcatFunction = functionBuilder.field('short_description');
myConcatFunction = functionBuilder.field('caller_id.name');
myConcatFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - constant(String constant)

Defines a constant value to use in the function. If used with the dayofweek() method, the string defines whether to use Sunday or Monday as the first day of the week.

Table 7. Parameters
Name Type Description
constant String A constant value used in a function.

When used with the dayofweek() method, the value defines whether the week starts on a Sunday or Monday.

  • 1: Week begins on Sunday.
  • 2: Week begins on Monday.

This definition enables the dayofweek() method to return the correct day of the week from a given date. If a value other than 1 or 2 is provided, the dayofweek() method uses Sunday as the first day of the week.

Table 8. Returns
Type Description
void

Scoped GlideDBFunctionBuilder - datediff()

Determines the duration using a given start date/time and end date/time.

Use the field(String field) method to define start and end date/time fields.

Table 9. Parameters
Name Type Description
None
Table 10. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myDateDiffFunction = functionBuilder.datediff();
myDateDiffFunction = functionBuilder.field('sys_updated_on');
myDateDiffFunction = functionBuilder.field('opened_at');
myDateDiffFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - dayofweek()

Returns an integer representing the day of the week for a given date.

Use the field(String field) method to define the given date/time. Use the constant(String constant) method to define whether the week starts on a Sunday or Monday.

This method can be used with MySQL, Oracle, and Microsoft SQL Server databases only. If using an Oracle database, the NLS_TERRITORY setting must be set to a territory with Sunday as the first day of the week.

Table 11. Parameters
Name Type Description
None
Table 12. Returns
Type Description
Integer

If the first day of the week is set to Sunday in the constant(String constant) method, return values are associated with the following days of the week:

  • 1: Sunday
  • 2: Monday
  • 3: Tuesday
  • 4: Wednesday
  • 5: Thursday
  • 6: Friday
  • 7: Saturday

If the first day of the week is set to Monday:

  • 1: Monday
  • 2: Tuesday
  • 3: Wednesday
  • 4: Thursday
  • 5: Friday
  • 6: Saturday
  • 7: Sunday

If a value other than 1 or 2 is provided in the constant(String constant) method, the dayofweek() method uses Sunday as the first day of the week.

var functionBuilder = new GlideDBFunctionBuilder();
var dayOfWeekFunction = functionBuilder.dayofweek();
dayOfWeekFunction = functionBuilder.field('opened_at');
dayOfWeekFunction = functionBuilder.constant('2');
dayOfWeekFunction = functionBuilder.build();

var gr = new GlideRecord('incident');
gr.addFunction(dayOfWeekFunction);
gr.query();
while(gr.next())
gs.log(gr.getValue(dayOfWeekFunction));

Scoped GlideDBFunctionBuilder - divide()

Divides the value of one integer field by another.

Use the field(String field) method to define fields on which the operation is performed.

Table 13. Parameters
Name Type Description
None
Table 14. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myDivideFunction = functionBuilder.divide();
myDivideFunction = functionBuilder.field('order');
myDivideFunction = functionBuilder.field('priority');
myDivideFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - field(String field)

Defines a field on which a SQL operation is performed.

Table 15. Parameters
Name Type Description
field String The field on which you are performing the SQL operation.
Table 16. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myAddingFunction = functionBuilder.add();
myAddingFunction = functionBuilder.field('order');
myAddingFunction = functionBuilder.field('priority');
myAddingFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - GlideDBFunctionBuilder()

Instantiates a GlideDBFunctionBuilder object.

Table 17. Parameters
Name Type Description
None
var builder = new GlideDBFunctionBuilder();

Scoped GlideDBFunctionBuilder - length()

Determines the number of code units in a field.

Use the field(String field) method to define fields on which the operation is performed.

Table 18. Parameters
Name Type Description
None
Table 19. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myLengthFunction = functionBuilder.length();
myLengthFunction = functionBuilder.field('short_description');
myLengthFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - multiply()

Multiplies the values of two integer fields.

Use the field(String field) method to define fields on which the operation is performed.

Table 20. Parameters
Name Type Description
None
Table 21. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var myMultiplyFunction = functionBuilder.multiply();
myMultiplyFunction = functionBuilder.field('order');
myMultiplyFunction = functionBuilder.field('priority');
myMultiplyFunction = functionBuilder.build();

Scoped GlideDBFunctionBuilder - subtract()

Subtracts the value of one integer field from another.

Use the field(String field) method to define fields on which the operation is performed.

Table 22. Parameters
Name Type Description
None
Table 23. Returns
Type Description
void
var functionBuilder = new GlideDBFunctionBuilder();
var mySubtractFunction = functionBuilder.subtract();
mySubtractFunction = functionBuilder.field('order');
mySubtractFunction = functionBuilder.field('priority');
mySubtractFunction = functionBuilder.build();