public static void failJobs(Connection conn) throws SQLException {
String method = "failJobs";
int location = 1000;
try {
Statement stmt = conn.createStatement();
String strSQL =
"INSERT INTO os.ao_queue (queue_id, status, queue_date, start_date, end_date, "
+ "error_message, num_rows, id, refresh_type, target_schema_name, target_table_name, target_append_only, "
+ "target_compressed, target_row_orientation, source_type, source_server_name, source_instance_name, "
+ "source_port, source_database_name, source_schema_name, source_table_name, source_user_name, "
+ "source_pass, column_name, sql_text, snapshot) "
+ "SELECT queue_id, 'failed' as status, queue_date, start_date, now() as end_date, "
+ "'Outsourcer stop requested' as error_message, num_rows, id, refresh_type, target_schema_name, "
+ "target_table_name, target_append_only, target_compressed, target_row_orientation, source_type, "
+ "source_server_name, source_instance_name, source_port, source_database_name, source_schema_name, "
+ "source_table_name, source_user_name, source_pass, column_name, sql_text, snapshot "
+ "FROM os.queue WHERE status = 'queued'";
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void recieveTrafficRates(String IP, float upload, float download) {
System.out.println("Insertion of Data into Database!");
Connection con = null;
String url = "jdbc:mysql://localhost:3306/";
String dbName = "traffic_report";
String driverName = "com.mysql.jdbc.Driver";
String userName = "******";
String password = "******";
String IP_address = IP;
float up = upload, down = download;
try {
Class.forName(driverName).newInstance();
con = DriverManager.getConnection(url + dbName, userName, password);
try {
Statement st = con.createStatement();
int val =
st.executeUpdate(
"INSERT current_traffic VALUES('" + IP + "','" + up + "','" + down + "')");
System.out.println("Data Entry process successfully!");
} catch (SQLException s) {
System.out.println("Table all ready exists!" + s);
}
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
public static String getVariable(Connection conn, String name) throws SQLException {
String method = "getVariable";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL = "SELECT os.fn_get_variable('" + name + "')";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
/* to search the DB table for req. video*/
boolean search(String file) {
boolean found = false;
Connection con = null;
String url =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";
try {
con = DriverManager.getConnection(url, "", "");
try {
Statement st = con.createStatement();
ResultSet rs = st.executeQuery("SELECT video FROM p3");
while (rs.next()) {
String colvalue = rs.getString("video");
if (colvalue.equalsIgnoreCase(file)) found = true;
}
} catch (SQLException s) {
System.out.println("SQL statement is not executed!");
}
} catch (Exception e) {
System.out.println(e);
}
return found;
}
public static void main(String args[]) {
int myport = 9090;
Socket s;
ServerSocket ss;
BufferedReader fromSoc, fromKbd;
PrintStream toSoc;
String line, msg;
try {
s = new Socket("169.254.63.10", 6016);
System.out.println("enter line");
System.out.println("connected");
toSoc = new PrintStream(s.getOutputStream());
toSoc.println(myport);
String url =
"jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)}; DBQ=D://db.mdb; DriverID=22;READONLY=true;";
Connection con = DriverManager.getConnection(url, "", "");
Statement st = con.createStatement();
String ip = "169.254.63.10";
int port = 6016;
String video = "rooney";
st.executeUpdate(
" insert into p3 values('" + ip + "' , '" + port + "' , '" + video + "' ); ");
fun();
} catch (Exception e) {
System.out.println("exception" + e);
}
}
public static String getVersion(Connection conn) throws SQLException {
String method = "getVersion";
int location = 1000;
try {
location = 2000;
String value = "";
location = 2100;
Statement stmt = conn.createStatement();
String strSQL =
"SELECT CASE "
+ "WHEN POSITION ('HAWQ 2.0.1' in version) > 0 THEN 'HAWQ_2_0_1' "
+ "WHEN POSITION ('HAWQ 2.0.0' in version) > 0 THEN 'HAWQ_2_0_0' "
+ "WHEN POSITION ('HAWQ 1' in version) > 0 THEN 'HAWQ_1' "
+ "WHEN POSITION ('HAWQ' in version) = 0 AND POSITION ('Greenplum Database' IN version) > 0 THEN 'GPDB' "
+ "ELSE 'OTHER' END "
+ "FROM version()";
if (debug) Logger.printMsg("Getting Variable: " + strSQL);
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
value = rs.getString(1);
}
location = 2300;
return value;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void customStartAll(Connection conn) throws SQLException {
String method = "customStartAll";
int location = 1000;
try {
Statement stmt = conn.createStatement();
int id = 0;
int gpfdistPort = 0;
String strSQL = "SELECT id\n";
strSQL += "FROM os.custom_sql";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
id = rs.getInt(1);
gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);
strSQL = "INSERT INTO os.ao_custom_sql\n";
strSQL +=
"(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
strSQL +=
"SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
+ gpfdistPort
+ "\n";
strSQL += "FROM os.custom_sql\n";
strSQL += "WHERE id = " + id;
stmt.executeUpdate(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
static void connectDb() {
// before: localhost/rsc
sqlURL = "jdbc:postgresql://" + sqlURL;
// after: "jdbc:postgresql://localhost/rsc";
// "jdbc:postgresql://webbie.berkeley.intel-research.net/rsc"
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException cnfe) {
System.out.println("Couldn't find the driver!");
System.out.println("Let's print a stack trace, and exit.");
System.exit(1);
}
Connection connection = null;
try {
connection = DriverManager.getConnection(sqlURL, sqlUser, sqlPassword);
query = connection.createStatement();
} catch (SQLException se) {
System.out.println("Couldn't connect: " + se);
System.exit(1);
}
}
public static void emailAlert(Connection conn, String errorMsg) throws SQLException {
String method = "emailAlert";
int location = 1000;
try {
Statement stmt = conn.createStatement();
String strSQL = "SELECT gp_elog('" + errorMsg + "',true)";
ResultSet rs = stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static int insertReplTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "insertReplTable";
int location = 1000;
int numRows = 0;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String externalTable = getExternalTableName(replTargetSchema, replTargetTable);
location = 2305;
// truncate the stage table before loading
truncateTable(conn, replTargetSchema, replTargetTable);
location = 2400;
Statement stmt = conn.createStatement();
location = 2500;
String strSQL =
"INSERT INTO \""
+ replTargetSchema
+ "\".\""
+ replTargetTable
+ "\" \n"
+ "SELECT * FROM \""
+ externalSchema
+ "\".\""
+ externalTable
+ "\"";
if (debug) Logger.printMsg("Executing SQL: " + strSQL);
location = 2600;
numRows = stmt.executeUpdate(strSQL);
location = 2700;
return numRows;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public void setStatement(Connection conn, String sqlString) throws SQLException {
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". TrafStatement.setStatement [" + stmtLabel + "]");
closeTStatement();
if (sqlString != null) {
stmt = conn.prepareStatement(sqlString);
if (LOG.isDebugEnabled())
LOG.debug(
serverWorkerName
+ ". T2 conn.prepareStatement ["
+ stmtLabel
+ "] sqlString :"
+ sqlString);
} else {
this.stmt = conn.createStatement();
if (LOG.isDebugEnabled())
LOG.debug(serverWorkerName + ". T2 conn.createStatement [" + stmtLabel + "]");
}
}
public static boolean createSchema(Connection conn, String schema) throws SQLException {
String method = "createSchema";
int location = 1000;
try {
location = 2000;
boolean found = false;
String strSQL =
"SELECT COUNT(*) \n"
+ "FROM INFORMATION_SCHEMA.SCHEMATA \n"
+ "WHERE SCHEMA_NAME = '"
+ schema
+ "'";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2306;
while (rs.next()) {
if (rs.getInt(1) > 0) found = true;
}
location = 2400;
if (!(found)) {
location = 2500;
String schemaDDL = "CREATE SCHEMA \"" + schema + "\"";
;
if (debug) Logger.printMsg("Schema DDL: " + schemaDDL);
location = 2600;
stmt.executeUpdate(schemaDDL);
}
location = 2700;
return found;
} catch (SQLException ex) {
return true;
}
}
public static boolean checkStageTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "checkStageTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2100;
String strSQL =
"SELECT NULL \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE table_schema = '"
+ externalSchema
+ "' \n"
+ " AND table_name = '"
+ stageTable
+ "'";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
location = 2200;
Statement stmt = conn.createStatement();
location = 2310;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2400;
while (rs.next()) {
found = true;
}
location = 2500;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static String getMax(Connection conn, String schema, String table, String columnName)
throws SQLException {
String method = "getMax";
int location = 1000;
try {
location = 2000;
String strSQL =
"SELECT MAX(\""
+ columnName.toLowerCase()
+ "\") \n"
+ "FROM \""
+ schema
+ "\".\""
+ table
+ "\"";
if (debug) Logger.printMsg("Executing sql: " + strSQL);
String max = "-1";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
max = rs.getString(1);
}
location = 2313;
if (max == null) {
max = "-1";
}
return max;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void truncateTable(Connection conn, String schema, String table)
throws SQLException {
String method = "truncateTable";
int location = 1000;
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2100;
String strSQL = "truncate table \"" + schema + "\".\"" + table + "\"";
if (debug) Logger.printMsg("Truncating table: " + strSQL);
location = 2200;
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public void login() {
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String id, pw, sql;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, "hr", "hr");
stmt = con.createStatement();
sql = "select * from chatuser where id = '" + lid.getText() + "'";
rs = stmt.executeQuery(sql);
if (rs.next()) {
pw = rs.getString(2);
nick = rs.getString(3);
if (lpw.getText().equals(pw)) {
jd.setVisible(false);
setVisible(true);
} else {
lpw.setText("일치하지 않습니다.");
}
} else {
lid.setText("일치하지 않습니다.");
}
} catch (Exception e1) {
e1.printStackTrace();
System.out.println("데이터 베이스 연결 실패!!");
} finally {
try {
if (rs != null) rs.close();
if (stmt != null) stmt.close();
if (con != null) con.close();
} catch (Exception e1) {
System.out.println(e1.getMessage());
}
}
}
public static void cancelJobs(Connection conn) throws SQLException {
String method = "cancelJobs";
int location = 1000;
try {
List<String> jobIdList = new ArrayList<String>();
Statement stmt = conn.createStatement();
String strSQL = "SELECT id FROM os.queue WHERE status = 'processing'";
ResultSet rs = stmt.executeQuery(strSQL);
while (rs.next()) {
jobIdList.add(rs.getString(1));
}
for (String jobId : jobIdList) {
strSQL = "SELECT os.fn_cancel_job(" + jobId + ")";
rs = stmt.executeQuery(strSQL);
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void setupReplicationTables(
Connection conn, String targetSchema, String targetTable, String columnName)
throws SQLException {
String method = "setupReplicationTables";
int location = 1000;
try {
location = 2000;
String archTable = getArchTableName(targetSchema, targetTable);
location = 2100;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String strSQL =
"SELECT os.fn_replication_setup('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ archTable
+ "', '"
+ columnName
+ "')";
location = 2312;
Statement stmt = conn.createStatement();
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void executeReplication(
Connection conn, String targetSchema, String targetTable, String appendColumnName)
throws SQLException {
String method = "executeReplication";
int location = 1000;
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2100;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2200;
String stageTable = getStageTableName(targetSchema, targetTable);
location = 2301;
String strSQL =
"SELECT os.fn_replication('"
+ targetSchema
+ "', '"
+ targetTable
+ "', '"
+ externalSchema
+ "', '"
+ stageTable
+ "', '"
+ appendColumnName
+ "');";
if (debug) Logger.printMsg("Executing function: " + strSQL);
location = 2400;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static void dropExternalTable(Connection conn, String targetSchema, String targetTable)
throws SQLException {
String method = "dropExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
String strSQL =
"DROP EXTERNAL TABLE IF EXISTS \"" + externalSchema + "\".\"" + externalTable + "\"";
if (debug) Logger.printMsg("Dropping External Table (if exists): " + strSQL);
location = 2303;
stmt.executeUpdate(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession(true);
try {
Object accountObject = session.getValue(ACCOUNT);
// If no account object was put in the session, or
// if one exists but it is not a hashtable, then
// redirect the user to the original login page
if (accountObject == null)
throw new RuntimeException("You need to log in to use this service!");
if (!(accountObject instanceof Hashtable))
throw new RuntimeException("You need to log in to use this service!");
Hashtable account = (Hashtable) accountObject;
String userName = (String) account.get("name");
//////////////////////////////////////////////
// Display Messages for the user who logged in
//////////////////////////////////////////////
out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>Contacts for " + userName + "</TITLE>");
out.println("</HEAD>");
out.println("<BODY BGCOLOR='#EFEFEF'>");
out.println("<H3>Welcome " + userName + "</H3>");
out.println("<CENTER>");
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con =
DriverManager.getConnection(
"jdbc:mysql://localhost/contacts?user=kareena&password=kapoor");
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT * FROM contacts WHERE userName='******' ORDER BY contactID");
out.println("<form name='deleteContactsForm' method='post' action='deleteContact'>");
out.println("<TABLE BGCOLOR='#EFEFFF' CELLPADDING='2' CELLSPACING='4' BORDER='1'>");
out.println("<TR BGCOLOR='#D6DFFF'>");
out.println("<TD ALIGN='center'><B>Contact ID</B></TD>");
out.println("<TD ALIGN='center'><B>Contact Name</B></TD>");
out.println("<TD ALIGN='center'><B>Comment</B></TD>");
out.println("<TD ALIGN='center'><B>Date</B></TD>");
out.println("<TD ALIGN='center'><B>Delete Contacts</B></TD>");
out.println("</TR>");
int nRows = 0;
while (rs.next()) {
nRows++;
String messageID = rs.getString("contactID");
String fromUser = rs.getString("contactName");
String message = rs.getString("comments");
String messageDate = rs.getString("dateAdded");
out.println("<TR>");
out.println("<TD>" + messageID + "</TD>");
out.println("<TD>" + fromUser + "</TD>");
out.println("<TD>" + message + "</TD>");
out.println("<TD>" + messageDate + "</TD>");
out.println(
"<TD><input type='checkbox' name='msgList' value='" + messageID + "'> Delete</TD>");
out.println("</TR>");
}
out.println("<TR>");
out.println(
"<TD COLSPAN='6' ALIGN='center'><input type='submit' value='Delete Selected Contacts'></TD>");
out.println("</TR>");
out.println("</TABLE>");
out.println("</FORM>");
} catch (Exception e) {
out.println("Could not connect to the users database.<P>");
out.println("The error message was");
out.println("<PRE>");
out.println(e.getMessage());
out.println("</PRE>");
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ignore) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ignore) {
}
}
}
out.println("</CENTER>");
out.println("</BODY>");
out.println("</HTML>");
} catch (RuntimeException e) {
out.println("<script language=\"javascript\">");
out.println("alert(\"You need to log in to use this service!\");");
out.println("</script>");
out.println("<a href='index.html'>Click Here</a> to go to the main page.<br><br>");
out.println(
"Or Click on the button to exit<FORM><INPUT onClick=\"javascipt:window.close()\" TYPE=\"BUTTON\" VALUE=\"Close Browser\" TITLE=\"Click here to close window\" NAME=\"CloseWindow\" STYLE=\"font-family:Verdana, Arial, Helvetica; font-size:smaller; font-weight:bold\"></FORM>");
log(e.getMessage());
return;
}
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
HttpSession session = request.getSession(true);
try {
Object accountObject = session.getValue(ACCOUNT);
// If no account object was put in the session, or
// if one exists but it is not a hashtable, then
// redirect the user to the original login page
if (accountObject == null)
throw new RuntimeException("You need to log in to use this service!");
if (!(accountObject instanceof Hashtable))
throw new RuntimeException("You need to log in to use this service!");
Hashtable account = (Hashtable) accountObject;
String userName = (String) account.get("name");
//////////////////////////////////////////////
// Display Messages for the user who logged in
//////////////////////////////////////////////
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
String lookupID = request.getParameter("LookupMemberID");
out.println("<HTML>");
out.println("<HEAD>");
out.println("<TITLE>Searching for member: lookupID</TITLE>");
out.println("</HEAD>");
out.println("<BODY BGCOLOR='#EFEFEF'>");
out.println("<H3><u>Searching for Member ID: " + lookupID + "</u></H3>");
out.println("<CENTER>");
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
con =
DriverManager.getConnection(
"jdbc:mysql://localhost/contacts?user=kareena&password=kapoor");
stmt = con.createStatement();
rs =
stmt.executeQuery(
"SELECT * FROM userstable WHERE UserID=" + Integer.parseInt(lookupID));
out.println("<TABLE BGCOLOR='#EFEFFF' CELLPADDING='2' CELLSPACING='4' BORDER='1'>");
out.println("<TR BGCOLOR='#D6DFFF'>");
out.println("<TD ALIGN='center'><B>Picture</B></TD>");
out.println("<TD ALIGN='center'><B>User Name</B></TD>");
out.println("<TD ALIGN='center'><B>Gender</B></TD>");
out.println("<TD ALIGN='center'><B>City / State</B></TD>");
out.println("<TD ALIGN='center'><B>Country</B></TD>");
out.println("<TD ALIGN='center'><B>About User</B></TD>");
out.println("<TD ALIGN='center'><B>User Profile</B></TD>");
out.println("<TD ALIGN='center'><B>Add to Contact List</B></TD>");
out.println("</TR>");
int i = 0;
String formName = "form";
String buttonName = "button";
while (rs.next()) {
String picture = rs.getString("FileLocation");
String user = rs.getString("UserName");
String city = rs.getString("City");
String state = rs.getString("State");
String country = rs.getString("Country");
String aboutUser = rs.getString("AboutMe1");
String gender = rs.getString("Gender");
formName += i;
buttonName += i;
out.println("<form name='" + formName + "' method='post' action='addContact'>");
out.println("<TR>");
out.println("<TD><img src='" + picture + "'</TD>");
out.println("<TD>" + user + "</TD>");
out.println("<TD>" + gender + "</TD>");
out.println("<TD>" + city + " / " + state + "</TD>");
out.println("<TD>" + country + "</TD>");
out.println("<TD>" + aboutUser + "</TD>");
out.println(
"<TD><A href='details.jsp?type=1&data="
+ lookupID
+ "'><IMG SRC='images/detail.jpg'></A></TD>");
out.println(
"<TD><input type='submit' value='Add to Contact List' name='"
+ buttonName
+ "'></TD>");
out.println("<input type='hidden' value='" + user + "' name='hiddenUser'>");
out.println("</TR>");
out.println("</form>");
i++;
}
out.println("</TABLE>");
} catch (Exception e) {
out.println("Could not connect to the users database.<P>");
out.println("The error message was");
out.println("<PRE>");
out.println(e.getMessage());
out.println("</PRE>");
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ignore) {
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException ignore) {
}
}
if (con != null) {
try {
con.close();
} catch (SQLException ignore) {
}
}
}
out.println("</CENTER>");
out.println("</BODY>");
out.println("</HTML>");
} catch (RuntimeException e) {
out.println("<script language=\"javascript\">");
out.println("alert(\"You need to log in to use this service!\");");
out.println("</script>");
out.println("<a href='index.html'>Click Here</a> to go to the main page.<br><br>");
out.println(
"Or Click on the button to exit<FORM><INPUT onClick=\"javascipt:window.close()\" TYPE=\"BUTTON\" VALUE=\"Close Browser\" TITLE=\"Click here to close window\" NAME=\"CloseWindow\" STYLE=\"font-family:Verdana, Arial, Helvetica; font-size:smaller; font-weight:bold\"></FORM>");
log(e.getMessage());
return;
}
}
private static void executeExt(Connection gpConn, int connectionId, String selectSQL)
throws Exception {
String method = "executeExt";
int location = 1000;
String sourceType = "";
String sourceServer = "";
String sourceInstance = "";
int sourcePort = 0;
String sourceDatabase = "";
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
Connection conn = null;
int fetchSize = 10;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getExtConnectionDetails(gpConn, connectionId);
location = 3020;
stmt = gpConn.createStatement();
location = 3030;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceType = rs.getString(1);
sourceServer = rs.getString(2);
sourceInstance = rs.getString(3);
sourcePort = rs.getInt(4);
sourceDatabase = rs.getString(5);
sourceUser = rs.getString(6);
sourcePass = rs.getString(7);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
CommonDB.outputData(conn, selectSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
private static void executeOS(
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String refreshType,
String appendColumnName,
int appendColumnMax,
Connection gpConn,
int queueId)
throws Exception {
String method = "executeOS";
int location = 1000;
String sourceUser = "";
String sourcePass = "";
String strSQL = "";
int fetchSize = 10;
Connection conn = null;
try {
location = 3000;
ResultSet rs;
Statement stmt;
location = 3010;
strSQL = GP.getQueueDetails(gpConn, queueId);
location = 3020;
stmt = gpConn.createStatement();
location = 3040;
rs = stmt.executeQuery(strSQL);
while (rs.next()) {
sourceUser = rs.getString(1);
sourcePass = rs.getString(2);
}
location = 3090;
if (sourceType.equals("sqlserver")) {
location = 3100;
conn = CommonDB.connectSQLServer(sourceServer, sourceInstance, sourceUser, sourcePass);
location = 3200;
// create SQL statement for selecting data
strSQL =
SQLServer.getSQLForData(
conn,
sourceDatabase,
sourceSchema,
sourceTable,
refreshType,
appendColumnName,
appendColumnMax);
location = 3300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 3400;
conn.close();
} else if (sourceType.equals("oracle")) {
location = 4000;
fetchSize = Integer.parseInt(GP.getVariable(gpConn, "oFetchSize"));
location = 4100;
conn =
CommonDB.connectOracle(
sourceServer, sourceDatabase, sourcePort, sourceUser, sourcePass, fetchSize);
location = 4200;
// execute the SQL Statement
strSQL =
Oracle.getSQLForData(
conn, sourceSchema, sourceTable, refreshType, appendColumnName, appendColumnMax);
location = 4300;
// execute the SQL Statement
CommonDB.outputData(conn, strSQL);
location = 4400;
conn.close();
}
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} finally {
if (conn != null) conn.close();
}
}
public QueryDBFrame() {
setTitle("QueryDB");
setSize(400, 300);
addWindowListener(
new WindowAdapter() {
public void windowClosing(WindowEvent e) {
System.exit(0);
}
});
getContentPane().setLayout(new GridBagLayout());
GridBagConstraints gbc = new GridBagConstraints();
authors = new JComboBox();
authors.setEditable(false);
authors.addItem("Any");
publishers = new JComboBox();
publishers.setEditable(false);
publishers.addItem("Any");
result = new JTextArea(4, 50);
result.setEditable(false);
priceChange = new JTextField(8);
priceChange.setText("-5.00");
try {
// 连接数据库
con = getConnection();
stmt = con.createStatement();
// 将数据库中的作者名添加到组合框
String query = "SELECT Name FROM Authors";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) authors.addItem(rs.getString(1));
// 将出版社名添加到组合框
query = "SELECT Name FROM Publishers";
rs = stmt.executeQuery(query);
while (rs.next()) publishers.addItem(rs.getString(1));
} catch (Exception e) {
result.setText("Error " + e);
}
gbc.fill = GridBagConstraints.NONE;
gbc.weightx = 100;
gbc.weighty = 100;
add(authors, gbc, 0, 0, 2, 1);
add(publishers, gbc, 2, 0, 2, 1);
gbc.fill = GridBagConstraints.NONE;
JButton queryButton = new JButton("Query");
queryButton.addActionListener(this);
add(queryButton, gbc, 0, 1, 1, 1);
JButton changeButton = new JButton("Change prices");
changeButton.addActionListener(this);
add(changeButton, gbc, 2, 1, 1, 1);
gbc.fill = GridBagConstraints.HORIZONTAL;
add(priceChange, gbc, 3, 1, 1, 1);
gbc.fill = GridBagConstraints.BOTH;
add(result, gbc, 0, 2, 4, 1);
}
public static void createExternalTable(
Connection conn,
String osServer,
String refreshType,
String sourceTable,
String targetSchema,
String targetTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createExternalTable";
int location = 1000;
try {
location = 2000;
String externalTable = getExternalTableName(targetSchema, targetTable);
location = 2100;
Statement stmt = conn.createStatement();
String createSQL =
"CREATE EXTERNAL TABLE \"" + externalSchema + "\".\"" + externalTable + "\" \n (";
location = 2309;
String strSQL =
"SELECT c.column_name, \n"
+ " CASE WHEN c.data_type = 'character' THEN c.data_type || '(' || c.character_maximum_length || ')' ELSE c.data_type END AS data_type \n"
+ "FROM INFORMATION_SCHEMA.COLUMNS c \n"
+ "WHERE table_schema = '"
+ targetSchema
+ "' \n"
+ " AND table_name = '"
+ targetTable
+ "' \n"
+ "ORDER BY ordinal_position";
location = 2400;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2500;
while (rs.next()) {
location = 2600;
if (rs.getRow() == 1) {
location = 2700;
createSQL = createSQL + "\"" + rs.getString(1) + "\" " + rs.getString(2);
} else {
location = 2800;
createSQL = createSQL + ", \n \"" + rs.getString(1) + "\" " + rs.getString(2);
}
}
location = 2900;
createSQL = createSQL + ") \n";
////////////////////////////////////////////
// Create location for External Table
////////////////////////////////////////////
location = 3000;
// replace space in the maxId because this could now be a date
maxId = maxId.replace(" ", "SPACE");
location = 3100;
String extLocation =
"LOCATION ('gpfdist://"
+ osServer
+ ":"
+ jobPort
+ "/config.properties+"
+ queueId
+ "+"
+ maxId
+ "+"
+ refreshType
+ "+"
+ sourceTable
+ "#transform=externaldata"
+ "')";
location = 3400;
extLocation = extLocation + "\n" + "FORMAT 'TEXT' (delimiter '|' null 'null' escape '\\\\')";
////////////////////////////////////////////
// Add createSQL with Java Command to exec.
////////////////////////////////////////////
location = 3500;
createSQL = createSQL + extLocation;
////////////////////////////////////////////
// Create new external web table
////////////////////////////////////////////
location = 4000;
if (debug) Logger.printMsg("Creating External Table: " + createSQL);
stmt.executeUpdate(createSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
} catch (Exception e) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + e.getMessage() + ")");
}
}
public static int executeSQL(Connection conn, String strSQL) throws SQLException {
String method = "executeSQL";
int location = 1000;
if (strSQL == null) strSQL = "";
try {
location = 2000;
int numRows = 0;
location = 2100;
String SQL = "";
location = 2150;
strSQL = strSQL.trim();
location = 2200;
int endPosition = strSQL.indexOf(";");
location = 2300;
Statement stmt = conn.createStatement();
location = 2400;
if (endPosition > -1) {
location = 2500;
while (endPosition > -1) {
location = 2600;
SQL = strSQL.substring(0, endPosition);
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 2700;
stmt.execute(SQL);
} else {
location = 2900;
numRows = numRows + stmt.executeUpdate(SQL);
}
location = 3100;
strSQL = strSQL.substring(endPosition + 1).trim();
location = 3200;
endPosition = strSQL.indexOf(";");
}
} else if (strSQL.length() > 0) {
location = 4000;
if (debug) Logger.printMsg("Executing sql: " + SQL);
// if select, execute query else execute update
if ((strSQL.toUpperCase()).startsWith("SELECT")) {
location = 4200;
stmt.execute(strSQL);
// discard results
} else {
location = 4300;
numRows = stmt.executeUpdate(strSQL);
}
}
location = 5000;
return numRows;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public static boolean createTargetTable(
Connection conn,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass)
throws Exception {
String method = "createTargetTable";
int location = 1000;
try {
location = 2000;
boolean found = false;
String strSQL =
"SELECT COUNT(*) \n"
+ "FROM INFORMATION_SCHEMA.TABLES \n"
+ "WHERE TABLE_SCHEMA = '"
+ targetSchema
+ "' \n"
+ " AND TABLE_NAME = '"
+ targetTable
+ "'";
location = 2100;
Statement stmt = conn.createStatement();
location = 2200;
ResultSet rs = stmt.executeQuery(strSQL);
location = 2307;
while (rs.next()) {
if (rs.getInt(1) > 0) found = true;
}
location = 2400;
if (!(found)) {
String tableDDL =
CommonDB.getGPTableDDL(
sourceType,
sourceServer,
sourceInstance,
sourcePort,
sourceDatabase,
sourceSchema,
sourceTable,
sourceUser,
sourcePass,
targetSchema,
targetTable,
targetAppendOnly,
targetCompressed,
targetRowOrientation);
if (debug) Logger.printMsg("Table DDL: " + tableDDL);
location = 2800;
stmt.executeUpdate(tableDDL);
}
location = 3000;
return found;
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public Design() throws Exception {
super.setBackground(Color.BLACK);
this.setTitle("");
con = getContentPane();
con.setLayout(null);
dim = tk.getDefaultToolkit().getScreenSize();
this.setTitle("Customer Peer Login");
l1 = new JLabel(new ImageIcon("plain.jpg"));
l1.setBounds(0, 0, 400, 400);
con.add(l1);
l1.setBorder(BorderFactory.createEtchedBorder(5, Color.black, Color.black));
title = new JLabel("CUSTOMER PEER LOGIN ");
title.setFont(new Font("Bookman Old Style", Font.ROMAN_BASELINE, 20));
title.setForeground(Color.red);
title.setBounds(80, 30, 300, 30);
l1.add(title);
l4 = new JLabel("CMACHINE NAME");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.BLUE);
l4.setBounds(70, 100, 160, 20);
// l4.setBorder(BorderFactory.createEtchedBorder(5,Color.green,Color.green));
l1.add(l4);
jtf2 = new JTextField();
jtf2.setBounds(250, 100, 100, 20);
jtf2.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf2);
l2 = new JLabel("CUSER LOGIN");
l2.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l2.setForeground(Color.blue);
l2.setBounds(70, 150, 120, 20);
l1.add(l2);
jtf1 = new JTextField();
jtf1.setBounds(250, 150, 100, 20);
jtf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jtf1);
l3 = new JLabel("CPASSWORD");
l3.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l3.setForeground(Color.blue);
l3.setBounds(70, 200, 120, 20);
l1.add(l3);
jptf1 = new JPasswordField();
jptf1.setBounds(250, 200, 100, 20);
jptf1.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(jptf1);
JLabel l4 = new JLabel("DAgent");
l4.setFont(new Font("Bookman Old Style", Font.BOLD, 16));
l4.setForeground(Color.blue);
l4.setBounds(70, 250, 120, 20);
l1.add(l4);
box = new JComboBox();
box.setBounds(250, 250, 100, 20);
box.setBorder(BorderFactory.createEtchedBorder(5, Color.green, Color.green));
l1.add(box);
b2 = new JButton("Register");
b2.setBounds(50, 300, 100, 20);
l1.add(b2);
b2.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
b3 = new JButton("Login");
b3.setBounds(150, 300, 100, 20);
b3.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b3);
b1 = new JButton("Cancel");
b1.setBounds(250, 300, 100, 20);
b1.setBorder(BorderFactory.createEtchedBorder(10, Color.BLUE, Color.BLUE));
l1.add(b1);
b1.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
dispose();
}
});
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
conn = DriverManager.getConnection("jdbc:odbc:agent");
} catch (Exception exp) {
}
try {
Statement satem = conn.createStatement();
ResultSet rsatem = satem.executeQuery("select * from Dagent");
while (rsatem.next()) {
String namem = rsatem.getString("uname");
box.addItem(namem);
}
} catch (Exception expo1) {
}
b2.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
exp.printStackTrace();
}
try {
packet p = new packet();
p.setaction("Creg");
p.setCuser(username);
p.setCpass(password);
p.setCmname(mechine);
p.setCDpeer(dname);
Socket soc = new Socket(servermachine, porte);
ObjectOutputStream out = new ObjectOutputStream(soc.getOutputStream());
out.writeObject(p);
ObjectInputStream in = new ObjectInputStream(soc.getInputStream());
packet rpac = (packet) in.readObject();
if (rpac.getaction().equals("ok")) {
JOptionPane.showMessageDialog(null, "Sucessfully Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
} else {
JOptionPane.showMessageDialog(null, "Already Registered");
jtf2.setText("");
jtf1.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
b3.addActionListener(
new ActionListener() {
public void actionPerformed(ActionEvent we) {
String username = jtf1.getText().trim();
String password = jptf1.getText().trim();
String mechine = jtf2.getText().trim();
String Dname = box.getSelectedItem().toString();
int porte = 0;
try {
Statement sate = conn.createStatement();
ResultSet rsate =
sate.executeQuery("select * from Dagent where uname='" + Dname + "'");
if (rsate.next()) {
servermachine = rsate.getString("umechine");
porte = rsate.getInt("ulistport");
System.out.println(servermachine);
}
System.out.println(servermachine);
} catch (Exception exp) {
}
try {
packet p1 = new packet();
p1.setaction("clogin");
p1.setCuser(username);
p1.setCpass(password);
p1.setCmname(mechine);
p1.setCDpeer(Dname);
Socket soc1 = new Socket(servermachine, porte);
ObjectOutputStream out1 = new ObjectOutputStream(soc1.getOutputStream());
out1.writeObject(p1);
ObjectInputStream in1 = new ObjectInputStream(soc1.getInputStream());
packet rpac1 = (packet) in1.readObject();
if (rpac1.getaction().equals("ok")) {
int port1 = 0;
try {
int portm = rpac1.getCport();
System.out.println("XXXXXXX" + portm);
// JOptionPane.showMessageDialog(null,"Sucessfully Started");
new Listen(portm);
new process(username, portm);
dispose();
} catch (Exception exp) {
}
} else {
JOptionPane.showMessageDialog(
null, "Enter valid username and password", "Server reply", 2);
jtf1.setText("");
jtf2.setText("");
jptf1.setText("");
}
} catch (Exception exp) {
}
}
});
setSize(400, 400);
show();
setLocation(150, 100);
setResizable(false);
}
public static void updateStatus(
Connection conn,
int queueId,
String status,
Timestamp queueDate,
Timestamp startDate,
String errorMessage,
int numRows,
int id,
String refreshType,
String targetSchema,
String targetTable,
boolean targetAppendOnly,
boolean targetCompressed,
boolean targetRowOrientation,
String sourceType,
String sourceServer,
String sourceInstance,
int sourcePort,
String sourceDatabase,
String sourceSchema,
String sourceTable,
String sourceUser,
String sourcePass,
String columnName,
String sqlText,
boolean snapshot)
throws SQLException {
String method = "updateStatus";
int location = 1000;
String strQueueId = setSQLString(queueId);
status = setSQLString(status);
String strQueueDate = setSQLString(queueDate);
String strStartDate = setSQLString(startDate);
errorMessage = setSQLString(errorMessage);
String strNumRows = setSQLString(numRows);
String strId = setSQLString(id);
refreshType = setSQLString(refreshType);
targetSchema = setSQLString(targetSchema);
targetTable = setSQLString(targetTable);
String strTargetAppendOnly = setSQLString(targetAppendOnly);
String strTargetCompressed = setSQLString(targetCompressed);
String strTargetRowOrientation = setSQLString(targetRowOrientation);
sourceType = setSQLString(sourceType);
sourceServer = setSQLString(sourceServer);
sourceInstance = setSQLString(sourceInstance);
String strSourcePort = setSQLString(sourcePort);
sourceDatabase = setSQLString(sourceDatabase);
sourceSchema = setSQLString(sourceSchema);
sourceTable = setSQLString(sourceTable);
sourceUser = setSQLString(sourceUser);
sourcePass = setSQLString(sourcePass);
columnName = setSQLString(columnName);
sqlText = setSQLString(sqlText);
String strSnapshot = setSQLString(snapshot);
try {
location = 2000;
Statement stmt = conn.createStatement();
location = 2400;
String strSQL =
"SELECT os.fn_update_status("
+ strQueueId
+ ", "
+ status
+ ", "
+ strQueueDate
+ ", "
+ strStartDate
+ ", ";
strSQL +=
errorMessage
+ ", "
+ strNumRows
+ ", "
+ strId
+ ", "
+ refreshType
+ ", "
+ targetSchema
+ ", "
+ targetTable
+ ", ";
strSQL +=
strTargetAppendOnly
+ ", "
+ strTargetCompressed
+ ", "
+ strTargetRowOrientation
+ ", "
+ sourceType
+ ", ";
strSQL +=
sourceServer
+ ", "
+ sourceInstance
+ ", "
+ strSourcePort
+ ", "
+ sourceDatabase
+ ", "
+ sourceSchema
+ ", ";
strSQL +=
sourceTable
+ ", "
+ sourceUser
+ ", "
+ sourcePass
+ ", "
+ columnName
+ ", "
+ sqlText
+ ", "
+ strSnapshot
+ ")";
if (debug) Logger.printMsg("Updating Status: " + strSQL);
location = 2500;
stmt.executeQuery(strSQL);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
