Solved

How do I download file storied in Oracle with a mouse click.

Posted on 2004-09-24
20
355 Views
Last Modified: 2010-04-01
I have used the uploadBean from http://www.javazoom.net/jzservlets/servlets.html to be able to upload files into Oracle database Long Raw column.

I am not sure how I could download it when user click on a link. I was thinking of an icon and when user click on the icon, there will be a prompt asking the user if they want to Save it or Open it.

Has anyone has example doing this in JSP?

Thank you.
0
Comment
Question by:Tuan_Jean
  • 11
  • 9
20 Comments
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12141190
You should upload/store file as a CLOB and read it as a CLOB if character data else go for BLOB .
sample cade to read and write to a clob to a table my_clob_table with a row as clob and another as varchar.

import java.util.*;
import javax.sql.*;
import java.sql.*;
import java.io.*;
import oracle.sql.*;
import oracle.jdbc.OracleResultSet;

public class testClob
{
  public static void main (String [] args) throws Exception
  {
    String content;
    String url = "jdbc:oracle:thin:@localhost:1521:ORAST";
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection (url,"username","password");
    conn.setAutoCommit(false);
    String cmd ="";
    Statement stmt = conn.createStatement();

    stmt.execute("drop table my_clob_table");
    stmt.execute("Create table my_clob_table(x varchar(30), c clob)");

    PreparedStatement pstmt = conn.prepareStatement("insert into my_clob_table values('row1',empty_clob())");
    pstmt.execute();

    ResultSet rset = stmt.executeQuery("SELECT * FROM my_clob_table for update");
    if(rset.next())
    {
      Clob clob = rset.getClob(2);
      Writer clobWriter = ((oracle.sql.CLOB)clob).getCharacterOutputStream();

      long size = 0;
      File txtFile = new File("c:\\marks.txt");
      size = txtFile.length();
      System.out.println("anup.txt length = " + size);
      FileReader reader = new FileReader(txtFile);

      char[] buffer = new char[(int)size];

      int nread = 0;
      while( (nread= reader.read(buffer)) != -1 )
      clobWriter.write( buffer, 0, nread); // Write to Clob

      reader.close();
      clobWriter.close();

      //stmt.execute("commit");

      cmd = "SELECT * FROM my_clob_table where x='row1'";
      ResultSet nrset = stmt.executeQuery (cmd);
      nrset.next();
      Clob nclob = nrset.getClob(2);

      Reader clobStream = nclob.getCharacterStream();
      StringBuffer cstring = new StringBuffer();
      // Read from the Clob stream and write to the stringbuffer
      int nchars = 0; // Number of characters read

      //Buffer holding characters being transferred
      char[] cbuffer = new char[(int)size];
      while((nchars = clobStream.read(cbuffer)) != -1) // Read from Clob
      cstring.append(cbuffer, 0, nchars); // Write to StringBuffer

      System.out.println( cstring.toString());

      clobStream.close(); // Close the Clob input stream
      nrset.close();
    }
    conn.close();
    rset.close();
    stmt.close();
  }
}
0
 

Author Comment

by:Tuan_Jean
ID: 12158113
Thank you for the prompt response. I have successfully made the file uploaded into the table CLOB field. Could you tell me how I could :

Show a link which when click will open the file in the CLOB with the above code?

Thank you in advance.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12159096
That would be pretty simple. All you have to do is create a link which will take you to a different jsp page where you have a textarea.
 Read the clob from the database by passing the file name depending on the link clicked and set the value of the text area accordingly.

CodingExpert
0
 

Author Comment

by:Tuan_Jean
ID: 12232629
CodingExpert,

I am sorry for any delay in update. I am still trying to get through the code you have suggested and to make it work for upload. Please could I keep this open for a few more days.

Thank you.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12245516
Tke your time and let me know if i can be of some help.
0
 

Author Comment

by:Tuan_Jean
ID: 12256594
CodingExperts,

Thank you for your patient. I did not success in getting the file uploaded into a CLOB. Hence I use the uploadBean from http://www.javazoom.net so that I could at least get 1 part working to test for the other.

I reckon I have got the test table working, however, the field is in Long Raw not CLOB.

I was testing with your code :



<html>
<head>
<meta http-equiv="Content-Type" content="application/msword; charset=UTF-8">
</head>
<%@ page language="java" import="java.sql.*,com.jspsmart.upload.*"%>  

<%  
Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();  
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@SERVER1:1521:LEARNING", "testuser", "password");  

Statement stmt = con.createStatement();  

ResultSet rs = stmt.executeQuery("SELECT * FROM UPLOADS");  

if (rs.next()){  

      Long nclob = rs.getLong();

      Reader longStream = nclob.getCharacterStream();
     
      StringBuffer cstring = new StringBuffer();
     
      int nchars = 0; // Number of characters read

     
      char[] cbuffer = new char[(int)size];
     
      while((nchars = longStream.read(cbuffer)) != -1) // Read from Clob
     
      cstring.append(cbuffer, 0, nchars); // Write to StringBuffer

      System.out.println( cstring.toString());

      longStream.close();

 
}  
rs.close();  
stmt.close();  
con.close();  
%>


I have got this error:

C:\tomcat4\work\Standalone\localhost\uploadbean_1.5\oracleLongRawDownload_jsp.java:58: cannot resolve symbol
symbol  : method getLong  ()
location: interface java.sql.ResultSet
      Long nclob = rs.getLong();


Can you please help me? also how do I get it the open with the correct mime/type application without prompting me to Save or Open.

Thank you in advance.

0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12265816
>>if (rs.next()){  
>>
>>      Long nclob = rs.getLong();

the api usage is rs.getLong(int columnIndex)

Which retrieves the value of the designated column in the current row of this ResultSet object as a long in the Java programming language.

"columnIndex" is the index of the Long column in the result set. This is a problem.

CodingExperts
0
 

Author Comment

by:Tuan_Jean
ID: 12274067
CodingExperts,

Sorry for my naiveness, I have since tried the following

if (rs.next()){  

    long bf =   rs.getLong("BINARYFILE");

}

I have got the following error:

org.apache.jasper.JasperException: Invalid column type
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:254)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)

I really not sure what I have missed out. Thank you again for your time.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12295358
>>long bf =   rs.getLong("BINARYFILE");

inside the rs.getLong(<long_column_name>). say if the column name of long column in ur table is myLongCol then the statement wud be :
 
long bf =   rs.getLong("myLongCol");

CodingExperts

0
 

Author Comment

by:Tuan_Jean
ID: 12317952
CodingExpert,

the "BINARYFILE" is the column name for the Long Raw type. Any idea why the error?

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

 
LVL 6

Expert Comment

by:CodingExperts
ID: 12461636
Hi,
I was out on a vacation. well well can you share the DB table structure containing the "BINARYFILE" column.

CodingExperts
0
 

Author Comment

by:Tuan_Jean
ID: 12468392
CodingExperts,

I was going to test the download and then come back and revisit the upload so that I could limit the error. For the upload I have been using the upload bean from http://www.javazoom.net/jzservlets/uploadbean/uploadbean.html

The table structure is:

CREATE TABLE UPLOADS (
      UPLOADID INT NOT NULL,
      FILENAME VARCHAR2(255),
      BINARYFILE LONG RAW,
      PRIMARY KEY (UPLOADID)
);

I think I have given up on this one. I reckon it could be an Oracle JDBC driver limitation when I download as LONG RAW.
----------------

You have earlier post your code which upload file into a CLOB field. Can you help me with this code, I reckon it is closer to what I required.

Say if we assume this table:

CREATE TABLE UPLOADS (
      UPLOADID INT NOT NULL,
      FILENAME VARCHAR2(255),
      BINARYFILE CLOB,
      PRIMARY KEY (UPLOADID)
);

and this is my initial HTML form page:

<FORM METHOD="POST" ACTION="do_upload.jsp" ENCTYPE="multipart/form-data">
<input type="FILE" name="myFILE" size="30">
<input type="submit" name="Submit" value="upload">
</FORM>

Thank you again.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12470046
HI,

What do you intend to do.
Display a page with all the filenames as link and on click of any one file you want to show the file.

If this is the case then things are easy. What you can do is create a upload folder in the web-context. on every upload you can insert just the file name in the DB and store the file in the uploads directory.
Now when one has to see this uploaded file he'll be directed to a page with all the file names(shown as links) fetched from DB . Now when a user clicks the file name link just read the file from the upload directory. This way one doesnot need to store to clobs in the DB.

CodingExperts

0
 

Author Comment

by:Tuan_Jean
ID: 12470058
CodingExperts,

My scenario was actually to see if I could link a file say to a training corse. or perhaps also to upload some images. I was thinking to have every data kept in database. I thought the code you have provided is very close.

Do you have any example about the method which you mentioned?

Thank you.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12471648
Once the file has been uploaded in upload directory in the root context with the filenames going in the DB. Now all you have to do is make  a jsp

in the resultset get all the file names and write assuming your root-context is test and within your rootcontext you have a upload folder then
<%
  ResultSet rs = stmt.executeQuery(sql);
  while(rs.next())
  {
%>
  <a href='<% request.getRootContext()+"/"+rs.getString("filename") %>'><%=rs.getString("filename") %> </a>
<%
  }
%>

0
 

Author Comment

by:Tuan_Jean
ID: 12480018
CodingExpert,

Thank you.

I can see a lot of benefit to have the document set in the directory. Do you have any simple code that could do this?

Thank you.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12490795
To upload the file

Use the MultipartRequest class written by Jason Hunter under servlets.com.  Go here to download the jar: http://www.servlets.com/cos/cos-05Nov2002.zip

Put cos.jar in your classpath.  Then write a servlet that accepts the request as a MultipartRequest object and you will be able to pull out the uploaded files... There is lots of documentation on how to use these classes on the internet including here:  http://www.servlets.com/cos/index.html

This is exactly why I'm telling you not to use jspsmartupload.  What a pain in the butt.  I always thought it was a pain until I found the one I was telling you about above.  For MultipartRequest, you need only put "cos.jar" on the classpath.  Then you can handle the upload so easily:

1) Download the zip and put "cos.jar" on classpath

2) Create an HTML form with upload button in a file called "form.html":

<form action="test.jsp" method="post" enctype="multipart/form-data">
<input type=file name=testfile>
<input type=submit>
</form>

3) Create an empty directory in "c:/upload"

4) Make a file called "test.jsp", and put:

<%@ page import="com.oreilly.servlet.*" %>
<%
 MultipartRequest mp = new MultipartRequest(request, "c:/upload");
 out.println("DONE");
%>

5) The file should have been uploaded.  You can find more command on what you cando with the MultipartRequest here: http://www.servlets.com/cos/javadoc/com/oreilly/servlet/MultipartRequest.html..
0
 
LVL 6

Accepted Solution

by:
CodingExperts earned 200 total points
ID: 12490825
in my last comment
>>MultipartRequest mp = new MultipartRequest(request, "c:/upload");
should be
MultipartRequest mp = new MultipartRequest(request, request.getRootContext()+"/upload");

now insert the file name into the DB by specifying diff. uploadid for files uploaded.
To display links to the files so uploaded.Create a jsp file with the following scriptlet
<%
  ResultSet rs = stmt.executeQuery("Select filename from uploads");
  while(rs.next())
  {
%>
  <a href='<% request.getRootContext()+"/upload/"+rs.getString("filename") %>'><%=rs.getString("filename") %> </a>
<%
  }
%>

good luck
CodingExperts
0
 

Author Comment

by:Tuan_Jean
ID: 12500026
Thank you.
0
 
LVL 6

Expert Comment

by:CodingExperts
ID: 12501102
Thank u too Tuan :-)
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

Find out what Office 365 Transport Rules are, how they work and their limitations managing Office 365 signatures.
We have come a long way with backup and data protection — from backing up to floppies, external drives, CDs, Blu-ray, flash drives, SSD drives, and now to the cloud.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

16 Experts available now in Live!

Get 1:1 Help Now