How can I integrate the MPS with MS SQL Server?

Hello,

I'd like to be able to call MPS-run functions from a MS SQL Server environment. I would consider two cases: (1) the Matlab procedure loads the data into memory, crunches it, and uploads the results back into the database; (2) the Matlab function behaves like a T-SQL function and can be called on the fly, e.g., SELECT norminv(p,mu,sigma) would provide the percentile-p of a normal distribution with mean m and standard deviation sigma.

What options are open ?

Thanks.

Alexandre

[Tim Choo]

(1) The MATLAB function hosted on MPS must be triggered in some fashion, e.g. from a C# application, Java application, etc. MPS hosted functions do not operate in a cron-job or scheduled task fashion. Assuming the function is triggered by an external cron job or scheduled task, the MPS function can read/write data from SQL Server using Database Toolbox functions and the ODBC driver for SQL Server. (2) In order to call the MPS function you would have to create a C# SQL CLR Stored Procedure in Visual Studio. In your code, you would make a HTTPWebRequest to call the MPS function through the REST API. This is POST call and the specifics of the JSON input/output payloads can be found in the MPS documentation here . You can then call this CLR Stored Procedure in your T-SQL query.

Answer 1

[Nicole Bonfatti]

Moving Tim's comment to an answer:

(1) The MATLAB function hosted on MPS must be triggered in some fashion, e.g. from a C# application, Java application, etc. MPS hosted functions do not operate in a cron-job or scheduled task fashion. Assuming the function is triggered by an external cron job or scheduled task, the MPS function can read/write data from SQL Server using Database Toolbox functions and the ODBC driver for SQL Server. (2) In order to call the MPS function you would have to create a C# SQL CLR Stored Procedure in Visual Studio. In your code, you would make a HTTPWebRequest to call the MPS function through the REST API. This is POST call and the specifics of the JSON input/output payloads can be found in the MPS documentation here . You can then call this CLR Stored Procedure in your T-SQL query.