@Override
public Set<EmpVO> getEmpsByDeptno(Integer deptno) {
Set<EmpVO> set = new LinkedHashSet<EmpVO>();
EmpVO empVO = null;
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
pstmt = con.prepareStatement(GET_Emps_ByDeptno_STMT);
pstmt.setInt(1, deptno);
rs = pstmt.executeQuery();
while (rs.next()) {
empVO = new EmpVO();
empVO.setEmpno(rs.getInt("empno"));
empVO.setEname(rs.getString("ename"));
empVO.setJob(rs.getString("job"));
empVO.setHiredate(rs.getDate("hiredate"));
empVO.setSal(rs.getDouble("sal"));
empVO.setComm(rs.getDouble("comm"));
empVO.setDeptno(rs.getInt("deptno"));
set.add(empVO); // Store the row in the vector
}
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
return set;
}
@Override
public int size(final DatabaseTable table) throws DatabaseException {
preOperationCheck();
final StringBuilder sb = new StringBuilder();
sb.append("SELECT COUNT(" + KEY_COLUMN + ") FROM ").append(table.toString());
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sb.toString());
resultSet = statement.executeQuery();
if (resultSet.next()) {
return resultSet.getInt(1);
}
} catch (SQLException e) {
final ErrorInformation errorInformation =
new ErrorInformation(
PwmError.ERROR_DB_UNAVAILABLE, "size operation failed: " + e.getMessage());
lastError = errorInformation;
throw new DatabaseException(errorInformation);
} finally {
close(statement);
close(resultSet);
}
updateStats(true, false);
return 0;
}
@Override
public List<Ordine> getOrdiniPerCliente(Cliente cliente) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
Ordine o = null;
List<Ordine> lo = new LinkedList<Ordine>();
try {
String str = "select codice,data,stato,id from ordini where cliente=?";
statement = connection.prepareStatement(str);
statement.setInt(1, cliente.getId());
ResultSet result = statement.executeQuery();
while (result.next()) {
o = new Ordine();
o.setCliente(cliente);
o.setCodiceOrdine(result.getString("codice"));
o.setData(result.getDate("data"));
o.setStato(result.getString("stato"));
o.setId(result.getInt("id"));
lo.add(o);
// da completare per restituire ordine completo di righe
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return lo;
}
@Override
public int registraOrdine(Ordine ordine) throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
int i;
if (this.getOrdineByCodice(ordine.getCodiceOrdine()) != null)
throw new PersistenceException("Ordine gia presente");
try {
String str = "insert into ordini (id,codice,data,stato,cliente) values (?,?,?,?,?)";
statement = connection.prepareStatement(str);
IdBroker id = new IdBrokerPostgres();
i = id.getId();
statement.setInt(1, i);
statement.setString(
2, new ClienteDAOImpl().getClienteById(ordine.getCliente().getId()).getNome() + i);
statement.setDate(3, new java.sql.Date(ordine.getData().getTime()));
statement.setString(4, ordine.getStato());
statement.setInt(5, ordine.getCliente().getId());
statement.executeUpdate();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return i;
}
@Override
public void delete(Integer deptno) {
int updateCount_EMPs = 0;
Connection con = null;
PreparedStatement pstmt = null;
try {
Class.forName(driver);
con = DriverManager.getConnection(url, userid, passwd);
// 1●設定於 pstm.executeUpdate()之前
con.setAutoCommit(false);
// 先刪除員工
pstmt = con.prepareStatement(DELETE_EMPs);
pstmt.setInt(1, deptno);
updateCount_EMPs = pstmt.executeUpdate();
// 再刪除部門
pstmt = con.prepareStatement(DELETE_DEPT);
pstmt.setInt(1, deptno);
pstmt.executeUpdate();
// 2●設定於 pstm.executeUpdate()之後
con.commit();
con.setAutoCommit(true);
System.out.println("刪除部門編號" + deptno + "時,共有員工" + updateCount_EMPs + "人同時被刪除");
// Handle any driver errors
} catch (ClassNotFoundException e) {
throw new RuntimeException("Couldn't load database driver. " + e.getMessage());
// Handle any SQL errors
} catch (SQLException se) {
if (con != null) {
try {
// 3●設定於當有exception發生時之catch區塊內
con.rollback();
} catch (SQLException excep) {
throw new RuntimeException("rollback error occured. " + excep.getMessage());
}
}
throw new RuntimeException("A database error occured. " + se.getMessage());
} finally {
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException se) {
se.printStackTrace(System.err);
}
}
if (con != null) {
try {
con.close();
} catch (Exception e) {
e.printStackTrace(System.err);
}
}
}
}
@Override
public List<Ordine> listaOrdini() throws PersistenceException { // ok
Connection connection = this.datasource.getConnection();
PreparedStatement statement = null;
List<Ordine> ordini = null;
Ordine ordine = null;
try {
String str =
"select ordini.codice as codice,stato,data,cliente,id "
+ "from ordini left outer join clienti on ordini.cliente=clienti.codice";
statement = connection.prepareStatement(str);
ResultSet result = statement.executeQuery();
ordini = new ArrayList<Ordine>();
while (result.next()) {
ordine = new Ordine();
ClienteDAOImpl cliente = new ClienteDAOImpl();
ordine.setCliente(cliente.getClienteById(result.getInt("cliente")));
ordine.setCodiceOrdine(result.getString("codice"));
ordine.setData(new java.util.Date(result.getDate("data").getDate()));
ordine.setStato(result.getString("stato"));
ordine.setId(result.getInt("id"));
ordini.add(ordine);
}
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
} finally {
try {
if (statement != null) statement.close();
if (connection != null) connection.close();
} catch (SQLException e) {
throw new PersistenceException(e.getMessage());
}
}
return ordini;
}
protected SDMSObject rowToObject(SystemEnvironment env, ResultSet r) throws SDMSException {
Long id;
Long smeId;
Long trId;
Long nextTriggerTime;
Integer timesChecked;
Integer timesTriggered;
Long creatorUId;
Long createTs;
Long changerUId;
Long changeTs;
long validFrom;
long validTo;
try {
id = new Long(r.getLong(1));
smeId = new Long(r.getLong(2));
trId = new Long(r.getLong(3));
nextTriggerTime = new Long(r.getLong(4));
timesChecked = new Integer(r.getInt(5));
timesTriggered = new Integer(r.getInt(6));
creatorUId = new Long(r.getLong(7));
createTs = new Long(r.getLong(8));
changerUId = new Long(r.getLong(9));
changeTs = new Long(r.getLong(10));
validFrom = 0;
validTo = Long.MAX_VALUE;
} catch (SQLException sqle) {
SDMSThread.doTrace(null, "SQL Error : " + sqle.getMessage(), SDMSThread.SEVERITY_ERROR);
throw new FatalException(
new SDMSMessage(
env,
"01110182045",
"TriggerQueue: $1 $2",
new Integer(sqle.getErrorCode()),
sqle.getMessage()));
}
if (validTo < env.lowestActiveVersion) return null;
return new SDMSTriggerQueueGeneric(
id,
smeId,
trId,
nextTriggerTime,
timesChecked,
timesTriggered,
creatorUId,
createTs,
changerUId,
changeTs,
validFrom,
validTo);
}
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 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() + ")");
}
}
/**
* Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
* error occured to the database connection.
*
* @param name the instructor's user name
* @return a vector containing the list of courses
* @throws InvalidDBRequestException
*/
public Vector getCourseList(String name) throws InvalidDBRequestException {
Vector courseList = new Vector();
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// get the course list
rs =
stmt.executeQuery(
"select course_num, course_name from course where instructor = '"
+ name
+ "' order by course_num");
while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
return courseList;
}
public static void execute(Object[] args, Hashtable aliases) throws UpdateException {
// System.out.println("______________EXECUTOR CALLED!!! ");
if (args == null) return;
String query = "begin ";
for (int i = 0; i < args.length; i++) {
query = query + (String) args[i] + ";";
if (GLOBAL.dstore_debug > 2) System.out.println(query);
}
query = query + " end;";
/*
for (int i=1;i<args.length;i++){
if (i!=args.length)
query = query +args[i]+",";
else query = query + args[i]+"); end;";
}
*/
ResultSet rset = null;
try {
rset = DATASTORE.executeQuery(query);
DATASTORE.conn.commit();
DSCollection.repeatLocks();
} catch (SQLException e) {
throw new UpdateException(e.getMessage(), 0);
}
}
public String insertarEmpleado(Empleado empleado) {
String mensaje = null;
try {
cn = Conexion.realizarConexion();
String sql = "insert into empleado values(?,?,?,?,?,?,?,?)";
ps = cn.prepareStatement(sql);
ps.setString(1, empleado.getCodigo());
ps.setString(2, empleado.getPaterno());
ps.setString(3, empleado.getMaterno());
ps.setString(4, empleado.getNombre());
ps.setString(5, empleado.getCiudad());
ps.setString(6, empleado.getDireccion());
ps.setString(7, empleado.getUsuario());
ps.setString(8, empleado.getClave());
ps.executeUpdate();
} catch (ClassNotFoundException ex) {
mensaje = ex.getMessage();
} catch (SQLException ex) {
mensaje = ex.getMessage();
} finally {
try {
ps.close();
cn.close();
} catch (Exception ex) {
mensaje = ex.getMessage();
}
}
return mensaje;
}
/**
* Sets the user to run as. This is for the case where the request was generated by the user and
* so the worker must set this value later.
*
* @param cq_id id of this entry in the queue
* @param user user to run the jobs as
*/
public void setRunAs(long cq_id, String user) throws MetaException {
try {
Connection dbConn = getDbConn();
try {
Statement stmt = dbConn.createStatement();
String s = "update COMPACTION_QUEUE set cq_run_as = '" + user + "' where cq_id = " + cq_id;
LOG.debug("Going to execute update <" + s + ">");
if (stmt.executeUpdate(s) != 1) {
LOG.error("Unable to update compaction record");
LOG.debug("Going to rollback");
dbConn.rollback();
}
LOG.debug("Going to commit");
dbConn.commit();
} catch (SQLException e) {
LOG.error("Unable to update compaction queue, " + e.getMessage());
try {
LOG.debug("Going to rollback");
dbConn.rollback();
} catch (SQLException e1) {
}
detectDeadlock(e, "setRunAs");
} finally {
closeDbConn(dbConn);
}
} catch (DeadlockException e) {
setRunAs(cq_id, user);
} finally {
deadlockCnt = 0;
}
}
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() + ")");
}
}
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 int updateTableDocType(Connection conn, ConnectionProvider connectionProvider)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " update c_doctype set ad_table_id = 'D1A97202E832470285C9B1EB026D54E2'"
+ " where docbasetype in ('ARR', 'APP')";
int updateCount = 0;
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(conn, strSql);
updateCount = st.executeUpdate();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releaseTransactionalPreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (updateCount);
}
public static int deleteTableAccess(Connection conn, ConnectionProvider connectionProvider)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " DELETE FROM ad_table_access"
+ " WHERE ad_table_id = '4D8C3B3C31D1410DA046140C9F024D17'"
+ " AND isreadonly = 'Y'"
+ " AND isexclude = 'N'"
+ " AND created <= (SELECT created FROM ad_tab WHERE ad_tab_id = 'FF8080812F213146012F2135BC25000E')";
int updateCount = 0;
PreparedStatement st = null;
try {
st = connectionProvider.getPreparedStatement(conn, strSql);
updateCount = st.executeUpdate();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releaseTransactionalPreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
return (updateCount);
}
public void excluir(Oriundo oriundo) throws SQLException {
Connection con =
DriverManager.getConnection(
new conexao().url,
new conexao().config.getString("usuario"),
new conexao().config.getString("senha"));
PreparedStatement ps = null;
String sqlExcluir = "DELETE FROM oriundo WHERE codigo=?";
try {
ps = con.prepareStatement(sqlExcluir);
ps.setInt(1, oriundo.getCodigo());
ps.executeUpdate();
JOptionPane.showMessageDialog(
null, "Ecluido Com Sucesso: ", "Mensagem do Sistema - Excluir", 1);
} catch (NumberFormatException e) {
JOptionPane.showMessageDialog(
null, "NumberFormaterExeption Erro: " + e.getMessage(), "ClasseDAO Func.Excluir", 0);
e.printStackTrace();
} catch (NullPointerException e) {
JOptionPane.showMessageDialog(
null, "NullPointerException Erro: " + e.getMessage(), "ClasseDAO Func. Excluir", 0);
e.printStackTrace();
} catch (SQLException e) {
JOptionPane.showMessageDialog(
null, "SQLException Erro: " + e.getMessage(), "ClasseDAO Func. Excluir", 0);
e.printStackTrace();
} catch (Exception e) {
JOptionPane.showMessageDialog(
null, "Exception Erro: " + e.getMessage(), "ClasseDAO Func. Excluir", 0);
e.printStackTrace();
} finally {
ps.close();
con.close();
}
}
public ArrayList<Empleado> listarEmpleado() {
ArrayList<Empleado> emple = new ArrayList<Empleado>();
try {
cn = Conexion.realizarConexion();
st = cn.createStatement();
String sql = "select * from empleado";
rs = st.executeQuery(sql);
while (rs.next()) {
emple.add(
new Empleado(
rs.getString(1),
rs.getString(2),
rs.getString(3),
rs.getString(4),
rs.getString(5),
rs.getString(6),
rs.getString(7),
rs.getString(8)));
}
} catch (ClassNotFoundException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} catch (SQLException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} finally {
try {
rs.close();
st.close();
cn.close();
} catch (Exception ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
}
}
return emple;
}
public String buscarEmpleado(String codigo) {
try {
cn = Conexion.realizarConexion();
st = cn.createStatement();
String sql =
"select chr_emplcodigo from empleado " + " where chr_emplcodigo = '" + codigo + "'";
rs = st.executeQuery(sql);
while (rs.next()) {
return rs.getString("chr_emplcodigo");
}
} catch (ClassNotFoundException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} catch (SQLException ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
} finally {
try {
rs.close();
st.close();
cn.close();
} catch (Exception ex) {
showMessageDialog(null, ex.getMessage(), "Excepción", 0);
}
}
return null;
}
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() + ")");
}
}
/**
* Check the user's name and password and verify that the user is an instructor. Throws
* InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured
* to the database connection.
*
* @param name user's user name
* @param pass user's password
* @throws InvalidDBRequestException
*/
public void instructorLogin(String name, String pass) throws InvalidDBRequestException {
try {
Connection db;
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
// check if instructor
rs = stmt.executeQuery("select password from instructor where login = '******'");
if (!rs.next()) {
if (debug) System.out.println("User not found in the instructor table");
throw new InvalidDBRequestException("Instructor not registered");
}
// check password
if (!rs.getString(1).equals(pass)) {
if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Server Error");
}
}
/**
* Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
* student's directory which relates to the course. Caution: vizualisation file will be deleted
* eventhough it also relates to another course if the student is also registered to that course.
* (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
* error occured during deletion, or other exception occured.
*
* @param username student's user name
* @param courseID course id (course number + instructor name)
* @throws InvalidDBRequestException
*/
public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
ResultSet rs;
int count = 0;
// check if student registered to the course
rs =
stmt.executeQuery(
"select * from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (!rs.next())
throw new InvalidDBRequestException("Student is not registered to the course");
// remove student from the course
count =
stmt.executeUpdate(
"delete from courseRoster where course_id = '"
+ courseID
+ "' and user_login = '******'");
if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");
// delete the quiz visualization files
rs =
stmt.executeQuery(
"select distinct unique_id, s.test_name from scores s, courseTest t "
+ "where s.test_name = t.test_name "
+ "and course_id = '"
+ courseID
+ "' "
+ "and user_login = '******'");
while (rs.next()) {
deleteVisualization(rs.getString(1), username, rs.getString(2));
count =
stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
}
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addstudent: " + e.getMessage());
throw new InvalidDBRequestException("???");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
/**
* Deletes students who are older than a certain number of years and not registered to any course.
*
* @param year students older than year are candidates to be deleted
* @throws InvalidDBRequestException
*/
public void deleteOldStudent(int year) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
// query all student who have been in the database longer than a number of years and not
// registered to any course
ResultSet rs =
stmt.executeQuery(
"select login, count(course_id) "
+ "from student s left join courseRoster r on login = user_login "
+ "where date_entered < SUBDATE(now(), INTERVAL "
+ new String().valueOf(year).trim()
+ " YEAR) "
+ "group by login, date_entered");
// delete them
while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
/** Creates a Pooled connection and adds it to the connection pool. */
private void installConnection() throws EmanagerDatabaseException {
logger.debug("enter");
PooledConnection connection;
try {
connection = poolDataSource.getPooledConnection();
connection.addConnectionEventListener(this);
connection.getConnection().setAutoCommit(false);
synchronized (connectionPool) {
connectionPool.add(connection);
logger.debug("Database connection added.");
}
} catch (SQLException ex) {
logger.fatal("exception caught while obtaining database " + "connection: ex = " + ex);
SQLException ex1 = ex.getNextException();
while (ex1 != null) {
logger.fatal("chained sql exception ex1 = " + ex1);
SQLException nextEx = ex1.getNextException();
ex1 = nextEx;
}
String logString;
EmanagerDatabaseException ede;
logString =
EmanagerDatabaseStatusCode.DatabaseConnectionFailure.getStatusCodeDescription()
+ ex.getMessage();
logger.fatal(logString);
ede =
new EmanagerDatabaseException(
EmanagerDatabaseStatusCode.DatabaseConnectionFailure, logString);
throw ede;
}
}
/**
* Delete a student from the database. Also deletes the student's folders. Throws
* InvalidDBRequestException if any error in database connection.
*
* @param username student's user name
* @throws InvalidDBRequestException
*/
private void purgeStudent(String username) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
int count;
// delete from scores
count = stmt.executeUpdate("delete from scores where user_login = '******'");
// delete from student
count = stmt.executeUpdate("delete from student where login = '******'");
// delete student's folder
File studentDir = new File("./StudentQuizzes/" + username);
if (!(studentDir.delete())) {
System.err.println("Error in deleting folder for student: " + username);
}
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
@Test
public void testTypeMismatchToDateFunctionBind() throws Exception {
printTestDescription();
initATableValues();
try {
String query = null;
if (tgtPH())
query =
"SELECT a_date FROM atable WHERE organization_id='"
+ tenantId
+ "' and a_date < TO_DATE(?)";
else if (tgtSQ() || tgtTR())
query = "SELECT a_date FROM atable WHERE organization_id='" + tenantId + "' and a_date < ?";
PreparedStatement statement = conn.prepareStatement(query);
statement.setDate(1, new Date(2));
statement.executeQuery();
if (tgtPH()) fail();
} catch (SQLException e) {
if (tgtPH())
assertTrue(
e.getMessage().contains("Type mismatch. expected: [VARCHAR] but was: DATE at TO_DATE"));
} finally {
}
}
/**
* Deletes an instructor from the database. Deletes the instructor's courses by invoking the
* deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or
* other database connection problems.
*
* @see deleteCourse
* @param instructor instructor's user name
* @throws InvalidDBRequestException
*/
public void deleteInstructor(String instructor) throws InvalidDBRequestException {
try {
Class.forName(GaigsServer.DBDRIVER);
db =
DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);
Statement stmt = db.createStatement();
int count;
// delete the instructor's courses
ResultSet rs =
stmt.executeQuery(
"select course_num from course where instructor = '" + instructor + "'");
while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor);
// delete the instructor's record
count = stmt.executeUpdate("delete from instructor where login ='******'");
rs.close();
stmt.close();
db.close();
} catch (SQLException e) {
System.err.println("Invalid SQL in addCourse: " + e.getMessage());
throw new InvalidDBRequestException("??? ");
} catch (ClassNotFoundException e) {
System.err.println("Driver Not Loaded");
throw new InvalidDBRequestException("Internal Server Error");
}
}
/**
* Method called by the Form panel to delete existing data.
*
* @param persistentObject value object to delete
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response deleteRecord(ValueObject persistentObject) throws Exception {
PreparedStatement stmt = null;
try {
EmpVO vo = (EmpVO) persistentObject;
// delete from WORKING_DAYS...
stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
stmt.close();
// delete from EMP...
stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
stmt.setString(1, vo.getEmpCode());
stmt.execute();
gridFrame.reloadData();
frame.getGrid().clearData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
public static SEPeriodNoData[] getPeriodNo(
ConnectionProvider connectionProvider, String CYearId, int firstRegister, int numberRegisters)
throws ServletException {
String strSql = "";
strSql =
strSql
+ " SELECT C_Period_ID AS ID, Name AS NAME"
+ " FROM C_Period"
+ " WHERE C_Year_ID=?"
+ " ORDER BY StartDate ASC";
ResultSet result;
Vector<java.lang.Object> vector = new Vector<java.lang.Object>(0);
PreparedStatement st = null;
int iParameter = 0;
try {
st = connectionProvider.getPreparedStatement(strSql);
QueryTimeOutUtil.getInstance().setQueryTimeOut(st, SessionInfo.getQueryProfile());
iParameter++;
UtilSql.setValue(st, iParameter, 12, null, CYearId);
result = st.executeQuery();
long countRecord = 0;
long countRecordSkip = 1;
boolean continueResult = true;
while (countRecordSkip < firstRegister && continueResult) {
continueResult = result.next();
countRecordSkip++;
}
while (continueResult && result.next()) {
countRecord++;
SEPeriodNoData objectSEPeriodNoData = new SEPeriodNoData();
objectSEPeriodNoData.id = UtilSql.getValue(result, "ID");
objectSEPeriodNoData.name = UtilSql.getValue(result, "NAME");
objectSEPeriodNoData.InitRecordNumber = Integer.toString(firstRegister);
vector.addElement(objectSEPeriodNoData);
if (countRecord >= numberRegisters && numberRegisters != 0) {
continueResult = false;
}
}
result.close();
} catch (SQLException e) {
log4j.error("SQL error in query: " + strSql + "Exception:" + e);
throw new ServletException(
"@CODE=" + Integer.toString(e.getErrorCode()) + "@" + e.getMessage());
} catch (Exception ex) {
log4j.error("Exception in query: " + strSql + "Exception:" + ex);
throw new ServletException("@CODE=@" + ex.getMessage());
} finally {
try {
connectionProvider.releasePreparedStatement(st);
} catch (Exception ignore) {
ignore.printStackTrace();
}
}
SEPeriodNoData objectSEPeriodNoData[] = new SEPeriodNoData[vector.size()];
vector.copyInto(objectSEPeriodNoData);
return (objectSEPeriodNoData);
}
