locked
Azure Mobile Services Custom API - Return Scope Identity On Insert RRS feed

  • Question

  • Hi

    I have a custom API in AMS. The API inserts data into a table via stored proc and is supposed to return the scope identity. However in my API, I cannot respond back to the javascript calling the API and pass that scope identity. If I log it in the AMS console, I find it as expected, but it seems it is not being sent to the javascript calling the API.

    exports.get = function(request, response) { var mssql = request.service.mssql; var sql = "exec [myschema].[myproc] " + myparameters; mssql.query(sql, { success: function(results) {

    //neither results nor results[0] is working;
    //the data is stored perfectly fine in the table;
    //the proc works perfectly fine if called in SSMS;
    //I am using the javascript "WindowsAzure.MobileServiceClient" client in front-end response.send(200, {result: results }); }, error: function(err) { console.log(err); response.send(530, { error: err }); } });


    Wednesday, October 29, 2014 2:37 PM

Answers

  • Hi Phillip and Carlos

    I have found a rather interesting approach to this issue. It might help us in solving the multiple call problem. It does not stop the RDBMS from calling the success handler multiple times, but now you can tell which result is for which action in SQL.

    I found sample code that a certain Mike Taulty wrote for this particular issue on his blog: mtaulty.com. Please see his code below.

    Thanks again

     // It took me longer than I would expect to get this call to mssql.query to work because
        // I failed to understand that it calls your success handler for each resultset
        // from the DB (I have two).
        var firstTime = true;
        
        request.service.mssql.query(sql, [request.body.customerId, request.body.addressId],
            {
                success: function(results)
                {                         
                    if (!firstTime)
                    {
                        // this is not very nice, I need a better way to deal with URIs
                        // to be more like a RESTful service.
                        response.location('?id=' + results[0].id);
                        response.send(201);
                    }          
                    firstTime = !firstTime;           
                }
            }
        );
    
    Reference: mtaulty<dot>com/CommunityServer/blogs/mike_taultys_blog/archive/2013/07/05/azure-mobile-services-update-custom-apis<dot>aspx
    • Marked as answer by IAMZWEED Monday, November 10, 2014 12:47 PM
    Monday, November 10, 2014 12:47 PM

All replies

  • The server code seems ok, can you post the code you are using on the client to look at the result?  It should be in the response.result object.  (In your case, response.result = { result: results })  

    Also are you using WinJS, JS, or inside a Cordova/PhoneGap application?

    One option to see if the issue lies in the server or the client, is to hit this custom API through a tool like Fiddler/Postman, and see if you see the expected raw response there. 

    Wednesday, October 29, 2014 4:22 PM
  • Hi

    Thanks for the speedy response.

    I am using Cordova in Telerik AppBuilder.

    I have tested in Postman and I get the same results:

    {
        "stuff": "nothing",
        "result": []
    }

    I have added dummy data in the response to see if that gets thrown out too, but it comes back fine (see attribute "stuff" above). The code on the API now looks like this:

     success: function(results) {
                var stuff = "nothing";
                response.send(200, { stuff: stuff, result: results });
            }

    As I mentioned before, the value gets returned when I call the PROC manually or through WAMS and is actually logged in the WAMS log.

    Thanks again for your time

    Thursday, October 30, 2014 8:16 AM
  • Hi again

    Just to add on.

    I have gone through this issue thoroughly. Something more to add on which might help us.

    It seems like the request is being executed twice for some reason. The first time, the value is empty. The second time, it actually contains the scope identity. The timestamps are exactly the same. I'm puzzled.

    Below is the last 2 logs I made with the code after that. (sorry for the ugly formatting, the editor doesn't seem to allow images for me)

    Information new result[{"neweventinstance":1217}] api/addevent.js   Thu Oct 30 2014, 10:22:07 AM
    Information result[object Object]                              api/addevent.js  Thu Oct 30 2014, 10:22:07 AM
    Information new result[]                                          api/addevent.js  Thu Oct 30 2014, 10:22:07 AM
    Information result                                                   api/addevent.js   Thu Oct 30 2014, 10:22:07 AM

    Code:

    var stuff = "nothing";
                console.log("result" + results);
                var newResult = JSON.stringify(results);
                console.log("new result" + newResult);
                response.send(200, { stuff: stuff, result:  newResult});

    Thursday, October 30, 2014 9:12 AM
  • What does your stored procedure do? What does it return?

    Carlos Figueira

    Thursday, October 30, 2014 3:16 PM
  • Hi again

    Just to add on.

    I have gone through this issue thoroughly. Something more to add on which might help us.

    It seems like the request is being executed twice for some reason. The first time, the value is empty. The second time, it actually contains the scope identity. The timestamps are exactly the same. I'm puzzled.

    Below is the last 2 logs I made with the code after that. (sorry for the ugly formatting, the editor doesn't seem to allow images for me)

    Information new result[{"neweventinstance":1217}] api/addevent.js   Thu Oct 30 2014, 10:22:07 AM
    Information result[object Object]                              api/addevent.js  Thu Oct 30 2014, 10:22:07 AM
    Information new result[]                                          api/addevent.js  Thu Oct 30 2014, 10:22:07 AM
    Information result                                                   api/addevent.js   Thu Oct 30 2014, 10:22:07 AM

    Code:

    var stuff = "nothing";
                console.log("result" + results);
                var newResult = JSON.stringify(results);
                console.log("new result" + newResult);
                response.send(200, { stuff: stuff, result:  newResult});

    Hi Carlos

    The above quote of my previous Edit tells you what the stored proc returns: [{"neweventinstance":1217}]. However, this is not available in the "success" function of the stored proc call in the API method.

    I have a feint suspicion here: request.service.mssql is actually called TWICE. Once for the insert/update action and once more for the select. I have a few more methods that have this issue. It's not only scope_identity that has this ill-effect, it is any API method that makes a call to an SQL statement/procedure with more than one action in it.

    Currently investigating further. I will make comments here as I progress.

    Thanks

    Monday, November 10, 2014 12:13 PM
  • Hi Phillip and Carlos

    I have found a rather interesting approach to this issue. It might help us in solving the multiple call problem. It does not stop the RDBMS from calling the success handler multiple times, but now you can tell which result is for which action in SQL.

    I found sample code that a certain Mike Taulty wrote for this particular issue on his blog: mtaulty.com. Please see his code below.

    Thanks again

     // It took me longer than I would expect to get this call to mssql.query to work because
        // I failed to understand that it calls your success handler for each resultset
        // from the DB (I have two).
        var firstTime = true;
        
        request.service.mssql.query(sql, [request.body.customerId, request.body.addressId],
            {
                success: function(results)
                {                         
                    if (!firstTime)
                    {
                        // this is not very nice, I need a better way to deal with URIs
                        // to be more like a RESTful service.
                        response.location('?id=' + results[0].id);
                        response.send(201);
                    }          
                    firstTime = !firstTime;           
                }
            }
        );
    
    Reference: mtaulty<dot>com/CommunityServer/blogs/mike_taultys_blog/archive/2013/07/05/azure-mobile-services-update-custom-apis<dot>aspx
    • Marked as answer by IAMZWEED Monday, November 10, 2014 12:47 PM
    Monday, November 10, 2014 12:47 PM