@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;
}
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;
}
/**
* 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) {
}
}
}
@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 {
}
}
/** 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;
}
}
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 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 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 MetadataBlob[] select(String selectString) {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
ResultSet rs = stmt.executeQuery();
List<MetadataBlob> list = new ArrayList<MetadataBlob>();
while (rs.next()) {
MetadataBlob f =
new MetadataBlob(
rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
list.add(f);
}
return list.toArray(new MetadataBlob[0]);
} catch (SQLException sqe) {
System.err.println("Error selecting: " + selectString);
sqe.printStackTrace();
return null;
} finally {
if (stmt != null)
try {
stmt.close();
} catch (SQLException sqe2) {
sqe2.printStackTrace();
}
}
}
public static List<RssFeed> loadFeeds() {
loadDriver();
Connection conn = null;
ResultSet rs = null;
List<RssFeed> feeds = new ArrayList<>();
try {
// database name
String dbName = "demoDB";
conn = DriverManager.getConnection(protocol + dbName + ";create=true", props);
rs = conn.createStatement().executeQuery("select id, title, url from rssFeed");
while (rs.next()) {
String title = rs.getString("title");
String url = rs.getString("url");
RssFeed rssFeed = new RssFeed(title, url);
rssFeed.id = rssFeed.link.hashCode();
feeds.add(rssFeed);
}
shutdown();
} catch (SQLException sqle) {
sqle.printStackTrace();
} finally {
close(rs);
close(conn);
}
return feeds;
}
public String getCustomerByCard(String cardId)
throws ClassNotFoundException, SQLException, Exception {
String sql = "";
Connection conn = null;
PreparedStatement stm = null;
ResultSet result = null;
try {
conn = getConnection();
try {
sql =
" select t1.* from t_cif_customer t1,t_pif_card t2 "
+ " where t1.cut_id = t2.cosumer_id and t2.card_id="
+ cardId;
stm = conn.prepareStatement(sql);
result = stm.executeQuery();
result.next();
CustomerDTO temp = new CustomerDTO();
return result.getString("stuemp_no").toString();
} finally {
if (stm != null) {
stm.close();
}
}
} catch (SQLException e) {
logger.error("²éѯÊý¾Ý¿âʧ°Ü");
e.printStackTrace();
return null;
} catch (Exception e) {
logger.error("²éѯÊý¾Ý¿âʧ°Ü");
e.printStackTrace();
return null;
}
}
protected void applySecurityToGFE(Connection gConn, String sql) {
Log.getLogWriter().info("execute authorization statement in GFE");
Log.getLogWriter().info("security statement is: " + sql);
try {
Statement stmt = gConn.createStatement();
stmt.execute(sql); // execute authorization
} catch (SQLException se) {
if (se.getSQLState().equals("42506") && SQLTest.testSecurity)
Log.getLogWriter()
.info("Got the expected exception for authorization," + " continuing tests");
else if (se.getSQLState().equals("42509") && SQLTest.testSecurity)
Log.getLogWriter()
.info(
"Got the expected grant or revoke operation "
+ "is not allowed exception for authorization,"
+ " continuing tests");
else if (se.getSQLState().equals("42Y03") && hasRoutine)
Log.getLogWriter()
.info(
"Got the expected not recognized as "
+ "a function or procedure exception for authorization,"
+ " continuing tests");
else SQLHelper.handleSQLException(se);
}
}
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();
}
}
/**
* 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");
}
}
/**
* 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;
}
// execute and get results
private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
throws SQLException {
BufferedWriter buffer = new BufferedWriter(writer);
Statement stmt = conn.createStatement();
stmt.execute(text);
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metadata = rs.getMetaData();
int nbCols = metadata.getColumnCount();
String[] labels = new String[nbCols];
int[] colwidths = new int[nbCols];
int[] colpos = new int[nbCols];
int linewidth = 1;
// read each occurrence
try {
while (rs.next()) {
for (int i = 0; i < nbCols; i++) {
Object value = rs.getObject(i + 1);
if (value != null) {
buffer.write(value.toString());
if (commaSeparator) buffer.write(",");
}
}
}
buffer.flush();
rs.close();
} catch (IOException ex) {
if (Debug.isDebug()) ex.printStackTrace();
// ok, exit from the loop
} catch (SQLException ex) {
if (Debug.isDebug()) ex.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() + ")");
}
}
public CFSecuritySecDeviceBuff[] readBuffByUserIdx(
CFSecurityAuthorization Authorization, UUID SecUserId) {
final String S_ProcName = "readBuffByUserIdx";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_read_secdev_by_useridx( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ " )";
if (stmtReadBuffByUserIdx == null) {
stmtReadBuffByUserIdx = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtReadBuffByUserIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByUserIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtReadBuffByUserIdx.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtReadBuffByUserIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtReadBuffByUserIdx.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtReadBuffByUserIdx.setString(argIdx++, SecUserId.toString());
try {
resultSet = stmtReadBuffByUserIdx.executeQuery();
} catch (SQLException e) {
if (e.getErrorCode() != 1329) {
throw e;
}
resultSet = null;
}
List<CFSecuritySecDeviceBuff> buffList = new LinkedList<CFSecuritySecDeviceBuff>();
while ((resultSet != null) && resultSet.next()) {
CFSecuritySecDeviceBuff buff = unpackSecDeviceResultSetToBuff(resultSet);
buffList.add(buff);
}
int idx = 0;
CFSecuritySecDeviceBuff[] retBuff = new CFSecuritySecDeviceBuff[buffList.size()];
Iterator<CFSecuritySecDeviceBuff> iter = buffList.iterator();
while (iter.hasNext()) {
retBuff[idx++] = iter.next();
}
return (retBuff);
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = 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");
}
}
private void putUpdateStoredBlock(StoredBlock storedBlock, boolean wasUndoable)
throws SQLException {
try {
PreparedStatement s =
conn.get()
.prepareStatement(
"INSERT INTO headers(hash, chainWork, height, header, wasUndoable)"
+ " VALUES(?, ?, ?, ?, ?)");
// We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
byte[] hashBytes = new byte[28];
System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28);
s.setBytes(1, hashBytes);
s.setBytes(2, storedBlock.getChainWork().toByteArray());
s.setInt(3, storedBlock.getHeight());
s.setBytes(4, storedBlock.getHeader().unsafeRimbitSerialize());
s.setBoolean(5, wasUndoable);
s.executeUpdate();
s.close();
} catch (SQLException e) {
// It is possible we try to add a duplicate StoredBlock if we upgraded
// In that case, we just update the entry to mark it wasUndoable
if (!(e.getSQLState().equals(POSTGRES_DUPLICATE_KEY_ERROR_CODE)) || !wasUndoable) throw e;
PreparedStatement s =
conn.get().prepareStatement("UPDATE headers SET wasUndoable=? WHERE hash=?");
s.setBoolean(1, true);
// We skip the first 4 bytes because (on prodnet) the minimum target has 4 0-bytes
byte[] hashBytes = new byte[28];
System.arraycopy(storedBlock.getHeader().getHash().getBytes(), 3, hashBytes, 0, 28);
s.setBytes(2, hashBytes);
s.executeUpdate();
s.close();
}
}
/**
* 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");
}
}
/**
* 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;
}
}
/**
* 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");
}
}
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);
}
}
/**
* 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");
}
}
/**
* excluir
*
* @param VO Usuario
* @return boolean
*/
public static boolean excluir(Usuario usuario) {
SigeDataBase db = new SigeDataBase();
try {
db.getConn().setAutoCommit(false);
db.prepareStatement("DELETE FROM permissao_usuario WHERE cd_usuario=?");
db.setInt(1, usuario.getCodigo());
db.executeUpdate();
db.getPreparedStatement().close();
db.prepareStatement("DELETE FROM usuarios WHERE cd_usuario=?");
db.setInt(1, usuario.getCodigo());
db.executeUpdate();
db.getConn().commit();
db.closeConnection();
return true;
} catch (SQLException ex) {
ex.printStackTrace();
try {
db.getConn().rollback();
db.closeConnection();
} catch (SQLException ex1) {
ex1.printStackTrace();
db.closeConnection();
}
return false;
}
}
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;
}