Nonstop Database, HiRDB Version 9 UAP Development Guide
This sample Java stored procedure obtains a calendar of the specified year and month.
/* 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:
javac sample1.java |
jar -cvf sample1.jar sample1.class |
INSTALL JAR 'sample1.jar' ; |
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; |
CALL calendar(?,?,?) |
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)
/* 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:
javac sample2.java |
jar cvf sample2.jar sample2.class |
INSTALL JAR 'sample2.jar' ; |
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; |
CALL jproc1(IN ?,IN ?) |
This example compresses and decompresses BLOB data using gzip and ungzip.
/* 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:
javac sample3.java |
jar -cvf sample3.jar sample3.class |
INSTALL JAR 'sample3.jar' ; |
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; |
INSERT INTO t1 values(10, ?, gzip(? AS BLOB(1M))) : SELECT c1, c2, gunzip(c3), length(c2), length(c3) from t1 |
This example uses a dynamic result set to return the result of retrieving two tables.
/* 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; } } |
/* 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; } } |
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; |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.