Upload a file in a BLOB Oracle variable
Hello all,
i am developing a web application to upload a binary file in an Oracle database (9i) in a BLOB variable.
the input is stored in a FILE object wich is temporary stored in a folder (C:\\APP). after the upload in the BLOB the file should be deleted. I get the following error
java.lang.NullPointerException
at com.merck.cdm.BlobOracle.insertBLOB(BlobOracle.java:98)
when I try to get the file for the FileInputStream. It seems I am not able to acces the FILE object once defined.
any help is welcome
thanks
claudio
this is the code:
******************************************************
package com.merck.cdm;
import java.sql.*;
import java.io.*;
import java.sql.PreparedStatement;
import java.util.*;
import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;
import javax.servlet.http.HttpServletRequest;
import oracle.jdbc.driver.*;
import oracle.sql.BLOB;
/**
* Insert record in the MEDIA table
*MEDIA (file_name varchar2(256), file_content BLOB);
*/
public class BlobOracle
{
private final static String hostname = "itpo0002.merck.com";
private final static String port = "1521";
private final static String sid = "TIRWEB";
private final static String username = "test_blob";
private final static String password = "password";
private static String fileLocation;
private static Connection connection;
public BlobOracle()
{
}
public boolean processAddRequest(HttpServletRequest request, String sAbsolutePath)
throws IOException, SQLException, Exception
{
boolean bParamsOk = true;
boolean bAttachOk = true;
//Acquire request parameters we need .
//5 MB's limit
//the work directory will be "TOMCAT_HOME\bin, and the user can provide another one like C:\workDir
//the file will be deleted as soon as the message will be sent
//m_mpReq = new MultipartRequest(request, ".", 5 * 1024 * 1024);
//m_mpReq = new MultipartRequest(request, "/opt/www/corp/ats-crf/java/ats-crf/attachment/", 5 * 1024 * 1024);
//String sWorkDir = File.separator + sAbsolutePath + "attachment" + File.separator;
MultipartRequest mpRequest = new MultipartRequest(request, "C:\\app", 8 * 1024 * 1024);
// upload file in a temp dir
//il form ?di tipo enctype="multipart/form-data" e quindi il metodo getParameter di java.lang.string non funziona
//bisogna quindi usare l'analogo metodo di MultipartRequest
String fileName = mpRequest.getFilesystemName("upload_file");
System.out.println("fileName is " + fileName);
File file; //creo un oggetto di tipo "file" dal file nella cartella temporanea
file = mpRequest.getFile("C:\\app");
//String fileName = file.getName();
//Connection;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@"+hostname+ ":"+ port +":"+ sid , username , password);
con.setAutoCommit(false);// we must control the commit SE METTO TRUE FUNZIONA MA DA UN ALTRO ERRORE;
insertBLOB(fileName, file, con);//passo il nome del file e l'oggetto file
return (bAttachOk && bParamsOk);
}
public void insertBLOB(String strFileName, File AttachedFile, Connection connection) throws SQLException, Exception
{
long ts1 = System.currentTimeMillis();
ResultSet rs = null;
Statement stmt = null;
//Create a statement.
StringBuffer sbSql = new StringBuffer();
sbSql.append("INSERT INTO media\n");
sbSql.append("(file_name, file_content)\n");
sbSql.append("values ('" + strFileName + "', empty_blob())");
connection.setAutoCommit(false);
stmt = connection.createStatement();
stmt.execute(sbSql.toString());
System.out.println(sbSql);
//Take back the record for update (we will insert the blob)
StringBuffer sbSqlBody = new StringBuffer();
sbSqlBody.append("SELECT file_content FROM media\n");
sbSqlBody.append("WHERE file_name = '" + strFileName + "' for update");
//Execute the query, and we must have one record so take it
rs = stmt.executeQuery(sbSqlBody.toString());
rs.next();
System.out.println(sbSqlBody);
//Use the OracleDriver resultset, we take the blob locator
BLOB blob = ((OracleResultSet)(rs)).getBLOB("file_content");
//copyFileIntoBlob(AttachedFile, blob);
System.out.println("Start copyFileIntoBlob");
System.out.println("attached length = " + AttachedFile.length());<-GOT THE ERROR HERE
FileInputStream instream = new FileInputStream(AttachedFile);
System.out.println("before getBinaryOutputStream");
OutputStream outstream = blob.getBinaryOutputStream();
System.out.println("after getBinaryOutputStream");
//Call getChunkSize() to determine the ideal chunk size to write to the BLOB,
//then create the buffer byte array.
int chunk = blob.getChunkSize();
byte[] buffer = new byte[chunk];
int length = -1;
while ((length = instream.read(buffer)) != -1)
outstream.write(buffer, 0, length);
instream.close();
outstream.close();
System.out.println("End copyFileIntoBlob");
long ts2 = System.currentTimeMillis();
connection.commit();
connection.close();
//cancella il file
AttachedFile.delete();
//cancella il file
System.out.println("\n"+ (ts2 - ts1) +" ms" );
}
******************************************************
