Using Embedded Java Stored Procedures

Stored procedures written in SQL still achieve the maximum performance boost, but Java stored procedures support a richer programming language, third-party libraries and an easy migration path from client-side code to server-side extension.

Format

A Java method used as a stored procedure must have these features to be successfully bound to the procedure:

Method Type

static

Return Type

void

Argument #1

java.Sql.Connection

Argument #2,3

For each parameter of the stored procedure there must be a parameter in the Java method of the corresponding type as listed in this table:

SQL Type IN Parameter INOUT or OUT Parameter

BOOLEAN

boolean

boolean[]

STRING, VARCHAR

java.lang.String

java.lang.String[]

SMALLINT

short

short[]

INT, INTEGER

int

int[]

BIGINT, DECIMAL, NUMBER

long

long[]

DECIMAL(scale), NUMBER(scale)

java.math.BigDecimal

java.math.BigDecimal[]

FLOAT, REAL

float

float[]

DOUBLE

double

double[]

DATE

java.sql.Date

java.sql.Date[]

TIMESTAMP

java.sql.Timestamp

java.sql.Timestamp[]

TIME

java.sql.Time

ava.sql.Time[]

BLOB, CLOB, BYTES, TEXT, VARBINARY

byte[]

byte[][]

Argument #N (if the stored procedure specifies the RETURNS option)

java.sql.ResultSet[]

When the stored procedure parameter is an INOUT or OUT parameter, the Java parameter is defined as an array that at runtime will be assigned to an array of one element only. This way, if the Java extension code reassigns the element of the array to a different object, the caller of the method can access the modified value.

If the procedure has the RETURNS TABLE clause, the corresponding Java method must have a final java.sql.ResultSet[] argument that can be used to populate the return table. The user can also replace the object with a new ResultSet obtained by running a query.

Example

Step 1 Create Java Class

package com.mycompany;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MyFirstJavaProc {
    public static void stored_proc_inout_args_db(Connection conn, String[] s, int[] n, double[] d, BigDecimal[] dec) throws SQLException {
        System.out.println(String.format("stored_proc_inout_args_db - before - s = '" + s[0] + "', n = " + n[0] + ", d = " + d[0] + ", dec = " + dec[0]));

        if(conn == null)
            return;

        Statement stmt = conn.createStatement();
        stmt.execute("select * from system.tables");
        ResultSet rs = stmt.getResultSet();
        if(rs.next())
        {
            s[0] = rs.getString("TABLENAME");
            n[0] = rs.getInt("TABLEID");
            d[0] = Double.valueOf(rs.getString("CURRENTVERSION"));
            dec[0] = rs.getBigDecimal("CARDINALITY");
            System.out.println(String.format("stored_proc_inout_args_db - after - s = '" + s[0] + "', n = " + n[0] + ", d = " + d[0] + ", dec = " + dec[0]));
        }
    }
}

Step 2 Compile the File

Compile the file created in Step 1 and package it into a JAR archive.

$ javac com/mycompany/MyFirstJavaProc.java
$ jar cf myfirstjavaproc.jar com/mycompany/MyFirstJavaProc.class

Step 3 Create javaclass in NuoSQL

To load the Java class into memory, run the following command in nuosql (authenticated as a user with the DBA role), where myfirstjavaproc.jar is the JAR file compiled in Step 2.

SQL> CREATE JAVACLASS myclassid FROM 'path/to/myfirstjavaproc.jar';

For a full description of syntax and parameters, see CREATE JAVACLASS.

Step 4 Create a NuoDB SQL stored procedure

SQL> CREATE PROCEDURE xyz(
        INOUT s STRING,
        INOUT n INTEGER,
        INOUT d DOUBLE,
        INOUT dec DECIMAL(10,2))
    LANGUAGE JAVA EXTERNAL 'myclassid:com.mycompany.MyFirstJavaProc.stored_proc_inout_args_db';

For a full description of syntax and parameters, see CREATE PROCEDURE.

Step 5 Invoke the Java Stored Procedure

Use EXECUTE or CALL.

SQL> EXECUTE xyz(?, ?, ?, ?);

For a full description of syntax and parameters, see EXECUTE.

Step 6 Unload the Java Class

When the jar file isn’t needed anymore, you can unload it unload by running the following nuosql command:

SQL> DROP JAVACLASS myclassid IF EXISTS;

See DROP JAVACLASS.

SecurityManager Limitations: In general, a Java stored procedure could perform any operation that Java allows. In practice though, because the Java code is being hosted by the Transaction Engine (TE) process, a few operations are prohibited via a custom SecurityManager. These types of operations do not work with Java Stored Procedures:

Loading native code Killing the current process
Launching new processes
Defining classes dynamically or creating new class loaders
Accessing files from the file system
Modifying system properties
Accessing the printer or the clipboard
Creating server sockets

See Unsupported Methods in Embedded Java API for important information information about differences between the NuoDB JDBC driver API and the Embedded Java API used by Java stored procedures and triggers.