table object
Provides functionality for working with specific tables.
Methods
del(itemOrId, options)
Deletes a specified itemID from the table.Parameters
Name
Type
Argument
Description
itemOrId
object
item to delete
options
callback parameter
insert(item, options)
Inserts the specified item into the table.Parameters
Name
Type
Argument
Description
item
object
item to insert
options
callback parameter
orderBy(arg1, arg2, …)
Returns a Query object instance where the query is ordered by the supplied column name arguments, in ascending order.Parameters
Name
Type
Argument
Description
Arg1
string
Major column to sort by
Arg2
string
optional, as are more column names
Next column to sort by
orderByDescending(arg1, arg2, …)
Returns a Query object instance where the query is ordered by the supplied column name arguments, in descending order.Parameters
Name
Type
Argument
Description
Arg1
string
Major column to sort by
Arg2
string
optional, as are more column names
Next column to sort by
Read (options)
Reads all data from the table and invokes the success handler specified on the options parameter passing in an array of results.Important
You should not call the read method on tables of unbounded size.
Parameters
Name
Type
Argument
Description
options
callback parameter
select(string)
Returns a Query object instance with the requested string projection applied.Parameters
Name
Type
Argument
Description
string
comma delimited string list
column names to return, single-quote enclosed, comma separated
select(function)
Returns a Query object instance with the requested string projection applied.Parameters
Name
Type
Argument
Description
function
function
Returns a Query object instance with the requested function projection applied. In the function definition, the this keyword accesses a row, and the dot operator accesses a column, and JavaScript functions can be applied to them.
skip(recordCount)
Returns a Query object instance that skips the first recordCount number of records.Parameters
Name
Type
Argument
Description
recordCount
integer
Number of records to skip
take(recordCount)
Returns a Query object instance that returns the recordCount number of records.Parameters
Name
Type
Argument
Description
recordCount
integer
Number of records to return
where(object)
Returns a Query object instance that is filtered based on the property values of the supplied JSON object.Parameters
Name
Type
Argument
Description
Object
object
JSON object with property values to fileter on
where(function)
Returns a Query object instance that is filtered based on the supplied function.Parameters
Name
Type
Argument
Description
function
function
Returns a Query object instance with the requested function filter applied. In the function definition, the this keyword accesses a row, and the dot operator accesses a column, and JavaScript functions can be applied to them.
update(item, options)
Inserts the specified item into the table.Parameters
Name
Type
Argument
Description
item
object
item to insert
options
callback function
Remarks
insert, update, and del methods accept an options object, which can have success or error handlers defined.
Query methods (orderBy, orderByDescending, select, skip, take and where all return a Query object. This object exposes these same methods, which enables you to compose queries as a series of method calls.
Here are some examples:
Projection query which returns selected columns
Where filter
TSQL code with filters
Example
This script shows how to run a projection query that returns only selected columns. The read parameter shows how the options parameter is coded.
var tableName = tables.getTable("TodoItem");
console.log("table name is " + tableName);
tableName.select('text', 'complete')
.read(
{ success: function(results) {
if (results.length > 0) {
console.log(results);
} else {
console.log('no results returned');
}
}
});;
Example
The following script calls the where method to filter the returned rows by the supplied object values. When at least one record is returned, it is assumed that the user has the necessary permission to submit an order and the insert is executed; otherwise an error is returned.
function insert(item, user, request) {
var permissionsTable = tables.getTable('permissions');
permissionsTable.where({
userId: user.userId,
permission: 'submit order'
}).read({
success: function(results) {
if (results.length > 0) {
// Permission record was found. Continue normal execution.
request.execute();
} else {
console.log('User %s attempted to submit an order without permissions.', user.userId);
request.respond(statusCodes.FORBIDDEN, 'You do not have permission to submit orders.');
}
}
});
}
Example
This example shows how to call a select with a function parameter.
tableName.select(function() { return this.id.substring(2,5) })
.read(
{ success: function(results) {
if (results.length > 0) {
console.log(results);
} else {
console.log('no results returned');
}
}});
Example
The following function, from a scheduled job, executes Transact-SQL that returns duplicate rows and then uses the del method to remove the duplicates.
function cleanup_channels() {
var sql = "SELECT MAX(Id) as Id, Uri FROM Channel " +
"GROUP BY Uri HAVING COUNT(*) > 1";
var channelTable = tables.getTable('Channel');
mssql.query(sql, {
success: function(results) {
if (results.length > 0) {
for (var i = 0; i < results.length; i++) {
channelTable.del(results[i].Id);
console.log('Deleted duplicate channel:' +
results[i].Uri);
}
} else {
console.log('No duplicate rows found.');
}
}
});
}