Inserting Blob throws Exception

[Joe San]

Guys,

I'm trying to insert a Blob datatype to a table and I run into the following exception:

INFO - The Root Cuase of the Exception is when inserting is ********** null
INFO - The Exception Message is ********** ORA-01460: Nicht implementierte oder nicht sinnvolle Umwandlung gefordert

java.sql.SQLException: ORA-01460: Nicht implementierte oder nicht sinnvolle Umwandlung gefordert

        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:169)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:543)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1405)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:822)
        at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:1610)
        at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:1535)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2053)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:398)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:449)
        at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:169)
        at de.dpwn.fcap.common.Test.insertBlob(Test.java:133)
        at de.dpwn.fcap.web.content.action.TestAction.execute(TestAction.java:42)
        at org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
        at org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
        at org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
        at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
        at org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:305)
        at org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:191)
        at org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
        at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
        at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:647)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:172)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:174)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:873)
        at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
        at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
        at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
        at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:689)
        at java.lang.Thread.run(Thread.java:619)

My method that does the insert,

    public String insertBlob(String id, String text) throws Exception {
        String resultText = null;
        final StringBuffer sb = new StringBuffer(); 
        log.info("The String text that we get from the User Interface is ********** " + text);
        final String predStmtString = "INSERT INTO TEMP_FORM T (T.TEMP_FORM_ID, T.CREATED_BY, T.STATUS,
                                                      T.CANVAS_CODE, T.JS_SNIPPET_CODE, T.GLOBAL_VARIABLES )" +
                                                      " VALUES( ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        final byte[] strBytes = text.getBytes();
        System.out.println();
        System.out.println();
        log.info("The Bytes that got converted is ********** " + strBytes);
        final ByteArrayInputStream binaryInputStr = new ByteArrayInputStream(strBytes);
        try {
            conn = DBConnectionManager.getInstance().getDBConnection(DBUtils.TEST_JNDI_DATASOURCE);
            if (text != null) {
                PreparedStatement ps = conn.prepareStatement(predStmtString);
                ps.setString(1, id);
                ps.setString(2, "myUserId");
                ps.setInt(3, 1);
                ps.setBinaryStream(4, binaryInputStr, binaryInputStr.available());
                ps.setBinaryStream(5, binaryInputStr, binaryInputStr.available());
                ps.setBinaryStream(6, binaryInputStr, binaryInputStr.available());
                ps.execute();
                ps.close();
            }
            conn.commit();
            conn.close();
        }
        catch (Exception e) {
            log.info("The Root Cuase of the Exception is when inserting is ********** " + e.getCause());
            log.info("The Exception Message is ********** " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
        try {
            conn = DBConnectionManager.getInstance().getDBConnection(DBUtils.FCAP_JNDI_DATASOURCE);
            if (text != null) {
                PreparedStatement ps = conn.prepareStatement("SELECT T.CANVAS_CODE FROM TEMP_FORM T where T.TEMP_FORM_ID = ?");
                ps.setString(1, id);
                ps.execute();
                ResultSet rs = ps.executeQuery();
                while (rs != null) {
                    Blob blob = rs.getBlob(1);
                    InputStream is = blob.getBinaryStream();
                    int i = 0;
                    while ((i = is.read()) != -1) {
                        sb.append(is);
                    }
                }
                ps.close();
            }
            conn.close();
        }
        catch (Exception e) {
            log.info("The Root Cuase of the Exception when fetching is ********** " + e.getCause());
            log.info("The Exception Message is ********** " + e.getMessage());
            e.printStackTrace();
            throw e;
        }
        return sb.toString();
    }

I use a Oracle 10.2.0 and JDK 1.4. Any idea as to what is going on?

[Fatih Keles]

Hi Jothi,

Blob columns need to initialize blob place holders, i suppose.
So do you mind first inserting "empty_blob()" and then updating it with blob content?

Something like this.

Regards,

Fatih.

[Joe San]

The problem is not with emply_blob but rather the size limitation of the Blob datatype in Oracle when using a thin JDBC driver.

[Fatih Keles]

How did you figure it out, and how did you solve it? Curious to learn

Regards,

Fatih.

[Joe San]

Actually, I get my connection object from the Tomcat JNDI where my web application is deployed. There is a connection property setting that I could do in order to have my CLOB inserted (I changed my BLOD datatype to CLOB) and this is the "SetBigStringTryClob", "true". But I was unable to set this in the Resource configuration in the context.xml of my Tomcat. So the Connection that I get from Tomcat seems not to fit for this situation. So I had to create another explicit Connection through my application and set this property explicitly there and do the insert and retrieval which now seems to work fine.

The following is what I have in the context.xml of my Tomcat

        <Resource name="jdbc/myDB" auth="Container" factory="org.apache.tomcat.dbcp.dbcp.BasicDataSourceFactory" 
type="javax.sql.DataSource" 
        	  driverClassName="oracle.jdbc.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:myDBase" username="my_owner" password="my_owner" 
                  connectionProperties="SetBigStringTryClob=true" removeAbandoned="true" removeAbandonedTimeout="30" 
maxWait="10000" maxActive="100" maxIdle="30" /> 

I really cannot understand why the connectionProperties is not respected by Tomcat. If I run using this setting I still keep getting the below exception... (sorry it is in German)

INFO - The Exception Message is ********** Datengrösee grösser als max. Grösse für diesen Typ: 27152
java.sql.SQLException: Datengrösee grösser als max. Grösse für diesen Typ: 27152

But however when having specifically creating a Connection object through my application rather than using Tomcat JNDI look up, I was able to get it done.

[Joe San]

Any reason as to why Tomcat is not able to do this?

[Joe San]

Somehow managed to get it work using Tomcat JNDI. Just did the following...

Replaced the classes12.jar from the tomcat/common/lib directory to a ojdbc14.jar and it worked!