Exportera (0) Skriv ut
Visa allt
EN
Det här innehållet finns inte tillgängligt på ditt språk men här finns den engelska versionen,

mssql object

Updated: August 30, 2013

Enables direct use of Transact-SQL code for working with tables in the SQL Database.

Can be used in all varieties of server scripts:

  1. table operation scripts like insert, update, read, and del;

  2. Scheduler scripts; and

  3. Custom APIs.

The mssql object has the following methods:

  • query

  • queryRaw

  • open

Executes a query, specified by a TSQL string; the results are returned to the success callback on the options object. The query can include parameters if the params parameter is present.

mssql.query(sql, params, options)

 

Parameter Description

sql

The TSQL string to execute. The ? placeholders, if present, are replaced with the contents of the params array.

params

Replace the ? placeholders in the sql string.

options

success and optionally error callback functions (see remarks for details).

Executes a query, specified by a TSQL string; the results are returned to the success callback on the options object. The query can include parameters if the params parameter is present. The returned results are in raw format.

The raw format is a JSON format that consists of a metadata section which describes the result set’s columns, followed by a rows section which contains multiple row elements, each of which has one entry for each column in the result set. For an example, see Work with server scripts in Mobile Services.

mssql.queryRaw(sql, params, options)

 

Parameter Description

sql

The TSQL string to execute. The ? placeholders, if present, are replaced with the contents of the params array.

params

Replace the ? placeholders in the sql string.

options

success and optionally error callback functions (see remarks for details).

Opens a connection to the Mobile Services SQL Database. The connection is returned as an argument to the success handler. You can then invoke the following functions on the connection object: close, queryRaw, query, beginTransaction, commit, and rollback

mssql.open(options)

 

Parameter Description

options

success and optionally error callback functions (see remarks for details).

 

Syntax Description

{ success: function(results) {…..}, error: function() { … }}

The success function provides access to the results of the operation on the SQL Database. The error function is optional.

All three methods of this object use the options parameter, which consists of two call-back functions: a success function, and an optional error function.

The success function takes a results parameter: this function is where you can process the results of the SQL Database operation.

The optional error function is used for error recovery beyond the default level already provided by Azure Mobile Services.

Failures can occur when there is a loss of connectivity to the database, an invalid object, or an incorrect query. By default when an error occurs, server scripts log the error and write an error result to the response. Because Mobile Services provides default error handling, you don't have to handle errors that may occur in the service.

You can override the default error handling by implementing explicit error handling if you want a particular compensating action or when you want to use the global console object to write more detailed information to the log.

For more information see Work with server scripts in Mobile Services.

The following query can be part of any variety of server script. It has no parameters and returns three records from the statusupdate table in the results parameter of the success function.

   mssql.query('select top 3 * from statusupdates', {
      success: function(results) {
         console.log(results);
      },
      error: function(err) {
                console.log("error is: " + err);
      }
   });

The following example implements custom authorization in an insert script by reading permissions for the request user from the permissions table. When the query is executed, the placeholder ? in the TSQL string is replaced with the supplied parameter, which is the userID field of the incoming user parameter.

function insert(item, user, request) {
    var sql = "SELECT _id FROM permissions WHERE userId = ? AND permission = 'submit order'";
    mssql.query(sql, [user.userId], {
        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.');
            }
        },
        error: function(err) {
           console.log("error is: " + err);
        }
    });
}

Visa:
© 2014 Microsoft