mssql object
Enables direct use of Transact-SQL code for working with tables in the SQL Database.
Can be used in all varieties of server scripts:
table operation scripts like insert, update, read, and del;
Scheduler scripts; and
Custom APIs.
For more detailed discussion and information see the Using Transact-SQL to access tables section in Work with server scripts in Mobile Services.
Methods
The mssql object has the following methods:
query
queryRaw
open
query
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.
Syntax
mssql.query(sql, params, options)
Parameters
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). |
queryRaw
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.
Syntax
mssql.queryRaw(sql, params, options)
Parameters
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). |
open
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
Syntax
mssql.open(options)
Parameters
Parameter |
Description |
|---|---|
options |
success and optionally error callback functions (see remarks for details). |
Remarks
options parameter
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.
Example
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);
}
});
Example
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);
}
});
}