NullPointerException when executing PreparedStatement in Oracle DB to insert Blob

Question

Anyone have an idea what I'm doing wrong here? I am trying to embed a blob (containing pdf in case this is important) in oracle db along with some additional information. I am using PreparedStatement

the 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 puts the date in the desired form, for example, to return:
date to_date ('2013-04-15 09:34:38', 'yyyy-mm-dd hh24: mi: ss')

When 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 me please!

Answer 1

In my application, I used File instead of Blob to enter the method and nested the BLOB 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(); } 

You might need a try-catch , but I hope this helps you.

Answer 2

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

Answer 3

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

I would expect a simple one:

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

Answer 4

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

• check the number of parameters in the db table
• check the position for blob.

However, I would rather recommend that you only store URl in the database, and not store binary file data.

Answer 5

For future search engines, I solved the problem with blob:

 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 all for your help.