From PTAGISWiki
Using Weblogic rowset object
IMPORT
import weblogic.jdbc.rowset.WLCachedRowSet;
INITIALIZE
public void initialize(String sPropsFilePath, boolean bForceRefresh)
throws ClassNotFoundException, FileNotFoundException, IOException {
// / initializes the DBConnection if needed
boolean bIsNew = (m_oDBConnection == null);
if (bIsNew) {
m_oDBConnection = new DBConnection();
}
synchronized (m_oDBConnection) {
if (bIsNew || bForceRefresh) {
System.out.println("Initializing the queryRunner class from \""
+ sPropsFilePath + "\".");
m_oDBConnection.initialize(sPropsFilePath, bForceRefresh);
}
}
}
SELECT
WLCachedRowSet oRowSet = m_oDBConnection.newCachedRowSetB();
String sCmd = "SELECT sql from wqb_query_details WHERE userid = ? and report_name = ?";
oRowSet.setCommand(sCmd);
oRowSet.setString(1, user);
oRowSet.setString(2, reportname);
oRowSet.execute();
oRowSet.next();
sSql = oRowSet.getString(1).trim();
INSERT
WLCachedRowSet oRowSet = m_oDBConnection.newCachedRowSetB();
sCmd = "INSERT INTO wqb_query_history " +
"(userid, report_name, start_time, end_time, status, sql) " +
"VALUES (?, ?, ?, ?, ?, ?)";
oRowSet.setCommand(sCmd);
oRowSet.setString(1, logrow.getUser());
oRowSet.setString(2, logrow.getRepname());
oRowSet.setTimestamp(3, logrow.getStart());
oRowSet.setTimestamp(4, null);
oRowSet.setString(5, "started");
oRowSet.setString(6, logrow.getSql());
oRowSet.execute();
UPDATE
WLCachedRowSet oRowSet = m_oDBConnection.newCachedRowSetB();
String sCmd = "UPDATE wqb_query_details SET start_time = ?, end_time = ?, status = ? " +
"WHERE userid = ? and report_name = ?";
oRowSet.setCommand(sCmd);
oRowSet.setTimestamp(1, logrow.getStart());
oRowSet.setTimestamp(2, null);
oRowSet.setString(3, "started");
oRowSet.setString(4, logrow.getUser());
oRowSet.setString(5, logrow.getRepname());
oRowSet.execute();
Using vanilla Java
INITIALIZE
public void initialize(Connection conn) {
try {
System.out.println("Loading the driver "+ DRIVER);
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
SELECT
String sCmd = "SELECT t1.report_name, t2.status, t2.row_count, "+
"t2.datasource, t1.start_time, t2.end_time, t2.modified "+
"FROM wqb_max_query_details t1, wqb_query_details t2 "+
"WHERE t1.report_name = t2.report_name and t1.start_time = t2.start_time "+
"and t1.userid='rday'";
//String sCmd = "SELECT * from wqb_query_details where userid = '" +
//sUser + "' and report_name = '" + sReport + "'";
try {
Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sCmd);
while (rs.next()) {
System.out.println("a row looks like: "+
rs.getString("report_name").trim() + " , " +
rs.getString("status").trim() + " , " +
rs.getTimestamp("start_time").toString() + " , " +
rs.getTimestamp("end_time").toString() + " , " +
rs.getString("status").trim() + " , " +
rs.getInt("row_count") + " , " +
rs.getString("datasource").trim());
}
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
INSERT
try {
Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
Statement stmt = conn.createStatement();
String sCmd = "INSERT INTO wqb_query_details (userid, report_name, start_time, status, row_count, sql)"
+ " VALUES ('" + logrow.getUser() + "', "
+ "'" + logrow.getRepname() + "', "
+ "{ts '" + logrow.getStart() + "'}, "
+ "'started', "
+ "'0', "
+ "'" + logrow.getSql() + "')";
System.out.println(sCmd);
int val = stmt.executeUpdate(sCmd);
System.out.println(val + " rows affected.");
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
UPDATE
try {
Connection conn = DriverManager.getConnection(URL,USER,PASSWORD);
Statement stmt = conn.createStatement();
String sCmd = "UPDATE wqb_query_details SET end_time = '"
+ logrow.getEnd()
+ "', status = 'not completed', row_count = '" + logrow.getRowCount()
+ "' WHERE userid = '" + logrow.getUser()
+ "' and report_name = '" + logrow.getRepname()
//+ "' and start_time = {ts '" + logrow.getStart() + "'}";
+ "' and start_time = timestamp '" + logrow.getStart() + "'";
System.out.println("trying to match with these values:");
System.out.println(sCmd);
System.out.println(logrow.getUser());
System.out.println(logrow.getRepname());
System.out.println(start);
int val = stmt.executeUpdate(sCmd);
stmt.close();
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
