Results 1 to 3 of 3
  1. #1
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

    Default How to upload a file to Oracle 10g DB using Jsp

    Hi

    I want to uplaod a file(csv or excel) to Oracle 10g database, using Jsp or Servlets....


    Addfile.jsp
    Java Code:
    <%@ page import="java.sql.*" %>
    <% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");%>
    <HTML>
    
        <BODY bgcolor="#99CCCC">
            <p>&nbsp;</p>
    <br>
            <h2 align="center">Upload File to Oracle Database</h2>
            <form id="form1" enctype="multipart/form-data" action="UploadFile.jsp" method="post">
                <table align="center">
                  
                    <tr>
                        <td>Browse File  </td>
                        <td><input align="center" type="file"  name="csvfile" />
                    </tr>
                </table>
                <p/>
                <center><input align="center" type="submit" value="Upload File"/></center>
            </form>
    
            
        </BODY>
    </HTML>
    UploadFile.jsp

    Java Code:
    <%@ page import="java.sql.*" %>
    <%@ page import="java.io.IOException" %>
    <%@ page import="java.io.PrintWriter" %>
    <%@ page import="java.util.List" %>
    <%@ page import="javax.servlet.ServletException" %>
    <%@ page import="javax.servlet.http.HttpServlet" %>
    <%@ page import="javax.servlet.http.HttpServletRequest" %>
    <%@ page import="javax.servlet.http.HttpServletResponse" %>
    <%@ page import="org.apache.commons.fileupload.FileItem" %>
    <%@ page import="org.apache.commons.fileupload.disk.DiskFileItemFactory" %>
    <%@ page import="org.apache.commons.fileupload.servlet.ServletFileUpload" %>
    
    <HTML>
    
        <BODY bgcolor="#99CCCC">
            <p>&nbsp;</p>
    <br>
    
    <%
            try {
                // Apache Commons-Fileupload library classes
                DiskFileItemFactory factory = new DiskFileItemFactory();
                ServletFileUpload sfu  = new ServletFileUpload(factory);
    
                if (! ServletFileUpload.isMultipartContent(request)) {
                    System.out.println("sorry. No file uploaded");
                    return;
                }
    
                // parse request
                List items = sfu.parseRequest(request);
                FileItem  PartNo = (FileItem) items.get(0);
                String photoid =  PartNo.getString();
    
                FileItem SerialNo = (FileItem) items.get(1);
                String phototitle = SerialNo.getString();
    
                // get uploaded file
                FileItem file = (FileItem) items.get(2);
    
                // Connect to Oracle
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "log", "log");
                con.setAutoCommit(false);
    
                PreparedStatement ps = con.prepareStatement("insert into InventoryDB_Main(PartNo,SerialNo) values(?,?)");
                ps.setString(1, photoid);
                ps.setString(2, phototitle);
                // size must be converted to int otherwise it results in error
                ps.setBinaryStream(3, file.getInputStream(), (int) file.getSize());
                ps.executeUpdate();
                con.commit();
                con.close();
                out.println("File Uploaded Successfully.");
            }
            catch(Exception ex) {
                out.println( "Error --> " + ex.getMessage());
            }
    
    %>
    This is my code...Its not uploading the file, it is giving me Error--> Index 1, size 1. Please help me ..how to do this. I am trying to upload a csv file to oracle db from jsp page, csv file has two fields PartNo and SerialNo. The Oracle table InventoryDB_main has 23 columns including PartNo, SerialNo. Please can anyone help me how to upload a file to oracle database.

    Thanks in advance
    Lissy.

  2. #2
    r035198x is offline Senior Member
    Join Date
    Aug 2009
    Posts
    2,388
    Rep Power
    7

    Default

    1.) Use a servlet to handle the upload and only use JSPs for presenting data.
    2.) You prepared a query with two parameters for your two columns but you are setting three parameters on the prepared statement. What column do you expect the third parameter to bind to?
    3.) Better read the file line by line into List<Record> then write a class that persists that List<Record>.
    4.) Even better is to use DB provided tools to upload the data. They have already written programs that handle data import and export and it would be a shame to rewrite something they already provide with the database.

  3. #3
    techsing14 is offline Member
    Join Date
    Oct 2010
    Posts
    24
    Rep Power
    0

Similar Threads

  1. Trying upload file
    By johniem in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 07-01-2010, 10:31 AM
  2. :large file upload to server(chunk upload)
    By tommy_725 in forum Networking
    Replies: 0
    Last Post: 10-16-2009, 12:21 AM
  3. Upload file - sevlet - Oracle
    By sriram6000 in forum Java Servlet
    Replies: 1
    Last Post: 10-16-2008, 06:46 AM
  4. how to upload a file?
    By tommy in forum JavaServer Pages (JSP) and JSTL
    Replies: 4
    Last Post: 06-30-2008, 02:50 PM
  5. File Upload
    By ShoeNinja in forum JavaServer Pages (JSP) and JSTL
    Replies: 0
    Last Post: 11-09-2007, 10:09 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •