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
Code:
<%@ page import="java.sql.*" %>
<% Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");%>
<HTML>
<BODY bgcolor="#99CCCC">
<p> </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
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> </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.
