Re: on the fly zip compression

From: <stephen.howard_at_us.pwcglobal.com>
Date: 16 Mar 2005 08:55:39 -0800
Message-ID: <1110992139.314031.89750@o13g2000cwo.googlegroups.com>

We do this for EDI BLOB's stored in our database. Below is the Java source code used We run it from a job, but you can adapt it.

/******************************************************************************************************

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "ZIPMESSAGES" AS

  import java.sql.*;
  import java.util.*;
  import java.io.*;
  import java.util.zip.*;

  public class zipMessages {
    public static void archive(String days) throws java.io.IOException,

                                                   SQLException,

java.lang.ClassNotFoundException{
      Class.forName("oracle.jdbc.driver.OracleDriver");
      //Connection conn = new
oracle.jdbc.driver.OracleDriver().defaultConnection();
      Connection conn =

DriverManager.getConnection("jdbc:oracle:thin:@hostname:port:sid","username","password");

      Blob blob ;
      InputStream strm = null ;
      int     bbuffSize = 100 ;
      byte[]  bbuff = new byte[bbuffSize] ;
      int     bytes_read = 0 ;
      ResultSet rset = null ;
      Statement stmt = conn.createStatement() ;

      try {

        CallableStatement cstmt = null;

          rset = stmt.executeQuery ("select docid, " +
                                           "datetime, " +
                                           "message " +


                                      "from rcvarchive o " +

                                      "where TRUNC(datetime) =

TRUNC(sysdate - " + Integer.parseInt(days) + ")");

        while (rset.next()) {
          blob = (Blob)rset.getObject(3);
          strm = blob.getBinaryStream();
          String initstr="begin " +
                           "insert into message_archive (docid," +
                                                        "datetime, " +
                                                        "message) " +
                             "values (?," +
                                     "?," +
                                     "EMPTY_BLOB());" +
                         "end;";
  	    cstmt = conn.prepareCall(initstr);

  	    cstmt.setString( 1, rset.getString("docid"));
  	    cstmt.setDate( 2, rset.getDate("datetime"));
  	    cstmt.executeUpdate();
  	    cstmt.close();

          initstr="update message_archive set message = (?) where docid
= ?";
          ByteArrayOutputStream outputStream = new
ByteArrayOutputStream(100);
          ZipOutputStream zipOutputStream = new

ZipOutputStream(outputStream);

          ZipEntry zipEntry = new ZipEntry(rset.getString("docid"));
          byte[] rawData = new byte[100];
          try {
            zipOutputStream.putNextEntry(zipEntry);
            bytes_read = strm.read(bbuff, 0, bbuffSize) ;
            while ( bytes_read > 0 ) {
              zipOutputStream.write(bbuff,0,bytes_read);
              bytes_read = strm.read(bbuff, 0, bbuffSize);
            }
            zipOutputStream.closeEntry();
            zipOutputStream.close();
          }
          catch (IOException ioException) {
            System.out.println(ioException.toString());
          }

          byte[] zippedRawData = outputStream.toByteArray();

          try {
            System.out.println("Processing " + rset.getString("docid")

+ "...zippedRawData.length = " + zippedRawData.length);

            PreparedStatement pstmt=conn.prepareStatement(initstr);
            pstmt.setBinaryStream(1, new
ByteArrayInputStream(zippedRawData), zippedRawData.length);
  	    pstmt.setString( 2, rset.getString("docid"));

            if (pstmt.executeUpdate() != 0) {
              System.out.println("Successfully updated " +
rset.getString("docid"));
            }
            else {
              System.out.println("ERROR ON " +
rset.getString("docid"));
            }

            conn.commit();
            pstmt.close();
          }
          catch (Exception sqlException) {
            System.out.println(sqlException);
          }
  	    conn.commit();
        }
      }
      finally {
        if (conn != null) conn.close();
        if (stmt != null) stmt.close();
        if (rset != null) rset.close();
        if (strm != null) strm.close();
      }

    }
  }
/

CREATE OR REPLACE PACKAGE utils IS
  PROCEDURE zip_messages (p_days IN VARCHAR2); END exel_mercator_utils;
/

******************************************************************************************************/

...and here is a JSP we use in Apache to extract the compressed blob back out and push to the browser as zip file...

<%@ page import="java.sql.*, java.util.zip.*" %> <%
try {

    //must use OCI/thick driver for this type of stream     Connection conn =
DriverManager.getConnection("jdbc:oracle:oci8:@tns_alias","username","password");

    ResultSet rset = null ;
    Statement stmt = null ;

    Blob blob ;
    // declarations for SQL stuff
    // declarations for reading LOB
    InputStream strm = null ;
    int bbuffSize = 100 ;
    byte[] bbuff = new byte[bbuffSize] ;     int bytes_read = 0 ;
    ServletOutputStream outstrm = null ;

    try {

      stmt = conn.createStatement() ;

      rset = stmt.executeQuery ("select message from message_archive where docid='" + request.getParameter("docid") + "'") ;

      if (rset.next()) {
        response.setContentType("application/zip") ;
        response.setHeader("Content-Disposition",
"attachment;filename=" + request.getParameter("docid") + ".zip");
        blob = (Blob)rset.getObject(1) ;
        strm = blob.getBinaryStream() ;
        outstrm = response.getOutputStream() ;
        bytes_read = strm.read(bbuff, 0, bbuffSize) ;

        while ( bytes_read > 0 ) {
          outstrm.write(bbuff, 0, bytes_read) ;
          bytes_read = strm.read(bbuff, 0, bbuffSize);
        }
      }
      else { %>
        <script>alert('No documents matched the criteria.')</script> <%
      }

    }
    catch (SQLException e) {

      if(e.toString().indexOf("Exhausted Resultset") > 0) %>
        <script>alert('No documents matched the criteria.')</script> <%
      else
      %> <b>ERROR : <%= e %></b>
      <%

    }
    finally {

      if (conn != null) conn.close();
      if (stmt != null) stmt.close();
      if (rset != null) rset.close();
      if (strm != null) strm.close();

    }
}
catch (SQLException e) {
  if(e.toString().indexOf("Exhausted Resultset") > 0) %>     <script>alert('NO documents matched the criteria.')</script> <%   else
  %> <b>ERROR : <%= e %></b>
  <%
}
%>

---

Regards,

Steve Received on Wed Mar 16 2005 - 10:55:39 CST