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() + ")");
}
}
private boolean login(String[] part) {
ResultSet rs = null;
try {
rs = beanOracle.selection("PASSWORD", "UTILISATEURS", "LOGIN = '******'");
} catch (SQLException e) {
System.err.println(e.getStackTrace());
}
String pwd = null;
try {
if (!rs.next()) {
SendMsg("ERR#Login invalide");
} else pwd = rs.getString("PASSWORD");
} catch (SQLException ex) {
System.err.println(ex.getStackTrace());
}
if (pwd.equals(part[2])) {
SendMsg("ACK");
return true;
} else SendMsg("ERR#Mot de passe incorrecte");
return false;
}
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 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 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() + ")");
}
}
public static void dropExternalReplTable(
Connection conn,
String sourceType,
String targetSchema,
String targetTable,
String sourceTable)
throws SQLException {
String method = "dropExternalReplTable";
int location = 1000;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String replSourceTable = getReplTableName(sourceType, sourceTable);
location = 2315;
dropExternalTable(conn, replTargetSchema, replTargetTable);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
private void inputLorryWithoutReserv(String[] request) {
ResultSet rs = null;
String[] idList = request[2].split("@");
try {
rs = beanOracle.selection("X, Y", "PARC", "ETAT=0");
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
String reponse = "ACK#";
ArrayList emplacement = new ArrayList();
try // on regarde si y'a assez de place et on recupere l'id de ces places.
{
for (int i = 0; i < idList.length; i++) {
if (rs.next()) {
reponse =
reponse + idList[i] + "==>(" + rs.getString("X") + ";" + rs.getString("Y") + ")@";
emplacement.add(rs.getString("X") + ";" + rs.getString("Y"));
} else {
SendMsg("ERR#Erreur pas assez de places");
return;
}
}
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
// On insert les containers ajoutés dans la BD et on leur met un numéro de réservation + on
// réserve leurs places
Random rand = new Random();
int resID = rand.nextInt(999999);
for (int i = 0; i < idList.length; i++) {
String[] coord = emplacement.get(i).toString().split(";");
HashMap<String, String> insertion = new HashMap();
HashMap<String, String> update = new HashMap();
insertion.put("ID_CONTAINER", idList[i]);
insertion.put("RESERVATION", Integer.toString(resID));
update.put("ETAT", "1");
try {
beanOracle.ecriture("CONTAINERS", insertion);
beanOracle.miseAJour("PARC", update, "X=" + coord[0] + " AND Y=" + coord[1]);
} catch (requeteException ex) {
System.err.println("Erreur d'insertion ");
}
}
SendMsg(reponse);
}
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 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 String getArchMax(
Connection conn, String targetSchema, String targetTable, String columnName)
throws SQLException {
String method = "getArchMax";
int location = 1000;
try {
location = 2000;
String archTable = getArchTableName(targetSchema, targetTable);
location = 2100;
String max = getMax(conn, externalSchema, archTable, columnName);
location = 2200;
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 static void createReplExternalTable(
Connection conn,
String osServer,
String refreshType,
String targetSchema,
String targetTable,
String sourceType,
String sourceTable,
String maxId,
int queueId,
int jobPort)
throws SQLException {
String method = "createReplExternalTable";
int location = 1000;
try {
location = 2000;
String replTargetSchema = externalSchema;
location = 2100;
String replTargetTable = getStageTableName(targetSchema, targetTable);
location = 2200;
String replSourceTable = getReplTableName(sourceType, sourceTable);
location = 2308;
createExternalTable(
conn,
osServer,
refreshType,
replSourceTable,
replTargetSchema,
replTargetTable,
maxId,
queueId,
jobPort);
} catch (SQLException ex) {
throw new SQLException(
"(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
}
}
public RunnableTraitementEntree(Socket s) {
CSocket = s;
try {
dis = new DataInputStream(new BufferedInputStream(CSocket.getInputStream()));
dos = new DataOutputStream(new BufferedOutputStream(CSocket.getOutputStream()));
} catch (IOException e) {
System.err.println("RunnableTraitement : Host non trouvé : " + e);
}
beanOracle = new BeanBDAccess();
try {
beanOracle.connexionOracle("localhost", 1521, "TRAFIC", "TRAFIC", "XE");
} catch (ClassNotFoundException ex) {
System.err.println("Class not found " + ex.getMessage());
} catch (SQLException ex) {
System.err.println("SQL Exception (oracle)" + ex.getMessage());
} catch (connexionException ex) {
System.err.println(ex.getNumException() + " -- " + 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 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 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() + ")");
}
}
/**
* Method declaration
*
* @return
*/
private Channel init() {
try {
mSocket.setTcpNoDelay(true);
mInput = new DataInputStream(new BufferedInputStream(mSocket.getInputStream()));
mOutput = new DataOutputStream(new BufferedOutputStream(mSocket.getOutputStream()));
String user = mInput.readUTF();
String password = mInput.readUTF();
Channel c;
try {
mServer.trace(mThread + ":trying to connect user " + user);
return mDatabase.connect(user, password);
} catch (SQLException e) {
write(new Result(e.getMessage()).getBytes());
}
} catch (Exception e) {
}
return null;
}
static void logPacket(byte[] packet) {
Date date = new Date();
Timestamp ts = new Timestamp(date.getTime());
String dataField = "time";
String dataValue = "'" + ts + "'";
int[] intPacket = new int[packet.length];
for (int i = 0; i < packet.length; i++) {
if (packet[i] < 0) intPacket[i] = (int) packet[i] + 256;
else intPacket[i] = (int) packet[i];
}
for (int i = 0; i < intPacket.length; i++) {
dataField += ", " + "b" + Integer.toString(i);
dataValue += ", '" + intPacket[i] + "'";
}
String queryString = "insert into " + tablename + " (" + dataField + ") VALUES ( ";
queryString += dataValue + ");";
try {
query.executeUpdate(queryString);
} catch (SQLException e) {
System.out.println("Query no good; " + e.getErrorCode() + "; " + queryString);
}
}
public void run() {
ObjectInputStream din = null;
try {
din = new ObjectInputStream(socket.getInputStream());
Packet packet = (Packet) din.readObject();
username = packet.getSender();
server.usernameSocketMapping.put(username, socket);
server.isAlive.add(username);
// Sh: Login validation message
if (packet.getType() == 0) {
try {
String password = packet.getMessage();
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT * FROM user_info where username='******' and password='******'");
if (!rs.next()) { // User does not exist
server.forwardToReceiver(new Packet(0, "Server", username, "Invalid"));
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
return;
} else { // Authentic user
server.forwardToReceiver(new Packet(0, "Server", username, "Authentic"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
// Sh: Signup message
if (packet.getType() == 2) {
try {
String[] message = packet.getMessage().split(":");
String name = message[0];
String password = message[1];
// String sex=message[2];
String username = message[2];
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery("SELECT * FROM user_info where username='******'");
if (rs.next()) { // Username already present
server.forwardToReceiver(new Packet(2, "Server", username, "Username present"));
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
return;
} else { // Register user
String query =
"INSERT INTO user_info VALUES('"
+ name
+ "','"
+ password
+ "',"
+ "NULL,'"
+ username
+ "')";
System.out.println(query);
st.executeUpdate(query);
server.forwardToReceiver(new Packet(2, "Server", username, "User Registererd"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}
System.out.println("State of isAlive map is: " + server.isAlive);
try {
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT sender,message,status FROM msg_buffer where receiver='"
+ username
+ "' order by sender,sno");
String sendflag = null;
String sender = null;
while (rs.next()) {
sender = rs.getString("sender");
int status = rs.getInt("status");
String message = rs.getString("message");
System.out.println("sender: " + sender);
System.out.println("receiver: " + username);
System.out.println("message: " + message);
server.forwardToReceiver(new Packet(status, sender, username, message));
System.out.println("Sending " + packet);
if (!sender.equals(sendflag)) {
server.forwardToReceiver(new Packet(6, username, sender, "Message Received"));
sendflag = sender;
}
}
st.executeUpdate("DELETE FROM msg_buffer where receiver='" + username + "'");
} catch (SQLException e1) {
e1.printStackTrace();
}
while (true) {
packet = (Packet) din.readObject();
if (packet.getType() == 3) // is an OK message
{
try {
packet.setSender(username);
System.out.println("Is OK message");
Statement st = Server.conn.createStatement();
ResultSet rs =
st.executeQuery(
"SELECT * FROM user_info where username='******'");
if (rs.next()) {
if (server.addFriend(packet.getSender(), packet.getReceiver(), true) == 1) {
// packet.setMessage("Already in friend list");
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "Already in friend list"));
System.out.println("ALready in list");
} else {
System.out.println("Sending " + packet);
server.forwardToReceiver(
new Packet(1, "Server", packet.getReceiver(), packet.getMessage()));
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), packet.getMessage()));
}
} else {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "User does not exist"));
}
} catch (SQLException e1) {
e1.printStackTrace();
}
} else if ((packet.getType() == 8)) // is a getFriends message
{
packet.setSender(username);
System.out.println("Type 8");
/*
* try { //Thread.sleep(3000); } catch (InterruptedException
* e) { // TODO Auto-generated catch block
* e.printStackTrace(); }
*/
String FriendsMessage = getFriendsMessage(packet);
server.forwardToReceiver(
new Packet(8, "Server", packet.getSender(), "Friends|" + FriendsMessage));
} else if (packet.getType() == 16) // Random conversation.
{
packet.setSender(username);
String receiver = server.getRandomPerson(packet.getSender());
if (!receiver.equals("No One Online")) {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), receiver + "| will talk to you now."));
server.forwardToReceiver(
new Packet(1, "Server", receiver, packet.getSender() + "| will talk to you now."));
} else {
System.out.println("No one online");
server.forwardToReceiver(new Packet(16, "Server", packet.getSender(), "No One Online"));
server.forwardToReceiver(new Packet(1, "Server", packet.getSender(), "No One Online"));
}
} else if (packet.getType() == 13) { // Webcam of live is started
synchronized (server.webcamAliveMapping) {
server.webcamAliveMapping.put(packet.getSender(), packet.getMessage());
}
System.out.println("State of webcamAliveMapping is: " + server.webcamAliveMapping);
} else if (packet.getType() == 14) { // Webcam of client is
// closed
if (server.webcamAliveMapping.containsKey(packet.getSender())) {
synchronized (server.webcamAliveMapping) {
server.webcamAliveMapping.remove(packet.getSender());
}
}
} else if (packet.getType() == 15) { // Client requested for
// another client's webcam
if (server.webcamAliveMapping.containsKey(
packet.getReceiver())) { // User Webcam is available
server.forwardToReceiver(
new Packet(
15,
"Server",
packet.getSender(),
server.webcamAliveMapping.get(packet.getReceiver())));
} else { // User Webcam not available
server.forwardToReceiver(
new Packet(15, "Server", packet.getSender(), "Webcam Not Found"));
server.forwardToReceiver(
new Packet(
1,
"Server",
packet.getSender(),
"Webcam for " + packet.getReceiver() + " is not available"));
}
} else if (packet.getType() == 17) {
ArrayList<String> friends = new ArrayList<String>();
friends = server.getfriendlist(packet.getSender());
if (friends.isEmpty()) {
server.forwardToReceiver(
new Packet(1, "Server", packet.getSender(), "Your friendlist is empty."));
} else {
for (int i = 0; i < friends.size(); i++) {
server.forwardToReceiver(
new Packet(1, packet.getSender(), friends.get(i), packet.getMessage()));
}
}
} else {
packet.setSender(username);
System.out.println("Sending " + packet);
server.forwardToReceiver(packet);
}
}
} catch (EOFException ie) {
} catch (IOException ie) {
ie.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} finally {
try {
din.close();
} catch (IOException e) {
e.printStackTrace();
}
synchronized (server.isAlive) {
server.isAlive.remove(username);
}
synchronized (server.usernameSocketMapping) {
server.usernameSocketMapping.remove(username);
}
System.out.println("State of isAlive map is: " + server.isAlive);
server.removeConnection(socket);
}
}
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();
}
}
private void listOperation(String[] request) {
String Select =
"ID_MOUVEMENT, MOUVEMENTS.ID_CONTAINER, ID_TRANSPORTEUR_ENTRANT, DATE_ARRIVEE, ID_TRANSPORTEUR_SORTANT, POIDS, DATE_DEPART, DESTINATION, ID_SOCIETE";
String From =
"MOUVEMENTS INNER JOIN CONTAINERS ON MOUVEMENTS.ID_CONTAINER = CONTAINERS.ID_CONTAINER";
String Where = null;
if (request[1].equals("societe")) Where = "CONTAINERS.ID_SOCIETE = '" + request[2] + "'";
if (request[1].equals("destination")) Where = "DESTINATION = '" + request[2] + "'";
if (request[1].equals("date"))
Where =
"To_date(DATE_ARRIVEE, 'DD/MM/YYYY') BETWEEN To_date('"
+ request[2]
+ "', 'DD/MM/YYYY') AND To_date('"
+ request[3]
+ "', 'DD/MM/YYYY')";
if (Where == null) {
SendMsg("ERR#Recherche impossible sur ce critere");
return;
}
ResultSet rs = null;
try {
rs = beanOracle.selection(Select, From, Where);
} catch (SQLException ex) {
SendMsg("ERR#Base de donnee inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
boolean empty = true;
String message = "";
try {
while (rs.next()) {
empty = false;
message =
message
+ rs.getString("ID_MOUVEMENT")
+ " --- "
+ rs.getString("ID_CONTAINER")
+ " --- "
+ rs.getString("ID_TRANSPORTEUR_ENTRANT")
+ " --- ";
message =
message
+ rs.getString("DATE_ARRIVEE")
+ " --- "
+ rs.getString("ID_TRANSPORTEUR_SORTANT")
+ " --- "
+ rs.getString("POIDS")
+ " --- ";
message =
message
+ rs.getString("DATE_DEPART")
+ " --- "
+ rs.getString("DESTINATION")
+ " --- "
+ rs.getString("ID_SOCIETE")
+ "#";
}
} catch (SQLException ex) {
SendMsg("ERR#Base de donnee inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
if (empty) {
SendMsg("ERR#Aucun resultats pour la societe " + request[2]);
return;
}
SendMsg("ACK#" + message);
}
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() + ")");
}
}
private void inputLorry(String[] request) {
ResultSet rs = null;
try {
rs = beanOracle.selection("ID_CONTAINER", "CONTAINERS", "RESERVATION = '" + request[1] + "'");
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry");
return;
}
boolean isResultEmpty = true;
int nbrElemParc = 0;
String[] idList = request[2].split("@");
System.out.println(request[2]);
try {
while (rs.next()) {
nbrElemParc++;
isResultEmpty = false;
String curId = null;
String id = rs.getString("ID_CONTAINER");
boolean invalidContainerID = true;
for (String s : idList) {
System.out.println(s + "---" + id);
curId = s;
if (s.equals(id)) {
invalidContainerID = false;
break;
}
}
if (invalidContainerID) {
SendMsg("ERR#Le container " + curId + " ne fait pas partie de la reservation");
return;
}
if (nbrElemParc >= idList.length) break;
}
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry resultat");
return;
}
if (isResultEmpty) {
SendMsg("ERR#Le numero de reservation demande n'existe pas");
return;
}
try {
rs = beanOracle.selection("X, Y", "PARC", "ETAT=1");
} catch (SQLException ex) {
SendMsg("ERR#Base de donnée inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
String reponse = "ACK#";
try {
for (int i = 0; i < idList.length; i++) {
if (rs.next()) {
reponse =
reponse + idList[i] + "==>(" + rs.getString("X") + ";" + rs.getString("Y") + ")@";
} else {
SendMsg("ERR#Erreur pas assez de places reservees");
return;
}
}
} catch (SQLException ex) {
SendMsg("ERR#Base de donnee inaccessible");
System.err.println("Erreur SQL exception input lorry" + ex.getStackTrace());
return;
}
SendMsg(reponse);
}
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() + ")");
}
}
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 void doGet(HttpServletRequest request, HttpServletResponse response)
throws IOException, ServletException {
response.setContentType("text/html");
PrintWriter out = response.getWriter();
ResultSet rs = null;
try {
// SET UP Context environment, to look for Data Pooling Resource
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB");
Connection dbcon = ds.getConnection();
// ########### SEARCH INPUT PARAMETERS, EXECUTE search
// #####################################################
Vector<String> params = new Vector<String>();
params.add(request.getParameter("fname"));
params.add(request.getParameter("lname"));
params.add(request.getParameter("title"));
params.add(request.getParameter("year"));
params.add(request.getParameter("director"));
List<Movie> movies = new ArrayList<Movie>();
movies = SQLServices.getMovies(params.toArray(new String[params.size()]), dbcon);
// ########## SET DEFAULT SESSION() PARAMETERS ####################################
request.getSession().removeAttribute("movies");
request.getSession().removeAttribute("linkedListMovies");
request.getSession().removeAttribute("hasPaged");
request.getSession().setAttribute("hasPaged", "no");
request.getSession().setAttribute("movies", movies);
request.getSession().setAttribute("currentIndex", "0");
request.getSession().setAttribute("defaultN", "5");
// ########## IF MOVIES FROM SEARCH NON-EMPTY ###########################################
List<String> fields = Movie.fieldNames();
int count = 1;
if (!movies.isEmpty()) {
request.setAttribute("movies", movies);
for (String field : fields) {
request.setAttribute("f" + count++, field);
}
request.getRequestDispatcher("../movieList.jsp").forward(request, response);
} else {
out.println("<html><head><title>error</title></head>");
out.println("<body><h1>could not find any movies, try your search again.</h1>");
out.println("<p> we are terribly sorry, please go back. </p>");
out.println("<table border>");
out.println("</table>");
}
dbcon.close();
} catch (SQLException ex) {
while (ex != null) {
System.out.println("SQL Exception: " + ex.getMessage());
ex = ex.getNextException();
}
} catch (java.lang.Exception ex) {
out.println(
"<html>"
+ "<head><title>"
+ "moviedb: error"
+ "</title></head>\n<body>"
+ "<p>SQL error in doGet: "
+ ex.getMessage()
+ "</p></body></html>");
return;
}
out.close();
}
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() + ")");
}
}