CODEWITHSUNDEEP
javaoracle-databaseprepared-statementblobsql-insert
olkoza
olkoza

Reputation: 725

NullPointerException when executing PreparedStatement on Oracle DB to insert Blob

does anyone have an idea what i am doing wrong here? I am trying to insert a blob (that contains a pdf in case that matters) into an oracle db together with some additional information. I am using a PreparedStatement

Code:

public void saveReportErgebnis(int reportId, Date erzeugung, int archiv,
        Blob pdf, String kommentar) throws Exception {

    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    byte[] tmp = new byte[(int) pdf.length()];
    if(kommentar == null){
        kommentar = "";
    }

    SimpleDateFormat erstellungSdf = new SimpleDateFormat(
            "yyyy-MM-dd HH:mm:ss");

    try {
    pdf.getBinaryStream().read(tmp);

    oracle.sql.BLOB oracleBlob = new oracle.sql.BLOB((OracleConnection) getConnection(), tmp);
    PreparedStatement prepStmt = getRTTDBHandler()
            .createPreparedStatement(
                    "INSERT INTO reportergebnis(report_id, erzeugung, archiv, pdf, kommentar) VALUES (?,?,?,?,?)");
    System.out.println("debug 2 oracle");
        prepStmt.setInt(1, reportId);
        prepStmt.setString(2, formatDateString(erstellungSdf.format(erzeugung)));
        prepStmt.setInt(3, archiv);
        prepStmt.setBlob(4, oracleBlob);
        prepStmt.setString(5, kommentar);
        prepStmt.execute();
    } catch (Exception e) {
        e.printStackTrace();
        loghandler.error(e.toString());
        throw e;
    }

}

(formatCode is putting the Date in the right form, e.g. for a return:
date to_date('2013-04-15 09:34:38','yyyy-mm-dd hh24:mi:ss')

When the PrepStatement is executed I get the following Exception:

{java.lang.ArrayIndexOutOfBoundsException
        at java.lang.System.arraycopy(Native Method)
        at oracle.jdbc.driver.DatumBinder.bind(OraclePreparedStatement.java:18279)
        at oracle.jdbc.driver.OraclePreparedStatement.setupBindBuffers(OraclePreparedStatement.java:3137)
        at oracle.jdbc.driver.OraclePreparedStatement.processCompletedBindRow(OraclePreparedStatement.java:2355)
        at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3579)
        at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3685)
        at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1088)
        at com.nundp.mc.modules.regressionsTest.db.OracleRTTDBHandler.saveReportErgebnis(OracleRTTDBHandler.java:963)
        at com.nundp.mc.modules.regressionsTest.report.ReportController.executeReport(ReportController.java:237)
        at org.apache.jsp.jsp.modules.Testszenario.ReportHandler_jsp._jspService(ReportHandler_jsp.java:156)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.ApplicationDispatcher.invoke(ApplicationDispatcher.java:630)
        at org.apache.catalina.core.ApplicationDispatcher.doInclude(ApplicationDispatcher.java:535)
        at org.apache.catalina.core.ApplicationDispatcher.include(ApplicationDispatcher.java:472)
        at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:968)
        at org.apache.jsp.jsp.McFrame_jsp._jspService(McFrame_jsp.java:284)
        at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
        at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
        at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
        at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
        at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
        at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
        at java.lang.Thread.run(Thread.java:619)}

I am using Oracle Database 10g Release 10.2.0.3.0. Help please!

Upvotes: 6

Views: 2776

Answers (5)

olkoza
olkoza

Reputation: 725

For future searchers, this is how I solved the blob problem:

public void saveReportErgebnis(int reportId, Date erzeugung, int archiv,
        Blob pdf, String kommentar) throws Exception {

    byte[] tmp = new byte[(int) pdf.length()];
    if (kommentar == null) {
        kommentar = "";
    }

    try {
        pdf.getBinaryStream().read(tmp);
        PreparedStatement prepStmt = getRTTDBHandler()
                .createPreparedStatement(
                        "INSERT INTO reportergebnis(report_id, erzeugung, archiv, pdf, kommentar) VALUES (?,?,?,?,?)");
        prepStmt.setInt(1, reportId);
        Timestamp ts = new Timestamp(erzeugung.getTime());
        prepStmt.setTimestamp(2, ts);
        prepStmt.setInt(3, archiv);
        prepStmt.setBytes(4, tmp);
        prepStmt.setString(5, "'" + kommentar + "'");
        prepStmt.execute();
    } catch (Exception e) {
        e.printStackTrace();
        loghandler.error(e.toString());
        throw e;
    }
}

Thank you very much everybody for the help.

Upvotes: 0

rusty
rusty

Reputation: 671

In my app I used a File instead of a Blob for the method input and have done the BLOB insert this way:

public void insertBlob(String filedesc, File file) {
    Connection con = DriverManager.getConnection(url, username, password);

    InputStream input = new FileInputStream(file);

    PreparedStatement pstmt = con.prepareStatement(
        "insert into schema.table values(?,?)");
    pstmt.setString(1, filedesc);
    pstmt.setBinaryStream(2, input);

    pstmt.execute();
}

Maybe you will need some try-catch, but I hope this will help You.

Upvotes: 1

cafebabe1991
cafebabe1991

Reputation: 5186

I think you must do some sanitary checks before using setBlob()

  1. check the number of parameters in your db table
  2. check the position for the blob.

However ,I would rather recommend you to store only the URl's in the database rather than storing the binary data of the file.

Upvotes: 0

Joop Eggen
Joop Eggen

Reputation: 109613

One remark: prepStmt.setDate(2, erzeugung); is simpler.

I would have expected a simple:

InputStream blobIS = pdf.getBinaryStream();
prepStmt.setBlob(4, blobIS, pdf.length());

... pdf.free();

Upvotes: 0

mahieus
mahieus

Reputation: 580

Try using the *createTemporary(yourConnection, false, oracle.sql.BLOB.DURATION_CALL)* function on the oracle.sql.BLOB to create your instance. After that just use the setBytes(...) to set its content.

Upvotes: 0

Related Questions