Hi,

For the portability of my app, I rather will not use the COMPRESS of http://dev.mysql.com/doc/mysql/en/string-functions.html but do this in Java beforehand. In order not to have to load into RAM at least the entire compressed output by e.g. creating a ByteArrayOutputStream with java.util.zip.GZIPOutputStream or java.util.zip.DeflaterOutputStream and then converting it back to an InputStream for the PreparedStatement stmt.setBinaryStream(int pos, InputStream stream, int length), I see two options:

------------ 1) create something like a smart DeflaterInputStream class that compresses as a stream. If anybody has such a thing, I would be interested in trying it.

(I started implementing it myself and got it working at least for single-byte reads (but far from production-readiness - perhaps I should rather extend java.io.PipedInputStream than trying to do my own efficient buffer management and do all the synchronize() blocking etc. --> contribution to org.apache.commons.compress.zip?))

------------- 2) use the "OutputStream java.sql.Blob.setBinaryStream(long pos) throws SQLException" and hope it is nicely working together with the compression and shipping off the input efficiently to the DB without needing much RAM.

I also tried to use that with versions mysql 4.0.23 and mysql-connector-java-3.1.7-bin.jar, but no luck: i.e. it all went through nicely, just on the DB, the blob remained what it was before I tried to upload the compressed file.

String cmd = "select file_blob FROM " + TBL_ATTA + " where atta_id = " + attachmentId + " for update"); log.debug(cmd); int i = 0; ResultSet rs = insAttachmentStmt2.executeQuery(); if (crs == null) { log.debug("row set is null!"); } if (!crs.next()) { log.debug(NO_MORE_SEL_RESULTS + i); } else { do { i++; Blob blob = rs.getBlob("file_blob"); OutputStream blobOS = blob.setBinaryStream(1);

Helpers.compress(attaFileStream,blobOS); blobOS.flush(); blobOS.close(); } while (rs.next()); }

This results in: DEBUG [main] (DBImpl.java:824) - select blob FROM TBL_ATTA where atta_id = 1941 for update DEBUG [main] (Helpers.java:597) - totLen: 43238

When i use the same Helpers.compress method on a ByteArrayOutputStream, convert it to an InputStream and use "void java.sql.PreparedStatement.setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException" as in 1), the 43K data arrives nicely at the DB-server, but obviously, this doesn't scale.

Any thoughts?

Ralf

Securely and spam-free via: https://www.privasphere.com/e.do?email=hau...@acm.org