This section presents an example of a stored procedure that uses the SELECT statement to retrieve BLOB data stored in the pics table, zips the data (compresses the data in the ZIP format), then returns it to the calling program.

• Definition of the Java stored procedure
  

  CREATE PROCEDURE get_pic(IN pic_num INTEGER , OUT pic_data BLOB(1M) ....1 LANGUAGE JAVA ................................2 EXTERNAL NAME 'mypack.jar:JStrPics.getZippedPic (int, byte[][])' ....3 PARAMETER STYLE JAVA; .............................4

Explanation

1. Defines the procedure name and parameters.
2. Specifies LANGUAGE.
3. Associates with the Java method.
4. Specifies PARAMETER STYLE.

• Body of the Java stored procedure
  

  import java.sql.*; import java.io.*; import java.util.zip.*; public class JStrPics{ .........................1 public static void getZippedPic(int jpic_num , byte[][] jpic_data) ..2 throws SQLException, IOException{ ..............3 Connection con = DriverManager.getConnection( ...4 "jdbc:hitachi:PrdbDrive","USER1","PASS1"); .....4 PreparedStatement pstmt = con.prepareStatement ..5 ("select p_name,p_data from pics where p_num = ?"); ....5 pstmt.setInt(1, jpic_num); .....................5 ResultSet rs = pstmt.executeQuery(); .........6 String name; ...................................7 byte[] srcPic; .................................7 while(rs.next()){ name = rs.getString("p_name"); ...............8 srcPic = rs.getBytes("p_data"); ..............9 } ByteArrayOutputStream baos = new ByteArrayOutputStream(); .......10 ZipOutputStream zos = new ZipOutputStream(baos); ..........10 ByteArrayInputStream bais = new ByteArrayInputStream(srcPic); ...10 ZipEntry ze = new ZipEntry(name); ..............10 zos.putNextEntry(ze); ..........................10 int len = 0; ...................................10 byte[] buff = new byte[1024]; ..................10 while((len = bais.read(buff)) != -1){ ..........10 zos.write(buff, 0, len); .......................10 } .............................................10 zos.closeEntry(); ..............................11 bais.close(); ..................................11 zos.close(); ...................................11 jpic_data[0] = baos.toByteArray(); .............12 baos.close(); ..................................12 return; ........................................13 } }

Explanation

1. Defines the class name.
2. Defines the method name and parameter name.
3. Defines an action to be taken in the event of an exception.
4. Obtains the Connection object.
5. Preprocesses the SELECT statement.
6. Executes the SELECT statement and obtains the result set.
7. Declares variables.
8. Obtains the value of the p_name column from the result set.
9. Obtains the value of the p_data column from the result set.
10. Compresses the data in the srcPic array in the ZIP format and stores it in the zos stream.
11. Closes the input and output streams.
12. Specifies the byte column of the baos stream in the method's OUT parameter.
13. End of method execution.

• Execution of the Java stored procedure
  

  import java.sql.*; import java.io.* ; public class Caller{ ................................1 public static void main(String[] args) ............2 throws SQLException, IOException{ ...............3 Connection con = DriverManager.getConnection( ....4 "jdbc:hitachi:PrdbDrive","USER1" ,"PASS1"); ......................4 CallableStatement cstmt = con.prepareCall("{call get_pic(?,?)}"); ...............5 cstmt.setInt(1, 10); ............................5 cstmt.registerOutParameter(2, java.sql.Types.LONGVARBINARY); ..5 cstmt.executeUpdate(); ..........................6 byte[] getPic = cstmt.getBytes(2); ..............7 ...... } }

Explanation

1. Defines the class name.
2. Defines the method name and parameter name.
3. Defines an action to be taken in the event of an exception.
4. Obtains the Connection object.
5. Preprocesses the CALL statement.
6. Executes the CALL statement.
7. Obtains the OUT parameter of the byte array type.