You have to insert the record using an empty LOB, then select the record for
update and write the byte stream to the LOB.
Here's an example insert method from my DAO that I used for the same purpose
recently.
Might not be the most efficient or prettiest code out there, but it works
like a champ. We've tested it with image files, Word, Excel, PDF, etc. The
VO being passed in has a FormFile field "DataFile", and a bunch of other
fields for name, type, etc.
I had to turn autocommit off to get this to work. On another project I did
something similar for CLOBS, but the transaction was handled through session
bean, so in that case take those lines out.
public int insert(StatsDocumentsVo vo) throws CapException, SQLException
{
long docId = getNextDocId();
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
BLOB binLgObj = null;
OutputStream out = null;
int rc = -1;
int ii = 1;
try
{
conn = getConnection();
conn.setAutoCommit(false); // to avoid "ORA-01002: fetch out of
sequence"
/*
* Insert the record with a blank holding spot for LOB
*/
ps = conn.prepareStatement(SQL_EXT_INSERT);
ps.setLong(ii++,docId );
ps.setString(ii++,vo.getDescr());
ps.setString(ii++, vo.getMimeType());
ps.setString(ii++,vo.getLastUpdUser());
ps.setString(ii++,vo.getDocumentName());
ps.setString(ii++,vo.getOsoNumber());
ps.setBlob(ii++, oracle.sql.BLOB.empty_lob());
log.debug("Ready to execute the insert statement (empty
BLOB).");
rc = ps.executeUpdate();
/*
* Get the record back from the database
*/
ps.close();
ps = conn.prepareStatement(SQL_GET_BY_PK);
ps.setLong(1, docId);
log.debug("Ready to execute the select statement to get ref to
the empty blob.");
rs = ps.executeQuery();
rs.next();
// get empty blob pointer out of result set
log.debug("Ready to get empty blob out of result set.");
binLgObj = ((OracleResultSet)rs).getBLOB("DATA_FILE");
// open up the output stream to write into blob
out = binLgObj.getBinaryOutputStream();
// write file byte[] into the blob
log.debug("Ready to actually write the file's byte[] array into
the blob.");
out.write( ((FormFile)vo.getDataFile()).getFileData());
log.debug("Closing output stream.");
out.close();
// commit the xaction
log.debug("Commit the xaction to the DB");
conn.commit();
log.debug("Closing result set, prepared statement.");
rs.close();
}
catch (java.io.FileNotFoundException e)
{
e.printStackTrace();
throw new CapException(e.getMessage());
}
catch (java.io.IOException e)
{
e.printStackTrace();
throw new CapException(e.getMessage());
}
finally
{
closeResources(conn,ps);
}
return rc;
}
-----Original Message-----
From: Claire Wall [mailto:[EMAIL PROTECTED]
Sent: Tuesday, December 23, 2003 9:44 AM
To: Struts Users Mailing List
Subject: converting a FormFile to a Blob object
hi,
I have a jsp page which uploads a file to a database. I have no problems
storing the file in an SQLServer or MySQL database by using the
setBinaryStream() method of a PreparedStatement. The file gets upload no
problem.
However, when I try to upload this file to an oracle database (BLOB column)
it does not work. I'm sure that i have to use the setBlob() method of the
PreparedStatement, however, my dilemma is that I cannot find a way to
convert my file from FormFile format to sql Blob format. Does anybody know
how to do this as I've looked everywhere and haven't come up with a
solution.
Any help would be much appreciated
thanks
claire
