none
[Node.js][Mobile Apps] How to execute a custom SQL query inside a read Table Script and return the results? RRS feed

  • Question

  • Since Mobile Apps does not support relationship between tables I’ve been using the server code (Table Script) to create custom SQL queries to update different tables when inserting into the database. 

    That works fine when inserting, but how can I do the same when reading? How can I override the query sent by the client with the results of my own?

    var readMiddleware = function(req,res,next){

        var query = {

            sql: 'SELECT...',

            parameters: [{ name: 'p1', value: param }]

        };

        req.azureMobile.data.execute(query).then(function(results){

            req.queryResult = results;

            next();

        });

    };

    table.read(function (context) {

        console.log(context.req.queryResults);

        // THEN WHAT? How can I return the queryResults to the client as the result of the read? 

        return context.execute(); 

    });

    So after the middleware runs and I have the result of the query I don’t know how to send it back to the client as the result of the read. 

    (I could do it using a custom API but I would loose the offline syncing at the client level) 

    I know this is something basic but I could not find it documented anywhere. 

    Thank you very much!





    Thursday, June 2, 2016 10:27 PM

Answers

  • OK, I've verified this is not a bug. When you register the custom middleware for the table, you need to tell the SDK when to mount the actual table operation, so something like:

        table.read.use(readMiddleware, table.operation)

    or

        table.use(tableMiddleware, table.execute)

    This allows you to specify middleware that runs either before or after the actual table operation.

    This is documented at http://azure.github.io/azure-mobile-apps-node/module-azure-mobile-apps_src_express_tables_table.html, but not very well, it's hard to pick up the subtlety.

    Thanks for reporting this, we'll improve the documentation situation.

    Please let us know how it goes.


    Friday, June 3, 2016 11:03 PM
  • Been thinking about this a bit more, and unfortunately, implementing a custom data source for incremental sync is not trivial.

    First issue is the custom data source. It must contain a source for datetime updated at and created at values as well as a rowversion column for optimistic concurrency. If you have this data for each record, you can map into the required format, but a bigger problem is...

    Extracting the required query from the request is difficult. The queryjs component we use to represent the query (context.query) doesn't expose this stuff in a nice way. The "easiest" way would probably be to parse the OData querystring manually. Lots of pitfalls and required knowledge here to end up with an endpoint that won't be fully functional.

    Finally, if you want it to be a read/write data source, you'll need to make sure the updated / created at and version data is updated as well.

    I can give you a sample for a simple read only "list" endpoint that doesn't support incremental sync, but it starts to get complex beyond that!

    • Marked as answer by Dan Flict Tuesday, June 21, 2016 12:11 PM
    Friday, June 17, 2016 2:22 AM

All replies

  • Hi Dan,

    There are two options

    - set the result of the query to context.req.results. The renderResults middleware then takes care of rendering the results to the client (https://github.com/Azure/azure-mobile-apps-node/blob/master/src/express/middleware/renderResults.js)

    - return a response directly by calling context.res.json(results) or similar. The express.js response object is documented at http://expressjs.com/en/4x/api.html#res.

    The two achieve the same end result. The only thing to keep in mind is that we may implement batching where these functions will be called multiple times in a single request. For this reason, I'd probably recommend setting the context.req.results property.

    Also, keep in mind that calling context.execute will cause the original query to execute, so unless you also intend to return or otherwise consume those results, you probably don't want to call it.

    Hope this helps!

    Dale

    Friday, June 3, 2016 12:35 AM
  • Actually, I just remembered another even better option - simply return the results from the table.read function, so instead of:

        return context.execute();

    call

        return context.req.queryResults;

    You can see in this code that it tests the response to see if it is a promise (like is returned from context.execute). If not, it just sets the req.results value.

    Friday, June 3, 2016 12:39 AM
  • You can leverage middleware to query custom queries in table operations. Here is the same requirement with you in SO https://stackoverflow.com/questions/35773617/query-other-tables-in-insert-function-of-azure-table-storage/35786589#35786589. You can refer to the answer for hits.
    Friday, June 3, 2016 1:06 AM
    Moderator
  • Thanks for the reply, Dale!

    I tried all 3 solutions, but none worked unfortunately. 

    First I tried “return context.req.queryResults” but the client kept waiting forever for the answer.  

    Since I know the object was there and was returned, I imagine the problem is related to the type of the returned data. So I tried “return { items: context.req.context.req.queryResults }”, encapsulating the answer into a new object. Now I got an actual error from the client: “The server did not return the expected total count”. Interesting. So do I need to encapsulate the SQL query result into a special type of object, with required fields like “total count”? 

    When trying the other solution, setting the result of the query to “return context.req.results(context.req.queryResults);” the server logged an error “context.req.results is not a function”. It did not even recognised it. 

    And finally, when trying “return context.res.json(context.req.queryResult);” I get the following error: “Can't set headers after they are sent.”

    From all the 3 option, the first looks more promising… But how to make it work? 

    Thank you very much! 


    Friday, June 3, 2016 12:29 PM
  • Hi Gary,

    Thank you for the reply. 

    The code exemple you sent me goes exactly until the same point I'm stuck. It logs the query result to the logs, but it dos not send it back to the client. It even runs "return context.execute()" after, executing the original query. 

    So after the middleware runs and I have the result of the query I don’t know how to send it back to the client as the result of the read. 

    Friday, June 3, 2016 12:33 PM
  • Hi Dan,

    Just coming up with a quick sample, one thing that just occurred - are you ensuring that context.req.queryResults is set correctly, i.e. the results are logged to the console correctly? I cannot see how the readMiddleware is mounted.

    Friday, June 3, 2016 10:20 PM
  • Hmm... There is a bug here somewhere. Will get back to you with a fix.
    Friday, June 3, 2016 10:26 PM
  • OK, I've verified this is not a bug. When you register the custom middleware for the table, you need to tell the SDK when to mount the actual table operation, so something like:

        table.read.use(readMiddleware, table.operation)

    or

        table.use(tableMiddleware, table.execute)

    This allows you to specify middleware that runs either before or after the actual table operation.

    This is documented at http://azure.github.io/azure-mobile-apps-node/module-azure-mobile-apps_src_express_tables_table.html, but not very well, it's hard to pick up the subtlety.

    Thanks for reporting this, we'll improve the documentation situation.

    Please let us know how it goes.


    Friday, June 3, 2016 11:03 PM
  • Hi Dale,

    Thanks again for answering.

    I'm sorry, my error, I forgot to copy this part of the code here in the forum. I was actually using the 

    table.read.use(readMiddleware, table.operation);

    since the start. So that's not the error...  Any other possibility comes to mind?

    (in answer to your previous question, yes, the results are being logged to the server console, so the SQL part of the code is working. It's actually the same as I've done successfully with custom APIs. I also verified the middleware is being called.)

    Thanks!



    • Edited by Dan Flict Sunday, June 5, 2016 9:28 AM clarification
    Saturday, June 4, 2016 11:15 AM
  • If the table.read function is being executed correctly, there is no reason why a context.res.json(results) should not send a response to the client. There is something else going on here. I would suggest running the code locally and using node-inspector to step through the code and see why results are not being returned.
    Monday, June 6, 2016 7:14 PM
  • Thanks, I'll try doing that and report back.
    • Edited by Dan Flict Monday, June 6, 2016 10:32 PM
    Monday, June 6, 2016 10:32 PM
  • Hi, sorry for the absence, I had to step away from this project for a week. 

    So I was not able to test locally. The whole point for me to use something like Azure Mobile Apps is having features like Table Scripts that eliminate the need of testing the server locally. Since the whole project was initialized and created through the portal, I believe the work needed to transfer and test it locally is not worth it? 

    Moving forward, I tested a bit more, but the problem persists. 

    I can send back any type of object, even hardcoded like

    return context.res.json({id: '123', property2: '123'});

    and I get a "The server did not return the expected total count" error at the client.  Same with 

    return {id: '123', property2: '123'};

    I think that points at the direction that for the offline sync to work at the client level, the returned object needs to be more than just the array with the content. Maybe I need to send back other fields like total_count etc... Am I too far?

    Thanks again!

    Monday, June 13, 2016 8:50 PM
  • Hi,

    Any sample for .NET Backend to query other table in a table controller?

    Tuesday, June 14, 2016 12:35 AM
  • Hi Dan,

    I'm glad that you are at least able to return a response to the client using context.res.json. I did a quick test of just returning results from the read function and it worked, so there must be something else at play.

    You can bring up a remote node-inspector debugger for you app when hosted on Azure by following the instructions at https://azure.microsoft.com/en-us/documentation/articles/app-service-web-nodejs-get-started/#debug-your-app-with-node-inspector. As you're working with the site directly using Visual Studio Online, you don't need to worry about the git steps.

    I didn't think that it was required to return the total count with offline sync, but you should be able to return the results in the following format to keep it happy:

    context.res.json({
      count: totalNumberOfResults,
      results: [
        { id: '123', property2: '123' }
      ]
    });

    Additionally, don't prefix this call with "return", i.e. don't return the results of the call to context.res.json.

    Let me know how it goes.

    Tuesday, June 14, 2016 1:47 AM
  • Hi again Dale, and thanks one more time for the patience! :)

    I don't know why I did not test this before, but I decided to see if using normal tables at the client instead of offline sync tables (here in iOS they are MSTable instead of MSSyncTable) works. And indeed it works!  

    So I can send data back to the client using "context.res.json()" normally when calling the read using normal tables. But as soon as I try using the same script with offline sync tables (MSSyncTables) I start getting errors, like "Can't set headers after they are sent".

    And sending the "count" like you wrote did not change the problem.

    I thought maybe if I opt out from "incremental sync" that could work, but it doesn't. 

    Sorry to turn the Node.js post into a Offline Sync post, but I guess that's where my problem resides.

    So I guess the question is: Using the READ table script, is it possible to modify the query that is returned to the client, when it's using offline sync?


    • Edited by Dan Flict Tuesday, June 14, 2016 10:38 AM
    Tuesday, June 14, 2016 10:37 AM
  • Hi Dan,

    There is an option you can specify when querying tables to include a total count. Not sure how the option is set on the iOS client, but with OData, it can be specified with a $inlinecount=allpages option. Essentially, it instructs the server to return a total count as well as a result set in the format I described above. Incremental sync clearly expects the results in the requested format. You'll need to return the results in the format specified to work with incremental sync.

    As far as the "Can't set headers after they are sent" issue goes, this occurs if you try to send a response after one has already been sent, e.g. calling res.json() twice in succession will cause this. Returning the result of the res.json() call from the read function will likely cause this.

    The simple answer to your question is "yes!". We just need to make sure the response is correctly formatted. We'll likely add a sample or some sort of documentation based on this thread. We appreciate you taking the time to work through your issues with us!

    Tuesday, June 14, 2016 5:07 PM
  • Hi Linda,

    You can find some information on using related tables at https://shellmonger.com/2016/05/27/30-days-of-zumo-v2-azure-mobile-apps-day-26-relationship-advice/.

    Hope this helps!

    Tuesday, June 14, 2016 9:24 PM
  • Yes, I figure it out.

    Thanks,

    Linda

    Thursday, June 16, 2016 3:02 AM
  • New official documentation based on this thread will be great! I bet a lot of people are having the same questions. 

    So I looked into the references but the only option that can be set when pulling into a offline sync table is the "PageSize" for pagination. Nowhere in the client that I can find you can specify how if the total count is to be included or not. You can also opt-out (by sending nul) from incremental sync, which I'm already doing. (http://azure.github.io/azure-mobile-services/iOS/v3/Classes/MSSyncTable.html)

    About the "Can't set header after they are sent" error: It's interesting because I did not change one bit of server code for it to work. The only change was in the client, I stopped using the offline sync table and used a regular table. If the server was sending the response twice surely the error would stay the same independently from what type of table I was using at the client?

    Waiting eagerly for a sample of code to help me with this. :)

    Thanks

    Thursday, June 16, 2016 11:51 AM
  • Sorry, I think I misled you a little, I wasn't being very clear. I was just illustrating that offline sync requests the result set in this alternate format that includes a total record count. We need to make sure we return the results in this format. I'll post a sample soon.

    Hmm... The can't set header issue must be occurring for a related reason. Impossible to speculate without knowing your exact setup, but I'll post this sample and things should make more sense.

    If you wanted to figure out exactly what is going on, you could put a breakpoint inside the send method of the express.js request module using node-inspector, somewhere like https://github.com/expressjs/express/blob/master/lib/response.js#L101, and look at the stack trace to see where it's being called from.

    Anyways, watch this space.

    Friday, June 17, 2016 12:51 AM
  • Been thinking about this a bit more, and unfortunately, implementing a custom data source for incremental sync is not trivial.

    First issue is the custom data source. It must contain a source for datetime updated at and created at values as well as a rowversion column for optimistic concurrency. If you have this data for each record, you can map into the required format, but a bigger problem is...

    Extracting the required query from the request is difficult. The queryjs component we use to represent the query (context.query) doesn't expose this stuff in a nice way. The "easiest" way would probably be to parse the OData querystring manually. Lots of pitfalls and required knowledge here to end up with an endpoint that won't be fully functional.

    Finally, if you want it to be a read/write data source, you'll need to make sure the updated / created at and version data is updated as well.

    I can give you a sample for a simple read only "list" endpoint that doesn't support incremental sync, but it starts to get complex beyond that!

    • Marked as answer by Dan Flict Tuesday, June 21, 2016 12:11 PM
    Friday, June 17, 2016 2:22 AM
  • Thanks Dale, you helped a lot!
    Tuesday, June 21, 2016 12:12 PM