Solved

save excel  file  into oracle DB as BLOB/CLOB

Posted on 2007-11-14
38
2,821 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
ID: 20287452
any resource ?
0
 
LVL 5

Expert Comment

by:deeppra
ID: 20287486
u got any error or exception??
0
 
LVL 5

Expert Comment

by:deeppra
ID: 20287493
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:cofactor
ID: 20287786
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
ID: 20287810
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
ID: 20287829
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
ID: 20287899
>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
ID: 20287914
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
ID: 20287968
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
ID: 20288071
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
ID: 20288112
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
ID: 20288122
yea try with that, If u get any exception post it
0
 

Author Comment

by:cofactor
ID: 20311107
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
ID: 20311121
Chek the items size
0
 

Author Comment

by:cofactor
ID: 20311165
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
ID: 20311177
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
ID: 20311183
and also there is no exception in the console !
everything is hushed up :(
0
 
LVL 5

Expert Comment

by:deeppra
ID: 20311195
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
ID: 20311228
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
 

Author Comment

by:cofactor
ID: 20311233
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
ID: 20311274
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
ID: 20311290
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
ID: 20311367
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
ID: 20311375
i cant see the image send to my email pradeepd12@gmail.com
0
 

Author Comment

by:cofactor
ID: 20311381
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
ID: 20311385
dont worry about the image ....i just printed that exception in the console ...so please take that .
0
 
LVL 5

Expert Comment

by:deeppra
ID: 20311392
0
 
LVL 5

Expert Comment

by:deeppra
ID: 20311403
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
ID: 20311489
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
ID: 20311500
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
ID: 20311510
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
ID: 20337648
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
ID: 20337661
modified that line to

byte bindata[]=new byte[1024];

0
 
LVL 5

Expert Comment

by:deeppra
ID: 20337893
If u dont have any questions accept my answer
0
 

Author Comment

by:cofactor
ID: 20337921
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
ID: 20337929
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
ID: 20338053
yea sure
0
 

Author Comment

by:cofactor
ID: 20348621
closing question.

it was helpful.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
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…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

770 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