Java Upload file into database field

[Rick Banster]

I have a Java/JSP web site based on an Oracle 9i database. I'm trying to upload files and insert them directly into a table in my database. I'm currently using the follow code:

fs = new FileInputStream(file);

Class.forName("oracle.jdbc.driver.OracleDriver");
connection=DriverManager.getConnection("jdbcracle:thin:@---","xxx","xxx")�;
connection.setAutoCommit(false);
pstmt = connection.prepareStatement("INSERT INTO UploadsTable (ID,Uploadfile)
VALUES (1,file)" );

pstmt.setInt(1,123);
pstmt.setBinaryStream(2,fs,(int)file.length());
pstmt.executeUpdate();

pstmt.close();
fs.close();

The code uploads; however, saves the file to my server. I do not want the file saved to my server, just inserted into my database. I would like to store the WHOLE file in a field not just the contents. Files I would like to save are .txt, .sgn, html, etc...

Please help! I would really appreciate it.

Thanks

[Jeanne Boyarsky]

Originally posted by Rick Ssss:
I would like to store the WHOLE file in a field not just the contents. Files I would like to save are .txt, .sgn, html, etc...

What does it mean to store a whole file rather than the contents? I don't understand.

[Jeanne Boyarsky]

Copied from PM:

Jeanne,

Thanks for your response. When I said I would like to store the whole file instead of its contents, I meant that the file would be stored in a field. Before I tried to insert a file in a field and I had to do it as a string, which messed the file up. I want to be able to do a query and have the file returned as a field, so the user can click on it to download it from the database, not the file server. Hope this makes sense. Basically I want to upload a file into the database as a field. Thanks again!

[Jeanne Boyarsky]

Rick,
It is common to use a BLOB or CLOB field for storing the contents of a file. It looks like you are using a BLOB, which is fine.

A file consists of two things: the file name and the file contents. If you store these in separate columns, you have all the data from the original file.

so the user can click on it to download it from the database

This is done through an interface rather than the database. (No need to involve a file server.) For example, you use the Java application to recreate the file.

[Rick Banster]

Thanks Jeanne. I'm new to using files with Java. Can you point me to some Java code that will recreate the file for me. It will be very helpful.

Also is there a way to not upload the file to my server before inserting it to the database? The code I have uploads and saves the file to my server before inserting it to my database.

Thanks again

[Jeanne Boyarsky]

Originally posted by Rick Banster:
Thanks Jeanne. I'm new to using files with Java. Can you point me to some Java code that will recreate the file for me. It will be very helpful.

Rick,
To retrieve a BLOB (file), see this example.

Originally posted by Rick Banster:
Also is there a way to not upload the file to my server before inserting it to the database? The code I have uploads and saves the file to my server before inserting it to my database.

What technology are you using to upload the file? Servlets? Standalone app?

[Rick Banster]

I'm using servlets. WebLogic 9.2.

For the full code I'm using please see http://www.coderanch.com/t/304856/JDBC/java/inserting-Blob-Oracle-DB