Creating models from stored procedures in MS SQL

[Clyde Lobo]

Hi,

I need to create models from existing stored procedures inside a SQL Server. I tried discovering them using the discover-api. But LB is not that advanced. Does any one know of ways/ solutions by which I can achieve the same?

Any links/hints/sample are welcome

Regards,

Clyde

[Raymond Feng]

You should be able to call SQL server stored procedure as follows:

1. Get the Connection object from app.dataSources.mssql.connector.client

2. Call mssql driver api:

var sql = require(‘mssql’);

var request = new sql.Request(connection);
    request.input('input_parameter', sql.Int, 10);
    request.output('output_parameter', sql.VarChar(50));
    request.execute('procedure_name', function(err, recordsets, returnValue) {
        // ... error checks

        console.dir(recordsets);
    }); 

https://github.com/patriksimek/node-mssql

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

--
You received this message because you are subscribed to the Google Groups "LoopbackJS" group.
To unsubscribe from this group and stop receiving emails from it, send an email to loopbackjs+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Clyde Lobo]

How do I get the app object?

[Raymond Feng]

The app object is available via:

1. require(‘server/server.js’)

2. Drop in a JS file under server/boot, for example:

module.exports = function(app) {

...

}

Thanks,

---

Raymond Feng

Co-Founder and Architect @ StrongLoop, Inc.

StrongLoop makes it easy to develop APIs in Node, plus get DevOps capabilities like monitoring, debugging and clustering.

[Clyde Lobo]

I should have specified this earlier,

I have created a remote methods in one of the models and trying to call the stored procedure. Whatever you have suggested does not work as the server module is not found. I tired creating a connection manually, but get the error that the connection has not been established.

Hope I have explained things clearly. Please feel free to ask for further details

Thanks, and sorry for the trouble.

[Clyde Lobo]

Hi,

I managed to get the connection object, its just one more level deep

   var connection = app.dataSources.mssql.connector.client.request;

  var request = connection();

request.execute('procedure_name', function(err, recordsets, returnValue) {
        // ... error checks

        console.dir(recordsets);
    }); 

[Jason McGinnis]

If I have used the commandline tool to generate models and api, can I update or override those models to use stored procedures?

[Jason McGinnis]

Let me clarify a little more of what my ultimate goal is.

I would like to use the StrongLoop framework to quickly get a project up and running.

I would generate the angular service script.

After everything is in place and working, I would like to do some performance tuning and point the models to crud stored procedures (that I have written or generated).

My vision is that the api broswer and everything else would not even know the difference.

[Karen Piotrowski]

Hi! Where did you put that code? in a file inside the /server/boot directory?

And Where did you specified the database you are using for mssql? I mean, where's the datasource for this if you are using node-mssql instead of the loopback connector for mssql? Or are you using both?

Thank you!!

[Michael DeMutis]

OK so we can execute stored procedures.  I was able to get this working without issue.  How can I go one step further and create my model based on the stored proc?  Is this going to be possible?