How can I integrate MATLAB Production Server with an Oracle Database?

2 views (last 30 days)
In my Oracle Database I have defined a TRIGGER AFTER INSERT on MYTABLE1. In this trigger I want to do some calculations on the inserted data and then insert the result into MYTABLE2. To perform these calculations I want to make use of my MATLAB Code which was deployed to an MATLAB Production Server (MPS). Is this possible?

Accepted Answer

MathWorks Support Team
MathWorks Support Team on 3 Mar 2021
Edited: MathWorks Support Team on 3 Mar 2021
Introduction
Through the MPS Java client classes and the server-side Java capabilities of Oracle it is definitely possible to call MPS from such a TRIGGER. A practical step-by-step example is shown below but before you follow these steps you my want to learn more about these two interfaces at:
Step-by-step Example
In this example we will deploy a simple MATLAB function which multiplies its input by two to a MPS. We will then configure an Oracle Server in such a way that whenever data is inserted into |MYTABLE1| a TRIGGER is executed which will call this MPS function to compute the doubled value of the inserted data and which will insert the result into |MYTABLE2|.
MATLAB
Use the "Production Server Compiler App" to compile the following MATLAB function:
function y = timestwo(x)
y = 2 * x;
Into an CTF-archive called OracleExample.ctf.
MPS
Create a new server instance using |mps-new| and make sure the configuration is correct with regards to the MCR location. The rest of this guide assumes all other configuration options were left at their default options meaning that port 9910 is used. Further we will assume that the MPS server and the Oracle Server are located on the same machine so we can work with |localhost| as MPS hostname. Then start the instance using |mps-start|. Now copy |OracleExample.ctf| into its |auto_deploy| directory.
Java
To verify that the MPS server is running correctly, implement the following Java class in a file named |MPSClient.java|:
import com.mathworks.mps.client.MATLABException;
import com.mathworks.mps.client.MWClient;
import com.mathworks.mps.client.MWHttpClient;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
public class MPSClient {
/**
* Interface for the OracleExample CTF archive
*/
public interface IOracleExample {
public double timestwo(double x) throws IOException, MATLABException;
}
/**
* Main method to allow testing the server from a standalone Java Application.
* @param args
*/
public static void main(String[] args) {
System.out.println(doTimesTwo(3.14));
}
/**
* Method which actually calls the MPS Server.
* @param x the input to be multiplied by two.
* @return the result.
*/
public static double doTimesTwo(double x) {
MWClient client = new MWHttpClient();
try {
// Create a proxy object
IOracleExample obj = client.createProxy(
new URL("http://localhost:9910/OracleExample"),
IOracleExample.class);
// Call the timestwo function on the server
return obj.timestwo(x);
} catch (MalformedURLException e) {
e.printStackTrace();
} catch (MATLABException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return 0;
}
}
Copy |mps_client.jar| into the same directory as this |MPSClient.java|. Then compile the application using:
javac -classpath mps_client.jar MPSClient.java
And run it using:
java -classpath .;mps_client.jar MPSClient
Which should produce the output:
6.28
Oracle Server
We can use this same Java class on the Oracle Server. Before we can do that however we need to make sure that |mps_client.jar| is loaded in the server. This can be done with the |loadjava| commandline tool which is shipped with Oracle server:
loadjava mps_client.jar -user system/myPassword@myDatabase
(Where you need to replace myPassword
and myDatabase with your actual password and database name.)
Once this has been loaded the custom class can be loaded into the database:
loadjava MPSClient.java -user system/myPassword@myDatabase
Then to make the class callable from SQL commands you will need to define a so called call specification. This is done through the following SQL command:
CREATE OR REPLACE FUNCTION MPSFUNCTION (X IN NUMBER) RETURN NUMBER AS LANGUAGE JAVA NAME 'MPSClient.doTimesTwo(double) return double';
Further the user which is going to execute this function will need to have permissions to access external hosts. In this example we assume that the SYSTEM
user will run the function and that the MPS server is localhost:9910 so we can run the following SQL command to grant the necessary permission:
BEGIN dbms_java.grant_permission( 'SYSTEM', 'SYS:java.net.SocketPermission', 'localhost:9910','connect,resolve' ); END;
With the following SQL commands we can create two new tables MYTABLE1
and MYTABLE2
both with a single column col1 which contains numeric data:
CREATE TABLE "SYSTEM"."MYTABLE1" ("COL1" NUMBER);
CREATE TABLE "SYSTEM"."MYTABLE2" ("COL1" NUMBER);
Now we can create a trigger on MYTABLE1
such that whenever data gets inserted into it, we call the timestwo
function on the MPS server and store the result in MYTABLE2:
CREATE OR REPLACE TRIGGER MPSTRIGGER AFTER INSERT ON MYTABLE1 FOR EACH ROW BEGIN INSERT INTO MYTABLE2 (col1) VALUES (MPSFUNCTION(:new.col1)); END;
If everything is working correctly, when we now insert data into MYTABLE1:
INSERT INTO TABLE MYTABLE1 (col1) VALUES(21)
We should automatically see the doubled values appearing in MYTABLE2:
SELECT * FROM MYTABLE2
Which should show the value |42| in |col1| of |MYTABLE2|.
Attachments
The code used in this example is attached below. This attachment also contains a MATLAB-file RunExample.m which uses Database Toolbox to connect to the Oracle Database and which can automatically perform all the SQL queries given above and if MATLAB is installed on the same machine as the Oracle Server load the JAR-files into the server. Note that you will need to update this script with the correct usernames, passwords and database names and hostnames. Also it assumes that the Oracle JDBC-driver has already been loaded.

More Answers (0)

Categories

Find more on Deployable Archive Creation in Help Center and File Exchange

Tags

No tags entered yet.

Products


Release

R2014a

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!