private String getMonth(String month) {
String query;
String i_month = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";
USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, month);
rs = pstmt.executeQuery();
if (rs.next()) {
i_month = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return i_month;
}
@Test
public void testPreparedStatementPooling() throws Exception {
Connection conn = getConnection();
assertNotNull(conn);
PreparedStatement stmt1 = conn.prepareStatement("select 'a' from dual");
assertNotNull(stmt1);
PreparedStatement stmt2 = conn.prepareStatement("select 'b' from dual");
assertNotNull(stmt2);
assertTrue(stmt1 != stmt2);
// go over the maxOpen limit
PreparedStatement stmt3 = null;
try (PreparedStatement ps = conn.prepareStatement("select 'c' from dual")) {
fail("expected SQLException");
} catch (SQLException e) {
}
// make idle
stmt2.close();
// test cleanup the 'b' statement
stmt3 = conn.prepareStatement("select 'c' from dual");
assertNotNull(stmt3);
assertTrue(stmt3 != stmt1);
assertTrue(stmt3 != stmt2);
// normal reuse of statement
stmt1.close();
PreparedStatement stmt4 = conn.prepareStatement("select 'a' from dual");
assertNotNull(stmt4);
}
protected void saveAccount(String name, double money) {
if (accountExists(name)) {
String sql = "UPDATE " + accounts + " SET " + pbalance + "=? WHERE " + pname + "=?";
try {
PreparedStatement prest = database.prepare(sql);
prest.setDouble(1, money);
prest.setString(2, name);
prest.executeUpdate();
prest.close();
} catch (SQLException e) {
}
} else {
String sql = "INSERT INTO " + accounts + " (" + pname + ", " + pbalance + ") VALUES (?, ?)";
try {
PreparedStatement prest = database.prepare(sql);
prest.setString(1, name);
prest.setDouble(2, money);
prest.executeUpdate();
prest.close();
} catch (SQLException e) {
}
}
}
/**
* @param conn
* @param fingerprint
* @param sampleID
* @return
* @throws SQLException
*/
private static void insertSampleSet(Connection conn, Fingerprint fingerprint, Integer sampleID)
throws SQLException {
if (fingerprint.getSampleSetID() == null) {
/*
* Insert whole new SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleID`) VALUES(?);";
PreparedStatement insertSampleSet =
conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
insertSampleSet.setInt(1, sampleID);
insertSampleSet.execute();
ResultSet rs = insertSampleSet.getGeneratedKeys();
if (rs.next()) {
fingerprint.setSampleSetID(rs.getInt(1));
}
rs.close();
insertSampleSet.close();
} else {
/*
* Insert new SampleID for existing SampleSetID.
*/
String query = "INSERT INTO `SampleSets`(`SampleSetID`,`SampleID`) VALUES(?, ?);";
PreparedStatement insertSampleSet = conn.prepareStatement(query);
insertSampleSet.setInt(1, fingerprint.getSampleSetID());
insertSampleSet.setInt(2, sampleID);
insertSampleSet.execute();
insertSampleSet.close();
}
}
/**
* ************************************************************************ Lineas de Remesa
*
* @param whereClause where clause or null (starting with AND)
* @return lines
*/
public MRemesaLine[] getLines(String whereClause, String orderClause) {
ArrayList list = new ArrayList();
StringBuffer sql = new StringBuffer("SELECT * FROM C_RemesaLine WHERE C_Remesa_ID=? ");
if (whereClause != null) sql.append(whereClause);
if (orderClause != null) sql.append(" ").append(orderClause);
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql.toString(), get_TrxName());
pstmt.setInt(1, getC_Remesa_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new MRemesaLine(getCtx(), rs));
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.saveError("getLines - " + sql, e);
} finally {
try {
if (pstmt != null) pstmt.close();
} catch (Exception e) {
}
pstmt = null;
}
//
MRemesaLine[] lines = new MRemesaLine[list.size()];
list.toArray(lines);
return lines;
} // getLines
public void test_prepCall_1() throws Exception {
DruidPooledConnection conn = dataSource.getConnection().unwrap(DruidPooledConnection.class);
MockPreparedStatement raw = null;
{
PreparedStatement stmt =
conn.prepareCall("SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
raw = stmt.unwrap(MockPreparedStatement.class);
stmt.close();
}
{
PreparedStatement stmt =
conn.prepareCall("SELECT 1", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
Assert.assertEquals(raw, stmt.unwrap(MockPreparedStatement.class));
stmt.close();
}
conn.getConnectionHolder().toString();
conn.getConnectionHolder().setLastActiveTimeMillis(0);
conn.getConnectionHolder().toString();
conn.getConnectionHolder().getUseCount();
conn.getConnectionHolder().getTimeMillis();
conn.close();
}
public static String saveSearchResult(String uuid, String building_ids, int srid, Connection conn)
throws Exception {
PreparedStatement psDelete = null;
PreparedStatement psAdd = null;
PreparedStatement psGet = null;
ResultSet rs = null;
try {
psDelete =
conn.prepareStatement(
"delete from cust_search_result where uu_id=? or create_time<now() - cast('1 day' as interval)");
psDelete.setString(1, uuid);
psDelete.executeUpdate();
if (building_ids.length() > 0) {
psAdd =
conn.prepareStatement(
"insert into cust_search_result (buid,uu_id) select distinct building_id,? from maps.building_to_customers "
+ "where building_id = ANY(string_to_array(?,',')::int[])");
psAdd.setString(1, uuid);
psAdd.setString(2, building_ids);
psAdd.executeUpdate();
psGet =
conn.prepareStatement(
"select ST_AsText(ST_transform(ST_Buffer(ST_Envelope (ST_Collect(the_geom)),100),?)) from buildings b"
+ " where buid= ANY(string_to_array(?,',')::int[])");
psGet.setInt(1, srid);
psGet.setString(2, building_ids);
rs = psGet.executeQuery();
if (rs.next()) {
String res = rs.getString(1);
return res;
}
}
return "";
} finally {
try {
psDelete.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psAdd.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psGet.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
rs.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
}
public boolean existeUsuario(Usuario usuario) {
String sql = "select * from usuario where usuario= ? and senha= ?";
try {
PreparedStatement stmt = this.connection.prepareStatement(sql);
stmt.setString(1, usuario.getUsuario());
stmt.setString(2, usuario.getSenha());
ResultSet rs = stmt.executeQuery();
// verifica se existe retorno na consulta
if (rs.next()) {
stmt.close();
return true;
} else {
stmt.close();
return false;
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
@Override
public void update(Connection conn, StatsRecordDAO r) throws SQLException {
if (r.getWord() == null) return;
PreparedStatement updateWords =
conn.prepareStatement(
"UPDATE words SET d = ?, nick = ?, word = ?, "
+ "repetitions = (repetitions+1) WHERE d = ? AND nick = ? "
+ "AND word = ?");
updateWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
updateWords.setString(2, r.getNick());
updateWords.setString(3, r.getWord());
updateWords.setDate(4, new java.sql.Date(r.getDate().getTime()));
updateWords.setString(5, r.getNick());
updateWords.setString(6, r.getWord());
int ret = updateWords.executeUpdate();
updateWords.close();
if (ret == 0) {
PreparedStatement insertWords = conn.prepareStatement("INSERT INTO words values(?, ?, ?, 1)");
insertWords.setDate(1, new java.sql.Date(r.getDate().getTime()));
insertWords.setString(2, r.getNick());
insertWords.setString(3, r.getWord());
insertWords.executeUpdate();
insertWords.close();
}
}
public String createVideo(String videoname, String videourl) throws SQLException {
PreparedStatement preparedStatement =
mysqlCon.prepareStatement("SELECT * FROM `IKSvideotag`.`video` WHERE `url`=?");
preparedStatement.setString(1, videourl);
ResultSet dataSet = preparedStatement.executeQuery();
String videoid = "";
if (dataSet.next()) {
videoid = dataSet.getString(1);
preparedStatement.close();
preparedStatement =
mysqlCon.prepareStatement("UPDATE `IKSvideotag`.`video` SET `name`=? WHERE `url`=?");
preparedStatement.setString(1, videoname);
preparedStatement.setString(2, videourl);
preparedStatement.executeUpdate();
preparedStatement.close();
} else {
preparedStatement.close();
preparedStatement =
mysqlCon.prepareStatement(
"INSERT INTO `IKSvideotag`.`video` (`name`,`url`) VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, videoid);
preparedStatement.setString(2, videourl);
preparedStatement.executeUpdate();
ResultSet rs = preparedStatement.getGeneratedKeys();
if (rs.next()) {
videoid = String.valueOf(rs.getInt(1));
}
preparedStatement.close();
}
return videoid;
}
/**
* Close the specified database connection.
*
* @param dbConnection The connection to be closed
*/
protected void close(Connection dbConnection) {
// Do nothing if the database connection is already closed
if (dbConnection == null) return;
// Close our prepared statements (if any)
try {
preparedCredentials.close();
} catch (Throwable f) {;
}
try {
preparedRoles.close();
} catch (Throwable f) {;
}
// Close this database connection, and log any errors
try {
dbConnection.close();
} catch (SQLException e) {
log(sm.getString("jdbcRealm.close"), e); // Just log it here
}
// Release resources associated with the closed connection
this.dbConnection = null;
this.preparedCredentials = null;
this.preparedRoles = null;
}
@Override
public AuthToken createAuthToken(String userid) throws SQLException {
Connection connection = null;
PreparedStatement stmt = null;
String token = null;
AuthToken authToken = null;
try {
connection = Database.getConnection();
stmt = connection.prepareStatement(AuthTokenDAOQuery.UUID);
ResultSet rs = stmt.executeQuery();
if (rs.next()) token = rs.getString(1);
else throw new SQLException();
stmt.close();
stmt = connection.prepareStatement(AuthTokenDAOQuery.CREATE_TOKEN);
stmt.setString(1, userid);
stmt.setString(2, token);
stmt.executeUpdate();
authToken = new AuthToken();
authToken.setToken(token);
authToken.setUserid(userid);
} catch (SQLException e) {
throw e;
} finally {
if (stmt != null) stmt.close();
if (connection != null) connection.close();
}
return authToken;
}
@Override
public UserInfo getUserByAuthToken(String token) throws SQLException {
UserInfo userInfo = null;
Connection connection = null;
PreparedStatement stmt = null;
try {
connection = Database.getConnection();
stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_USER_BY_TOKEN);
stmt.setString(1, token);
ResultSet rs = stmt.executeQuery();
if (rs.next()) {
userInfo = new UserInfo();
userInfo.setName(rs.getString("id"));
stmt.close();
stmt = connection.prepareStatement(AuthTokenDAOQuery.GET_ROLES_OF_USER);
stmt.setString(1, userInfo.getName());
rs = stmt.executeQuery();
while (rs.next()) {
String role = rs.getString("role");
userInfo.getRoles().add(Role.valueOf(role));
}
}
} catch (SQLException e) {
throw e;
} finally {
if (stmt != null) stmt.close();
if (connection != null) connection.close();
}
return userInfo;
}
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();
}
}
/* ------------------------------------------------------------ */
@Override
protected UserIdentity loadUser(String username) {
try {
if (null == _con) connectDatabase();
if (null == _con) throw new SQLException("Can't connect to database");
PreparedStatement stat = _con.prepareStatement(_userSql);
stat.setObject(1, username);
ResultSet rs = stat.executeQuery();
if (rs.next()) {
int key = rs.getInt(_userTableKey);
String credentials = rs.getString(_userTablePasswordField);
stat.close();
stat = _con.prepareStatement(_roleSql);
stat.setInt(1, key);
rs = stat.executeQuery();
List<String> roles = new ArrayList<String>();
while (rs.next()) roles.add(rs.getString(_roleTableRoleField));
stat.close();
return putUser(
username,
Credential.getCredential(credentials),
roles.toArray(new String[roles.size()]));
}
} catch (SQLException e) {
LOG.warn("UserRealm " + getName() + " could not load user information from database", e);
closeConnection();
}
return null;
}
public boolean adicionaStatus(StatusChamado status) throws BusinessException {
Connection con = null;
String sql = null;
try {
con = Conexao.getInstance().obterConexao();
con.setAutoCommit(false);
String origem = Conexao.getInstance().obterOrigem();
sql = FabricaSql.getSql(origem + INSERIR_STATUS);
if (DEBUG) System.out.println(sql);
PreparedStatement stmt = con.prepareStatement(sql);
// TODO -colocar os parametros
int qtd = stmt.executeUpdate();
if (DEBUG) System.out.println("QTDE: " + qtd);
if (qtd != 1) {
con.rollback();
stmt.close();
throw new BusinessException("Quantidade de linhas afetadas inválida: " + qtd);
} else con.commit();
stmt.close();
} catch (SQLException e) {
SQLExceptionHandler.tratarSQLException(this.getClass().getName(), e);
return false;
} finally {
Conexao.getInstance().fecharConexao(con);
}
return true;
}
public boolean isStopWord(String word) throws SQLException {
String query = "select labels from Word where content like ?";
PreparedStatement statement = connection.prepareStatement(query);
statement.setString(1, word);
ResultSet set = statement.executeQuery();
if (!set.next()) {
set.close();
statement.close();
return false;
} else {
String label = set.getString(1);
set.close();
statement.close();
if (label == null) return false;
else if (label.indexOf("stop") != -1) return true;
else return false;
}
}
public static void insert(String sUserID, String accessCode) {
PreparedStatement ps = null;
String sInsert =
"INSERT INTO AccessLogs (accessid, userid, accesstime,accesscode) VALUES (?, ?, ?,?)";
Connection ad_conn = MedwanQuery.getInstance().getAdminConnection();
try {
ps = ad_conn.prepareStatement(sInsert);
ps.setInt(1, MedwanQuery.getInstance().getOpenclinicCounter("AccessLogs"));
ps.setInt(2, Integer.parseInt(sUserID));
ps.setTimestamp(3, ScreenHelper.getSQLTime());
ps.setString(4, accessCode);
ps.executeUpdate();
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (ps != null) ps.close();
ad_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
private void saveBuildings(int building_id, String customers, Connection conn) throws Exception {
PreparedStatement psDelete = null;
PreparedStatement psAdd = null;
try {
psDelete =
conn.prepareStatement("delete from maps.building_to_customers where building_id=?");
psDelete.setInt(1, building_id);
psDelete.executeUpdate();
if (customers.length() > 0) {
String cusIds[] = customers.split(",");
psAdd =
conn.prepareStatement(
"insert into maps.building_to_customers (building_id,cusid) values (?,?)");
for (String cusId : cusIds) {
psAdd.setInt(1, building_id);
psAdd.setInt(2, new Integer(cusId));
psAdd.addBatch();
}
psAdd.executeBatch();
}
} finally {
try {
psDelete.close();
} catch (Exception e2) {
// TODO: handle exception
}
try {
psAdd.close();
} catch (Exception e2) {
// TODO: handle exception
}
}
}
public static List getLastAccess(String patientId, int nb) {
PreparedStatement ps = null;
ResultSet rs = null;
List l = new LinkedList();
String sSelect = " SELECT * FROM AccessLogs a WHERE accesscode = ? ORDER BY accessid DESC";
Connection ad_conn = MedwanQuery.getInstance().getAdminConnection();
try {
ps = ad_conn.prepareStatement(sSelect);
ps.setString(1, patientId);
rs = ps.executeQuery();
int i = 0;
while (rs.next()) {
if (nb == 0 || i <= nb) {
Object sReturn[] = {rs.getTimestamp("accesstime"), rs.getString("userid")};
l.add(sReturn);
}
i++;
}
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if (rs != null) rs.close();
if (ps != null) ps.close();
ad_conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
return l;
}
public void test_prepCall() throws Exception {
DruidPooledConnection conn = dataSource.getConnection().unwrap(DruidPooledConnection.class);
MockPreparedStatement raw = null;
{
PreparedStatement stmt =
conn.prepareCall(
"SELECT 1",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
raw = stmt.unwrap(MockPreparedStatement.class);
stmt.close();
}
{
PreparedStatement stmt =
conn.prepareCall(
"SELECT 1",
ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY,
ResultSet.HOLD_CURSORS_OVER_COMMIT);
Assert.assertEquals(raw, stmt.unwrap(MockPreparedStatement.class));
stmt.close();
}
conn.close();
}
public void closeConnections() throws SQLException {
if (itemPreparedStatement != null) {
itemPreparedStatement.close();
}
if (itemConnection != null) {
itemConnection.close();
}
if (holdingsPreparedStatement != null) {
holdingsPreparedStatement.close();
}
if (holdingsConnection != null) {
holdingsConnection.close();
}
if (bibResultSet != null) {
bibResultSet.close();
}
if (bibStatement != null) {
bibStatement.close();
}
if (bibConnection != null) {
bibConnection.close();
}
if (connection != null) {
connection.close();
}
}
public void copyData(Connection conn) throws SQLException {
ResultSet rs = null;
PreparedStatement stmt = null;
PreparedStatement stmt2 = null;
String q2 = "insert into modmodlinks2 (pModID,cModID,courseID) values (?,?,?)";
try {
String q =
"select parentModID,childModID,courseID from modmodulelinks order by parentModId,childModID";
stmt = conn.prepareStatement(q);
rs = stmt.executeQuery();
while (rs.next()) {
int p = rs.getInt(1);
int c = rs.getInt(2);
int crs = rs.getInt(3);
stmt2 = conn.prepareStatement(q2);
stmt2.setInt(1, p);
stmt2.setInt(2, c);
stmt2.setInt(3, crs);
System.out.println("Insert p=" + p + " c=" + c + " crs=" + crs);
stmt2.executeUpdate();
stmt2.close();
}
} finally {
if (stmt != null) stmt.close();
if (rs != null) rs.close();
}
}
/**
* This method queries the database to get the name of the Billing System.
*
* @exception SQLException, if query fails
* @author
*/
public String getBlgsysnm(String blgsys) {
String query;
String blgsysnm = "";
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select bs_nm from blg_sys where bs_id = ?";
USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, blgsys);
rs = pstmt.executeQuery();
if (rs.next()) {
blgsysnm = rs.getString(1);
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
}
}
return blgsysnm;
}
/**
* @param date
* @param vipType 黄钻为0，红钻为1
* @param type 活跃用户为0，新增用户为1
* @param list
* @throws SQLException
*/
private void saveRecord(Date date, int vipType, int type, int[][] list) throws SQLException {
PreparedStatement pstmt;
for (int isYearVip = 0; isYearVip < 2; isYearVip++) {
for (int vipLevel = 0; vipLevel < 8; vipLevel++) {
pstmt =
con.prepareStatement(
"delete from vip_level where date=? and type=? and is_year_vip=? and vip_level=? and vip_type=?");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.execute();
pstmt.close();
pstmt =
con.prepareStatement(
"insert into vip_level(date,type,is_year_vip,vip_level,vip_type,count) values(?,?,?,?,?,?)");
pstmt.setDate(1, new java.sql.Date(date.getTime()));
pstmt.setInt(2, type);
pstmt.setInt(3, isYearVip);
pstmt.setInt(4, vipLevel);
pstmt.setInt(5, vipType);
pstmt.setInt(6, list[isYearVip][vipLevel]);
pstmt.execute();
pstmt.close();
}
}
}
/**
* This method queries the database to get the list of the Billing Systems.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getYears(String year) {
String query;
Vector years = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, year);
rs = pstmt.executeQuery();
while (rs.next()) {
years.addElement(rs.getString(1));
years.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return years;
}
/**
* 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) {
}
}
}
/**
* This method queries the database to get the details related to the bp_id passed.
*
* @exception SQLException, if query fails
* @author
*/
public Vector getBpdet(String bpid) {
String query;
Vector bpdet = new Vector();
PreparedStatement pstmt = null;
ResultSet rs = null;
query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";
try {
pstmt = conn.prepareStatement(query);
pstmt.setString(1, bpid);
rs = pstmt.executeQuery();
while (rs.next()) {
bpdet.addElement(rs.getString(1));
bpdet.addElement(rs.getString(2));
}
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException ex) {
USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
try {
if (rs != null) rs.close();
if (pstmt != null) pstmt.close();
} catch (SQLException e) {
USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
}
}
return bpdet;
}
/**
* Get Details
*
* @return array of details
*/
public MCommissionDetail[] getDetails() {
String sql = "SELECT * FROM C_CommissionDetail WHERE C_CommissionAmt_ID=?";
ArrayList<MCommissionDetail> list = new ArrayList<MCommissionDetail>();
PreparedStatement pstmt = null;
try {
pstmt = DB.prepareStatement(sql, get_TrxName());
pstmt.setInt(1, getC_CommissionAmt_ID());
ResultSet rs = pstmt.executeQuery();
while (rs.next()) list.add(new MCommissionDetail(getCtx(), rs, get_TrxName()));
rs.close();
pstmt.close();
pstmt = null;
} catch (Exception e) {
log.error(sql, e);
}
try {
if (pstmt != null) pstmt.close();
pstmt = null;
} catch (Exception e) {
pstmt = null;
}
// Convert
MCommissionDetail[] retValue = new MCommissionDetail[list.size()];
list.toArray(retValue);
return retValue;
} // getDetails
public void excluir(Balanca balanca) throws ClassNotFoundException {
this.balanca = balanca;
String sql = "delete from BALANCA" + " where IDBALANCA = ?";
try {
Connection con = new ConnectionFactory().getConnection();
PreparedStatement stmt = con.prepareStatement(sql);
stmt.setInt(1, this.balanca.getIdBalanca());
stmt.executeUpdate();
stmt.close();
con.close();
// SQL Server
Connection conS = new ConnectionFactory().getServer();
stmt = conS.prepareStatement(sql);
stmt.setInt(1, this.balanca.getIdBalanca());
stmt.executeUpdate();
stmt.close();
conS.close();
} catch (SQLException e) {
throw new RuntimeException(e + "Erro na Conexão");
}
}