Sample external Java stored routines provided with HiRDB

This sample Java stored procedure obtains a calendar of the specified year and month.

External Java procedure (filename: sample1.java)

/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 sample program of Java stored procedure 1         */
/************************************************************************/
import java.lang.*;
import java.util.*;
 
/************************************************************************/
/* name = sample_1 class                                                */
/************************************************************************/
public class sample1 {
    /*==================================================================*/
    /* name = main method for debugging                                 */
    /*==================================================================*/
    public static void main(java.lang.String[] args) {
        java.lang.Integer year = new Integer(args[0]);
        java.lang.Integer month = new Integer(args[1]);
        java.lang.String calendar[] = new String[1];
        calendar(year, month, calendar);
        System.out.println(calendar[0]);
    }
 
    /*====================================================================*/
    /* name = sample_1 method                                             */
    /*====================================================================*/
    public static void calendar(java.lang.Integer year,
                               java.lang.Integer month,
                                java.lang.String[] calendar) {
        int DayOfWeek;          // first day of the week in the specified month
        int week;               // For linefeed control
        int wyear = year.intValue();    // Year work
        int wmonth = month.intValue();  // Month work
 
        // Creating the calendar header
        calendar[0] = "         " +  wyear + " / " + wmonth + "\n";
        calendar[0] += "Sun Mon Thu Wed Tue Fri Sat\n";
 
        // Generating the calendar object 
        Calendar target_cal = new GregorianCalendar(wyear, wmonth - 1, 1);
 
        // Calculating the first day of the week in the specified month
        DayOfWeek = target_cal.get(Calendar.DAY_OF_WEEK);
 
        // Specifying spaces up to the first day of the week
        for (week = 1; week < DayOfWeek; week++) {
            calendar[0] += "    ";
        }
        // Specifying the date
        for (;
             target_cal.get(Calendar.MONTH) == wmonth - 1;
             target_cal.add(Calendar.DATE, 1), week++) {
            // Adjusting spaces according to the date and digits
            if (target_cal.get(Calendar.DATE) < 10) {
                calendar[0] += "  " + target_cal.get(Calendar.DATE);
            } else {
                calendar[0] += " " + target_cal.get(Calendar.DATE);
            }
            // Specifying padding characters between dates
            if (week == 7) {
                calendar[0] += "\n";
                week = 0;
            } else {
                calendar[0] += " ";
            }
        }
 
        return;
    }
}

The following is an example of defining and executing a Java stored procedure using the external Java procedure shown above:

Compiling the Java file (for HP-UX)
javac sample1.java
Creating the JAR file (for HP-UX)
jar -cvf sample1.jar sample1.class
Registering the JAR file in HiRDB (using INSTALL JAR SQL statement)
INSTALL JAR 'sample1.jar' ;

Defining the Java stored procedure

CREATE PROCEDURE calendar(IN pyear INT, IN pmonth INT, OUT
                                        calendar VARCHAR(255))
  LANGUAGE JAVA
  EXTERNAL NAME 'sample1.jar:sample1.calendar(java.lang.Integer,
                 java.lang.Integer,java.lang.String[])
                 returns void'
  PARAMETER STYLE JAVA
end_proc;

Executing the Java stored procedure
CALL calendar(?,?,?)

(2) Sample 2

This example accepts the specified date as a processing range and updates the total for the goods_no column in that range.

The example assumes that the table is defined as follows:

CREATE TABLE master_t1 (goods_no int,total_quantity dec(17,2))
CREATE TABLE tran_t1(goods_no int,quantity_1 dec(17,2),entrydate date)

External Java procedure (filename: sample2.java)

/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 Java stored sample 2                              */
/************************************************************************/
import java.lang.*;
import java.math.*;
import java.sql.*;
 
/************************************************************************/
/* name = sample_2 class                                                */
/************************************************************************/
public class sample2 {
    /*==================================================================*/
    /* name = main method for debugging                                 */
    /*==================================================================*/
    public static void main(String args[]) throws SQLException {
        java.sql.Date fromdate = Date.valueOf("1996-06-01");
        java.sql.Date todate = Date.valueOf("1996-06-30");
        try {
        // Registering the Driver class
            Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver");
        } catch (ClassNotFoundException ex) {
                System.out.println("\n*** ClassNotFoundException caught ***\n");
                ex.printStackTrace();
                System.out.println ("");
                System.out.println("\n*************************************\n");
                return;
        }
 
        jproc1(fromdate, todate);
    }
    /*==================================================================*/
    /* name = sample_2 method                                           */
    /*==================================================================*/
    public static void jproc1(java.sql.Date fromdate, java.sql.Date todate)
                                                         throws SQLException {
 
        java.lang.Integer x_goods_no;
        java.math.BigDecimal x_quantity_1, x_total_quantity;
        try {
            // Creating a connection object (CONNECT not issued within the Java 
                                                          procedure) 
            java.sql.Connection con =
              DriverManager.getConnection("jdbc:hitachi:hirdb");
 
            con.setAutoCommit(false);  // Suppressing automatic commit 
 
            // SELECT (stmt1) preprocessing 
            java.sql.PreparedStatement stmt1 =
              con.prepareStatement("SELECT goods_no, quantity_1
                                                   , entrydate FROM tran_t1
              WHERE entrydate BETWEEN ? AND ? ORDER BY entrydate");
            // SELECT (stmt2) preprocessing (outside the loop) 
            java.sql.PreparedStatement stmt2 =
              con.prepareStatement("SELECT total_quantity FROM master_t1
              WHERE goods_no = ?");
 
            // INSERT (stmt3) preprocessing (outside the loop) 
            java.sql.PreparedStatement stmt3 =
              con.prepareStatement("INSERT INTO master_t1 VALUES(?, ?)");
 
            // UPDATE (stmt4) preprocessing (outside the loop) 
            java.sql.PreparedStatement stmt4 =
              con.prepareStatement("UPDATE master_t1 SET total_quantity = ?
              WHERE goods_no = ?");
 
             // Specifying SELECT (stmt1) input parameters 
            stmt1.setDate(1, fromdate);
            stmt1.setDate(2, todate);
 
            // Executing SELECT (stmt1) 
            java.sql.ResultSet rs1 = stmt1.executeQuery();
            while (rs1.next()) {
                // Obtaining the retrieval result of SELECT (stmt1) 
                x_goods_no = (Integer)rs1.getObject("goods_no");
                x_quantity_1 = rs1.getBigDecimal("quantity_1");
 
                // Specifying SELECT (stmt2) input parameter 
                stmt2.setObject(1, x_goods_no);
 
                // Executing SELECT (stmt2) 
                java.sql.ResultSet rs2 = stmt2.executeQuery();
 
                // Checking whether or not goods_no has been registered to
                                                             determine action
                if (!rs2.next()) {      // Not registered ==> Add a new entry
                    // Closing the SELECT (stmt2) cursor before updating
                    rs2.close();
 
                    // Specifying INSERT (stmt3) input parameters
                    stmt3.setObject(1, x_goods_no);
                    stmt3.setBigDecimal(2, x_quantity_1);
 
                    // Executing INSERT (stmt3) 
                    stmt3.executeUpdate();
                } else {                // Registered ==> Update the
                                                          existing entry
                    // Obtaining the current value
                    x_total_quantity = rs2.getBigDecimal("total_quantity");
 
                    // Incrementing
                    x_total_quantity = x_total_quantity.add(x_quantity_1);
 
                    // Closing SELECT (stmt2) cursor before updating
                    rs2.close();
 
                    // Specifying UPDATE (stmt4) input parameters
                    stmt4.setBigDecimal(1, x_total_quantity);
                    stmt4.setObject(2, x_goods_no);
                    stmt4.executeUpdate() ;
 
                }
            }
 
            // Closing SELECT (stmt1) cursor
            rs1.close();
 
            // Releasing each statement object 
            stmt1.close();
            stmt2.close();
            stmt3.close();
            stmt3.close();
 
            // Disconnecting 
            con.close();
 
        } catch (SQLException ex) { // SQL error handling procedure 
            SQLException fast_ex = ex;
 
            System.out.println("\n***** SQLException caught *****\n");
            while (ex != null) {
                System.out.println ("SQLState: " + ex.getSQLState ());
                System.out.println ("Message:  " + ex.getMessage ());
                System.out.println ("Vendor:   " + ex.getErrorCode ());
                ex.printStackTrace();
                ex = ex.getNextException ();
                System.out.println ("");
            }
            System.out.println("*******************************\n");
 
            throw fast_ex;
        }
        return;
    }
}

The following is an example of defining and executing a Java stored procedure using the external Java procedure shown above:

Compiling the Java file (for HP-UX)
javac sample2.java
Creating the JAR file (for HP-UX)
jar cvf sample2.jar sample2.class
Registering the JAR file in HiRDB (using INSTALL JAR SQL statement)
INSTALL JAR 'sample2.jar' ;

Defining the Java stored procedure

CREATE PROCEDURE jproc1(IN fromdate DATE, IN todate DATE)
  LANGUAGE JAVA
  EXTERNAL NAME 'sample2.jar:sample2.jproc1(java.sql.Date, java.sql.Date)
                 returns void'
  PARAMETER STYLE JAVA
end_proc;

Executing the Java stored procedure
CALL jproc1(IN ?,IN ?)

(3) Sample 3

This example compresses and decompresses BLOB data using gzip and ungzip.

External Java function (filename: sample3.java)

/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 Java stored sample 3                              */
/************************************************************************/
import java.util.zip.*;
import java.io.*;
 
public class sample3 {
    private final static int BUFF_SIZE = 4096;
 
    /*==================================================================*/
    /* name = main method for debugging                                 */
    /*==================================================================*/
    public static void main(String[] args) throws IOException {
        // Obtaining input data 
        String sin = args[0];
        byte[] bin = args[0].getBytes();
        System.out.println("input data : " + sin);
 
        // GZIP(BLOB)
        byte[] bwork = gzip(bin);
        System.out.println("gzip(BLOB)  : " +
                           bin.length +  "=>" + bwork.length +
                           "(" + (bwork.length * 100 / bin.length) + "%): " +
                           "");
 
        // GUNZIP(BLOB)
        byte[] bout = gunzip(bwork);
        System.out.println("gunzip(BLOB): " +
                           bwork.length +  "=>" + bout.length +
                           "(" + (bout.length * 100 / bwork.length) + "%): " +
                           new String(bout));
 
        return;
    }
 
    /*==================================================================*/
    /* name = sample_3 method [gzip(BLOB)]                              */
    /*==================================================================*/
    public static byte[] gzip(byte indata[]) {
        // Creating a stream for output of compressed data 
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
 
        // Output of compressed data 
        try {
            GZIPOutputStream zos = new GZIPOutputStream(baos);
            zos.write(indata, 0, indata.length);
            zos.close();
            baos.close();
        } catch (IOException ex) {
            System.out.println("gzip(BLOB): IOException: " + ex);
        ex.printStackTrace();
        }
 
        // Creating a byte array after compressing return value
        byte[] outdata = baos.toByteArray();
        return outdata;
    }
 
    /*==================================================================*/
    /* name = sample_3 method [gunzip(BLOB)]                            */
    /*==================================================================*/
    public static byte[] gunzip(byte[] indata) {
        int rlen;                    // Actual input/output length
        byte[] buff = new byte[BUFF_SIZE]; // Input/output buffer 
 
        // Creating a stream for input of compressed data
        ByteArrayInputStream bais = new ByteArrayInputStream(indata);
        // Creating a stream for output of decompressed data 
        ByteArrayOutputStream baos = new ByteArrayOutputStream();
 
        // Input of compressed data and output of decompressed data 
        try {
            GZIPInputStream zis = new GZIPInputStream(bais);
 
            while ((rlen = zis.read(buff, 0, buff.length)) >= 0) {
              baos.write(buff, 0, rlen);
            }
 
            zis.close();
            bais.close();
            baos.close();
        } catch (IOException ex) {
            System.out.println("gunzip(BLOB): IOException: " + ex);
        ex.printStackTrace();
        }
 
        // Creating a byte array after decompressing return value
        byte[] outdata = baos.toByteArray();
 
        return outdata;
    }
}

The following is an example of defining and executing a Java stored procedure using the external Java function shown above:

Compiling the Java file (for HP-UX)
javac sample3.java
Creating the JAR file (for HP-UX)
jar -cvf sample3.jar sample3.class
Registering the JAR file in HiRDB (using INSTALL JAR SQL statement)
INSTALL JAR 'sample3.jar' ;

Defining the Java stored procedure

CREATE FUNCTION gzip(indata BLOB(1M)) RETURNS BLOB(1M)
  LANGUAGE JAVA
  EXTERNAL NAME 'sample3.jar:sample3.gzip(byte[]) returns byte[]'
  PARAMETER STYLE JAVA
end_proc;
CREATE FUNCTION gunzip(indata BLOB(1M)) RETURNS BLOB(1M)
  LANGUAGE JAVA
  EXTERNAL NAME 'sample3.jar:sample3.gunzip(byte[]) returns byte[]'
  PARAMETER STYLE JAVA
end_proc;

Executing the Java stored procedure

INSERT INTO t1 values(10, ?, gzip(? AS BLOB(1M)))
     :
SELECT c1, c2, gunzip(c3), length(c2), length(c3) from t1

(4) Sample 4

This example uses a dynamic result set to return the result of retrieving two tables.

External Java procedure (filename: sample4rs.java)

/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 Java stored Result Set connection job             */
/************************************************************************/
import java.lang.*;
import java.math.*;
import java.sql.*;
 
/************************************************************************/
/* name = Result Set connection class (procedure side)                  */
/************************************************************************/
public class sample4rs {
    /*==================================================================*/
    /* name = main method for debugging                                 */
    /*==================================================================*/
    public static void main(String args[]) throws SQLException {
        java.lang.Integer p1 = new Integer(10);
    int[]   cr_cnt = null;
    java.sql.ResultSet[] rs1 = null;
    java.sql.ResultSet[] rs2 = null;
 
    try {
        // Registering Driver class 
        Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver");
    } catch (ClassNotFoundException ex) {
        System.out.println("\n***** ClassNotFoundException caught *****\n");
        ex.printStackTrace();
        System.out.println ("");
        System.out.println("*******************************\n");
        return;
    }
 
    rs_proc(p1, cr_cnt, rs1, rs2);
    }
 
    /*==================================================================*/
    /* name = Result Set connection method                              */
    /*==================================================================*/
    public static void rs_proc(java.lang.Integer p1,int icnt_cr[],
                    java.sql.ResultSet[] rs1,
                    java.sql.ResultSet[] rs2) throws SQLException {
        java.lang.Integer x_goods_no;
        java.math.BigDecimal x_quantity_1, x_total_quantity;
 
        try {
            // Creating a connection object (CONNECT not issued within 
                                             Java procedure)
            java.sql.Connection con =
                DriverManager.getConnection("jdbc:hitachi:hirdb");
 
            con.setAutoCommit(false);  // Suppressing automatic commit
 
            // SELECT (stmt1) preprocessing 
            java.sql.PreparedStatement stmt1 =
                con.prepareStatement("SELECT c1, c2 FROM rs_t1 WHERE c1 > ?");
 
            // Specifying SELECT (stmt1) input parameter
            stmt1.setInt(1, p1.intValue());
 
            // SELECT (stmt2) preprocessing 
            java.sql.PreparedStatement stmt2 =
                con.prepareStatement("SELECT c1, c2 FROM rs_t2 WHERE c1 > 10");
 
            // Executing SELECT (stmt1) 
            rs1[0] = stmt1.executeQuery();
 
            // Executing SELECT (stmt2) 
            rs2[0] = stmt2.executeQuery();
 
            // Number of dynamic result sets 
            icnt_cr[0] = 2;
 
            // Executing SELECT (stmt2) (retrieving only one row)
            rs2[0].next();
 
         } catch (SQLException ex) { // SQL error handling procedure 
            SQLException fast_ex = ex;
 
            System.out.println("\n***** SQLException caught *****\n");
            while (ex != null) {
                System.out.println ("SQLState: " + ex.getSQLState ());
                System.out.println ("Message:  " + ex.getMessage ());
                System.out.println ("Vendor:   " + ex.getErrorCode ());
                ex.printStackTrace();
                ex = ex.getNextException ();
                System.out.println ("");
            }
            System.out.println("*******************************\n");
            throw fast_ex;
        }
 
        return;
    }
}

UAP (sample4ap.java)

/* ALL RIGHTS RESERVED,COPYRIGHT (C)2000,HITACHI,LTD. */
/* LICENSED MATERIAL OF HITACHI,LTD. */
/************************************************************************/
/* name = HiRDB 06-00 Java stored Result Set connection object          */
/************************************************************************/
import java.lang.*;
import java.math.*;
import java.sql.*;
 
/************************************************************************/
/* name = Result Set connection class (CALL side)                       */
/************************************************************************/
public class sample4ap {
    /*==================================================================*/
    /* name = main method for debugging                                 */
    /*==================================================================*/
    public static void main(String args[]) throws SQLException {
        try {
 
        // Registering Driver class 
                Class.forName("JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver");
        } catch (ClassNotFoundException ex) {
            System.out.println("\n***** ClassNotFoundException caught *****\n");
            ex.printStackTrace();
            System.out.println ("");
            System.out.println("*******************************\n");
            return;
        }
        rs_call();
    }
    /*==================================================================*/
    /* name = Result Set connection method                              */
    /*==================================================================*/
    public static void rs_call() throws SQLException {
        java.lang.Integer xc1;
        java.lang.String xc2;
        int cr_cnt[] = new int[1];
 
        try {
            // Creating a connection object (CONNECT not issued within 
                                             Java procedure) 
            java.sql.Connection con =
               DriverManager.getConnection
                             ("jdbc:hitachi:hirdb", "\"USER1\""
                                                      , "\"PASS1\"");
 
            con.setAutoCommit(false);  // Suppressing automatic commit 
 
            // CALL (stmt1) preprocessing 
            java.sql.CallableStatement stmt1 =
                con.prepareCall("{CALL rs_proc(?,?)}");
 
            // Specifying CALL (stmt1) input parameters
            stmt1.setInt(1, 10);
            stmt1.registerOutParameter(2, java.sql.Types.INTEGER);
 
            // Executing CALL (stmt1) 
            stmt1.execute();
 
            // Obtaining CALL (stmt1) output parameter
            cr_cnt[0] = stmt1.getInt(2);
 
            System.out.println("cr_cnt=" + cr_cnt[0] + "\n");
 
            // Obtaining dynamic result set 
            java.sql.ResultSet rs = stmt1.getResultSet();
 
            while (rs.next()) {
                // Obtaining SELECT (stmt1) retrieval result 
                xc1 = (Integer)rs.getObject("c1");
                xc2 = (String)rs.getObject("c2");
                System.out.println("xc1=" + xc1 + ",xc2=" + xc2 + "\n");
            }
 
            // Closing the cursor
            rs.close();
 
            if (stmt1.getMoreResults()) {
                rs = stmt1.getResultSet();
                while (rs.next()) {
                    // Obtaining SELECT (stmt1) retrieval result
                    xc1 = (Integer)rs.getObject("c1");
                    xc2 = (String)rs.getObject("c2");
                    System.out.println("xc1=" + xc1 + ",xc2=" + xc2 + "\n");
                }
            }
 
            // Closing the cursor
            rs.close();
 
            // Releasing each statement object 
            stmt1.close();
 
            // Disconnecting 
            con.close();
 
        } catch (SQLException ex) { // SQL error handling procedure
            SQLException fast_ex = ex;
 
            System.out.println("\n***** SQLException caught *****\n");
            while (ex != null) {
                System.out.println ("SQLState: " + ex.getSQLState ());
                System.out.println ("Message:  " + ex.getMessage ());
                System.out.println ("Vendor:   " + ex.getErrorCode ());
                ex.printStackTrace();
                ex = ex.getNextException ();
                System.out.println ("");
            }
            System.out.println("*******************************\n");
 
            throw fast_ex;
        }
 
        return;
    }
}

Defining the Java stored procedure

CREATE PROCEDURE rs_proc(IN p1 INT,OUT cr_cnt INT)
  DYNAMIC RESULT SETS 2
  LANGUAGE JAVA
  EXTERNAL NAME 'sample4.jar:sample4rs.rs_proc(java.lang.Integer, int[]
                                                                , java.sql.
ResultSet[], java.sql.ResultSet[]) returns void'
  PARAMETER STYLE JAVA
end_proc;

9.3.2 Sample external Java stored routines provided with HiRDB

(1) Sample 1

(2) Sample 2

(3) Sample 3

(4) Sample 4