0

I have the following task:

  1. pass a file through SOAP
  2. save the file in a PostgreSQL database

The file should be passed as a stream.

For the first part of the task I used MTOM mechanism. It works fine. For the second part I tried to use setBinaryStream method. To check that the method is truly streaming I restricted Java heap to 128 Mb and passed a ~900Mb file. As a result I got out of memory exception.

So I tried to use Large Objects. This mechanims normally works with no exception. But I do not want to use this mechanism because of extra operaitons needed while object deletion and because of some security considerations.

Here the used code with setBinaryStream:

conn = getConnection();
PreparedStatement ps = null;
DataHandler dh = streamType.getHandler();
InputStream is = null;
try {
    is = dh.getInputStream();
    ps = conn.prepareStatement("INSERT INTO files VALUES (?, ?)");
    ps.setString(1, streamType.getName());
    ps.setBinaryStream(2, is);
    ps.executeUpdate();
} 
...

And this one with Large Objects:

conn = getConnection();
DataHandler dh = streamType.getHandler();
PreparedStatement ps = null;
InputStream is = null;
try {
    is = dh.getInputStream();
    conn.setAutoCommit(false);
    LargeObjectManager lobj = ((org.postgresql.PGConnection) conn).getLargeObjectAPI();
    int oid = lobj.create(LargeObjectManager.READ | LargeObjectManager.WRITE);
    LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);
    byte buf[] = new byte[65536];
    int s, tl = 0;
    while ((s = is.read(buf, 0, 65536)) > 0) {
        obj.write(buf, 0, s);
        tl += s;
    }
    obj.close();
    ps = conn.prepareStatement("INSERT INTO fileslo VALUES (?, ?)");
    ps.setString(1, streamType.getName());
    ps.setInt(2, oid);
    ps.executeUpdate();
    conn.commit();
}
...

May be there is some other way to stream data into the database without Large Objects?

DrDecay
  • 59
  • 1
  • 10
  • 1
    You are expected to [set the stream's length](https://jdbc.postgresql.org/documentation/head/binary-data.html#binary-data-example): *The length parameter to setBinaryStream must be correct. There is no way to indicate that the stream is of unknown length. If you are in this situation, you must read the stream yourself into temporary storage and determine the length. Now with the correct length you may send the data from temporary storage on to the driver.* – pozs Apr 28 '17 at 12:57
  • @pozs: thank you for the reply. But: 1) used setBinaryStream method does not expect the third parameter. In some previous versions, this method was not implemented and the appropriate exception was thrown. But this one works; 2) method implementation that excepts the data length as the third parameter also leads to the out of memory exception – DrDecay May 01 '17 at 12:49
  • there is not much documentation beyond this, but upon examining the JDBC driver code: the two-parameter variant (without the length) reads the stream into memory (up until ~50kB) & writes to a temporary file after (see `org.postgresql.util.StreamWrapper(InputStream)`). The 3 parameter variant just uses the provided stream. Then, the used stream is sent to the server in ~8kB chuncks (see `org.postgresql.core.PGStream.sendStream(InputStream, int)`). -- You might get OOM exception elsewhere, please post every detail (your stream-related codes & special configuration). – pozs May 02 '17 at 08:22
  • *Sidenote*: `PreparedStatement`'s `setBlob(int, InputStream [, long])` methods can handle PostgreSQL's large objects less painfully. At least at the JDBC API level. You would still suffer LOs' other disadvantages though. – pozs May 02 '17 at 08:36

0 Answers0