saving InputStream as clob in oracle using jdbc

Question

I have a csv file uploaded by user, which I need to store as a Clob, in oracle table.

Therefore I have this code:

 MultipartHttpServletRequest mr = (MultipartHttpServletRequest) ctx.getRequest();
    final MultipartFile f = mr.getFile("datafile");
    final InputStream is = f.getInputStream();
     ...
   jdbc.getJdbcOperations().execute(sql, new PreparedStatementCallback<Integer>() {
     public Integer doInPreparedStatement(final PreparedStatement psInsert) throws SQLException,
                            DataAccessException {
     ...
    psInsert.setCharacterStream(1, new InputStreamReader(is));
    psInsert.executeUpdate();
   }
});

Also, I've tried using methods setClob and setAsciiStream of PreparedStatement, as well as I've tried this approach (setting the size of the file), but result is the same -

java.lang.AbstractMethodError
    org.apache.commons.dbcp.DelegatingPreparedStatement.setAsciiStream(DelegatingPreparedStatement.java:338)
    org.apache.commons.dbcp.DelegatingPreparedStatement.setAsciiStream(DelegatingPreparedStatement.java:338)
    org.apache.commons.dbcp.DelegatingPreparedStatement.setAsciiStream(DelegatingPreparedStatement.java:338)

The underlying InputStream is ByteArrayInputStream (if that could make any difference)

PS: The table really has CLOB field:

P_FILE CLOB NOT NULL,

UPD: I haven't actually tried the Oracle implemented methods. It works, the only problem is the oracle driver implements not all of the methods comparing to those which are in the PreparedStatement interface. The class to look at the possible available methods is OraclePreparedStatement ...

have you tried setcharacterstream? http://stackoverflow.com/a/5067581/706695 — HRgiger, Sep 07 '12 at 15:01
@HRgiger Yes I'm using setCharacterStream - this is what in my question actually. Only difference with answer you've linked - I'm using not StringReader, but InputStreamReader.. I could read everything to a string, and then create a StreamReader (it's actually works!) - but it's just a messy solution — javagirl, Sep 07 '12 at 15:10
im not sure if i understand correctly, but clob is for text input. if you want byte input use blob. in case the input stream is actually textual, maybe try reading it into a string and writing that to the db. you can make setString work with clob btw. — yael alfasi, Sep 07 '12 at 22:58

score 1 · Answer 1 · answered Sep 07 '12 at 16:14

1

From the AbstractMethodError javadoc:

Thrown when an application tries to call an abstract method. Normally, this error is caught by the compiler; this error can only occur at run time if the definition of some class has incompatibly changed since the currently executing method was last compiled.

Check to make sure all of your classes are current. I would do a clean and rebuild of your entire project. Also, make sure your compile-time and runtime classpaths are equivalent (as far as library versions, etc.)

answered Sep 07 '12 at 16:14

GriffeyDog

8,186
3
22
34

@GriffeyDoc everything compiles successfully, but when you have a spring application, with a beans loaded through ApplicationContext, and when actual objects like Connection are hidden under bunch of Wrappers - it gets complicated. This case with DelegatingPreparedStatement - is the one – javagirl Sep 07 '12 at 16:58

score 0 · Answer 2 · answered Sep 08 '12 at 14:59

Sormula makes it easy to read/write any type by using a TypeTranslator. See org.sormula.examples.blob package in the project. The code would be similar for CLOB.

public class WidgetTanslator1 implements TypeTranslator<Widget>
{
    public void write(PreparedStatement preparedStatement, int parameterIndex, Widget parameter) throws Exception
    {
        // convert from domain object to bytes
        ByteArrayOutputStream bos = new ByteArrayOutputStream(1000);
        try (ObjectOutputStream oos = new ObjectOutputStream(bos))
        {
            oos.writeObject(parameter);

            // convert bytes to jdbc blob
            preparedStatement.setBlob(parameterIndex, new SerialBlob(bos.toByteArray()));
        }
    }


    public Widget read(ResultSet resultSet, int parameterIndex) throws Exception
    {
        // convert from jdbc blob to bytes to domain object
        Blob blob = resultSet.getBlob(parameterIndex);
        try (ObjectInputStream ois = new ObjectInputStream(new ByteArrayInputStream(blob.getBytes(1, (int)blob.length()))))
        {
            return (Widget)ois.readObject();
        }
    }
}

Annotate CLOB field like this:

@ImplicitType(translator=WidgetTanslator1.class)
Widget widget;

saving InputStream as clob in oracle using jdbc

2 Answers2