Howto: Put RTF files with Pictures into Blob / Clob
Hey gents,
I am having a bit of a problem at the moment. My objective is to store RTF files who, at times, contain pictures into an Oracle 9.2 Database.
So far, I've tried to use CLOBs and BLOBs; and while getting the data into the database doesn't seem to be a problem, I have all the troubles in the world to retrieve the data and put it into a file.
To get the data into the database I use the following code:
conn.getDBConnection();
System.out.println(sqlQuery);
pstmt = conn.dbConnection.prepareStatement(sqlQuery);
pstmt.setString(1, fileName);
pstmt.setString(2, absolutePath);
File file =new File(absolutePath);
InputStream is = (InputStream)new FileInputStream(file);
pstmt.setBinaryStream(3, is, (int)file.length());
pstmt.setString(4,"1");
pstmt.executeUpdate();
and from the quite long hexadecimal values I get when trying to see the result in Aqua Data Studio, it seems to work.
"conn" is the connection;
sqlQuery the query string;
pstmt the PreparedStatement;
fileName and absolutePath are two Strings - storing the file name and the absolute path of the given file.
So far, I've tried to get the data back using:
conn.getDBConnection();
pstmt = conn.dbConnection.prepareStatement(sqlQuery);
rsts = pstmt.executeQuery();
File tmpFile = File.createTempFile("downloaded_",".rtf");
absolutePathTempFile = tmpFile.getAbsolutePath();
java.io.ObjectOutputStream out =new ObjectOutputStream
(new FileOutputStream(absolutePathTempFile));
if ( rsts.next() ){
out.writeObject(rsts.getClob(1));
}
So far to no avail; and - as I want to hastily add - without really understanding what was happening - as I failed to find a tutorial, how to or any hints I could use to put to good use.
Is there somebody who got an idea on what to do, so that I'd be able to sucessfully "upload" and "download" a RTF file (with some pictures) into a Oracle 9.2 (if that makes any difference) database?
Any help would be greatly appreciated
Bob
[2468 byte] By [
bhentgesa] at [2007-11-27 7:41:41]
# 2
Em ;-)
I actually stated that I was using an Oracle 9.2 database in my first post.
The main problem is, that I can now (through some modifications - if anybody is interested leave a note) read the text out of my RTF files, but the image still comes over wobbly out of the CLOBs.
I tried to use BLOBs, but didn't get the data back in the way I wanted/needed it ;-) The biggest annoyance however was that I couldn't upload files smaller then 4096byte. And those files are quite common if you are working with RTF files which, most of the time, do not contain images.
Installed a new oracle jdbc driver a minute ago. Will see if this works ,-)
# 3
This is a bit embarrassing, I have to admit.
Seems like the solution I had at the very beginning is actually one who worked. My perception of what was correct and what wasn't was seriously altered by "Aqua Data Studio" who kept telling me that the BLOB hadn't been transmitted correctly. Or wait, let's rephrase that. I suspected it hadn't been transmitted correctly because all the "Aqua Data Studio" gave me for files smaller then 4096 Bytes was: "Err".
A coworker tried - by chance - "SQLTools" to check for the values, and go figure - they've been there from the beginning...
So anyhow, as I've tried to get some help here - let me at least share the working code samples with you.
Upload RTF to BLOB:
pstmt = conn.dbConnection.prepareStatement(sqlQuery);
pstmt.setString(1, fileName);
pstmt.setString(2, absolutePath);
java.io.File file = new File(absolutePath);
java.io.InputStream is = (InputStream) new FileInputStream(file);
pstmt.setBinaryStream(3, is, (int)file.length());
pstmt.executeUpdate();
pstmt is my PreparedStatement;
fileName and absolutePath are Strings containing what they say they do
the rest should be obvious. ;)
Code to retrieve a Binary File from a BLOB and write it to the disc:
writeBinaryFile(readBlob(rsts, 1), absolutePath);
call it whereever you need it, and have these procedures ready:
public static boolean writeBinaryFile(byte[] data, String absolutePath) {
boolean result = true;
if (data == null) {
System.out.println("byte[] data == null!");
}
try {
BufferedOutputStream output;
output = new BufferedOutputStream
(new FileOutputStream(absolutePath));
output.write(data, 0, data.length);
output.close();
} catch (Exception e) {
e.printStackTrace();
result = false;
}
return result;
}
public static byte[] readBlob(ResultSet rsts, int num) {
try {
if (rsts.next()) {
Blob b = rsts.getBlob(num);
int len = (int) b.length();
return b.getBytes(1, len);
} else {
return null;
}
} catch (Exception e) {
System.out.println("Exception: " + e.getMessage());
return null;
}
}
That aside, if you want to auto-open a file under Microsoft Windows you might find this sample here useful.
public void openDownloadedFile(String absolutePath) {
try {
Runtime.getRuntime().exec("rundll32 SHELL32.DLL,ShellExec_RunDLL " +
absolutePath);
} catch (Exception e) {
System.out.println("Exception thrown @ \" open downloaded file \":"+
e.getStackTrace() + " " + e.getMessage());
}
}
If there's any trouble with variable names I haven't explained, just reply to this topic - I'll watch it for some more time.
Cheers and enjoy the week end.
