hi friends
I am using EJB Message Driven bean datas were received in very fastest way(per min 200 msg) in the onMessage() in bean call.I am adding the msgs to the vector and passed to the other class.That class is doing all the database Operation.for this scenario i am using PreparedStatement and batchUpdation.In this class i am getting BatchException.I have attched the error log here and class code also.
this is the error lo
12:21:25,765 INFO [STDOUT] record size :1
12:21:25,765 INFO [STDOUT] Inisde the Tick data
12:21:25,765 INFO [STDOUT] db in end :EURNZD,04/04/2008,06:40:39,1.9983,1.9983,
1.9993,T
12:21:25,765 INFO [STDOUT] Error in updateMessageToDB :java.sql.BatchUpdateExce
ption: Batch entry 0 insert into tick(itemcode,date,time,last_price,bid_price,as
k_price)values(EURNZD,04/04/2008,06:40:39,1.9983,1.9983,1.9993) was aborted. Ca
ll getNextException to see the cause.
12:21:25,890 INFO [STDOUT] Inside the onMessage method
12:21:25,890 INFO [STDOUT] setdata
12:21:25,890 INFO [STDOUT] Inside the startmethod()
12:21:25,890 INFO [STDOUT] Inisde the updateMessageToDB method in the DBOperati
on[EURGBP,04/04/2008,06:40:39,0.7847,0.7847,0.7854,T]
12:21:25,890 INFO [STDOUT] record size :1
12:21:25,906 INFO [STDOUT] Inisde the Tick data
12:21:25,906 INFO [STDOUT] db in end :EURGBP,04/04/2008,06:40:39,0.7847,0.7847,
0.7854,T
12:21:25,906 INFO [STDOUT] Error in updateMessageToDB :java.sql.BatchUpdateException: Batch entry 0 insert into tick(itemcode,date,time,last_price,bid_price,as
k_price)values(EURGBP,04/04/2008,06:40:39,0.7847,0.7847,0.7854) was aborted. Ca
ll getNextException to see the cause.
class code
public void updateMessageToDB(Vector dataclone)
{
System.out.println("Inisde the updateMessageToDB method in the DBOperation" +dataclone);
PreparedStatement tick_pstmt=null;
PreparedStatement onemin_pstmt=null;
PreparedStatement fivemin_pstmt=null;
PreparedStatement tenmin_pstmt=null;
PreparedStatement thirtymin_pstmt=null;
PreparedStatement hourly_pstmt=null;
PreparedStatement daily_pstmt=null;
PreparedStatement weekly_pstmt=null;
dbcon=getDBConnection();
//System.out.println("after creating the connection");
synchronized (dataclone)
{
try
{
System.out.println("record size :"+dataclone.size());
dbcon.setAutoCommit(false);
tick_pstmt=dbcon.prepareStatement("insert into tick(itemcode,date,time,last_price,bid_price,ask_price)values(?,?,?,?,?,?)");
onemin_pstmt=dbcon.prepareStatement("insert into one_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
fivemin_pstmt=dbcon.prepareStatement("insert into five_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
tenmin_pstmt=dbcon.prepareStatement("insert into ten_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
thirtymin_pstmt=dbcon.prepareStatement("insert into thirty_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
hourly_pstmt=dbcon.prepareStatement("insert into one_hour(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
daily_pstmt=dbcon.prepareStatement("insert into one_day(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
weekly_pstmt=dbcon.prepareStatement("insert into one_week(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
for(int i=0;i<dataclone.size();i++)
{
//String[] pricelist=((TextMessage)msglist.get(i)).getText().split(",");
String[] pricelist=((String)dataclone.get(i)).split(",");
if(pricelist.length==8)
{
//System.out.println("db in start :"+pricelist[0]+","+pricelist[1]+","+pricelist[2]+","+pricelist[3]+","+pricelist[4]+","+pricelist[5]+","+pricelist[6]+","+pricelist[7]);
String itm_name=pricelist[0];
String itm_time=pricelist[1];
String itm_date=pricelist[2];
String itm_open=pricelist[3];
String itm_high=pricelist[4];
String itm_low=pricelist[5];
String itm_close=pricelist[6];
String itm_type=pricelist[7];
if(itm_type.equals("O"))
{
System.out.println("Inside the one munite data in the DBOperation ");
onemin_pstmt.setString(1 ,itm_name);
onemin_pstmt.setString(2 ,itm_date);
onemin_pstmt.setString(3 ,itm_time);
onemin_pstmt.setString(4 ,itm_open);
onemin_pstmt.setString(5 ,itm_high);
onemin_pstmt.setString(6 ,itm_low);
onemin_pstmt.setString(7 ,itm_close);
//int onemin=onemin_pstmt.executeUpdate();
onemin_pstmt.addBatch();
}
else if(itm_type.equals("5"))
{
System.out.println("Inside the Five munite data in the DBOperation");
//System.out.println(itm_name);
fivemin_pstmt.setString(1 ,itm_name);
fivemin_pstmt.setString(2 ,itm_date);
fivemin_pstmt.setString(3 ,itm_time);
fivemin_pstmt.setString(4 ,itm_open);
fivemin_pstmt.setString(5 ,itm_high);
fivemin_pstmt.setString(6 ,itm_low);
fivemin_pstmt.setString(7 ,itm_close);
//int fivemin=fivemin_pstmt.executeUpdate();
fivemin_pstmt.addBatch();
}
else if(itm_type.equals("1"))
{
System.out.println("Inside the ten munite data in the DBOperation");
tenmin_pstmt.setString(1 ,itm_name);
tenmin_pstmt.setString(2 ,itm_date);
tenmin_pstmt.setString(3 ,itm_time);
tenmin_pstmt.setString(4 ,itm_open);
tenmin_pstmt.setString(5 ,itm_high);
tenmin_pstmt.setString(6 ,itm_low);
tenmin_pstmt.setString(7 ,itm_close);
//int tenmin=tenmin_pstmt.executeUpdate();
tenmin_pstmt.addBatch();
}
else if(itm_type.equals("3"))
{
System.out.println("Inside the Thirty munite Data in the DBOperation");
thirtymin_pstmt.setString(1 ,itm_name);
thirtymin_pstmt.setString(2 ,itm_date);
thirtymin_pstmt.setString(3 ,itm_time);
thirtymin_pstmt.setString(4 ,itm_open);
thirtymin_pstmt.setString(5 ,itm_high);
thirtymin_pstmt.setString(6 ,itm_low);
thirtymin_pstmt.setString(7 ,itm_close);
//int thirtymin=thirtymin_pstmt.executeUpdate();
thirtymin_pstmt.addBatch();
}
else if(itm_type.equals("H"))
{
System.out.println("Inside One Hour Data in the DBOperation");
hourly_pstmt.setString(1 ,itm_name);
hourly_pstmt.setString(2 ,itm_date);
hourly_pstmt.setString(3 ,itm_time);
hourly_pstmt.setString(4 ,itm_open);
hourly_pstmt.setString(5 ,itm_high);
hourly_pstmt.setString(6 ,itm_low);
hourly_pstmt.setString(7 ,itm_close);
int hourly=hourly_pstmt.executeUpdate();
//hourly_pstmt.addBatch();
}
else if(itm_type.equals("D"))
{
System.out.println("Inside the One Day Data in the DBOperation");
daily_pstmt.setString(1 ,itm_name);
daily_pstmt.setString(2 ,itm_date);
daily_pstmt.setString(3 ,itm_time);
daily_pstmt.setString(4 ,itm_open);
daily_pstmt.setString(5 ,itm_high);
daily_pstmt.setString(6 ,itm_low);
daily_pstmt.setString(7 ,itm_close);
//int daily=daily_pstmt.executeUpdate();
daily_pstmt.addBatch();
}
else if(itm_type.equals("W"))
{
System.out.println("Inside the One week data");
weekly_pstmt.setString(1 ,itm_name);
weekly_pstmt.setString(2 ,itm_date);
weekly_pstmt.setString(3 ,itm_time);
weekly_pstmt.setString(4 ,itm_open);
weekly_pstmt.setString(5 ,itm_high);
weekly_pstmt.setString(6 ,itm_low);
weekly_pstmt.setString(7 ,itm_close);
//int weekly=weekly_pstmt.executeUpdate();
weekly_pstmt.addBatch();
}
}
else
{
//tick updation
System.out.println("Inisde the Tick data");
String itm_name=pricelist[0];
String itm_date=pricelist[1];
String itm_time=pricelist[2];
String itm_last=pricelist[3];
String itm_bid=pricelist[4];
String itm_ask=pricelist[5];
System.out.println("db in end :"+itm_name+","+itm_date+","+itm_time+","+itm_last+","+itm_bid+","+itm_ask+","+pricelist[6]);
tick_pstmt.setString(1 ,itm_name);
tick_pstmt.setString(2 ,itm_date);
tick_pstmt.setString(3 ,itm_time);
tick_pstmt.setString(4 ,itm_last);
tick_pstmt.setString(5 ,itm_bid);
tick_pstmt.setString(6 ,itm_ask);
//int tick=tick_pstmt.executeUpdate();
tick_pstmt.addBatch();
int tick_result[]=tick_pstmt.executeBatch();
}
int tick_result[]=tick_pstmt.executeBatch();
int onemin_result[] = onemin_pstmt.executeBatch();
int fivmin_result[] = fivemin_pstmt.executeBatch();
int tenmin_result[] = tenmin_pstmt.executeBatch();
int thirtymin_result[] = thirtymin_pstmt.executeBatch();
int onehour_result[] = hourly_pstmt.executeBatch();
int daily_result[] = daily_pstmt.executeBatch();
int weekly_result[] = weekly_pstmt.executeBatch();
tick_pstmt.clearBatch();
onemin_pstmt.clearBatch();
fivemin_pstmt.clearBatch();
tenmin_pstmt.clearBatch();
thirtymin_pstmt.clearBatch();
hourly_pstmt.clearBatch();
daily_pstmt.clearBatch();
weekly_pstmt.clearBatch();
//UpdateionStatus=false;
//System.out.println(((TextMessage)msglist.get(i)).getText());
}
newinstance=true;
dbcon.setAutoCommit(true);
//System.out.println("Db Updation completed");
tick_pstmt.close();
onemin_pstmt.close();
fivemin_pstmt.close();
tenmin_pstmt.close();
thirtymin_pstmt.close();
hourly_pstmt.close();
daily_pstmt.close();
weekly_pstmt.close();
dbcon.close();
}
catch(Exception e)
{
try
{
//tick_pstmt.close();
//onemin_pstmt.close();
//fivemin_pstmt.close();
//tenmin_pstmt.close();
//thirtymin_pstmt.close();
//hourly_pstmt.close();
//daily_pstmt.close();
//weekly_pstmt.close();
}
catch (Exception E)
{
System.out.println("Exception while closing the connection"+E.getClass());
}
newinstance=true;
System.out.println("Error in updateMessageToDB :"+e);
}
please help me out of this.
thanks and regards
Naresh
I am using EJB Message Driven bean datas were received in very fastest way(per min 200 msg) in the onMessage() in bean call.I am adding the msgs to the vector and passed to the other class.That class is doing all the database Operation.for this scenario i am using PreparedStatement and batchUpdation.In this class i am getting BatchException.I have attched the error log here and class code also.
this is the error lo
12:21:25,765 INFO [STDOUT] record size :1
12:21:25,765 INFO [STDOUT] Inisde the Tick data
12:21:25,765 INFO [STDOUT] db in end :EURNZD,04/04/2008,06:40:39,1.9983,1.9983,
1.9993,T
12:21:25,765 INFO [STDOUT] Error in updateMessageToDB :java.sql.BatchUpdateExce
ption: Batch entry 0 insert into tick(itemcode,date,time,last_price,bid_price,as
k_price)values(EURNZD,04/04/2008,06:40:39,1.9983,1.9983,1.9993) was aborted. Ca
ll getNextException to see the cause.
12:21:25,890 INFO [STDOUT] Inside the onMessage method
12:21:25,890 INFO [STDOUT] setdata
12:21:25,890 INFO [STDOUT] Inside the startmethod()
12:21:25,890 INFO [STDOUT] Inisde the updateMessageToDB method in the DBOperati
on[EURGBP,04/04/2008,06:40:39,0.7847,0.7847,0.7854,T]
12:21:25,890 INFO [STDOUT] record size :1
12:21:25,906 INFO [STDOUT] Inisde the Tick data
12:21:25,906 INFO [STDOUT] db in end :EURGBP,04/04/2008,06:40:39,0.7847,0.7847,
0.7854,T
12:21:25,906 INFO [STDOUT] Error in updateMessageToDB :java.sql.BatchUpdateException: Batch entry 0 insert into tick(itemcode,date,time,last_price,bid_price,as
k_price)values(EURGBP,04/04/2008,06:40:39,0.7847,0.7847,0.7854) was aborted. Ca
ll getNextException to see the cause.
class code
public void updateMessageToDB(Vector dataclone)
{
System.out.println("Inisde the updateMessageToDB method in the DBOperation" +dataclone);
PreparedStatement tick_pstmt=null;
PreparedStatement onemin_pstmt=null;
PreparedStatement fivemin_pstmt=null;
PreparedStatement tenmin_pstmt=null;
PreparedStatement thirtymin_pstmt=null;
PreparedStatement hourly_pstmt=null;
PreparedStatement daily_pstmt=null;
PreparedStatement weekly_pstmt=null;
dbcon=getDBConnection();
//System.out.println("after creating the connection");
synchronized (dataclone)
{
try
{
System.out.println("record size :"+dataclone.size());
dbcon.setAutoCommit(false);
tick_pstmt=dbcon.prepareStatement("insert into tick(itemcode,date,time,last_price,bid_price,ask_price)values(?,?,?,?,?,?)");
onemin_pstmt=dbcon.prepareStatement("insert into one_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
fivemin_pstmt=dbcon.prepareStatement("insert into five_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
tenmin_pstmt=dbcon.prepareStatement("insert into ten_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
thirtymin_pstmt=dbcon.prepareStatement("insert into thirty_minute(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
hourly_pstmt=dbcon.prepareStatement("insert into one_hour(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
daily_pstmt=dbcon.prepareStatement("insert into one_day(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
weekly_pstmt=dbcon.prepareStatement("insert into one_week(itemcode,date,time,open_price,high_price,low_price,close_price)values(?,?,?,?,?,?,?)");
for(int i=0;i<dataclone.size();i++)
{
//String[] pricelist=((TextMessage)msglist.get(i)).getText().split(",");
String[] pricelist=((String)dataclone.get(i)).split(",");
if(pricelist.length==8)
{
//System.out.println("db in start :"+pricelist[0]+","+pricelist[1]+","+pricelist[2]+","+pricelist[3]+","+pricelist[4]+","+pricelist[5]+","+pricelist[6]+","+pricelist[7]);
String itm_name=pricelist[0];
String itm_time=pricelist[1];
String itm_date=pricelist[2];
String itm_open=pricelist[3];
String itm_high=pricelist[4];
String itm_low=pricelist[5];
String itm_close=pricelist[6];
String itm_type=pricelist[7];
if(itm_type.equals("O"))
{
System.out.println("Inside the one munite data in the DBOperation ");
onemin_pstmt.setString(1 ,itm_name);
onemin_pstmt.setString(2 ,itm_date);
onemin_pstmt.setString(3 ,itm_time);
onemin_pstmt.setString(4 ,itm_open);
onemin_pstmt.setString(5 ,itm_high);
onemin_pstmt.setString(6 ,itm_low);
onemin_pstmt.setString(7 ,itm_close);
//int onemin=onemin_pstmt.executeUpdate();
onemin_pstmt.addBatch();
}
else if(itm_type.equals("5"))
{
System.out.println("Inside the Five munite data in the DBOperation");
//System.out.println(itm_name);
fivemin_pstmt.setString(1 ,itm_name);
fivemin_pstmt.setString(2 ,itm_date);
fivemin_pstmt.setString(3 ,itm_time);
fivemin_pstmt.setString(4 ,itm_open);
fivemin_pstmt.setString(5 ,itm_high);
fivemin_pstmt.setString(6 ,itm_low);
fivemin_pstmt.setString(7 ,itm_close);
//int fivemin=fivemin_pstmt.executeUpdate();
fivemin_pstmt.addBatch();
}
else if(itm_type.equals("1"))
{
System.out.println("Inside the ten munite data in the DBOperation");
tenmin_pstmt.setString(1 ,itm_name);
tenmin_pstmt.setString(2 ,itm_date);
tenmin_pstmt.setString(3 ,itm_time);
tenmin_pstmt.setString(4 ,itm_open);
tenmin_pstmt.setString(5 ,itm_high);
tenmin_pstmt.setString(6 ,itm_low);
tenmin_pstmt.setString(7 ,itm_close);
//int tenmin=tenmin_pstmt.executeUpdate();
tenmin_pstmt.addBatch();
}
else if(itm_type.equals("3"))
{
System.out.println("Inside the Thirty munite Data in the DBOperation");
thirtymin_pstmt.setString(1 ,itm_name);
thirtymin_pstmt.setString(2 ,itm_date);
thirtymin_pstmt.setString(3 ,itm_time);
thirtymin_pstmt.setString(4 ,itm_open);
thirtymin_pstmt.setString(5 ,itm_high);
thirtymin_pstmt.setString(6 ,itm_low);
thirtymin_pstmt.setString(7 ,itm_close);
//int thirtymin=thirtymin_pstmt.executeUpdate();
thirtymin_pstmt.addBatch();
}
else if(itm_type.equals("H"))
{
System.out.println("Inside One Hour Data in the DBOperation");
hourly_pstmt.setString(1 ,itm_name);
hourly_pstmt.setString(2 ,itm_date);
hourly_pstmt.setString(3 ,itm_time);
hourly_pstmt.setString(4 ,itm_open);
hourly_pstmt.setString(5 ,itm_high);
hourly_pstmt.setString(6 ,itm_low);
hourly_pstmt.setString(7 ,itm_close);
int hourly=hourly_pstmt.executeUpdate();
//hourly_pstmt.addBatch();
}
else if(itm_type.equals("D"))
{
System.out.println("Inside the One Day Data in the DBOperation");
daily_pstmt.setString(1 ,itm_name);
daily_pstmt.setString(2 ,itm_date);
daily_pstmt.setString(3 ,itm_time);
daily_pstmt.setString(4 ,itm_open);
daily_pstmt.setString(5 ,itm_high);
daily_pstmt.setString(6 ,itm_low);
daily_pstmt.setString(7 ,itm_close);
//int daily=daily_pstmt.executeUpdate();
daily_pstmt.addBatch();
}
else if(itm_type.equals("W"))
{
System.out.println("Inside the One week data");
weekly_pstmt.setString(1 ,itm_name);
weekly_pstmt.setString(2 ,itm_date);
weekly_pstmt.setString(3 ,itm_time);
weekly_pstmt.setString(4 ,itm_open);
weekly_pstmt.setString(5 ,itm_high);
weekly_pstmt.setString(6 ,itm_low);
weekly_pstmt.setString(7 ,itm_close);
//int weekly=weekly_pstmt.executeUpdate();
weekly_pstmt.addBatch();
}
}
else
{
//tick updation
System.out.println("Inisde the Tick data");
String itm_name=pricelist[0];
String itm_date=pricelist[1];
String itm_time=pricelist[2];
String itm_last=pricelist[3];
String itm_bid=pricelist[4];
String itm_ask=pricelist[5];
System.out.println("db in end :"+itm_name+","+itm_date+","+itm_time+","+itm_last+","+itm_bid+","+itm_ask+","+pricelist[6]);
tick_pstmt.setString(1 ,itm_name);
tick_pstmt.setString(2 ,itm_date);
tick_pstmt.setString(3 ,itm_time);
tick_pstmt.setString(4 ,itm_last);
tick_pstmt.setString(5 ,itm_bid);
tick_pstmt.setString(6 ,itm_ask);
//int tick=tick_pstmt.executeUpdate();
tick_pstmt.addBatch();
int tick_result[]=tick_pstmt.executeBatch();
}
int tick_result[]=tick_pstmt.executeBatch();
int onemin_result[] = onemin_pstmt.executeBatch();
int fivmin_result[] = fivemin_pstmt.executeBatch();
int tenmin_result[] = tenmin_pstmt.executeBatch();
int thirtymin_result[] = thirtymin_pstmt.executeBatch();
int onehour_result[] = hourly_pstmt.executeBatch();
int daily_result[] = daily_pstmt.executeBatch();
int weekly_result[] = weekly_pstmt.executeBatch();
tick_pstmt.clearBatch();
onemin_pstmt.clearBatch();
fivemin_pstmt.clearBatch();
tenmin_pstmt.clearBatch();
thirtymin_pstmt.clearBatch();
hourly_pstmt.clearBatch();
daily_pstmt.clearBatch();
weekly_pstmt.clearBatch();
//UpdateionStatus=false;
//System.out.println(((TextMessage)msglist.get(i)).getText());
}
newinstance=true;
dbcon.setAutoCommit(true);
//System.out.println("Db Updation completed");
tick_pstmt.close();
onemin_pstmt.close();
fivemin_pstmt.close();
tenmin_pstmt.close();
thirtymin_pstmt.close();
hourly_pstmt.close();
daily_pstmt.close();
weekly_pstmt.close();
dbcon.close();
}
catch(Exception e)
{
try
{
//tick_pstmt.close();
//onemin_pstmt.close();
//fivemin_pstmt.close();
//tenmin_pstmt.close();
//thirtymin_pstmt.close();
//hourly_pstmt.close();
//daily_pstmt.close();
//weekly_pstmt.close();
}
catch (Exception E)
{
System.out.println("Exception while closing the connection"+E.getClass());
}
newinstance=true;
System.out.println("Error in updateMessageToDB :"+e);
}
please help me out of this.
thanks and regards
Naresh