I have a program that get data from MYSQL DB and send to SAP using JAVA RFC. Then write data back from SAP to MYSQL DB. I am using TimerTask and thread will run every 4 hours. Problem is thread is running great for the first time. But I am getting a communications link failure error since the second run every 4 hours. I think, I am correctly close the connections also.
This is my code.
public class CreatePO extends TimerTask {
private Object[][] itemData; // Object for table data
RFCHandler handler;
public CreatePO() {
handler = new RFCHandler();
}
@Override
public void run() {
// TODO Auto-generated method stub
try {
getItemFromDB();
sendDataToSap();
} catch (Exception e) {
e.printStackTrace();
}
}
private void getItemFromDB() {
// TODO Auto-generated method stub
Connection connection = null;
DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date date = new Date();
System.out.println(dateFormat.format(date));
String queryOne = "SELECT reference_no, vendor_acc_no, date, mat_no,"
+ "po_qty, po_unit_measure, rate, order_price_unit, plant, user_name, email_id "
+ "FROM tbl_po_data WHERE status <> 'X'";
try {
pooler = DBPool_POSMS.getInstance();
dataSource1 = pooler.getDataSource();
} catch (Exception e1) {
e1.printStackTrace();
}
try {
connection = dataSource1.getConnection();
connection.setAutoCommit(false);
Statement st = connection.createStatement();
ResultSet rs = st.executeQuery(queryOne);
int lineitem = 0;
try {
rs.last();
rcount = rs.getRow();
rs.beforeFirst();
} catch (Exception ex) {
ex.printStackTrace();
}
itemData = new Object[11][rcount];
while (rs.next()) {
itemData[0][lineitem] = rs.getString("reference_no");
itemData[1][lineitem] = rs.getString("vendor_acc_no");
itemData[2][lineitem] = rs.getDate("date");
lineitem = lineitem + 1;
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void sendDataToSap() {
System.out.println("Send Data to Sap");
Table IT_LIST = null;
Table IT_RESPONSE = null;
try {
if (rcount > 0) {
handler.createRFCFunction("ZSL_SMS_PO_CREATION");
IT_LIST = handler.getTablePara("IT_LIST");
for (int x = 0; x < rcount; x++) {
IT_LIST.appendRow();
IT_LIST.setValue(itemData[0][x], "REFNO");
IT_LIST.setValue(itemData[1][x], "LIFNR");
IT_LIST.setValue(itemData[2][x], "EBDAT");
}
System.out.println(IT_LIST);
handler.excFunction();
IT_RESPONSE = handler.getTablePara("IT_RESPONSE");
handler.releaseClient();
int int_row = IT_RESPONSE.getNumRows();
System.out.println("int_row " + int_row);
if (int_row > 0) {
this.tableOparator(IT_RESPONSE);
}
}
} catch (Exception e) {
// TODO: handle exception
handler.releaseClient();
e.printStackTrace();
}
finally {
rcount = 0;
}
}
private void tableOparator(Table table) throws Exception {
pooler = DBPool_POSMS.getInstance();
dataSource = pooler.getDataSource();
Connection con = dataSource.getConnection();
con.setAutoCommit(false);
qex = new DBTableQueryExcecutre(con);
StringBuilder sbQuery = new StringBuilder("INSERT INTO tbl_created_po (reference_no,po_no) VALUES");
System.out.println("COL -->" + table.getNumRows() + " records to insert for tbl_created_po");
// --- create query ---------------------------------
for (int i = 0; i < table.getNumRows(); i++) {
// table.setRow(i);
sbQuery.append("(?,?),");
}
sbQuery.deleteCharAt(sbQuery.length() - 1);
sbQuery.append(";");
qex.setUpdateQuery(sbQuery.toString());
elements.clear();
for (int i = 0; i < table.getNumRows(); i++) {
table.setRow(i);
elements.add(table.getString("REFNO").trim());
elements.add(table.getString("EBELN").trim());
}
qex.updateInsertQuery(elements);
con.commit();
for (int i = 0; i < table.getNumRows(); i++) {
table.setRow(i);
String sbQuery2 = "update tbl_po_data set status = 'X', po_no = '" + table.getString("EBELN").trim()
+ "' where reference_no = '" + table.getString("REFNO").trim() + "';";
int rcount = qex.runQuery(sbQuery2);
con.commit();
System.out.println("tbl_po_data Rows --> " + (i + rcount) + " Status Updated");
}
qex.closeConnections();
con.close();
System.out.println("Cycle Finished....");
System.out.println("Connection Closed");
}
}
My LogCat.....
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet successfully received from the server was 14,391,063 milliseconds ago. The last packet sent successfully to the server was 14,391,344 milliseconds ago.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3871)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5333)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
at CreatePO.getItemFromDB(CreatePO.java:84)
at CreatePO.run(CreatePO.java:52)
at java.util.TimerThread.mainLoop(Timer.java:555)
at java.util.TimerThread.run(Timer.java:505)
Caused by: java.net.SocketException: Connection reset by peer: socket write error
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3852)
... 10 more
