Reading records from a file and inserting in oracle database
0 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   amerta_singh
Posted On:   Monday, April 9, 2001 08:41 PM

hi. My problem is that i have to read the millions of records from the file and inserting them into the database using stored procedure. >> what i need is to achieve the speeed and less time. ***** here is the code which I am using*** >>> but it take 42 minutes to insert the one lac (100000) records >> i want it to be 20 minutes >> kindly help me i will be highly thankful to all of u. ****** my code****** import java.io.*; import java.util.*; import java.sql.*; import com.comcept.database.*; public class read { private PushbackInputStream fis; public StringWriter strwriter; pri   More>>

hi.

My problem is that i have to read the millions of records from the file and inserting them into the database using stored procedure.
>> what i need is to achieve the speeed and less time.

***** here is the code which I am using***

>>> but it take 42 minutes to insert the one lac (100000) records
>> i want it to be 20 minutes

>> kindly help me i will be highly thankful to all of u.

****** my code******
import java.io.*;
import java.util.*;
import java.sql.*;

import com.comcept.database.*;

public class read
{
private PushbackInputStream fis;
public StringWriter strwriter;
private Statement statement;
private CallableStatement cstatement;
private PreparedStatement pstatement;
private Connection connection;
private dbinfo info;
private dbconnection dbconn;
private String omcreport;



public read() throws IOException
{

fis = new PushbackInputStream(new FileInputStream("abc.dat"));

// these are basically the utility classes for establishing the database connection
// written by me
// simply connection methods can be used
dbinfo info = new dbinfo("","192.168.0.58","1521","orcl","msdetest","msdetest");
dbconn= new dbconnection();
dbconn.orcldbConnection(info);
this.connection= dbconn.conn;

}// end constructor



private byte[] readNameLength() throws IOException
{

byte buff[] = new byte[4];
int gg;

for(int i=0;i <4;i++)
{

buff[i] = (byte)fis.read();
}

return buff;

}

private byte[] readYearLength() throws IOException
{

byte buff[] = new byte[4];

for(int i=0;i <4;i++)
{
buff[i] = (byte)fis.read();
}

return buff;

}


private byte[] readCounterLength() throws IOException
{

byte buff[] = new byte[4];

for(int i=0;i <4;i++)
{
buff[i] = (byte)fis.read();
}

return buff;

}


private int getYearLength (byte b[] ) throws IOException
{

int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);

return hh;

}

private int getNameLength(byte b[] ) throws IOException
{
int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);


return hh;


}


private int getCounterLength (byte b[] ) throws IOException
{

int hh=0;
for (int i=3;i>-1;i--) hh |= ((b[i] & 0xff) < < 8*i);
return hh;

}

private String getName(int len ) throws IOException
{

String S = "";
for(int i=0;i
{
S+=(char)fis.read();
}

return S;

}
private String getData()throws IOException
{

byte buff1[] =readCounterLength();
int tp =getCounterLength(buff1);

byte buff2[] = readCounterLength();
int counter1 =getCounterLength(buff2);
byte buff3[] = readCounterLength();
int counter2 =getCounterLength(buff3);
byte buff4[] = readCounterLength();
int counter3 =getCounterLength(buff4);
byte buff5[] = readCounterLength();
int counter4 =getCounterLength(buff5);
byte buff6[] = readCounterLength();
int counter5 =getCounterLength(buff6);
byte buff7[] = readCounterLength();
int counter6 =getCounterLength(buff7);
byte buff8[] =readCounterLength();
int counter7 =getCounterLength(buff8);
byte buff9[] =readCounterLength();
int counter8 =getCounterLength(buff9);

String tpcounter =""+tp+","+counter1+","+counter2+","+counter3+","+counter4+","+counter5+","+counter6+","+counter7+","+counter8;
return tpcounter;
}


public static void main(String args[])throws IOException
{

read r = new read();

byte buff[] = r.readNameLength();
int length = r.getNameLength(buff);
System.out.println("length = "+length);
String name = r.getName(length);
System.out.println("name = "+name);
int ext = r.fis.read();
System.out.println("Ex Type = "+ext);
int day = r.fis.read();
System.out.println("day = "+day);
int month = r.fis.read();
System.out.println("month = "+month);
byte buff1[] = r.readYearLength();
int year = r.getYearLength(buff1);
System.out.println("year = "+year);

String date =""+day+"-"+month+"-"+year;


int counter = 0;
String dummydate = "14-12-2001";


while(r.fis.available()>0)
{


String q = r.getData();

String query = "call sp_UpdDwnRecord ( \'"+name+"\',\'"+"20-04-2001"/*date.trim()*/+"\',"+q+")";
String record = name+" "+date+" "+q;
try
{

r.pstatement = r.connection.prepareStatement(query);
r.pstatement.execute();
r.pstatement.close();
}
catch (Exception e)
{
System.out.println("exception"+e.getMessage());

}
counter++;
System.out.println(""+counter+" "+record);
}

}







}// end clsss
**** end code ****

   <<Less
About | Sitemap | Contact