Share via


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:

  1. Projection query which returns selected columns

  2. Where filter

  3. 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.');
            }
        }
    });
}

See Also

Mobile Services JavaScript (Node.js) backend library