save excel file into oracle DB as BLOB/CLOB

any resource ?

u got any error or exception??

see this like for some sample progam to read and write BLOB data types

http://www.javangelist.de/space/JDBC/Blob
http://www.java2s.com/Code/Java/Database-SQL-JDBC/BlobJDBCdealswithBinaryData.htm
http://support.bea.com/application_content/product_portlets/support_patterns/wls/CLOBBLOBDataTypeHandlingIssuesPattern.html

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 ?

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

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

>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 ?

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.

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.

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

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 ?

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 ?

Chek the items size

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

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 .

and also there is no exception in the console !
everything is hushed up :(

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

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.

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...

I got

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

An exception occured java.lang.NoClassDefFoundError


what to do now :(

which calss clauses the exception after the exception u have the Class name like this

java.lang.NoClassDefFoundError "Class name"

somehow i am not getting that  class.

here is the screenshot  you can see

http://img252.imageshack.us/img252/5462/testqz8.png

i cant see the image send to my email pradeepd12@gmail.com

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 :(

dont worry about the image ....i just printed that exception in the console ...so please take that .

See this forum
http://www.velocityreviews.com/forums/t151220-apache-fileupload-javalangnoclassdeffounderror-orgapachecommonsiofilecleaner.html

check all the dependency

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

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.

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 ?

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;
}

Select allOpen in new window

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 ?

modified that line to

byte bindata[]=new byte[1024];

If u dont have any questions accept my answer

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.

i am sending id and filename to the servlet from the jsp link.

you can look here about the problem i am facing
https://www.experts-exchange.com/questions/22978564/url-not-working.html

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

thanks

yea sure

closing question.

it was helpful.