import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Properties;
import java.util.Timer;
import java.util.TimerTask;
import javax.mail.Message;
import javax.mail.Session;
import javax.mail.Transport;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeMessage;
public class ReplicationErrorFixer extends TimerTask {
static String sql = "SELECT count(1)as COUNT "
+ "from dbo.product p,"
+ " dbo.product_price pp,v$instance vi "
+ "where p.project_number=pp.project_number and pp.country_code=pp.country_sold_in "
+ "and pp.country_code='US' and p.isbn in ('0077224183')";
static Connection conn;
static PreparedStatement ps;
static ResultSet rs;
static Timer timer = new Timer();
public static void main(String[] args) {
System.out.println("in main...");
preapareDBParams();
TimerTask tt = new ReplicationErrorFixer();
timer.schedule(tt, 0, 5 * 60 * 1000);
}
public void run() {
System.out.println("in run ... " + new Date());
try {
rs = ps.executeQuery();
while (rs.next()) {
int numberOfRecords = new Integer(rs.getString("COUNT"))
.intValue();
System.out.println("No of records = " + numberOfRecords);
if (numberOfRecords == 1) {
sendMail("1 records found. Trying to insert new record.");
boolean insSuc = insertRecord();
if (insSuc) {
timer.cancel();
closeObjects();
}
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
public static void preapareDBParams() {
System.out.println("in preapareDBParams ...");
try {
Class.forName("oracle.jdbc.OracleDriver");
conn = DriverManager.getConnection(
"jdbc:oracle:thin:@152.159.46.57:1521:prod", "scsingh",
"sh!v1234");
ps = conn.prepareStatement(sql);
} catch (SQLException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public void sendMail(String str) {
try {
System.out.println("will send a mail with content: " + str);
Properties props = new Properties();
props.put("mail.smtp.host", "205.246.29.36");
Session s = Session.getInstance(props, null);
InternetAddress from = new InternetAddress("mail@mail.com");
InternetAddress to = new InternetAddress("shivsi@rsgsystems.com");
MimeMessage message = new MimeMessage(s);
message.setFrom(from);
message.addRecipient(Message.RecipientType.TO, to);
message.setSubject(str);
message.setText(str + new Date());
Transport.send(message);
} catch (Exception e) {
e.printStackTrace();
}
}
public boolean insertRecord() {
String insStr = "INSERT INTO dbo.product_price@product.world"
+ " VALUES ('US',1144057,'NET','USD','MHHE',3.5,Sysdate,'A','Manual_ALEKS_Price_Update',Sysdate,'US')";
try {
PreparedStatement insstmt = conn.prepareStatement(insStr);
insstmt.execute();
conn.commit();
sendMail("Insert was successful.");
return true;
} catch (SQLException e) {
sendMail("Insert was unsuccessful.");
e.printStackTrace();
return false;
}
}
public void closeObjects() {
try {
conn.close();
ps.close();
rs.close();
System.exit(1);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
