Solved

save excel  file  into oracle DB as BLOB/CLOB

Posted on 2007-11-14
38
2,734 Views
Last Modified: 2008-02-01
I have a jsp page.

it has a upload button.

user will upload an excel file.

excel file will be saved into the oracle database.


 Question:
 
I am stuck at the last requirement i.e on the saving part into DB.
i am looking for a sample code to save the excel file directly into oracle DB ( CLOB/BLOB ?)?

can you help ?
0
Comment
Question by:cofactor
  • 22
  • 16
38 Comments
 

Author Comment

by:cofactor
Comment Utility
any resource ?
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
u got any error or exception??
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
0
 

Author Comment

by:cofactor
Comment Utility
Hi i saw  http://www.javangelist.de/space/JDBC/Blob
they have code // for blob insertion

PreparedStatement statement = connection.prepareStatement(
  "INSERT INTO MyTable (BlobColumn) VALUES (?)" );

File imageFile = new File( "myImage.jpg" );  // How do i get the real file name and location from the jsp ??
InputStream in = new FileInputStream( imageFile );
....................
....................
blah blah


How do i get the real file name and location from the client  PC   ??

i think , i need to write some code in the servlet to  get the real file name and location of the uploaded file from the client PC....right ?

I dont know what to write .

Do i need to use third party package for this ?




0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
Then you have to upload the file to the server and then try to put it in database.
Struts provides some utils for file upload see the below link

http://www.roseindia.net/struts/strutsfileupload.shtml
http://www.roseindia.net/struts/strutsfileuploadandsave.shtml
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
In u r action class u can get the file content in bytes using this method as given in the example program

 byte[] fileData    = myFile.getFileData();

Then use ByteArrayInputStream to create a inputstream and set that one in statement

ByteArrayInputStream in = new ByteArrayInputStream(fileData);

statement.setBinaryStream(1,  // parameter index
         in, // InputStream to read from
         (int) fileData.length() // number of bytes to read
0
 

Author Comment

by:cofactor
Comment Utility
>Then you have to upload the file to the server and then try to put it in database

hmmm...ok...it looks fine but it would have been nicer if i could avoid uploading into server .

I would have been much more happier if i could  DIRECTLY  save  the file to the Database ....because saving into server is of no use to me.

can i get that flexibility ?

is it impossible ? too complex ?



0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
No actually we are not creating the file in the server side we are just transfering the content from the client to server and its stored in the server memory not stored in a file and from the memory we are creating InputStream and storing the content into the database. So nowhere we are storing the file.
0
 

Author Comment

by:cofactor
Comment Utility
ok.

thats fine then .

but you have a struts file upload example.

i have only jsp and servlet ...no struts .

so, myFile in your code ...is formbean  i think which does not exist in my case.

Anyway,

here is my jsp snippet

<tr>
<td width="20%" colspan="2">Browse file</td>
<td width="50%" colspan="1">
<input type="file" name="file" id="file"/>
</td>
<td width="30%" colspan="1"></td>
</tr>
<tr>
<td width="20%" colspan="2">Upload file</td>
<td width="50%" colspan="1">
<input type="Submit" value="Upload"/>
</td>
<td width="30%" colspan="1"></td>
</tr>


from here , can you please tell me what should i write in my servlet code to get the filename and real path ?

I am not using struts.
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
http://cit3.cdn.swin.edu.au/utilities/upload/File_Upload_Using_Java.html --- very simple just using servlet with out any other library

You can use Apache file upload to make u r job easy you can download it from
http://commons.apache.org/fileupload/

and some example on it

http://www.geekinterview.com/question_details/17234
http://commons.apache.org/fileupload/using.html

0
 

Author Comment

by:cofactor
Comment Utility
Ok.

i saw http://www.geekinterview.com/question_details/17234

and i copied this code from there...

public class UploadFile extends HttpServlet {

public void doPost(HttpServletRequest req,HttpServletResponse res)
{
try{

FileUpload fup=new FileUpload();
boolean isMultipart = FileUpload.isMultipartContent(req);
// Create a new file upload handler
System.out.println(isMultipart);
DiskFileUpload upload = new DiskFileUpload();

// Parse the request
List /* FileItem */ items = upload.parseRequest(req);

Iterator iter = items.iterator();
while (iter.hasNext()) {

FileItem item = (FileItem) iter.next();

if (item.isFormField()) {
System.out.println("its a field");
} else {
System.out.println("its a file");
System.out.println(item.getName());
File cfile=new File(item.getName()); // got it




Now , i have got the File object from the client in memory and from here i can plug this into our old blob code

like this way ....

InputStream in = new FileInputStream( cfile ); // plugged cfile here
statement.setBinaryStream(
         1,  // parameter index
         in, // InputStream to read from
         (int) imageFile.length() // number of bytes to read
);
statement.executeUpdate();




please comment on this .

is this ok ?

0
 
LVL 5

Accepted Solution

by:
deeppra earned 300 total points
Comment Utility
yea try with that, If u get any exception post it
0
 

Author Comment

by:cofactor
Comment Utility
FileUpload fup=new FileUpload();
      boolean isMultipart = FileUpload.isMultipartContent(request);

      System.out.println("isMultipart"+isMultipart);  // it prints true
      DiskFileUpload upload = new DiskFileUpload();
      System.out.println("upload"+upload);  // it prints uploadorg.apache.commons.fileupload.DiskFileUpload@705e69

      List /* FileItem */ items = upload.parseRequest(request);
      System.out.println("items"+items); // no other  print from here !!!! ....LINE-1
      Iterator iter = items.iterator();
      System.out.println("iter"+iter);
      while (iter.hasNext()) {
            System.out.println("i");
      FileItem item = (FileItem) iter.next();

      if (item.isFormField()) {
      System.out.println("its a field");
      } else {
      System.out.println("its a file");
      System.out.println(item.getName());
      File cfile=new File(item.getName());
      System.out.println("its a file"+cfile);
      Connection con=(new DBUtil()).getConnection();
      //Statement stmt=con.createStatement();


i am not getting any prints from .LINE-1

can you please tell whats wrong there ?
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
Chek the items size
0
 

Author Comment

by:cofactor
Comment Utility
ok,  printed the size ....but no print coming :(


System.out.println("isMultipart"+isMultipart);
      DiskFileUpload upload = new DiskFileUpload();
      System.out.println("upload"+upload);
//       Parse the request
      List /* FileItem */ items = upload.parseRequest(request);
      System.out.println("size of items"+items.size());
      System.out.println("items"+items);
      Iterator iter = items.iterator();
      System.out.println("iter"+iter);


console output:
isMultiparttrue
uploadorg.apache.commons.fileupload.DiskFileUpload@253cde
0
 

Author Comment

by:cofactor
Comment Utility
is it because of DiskFileUpload()  and  parseRequest  method deprecated ? because myeclipse  editor is saying these are deprecated.

However, i dont  believe that it could be the cause ....at least i could get some print anyhow .
i am building and deploying freshly everytime .
0
 

Author Comment

by:cofactor
Comment Utility
and also there is no exception in the console !
everything is hushed up :(
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
u enclosed every thing inside try catch block if ur then in the catch block try to print the stacktrace. And also u can degug and see in the Eclipse IDE so that u can know where its going worng
0
 

Author Comment

by:cofactor
Comment Utility
did the debug mode

the  trouble some area is
List /* FileItem */ items = upload.parseRequest(request);  ----> control goes out from here.

investigating whats wrong here .
if u have any input  here plz tell, will look into that too.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:cofactor
Comment Utility
some more update.....

though i already have a try-catch bolck wrapped in my code already

.....................
........................
}catch(Exception e){System.out.println(e);}  // but this catch is not catching and printing exception .

i think , its a different kind of exception...something servlet  invokation exception .

not sure ...still investigating more...
0
 

Author Comment

by:cofactor
Comment Utility
I got

List /* FileItem */ items = upload.parseRequest(request);  
is throwing  :

An exception occured java.lang.NoClassDefFoundError


what to do now :(
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
which calss clauses the exception after the exception u have the Class name like this

java.lang.NoClassDefFoundError "Class name"
0
 

Author Comment

by:cofactor
Comment Utility
somehow i am not getting that  class.

here is the screenshot  you can see

http://img252.imageshack.us/img252/5462/testqz8.png
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
i cant see the image send to my email pradeepd12@gmail.com
0
 

Author Comment

by:cofactor
Comment Utility
finally got it

java.lang.NoClassDefFoundError: org/apache/commons/io/FileCleaner
        at org.apache.commons.fileupload.disk.DiskFileItem.getTempFile(DiskFileItem.java:600)
        at org.apache.commons.fileupload.disk.DiskFileItem.getOutputStream(DiskFileItem.java:540)
        at org.apache.commons.fileupload.FileUploadBase.parseRequest(FileUploadBase.java:354)
        at org.apache.commons.fileupload.FileUploadBase.parseRequest(FileUploadBase.java:302)
        at com.pwc.controller.ExcelUploadServlet.doPost(ExcelUploadServlet.java:106)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at weblogic.servlet.internal.ServletStubImpl$ServletInvocationAction.run(ServletStubImpl.java:1072)
        at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:465)
        at weblogic.servlet.internal.ServletStubImpl.invokeServlet(ServletStubImpl.java:348)
        at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:6981)
        at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
        at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:121)
        at weblogic.servlet.internal.WebAppServletContext.invokeServlet(WebAppServletContext.java:3892)
        at weblogic.servlet.internal.ServletRequestImpl.execute(ServletRequestImpl.java:2766)
        at weblogic.kernel.ExecuteThread.execute(ExecuteThread.java:224)
        at weblogic.kernel.ExecuteThread.run(ExecuteThread.java:183)
org.apache.commons.fileupload.FileUploadException: Connection reset



any suggestion now :(

i have the latest commons upload jar 1.2

i cant believe that its a faulty one :(
0
 

Author Comment

by:cofactor
Comment Utility
dont worry about the image ....i just printed that exception in the console ...so please take that .
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
download the Commons IO here

http://commons.apache.org/io/

File Cleaner class found in that  CommonsIO API
http://commons.apache.org/io/apidocs/org/apache/commons/io/FileCleaner.html
0
 

Author Comment

by:cofactor
Comment Utility
yes...you are amazing man.

it worked like a charm.

data is going into database perfectly.

you are excellent .

one more question
in fact when my user clicks on a link ( i am providing an id from the DB's BLOB ) , he would be able to download that excel file . he must get a download prompt.

can you please provide some example code .

I am increasing point.


0
 

Author Comment

by:cofactor
Comment Utility
i want to give a download prompt to the user .

as u know i have BLOB data in DB .

so , how does this BLOB data will give a download prompt so that user can download the excel file when they click on a download link ?
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
for info see this link
http://ibphoenix.com/main.nfs?page=ibp_iclient_blob

after getting the data in byte array in the servlet output stream write all the byte content and you need to set the content-type and some other headers like this

res.setContentType("application/x-download");
res.setHeader("Content-Disposition", "attachment; filename=" + filename);

for more info see this link
http://www.onjava.com/pub/a/onjava/excerpt/jebp_3/index3.html
http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=7&t=016177
public byte[] selectBlob( int rowid ) {
 

// In this example I'm assuming there's an open, active

// Connection instance called 'con'.
 

// This examples uses an imaginary SQL table of the following

// form:

//

// CREATE TABLE blobs (

//    ROWID INT NOT NULL,

//    ROWDATA BLOB,

//

//    PRIMARY KEY (rowid)

// );
 

try {
 

Statement sment = con.createStatement();
 

String sql = "SELECT rowid, rowdata FROM blobs WHERE rowid = " + rowid;
 

ResultSet rs = sment.executeQuery(sql);

				

byte[] returndata = null;
 

if ( rs.next() ) {
 

try {
 

// The ByteArrayOutputStream buffers all bytes written to it

// until we call getBytes() which returns to us an array of bytes:

ByteArrayOutputStream baos = new ByteArrayOutputStream(1024);
 

// Create an input stream from the BLOB column. By default, rs.getBinaryStream()

// returns a vanilla InputStream instance. We override this for efficiency

// but you don't have to:

BufferedInputStream bis = new BufferedInputStream( rs.getBinaryStream("fieldblob") );
 

// A temporary buffer for the byte data:

byte bindata[1024];
 

// Used to return how many bytes are read with each read() of the input stream:

int bytesread = 0;
 

// Make sure its not a NULL value in the column:

if ( !rs.wasNull() ) {
 

if ( (bytesread = bis.read(bindata,0,bindata.length)) != -1 ) {
 

// Write out 'bytesread' bytes to the writer instance:

baos.write(bindata,0,bytesread);
 

} else {
 

// When the read() method returns -1 we've hit the end of the stream,

// so now we can get our bytes out of the writer object:

returndata = baos.getBytes();
 

}

}
 

// Close the binary input stream:

bis.close();
 

} catch ( IOException ioe ) {

System.err.println("Problem retrieving binary data: " + ioe);

} catch ( ClassNotFoundException cnfe ) {

System.err.println("Problem retrieving binary data: " + cnfe);

}

}
 

rs.close();

sment.close();
 

} catch ( SQLException se ) {

System.err.println("Couldn't retrieve binary data: " + se);

} finally {

con.close();

}
 

return returndata;

}

Open in new window

0
 

Author Comment

by:cofactor
Comment Utility
thanks for the example code.

i dont understand why MyEclipse is saying

byte bindata[1024]; // syntax error on token 1024 ... delete this token .

any idea ?
0
 

Author Comment

by:cofactor
Comment Utility
modified that line to

byte bindata[]=new byte[1024];

0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
If u dont have any questions accept my answer
0
 

Author Comment

by:cofactor
Comment Utility
yea...deeppra ....your answer is almost perfect .

i am just testing download code.

somehow , i am not  able to call the servlet .
so,  not able to test .

will close this question very soon.

0
 

Author Comment

by:cofactor
Comment Utility
i am sending id and filename to the servlet from the jsp link.

you can look here about the problem i am facing
http://www.experts-exchange.com/Programming/Languages/Java/Q_22978564.html

i will close this question  as soon as i can test it .

thanks
0
 
LVL 5

Expert Comment

by:deeppra
Comment Utility
yea sure
0
 

Author Comment

by:cofactor
Comment Utility
closing question.

it was helpful.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now