Using Transactions in the Azure Custom API for Mobile Services
I am using azure mobile service with javascript backend to execute custom API.
I need to execute multiple independent sql statements (delete, update). I need to have a transaction to ensure that I only execute the transaction after all sql statements have been executed.
I am having a hard time finding a lot of documentation on this.
I started here http://msdn.microsoft.com/en-us/library/jj554212.aspx and as pointed out is called connection.beginTransaction ()
mssql.open({
success: function(connection) {
connection.beginTransaction();
...
I don't know how I should proceed from there. If I try to execute a normal sql delete statement using the connection object, I get the following error:
connection.query(deleteStatement, [parameter1, ], {
success: function(results)
{
connection.commit();
response.send(statusCodes.OK, { message : '' });
},
error: function(err) {
connection.rollback();
console.log("error: " + err);
response.send(statusCodes.Error, {message : err});
}
});
Error: [msnodesql] Invalid parameter (s) passed to function request or queryRaw.
On the other hand, if I execute the same query (with the same syntax) on the original mssql object, it turns out that the connection.commit () and connection.rollback () statements do nothing. The request works, the items are removed ... But they are removed even if I call connection.rollback () as the last step.
I expect that I should call connection.query and submit the transaction ... somehow. I cannot find any documentation regarding this.
How to properly execute transactions in Azure Mobile Service - Custom API (javascript backend)? Thank!
source to share
I got it working. It turns out the syntax for this is slightly different from what is expected. I couldn't find any documentation on the matter, it looks like we know quite a bit about transactions.
Here's what I ended up using:
var mssql = request.service.mssql;
mssql.open({
success: function(connection) {
connection.beginTransaction( function (error)
{
var statement1 = 'delete ... where X = ?'
connection.query(statement1, [parameter1, ], function(err, results)
{
if (!isBlank(err))
{
console.log("Error:" + err);
connection.rollback();
response.send(statusCodes.Error, {message : ''});
connection.close();
return;
}
var statement2 = 'delete ... where X = ?'
connection.query(statement2, [parameter2, ], function(err, results)
{
if (!isBlank(err))
{
console.log("Error:" + err);
connection.rollback();
response.send(statusCodes.Error, {message : ''});
connection.close();
return;
}
else
{
//daisy chain more statements if necessary. When the last one succeeds:
connection.commit();
response.send(statusCodes.OK, { message : '' });
connection.close();
}
}
});
}
},
error: function(err) {
console.log("Error : " + err);
response.send(statusCodes.Error, {message : err});
}
});
I find this syntax awkward, but it works. Any improvements or recommendations on this template are more than welcome!
source to share
The mssql.open function returns the node-sqlserver.open object; it was just linked to your connection string. We are working on updating this documentation. You can look at the github project for node-sqlserver and look at the unit tests: https://github.com/Azure/node-sqlserver
Work on actively preparing documentation for this - sorry for the trouble.
source to share