The datafile which we need to upload to tables in our project, is an external ".CSV" file which has text in the following format (This is just 1/20 th part of it..the remaining has more sets as these, nearly 20 sets of such data with different values)
@110519120031<m02m00-0315>160xxxxxxxxxxxx
@110519120032<m02m02-0315>204xxxxxxxxxxxx
@110519120033<m02m04-0315>155xxxxxxxxxxxx
As you can see, this needs pattern matching.. Hence we used JSP's with String Tokenizers and JDBC for uploading the values..
Specifications of our system are :
Apache Tomcat 5.0
Oracle 10g XE
and JDK 1.6.0_02
For which we could not find a suitable SQL developer hence we used PL/SQL commands instead..
The upload form we used contained a basic BROWSE button that redirected to the following JSP when a file was selected
<%@ page import="javax.servlet.http.*"%>
<%@ page import="java.io.*"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.IOException" %>
<%@ page import="java.io.PrintWriter" %>
<%@ page import="java.util.*" %>
<%@ page import="java.util.StringTokenizer" %>
<%@ page import="javax.servlet.ServletException" %>
<%@ page import="javax.servlet.http.HttpServlet" %>
<%@ page import="javax.servlet.http.HttpServletRequest" %>
<%@ page import="javax.servlet.http.HttpServletResponse" %>
<% int i,p=0,q=0,a,b,c,d,e,f,n=0,k=0,l=0;
String r,code;
String avi;
String str1;
String str2;
String str3;
String name=request.getParameter("datafile");
String data[]=new String[4000];
String data1[]=new String[4000];
String data2[]=new String[4000];
out.println(name);
FileInputStream in = new FileInputStream(name);
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","mblm","1321213");
conn.setAutoCommit( false );
Statement stmt=con.createStatement();
BufferedReader br = new BufferedReader(new InputStreamReader(in,"UTF-8"));
String strLine;
while ((strLine = br.readLine())!= null)
{
if(strLine.startsWith("@"))
{
a=strLine.indexOf("=");
b=strLine.indexOf(">");
c=strLine.indexOf("<");
d=strLine.indexOf("-");
code=strLine.substring(c+1,d);
if(code.equals("M02m00"))
{
if(!(a<0))
{
i=0;
String line=strLine.substring(a+1,b);
StringTokenizer st = new StringTokenizer(line,"=,>");
while(st.hasMoreTokens())
{
data1[i]=st.nextToken();
i++;
}
}
}
if(code.equals("M02m02"))
{
if(!(a<0))
{
i=0;
String line=strLine.substring(a+1,b);
StringTokenizer st = new StringTokenizer(line,"=,>");
while(st.hasMoreTokens())
{
data[i]=st.nextToken();
i++;
}
}
}
if(code.equals("M02m04"))
{
if(!(a<0))
{
i=0;
String line=strLine.substring(a+1,b);
StringTokenizer st = new StringTokenizer(line,"=,>");
while(st.hasMoreTokens())
{
avi=st.nextToken();
data2[i]=st.nextToken();
i++;
n++;
}
}
}
out.println(n);
}
while(k<n)
{
str1=data1[k];
str2=data[k];
str3=data2[k];
out.println(str1);
ResultSet Rs1=stmt.executeQuery(" Insert /*+ APPEND_VALUES */ into AIRTEMP(LEVEL1,LEVEL2,LEVEL3) values('"+str1+"','"+str2+"','"+str3+"')");
k++;
}
}
%>
The difficulty we have is that this code cannot upload more than 500 records into the table and that too it doesnt upload properly Plz help
[edit]SHOUTING removed, < and > HTML encoded, spurious closing tags removed - OriginalGriff[/edit]