public void updateServiceType(
CFSecurityAuthorization Authorization, CFSecurityServiceTypeBuff Buff) {
final String S_ProcName = "updateServiceType";
ResultSet resultSet = null;
try {
int ServiceTypeId = Buff.getRequiredServiceTypeId();
String Description = Buff.getRequiredDescription();
int Revision = Buff.getRequiredRevision();
Connection cnx = schema.getCnx();
String sql = "exec sp_update_svctype ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?";
if (stmtUpdateByPKey == null) {
stmtUpdateByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtUpdateByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtUpdateByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtUpdateByPKey.setString(argIdx++, "SVCT");
stmtUpdateByPKey.setInt(argIdx++, ServiceTypeId);
stmtUpdateByPKey.setString(argIdx++, Description);
stmtUpdateByPKey.setInt(argIdx++, Revision);
resultSet = stmtUpdateByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityServiceTypeBuff updatedBuff = unpackServiceTypeResultSetToBuff(resultSet);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
Buff.setRequiredDescription(updatedBuff.getRequiredDescription());
Buff.setRequiredRevision(updatedBuff.getRequiredRevision());
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected a single-record response, " + resultSet.getRow() + " rows selected");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
public StoredTransactionOutput getTransactionOutput(Sha256Hash hash, long index)
throws BlockStoreException {
maybeConnect();
PreparedStatement s = null;
try {
s =
conn.get()
.prepareStatement(
"SELECT height, value, scriptBytes FROM openOutputs "
+ "WHERE hash = ? AND index = ?");
s.setBytes(1, hash.getBytes());
// index is actually an unsigned int
s.setInt(2, (int) index);
ResultSet results = s.executeQuery();
if (!results.next()) {
return null;
}
// Parse it.
int height = results.getInt(1);
BigInteger value = new BigInteger(results.getBytes(2));
// Tell the StoredTransactionOutput that we are a coinbase, as that is encoded in height
StoredTransactionOutput txout =
new StoredTransactionOutput(hash, index, value, height, true, results.getBytes(3));
return txout;
} catch (SQLException ex) {
throw new BlockStoreException(ex);
} finally {
if (s != null)
try {
s.close();
} catch (SQLException e) {
throw new BlockStoreException("Failed to close PreparedStatement");
}
}
}
@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;
}
/**
* Tests the implementation of getCharacterStream(long pos, long length).
*
* @throws Exception
*/
public void testGetCharacterStreamLong() throws Exception {
String str1 = "This is a test String. This is a test String";
Reader r1 = new java.io.StringReader(str1);
PreparedStatement ps = prepareStatement("insert into BLOBCLOB(ID, CLOBDATA) values(?,?)");
int id = BlobClobTestSetup.getID();
ps.setInt(1, id);
ps.setCharacterStream(2, r1);
ps.execute();
ps.close();
Statement st = createStatement();
ResultSet rs = st.executeQuery("select CLOBDATA from " + "BLOBCLOB where ID=" + id);
rs.next();
Clob clob = rs.getClob(1);
Reader r_1 = clob.getCharacterStream(2L, 5L);
String str2 = str1.substring(1, 6);
Reader r_2 = new java.io.StringReader(str2);
assertEquals(r_2, r_1);
rs.close();
st.close();
}
/**
* Returns the siteId for the site with the given source, name, and type. If no such site is
* found, this method returns 0;
*/
private static int queryLookupSiteId(
int sourceID, String siteInternalName, int siteType, Connection c) throws SQLException {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps =
c.prepareStatement(
"select site_id from dat_customer_sites "
+ "where source_id = ? and internal_name = ? and site_type = ?");
int siteID = 0;
ps.setInt(1, sourceID);
ps.setString(2, siteInternalName);
ps.setInt(3, siteType);
rs = ps.executeQuery();
if (rs.next()) {
siteID = rs.getInt(1);
}
return siteID;
} finally {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
}
}
/** Test large batch behavior. */
public void testLargeBatch() throws Exception {
final int n = 5000;
getConnection().close();
Statement stmt = con.createStatement();
stmt.executeUpdate("create table #testLargeBatch (val int)");
stmt.executeUpdate("insert into #testLargeBatch (val) values (0)");
PreparedStatement pstmt = con.prepareStatement("update #testLargeBatch set val=? where val=?");
for (int i = 0; i < n; i++) {
pstmt.setInt(1, i + 1);
pstmt.setInt(2, i);
pstmt.addBatch();
}
int counts[] = pstmt.executeBatch();
// System.out.println(pstmt.getWarnings());
assertEquals(n, counts.length);
for (int i = 0; i < n; i++) {
assertEquals(1, counts[i]);
}
pstmt.close();
ResultSet rs = stmt.executeQuery("select count(*) from #testLargeBatch");
assertTrue(rs.next());
assertEquals(1, rs.getInt(1));
assertFalse(rs.next());
rs.close();
stmt.close();
}
@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);
}
}
}
}
public void deleteURLProtocol(
CFSecurityAuthorization Authorization, CFInternetURLProtocolBuff Buff) {
final String S_ProcName = "deleteURLProtocol";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
int URLProtocolId = Buff.getRequiredURLProtocolId();
String sql =
"SELECT "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_urlproto( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " ) as DeletedFlag";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setInt(argIdx++, URLProtocolId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
resultSet = stmtDeleteByPKey.executeQuery();
if (resultSet.next()) {
boolean deleteFlag = resultSet.getBoolean(1);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
void update_war(int db_id, long duration, long remaining, long time_to_start, int current_chain) {
Connection con = null;
try {
con = pool.getConnection(timeout);
PreparedStatement s =
con.prepareStatement(
"UPDATE `wars` SET `duration` = ? ,`remaining` = ?, `time_to_start` = ?, `current_chain` = ? WHERE id = ?");
s.setLong(1, duration);
s.setLong(2, remaining);
s.setLong(3, time_to_start);
s.setInt(4, current_chain);
s.setInt(5, db_id);
s.executeUpdate();
s.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
}
@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;
}
/**
* Returns the previous period
*
* @param period MPeriod
* @param periodCount Count
* @param trx trx
* @param ctx Ctx
* @return MPeriod
*/
public static MPeriod getPreviousPeriod(MPeriod period, Ctx ctx, Trx trx) {
MPeriod newPeriod = null;
String sql =
"SELECT * FROM C_Period WHERE "
+ "C_Period.IsActive='Y' AND PeriodType='S' "
+ "AND C_Period.C_Year_ID IN "
+ "(SELECT C_Year_ID FROM C_Year WHERE C_Year.C_Calendar_ID = ? ) "
+ "AND ((C_Period.C_Year_ID * 1000) + C_Period.PeriodNo) "
+ " < ((? * 1000) + ?) ORDER BY C_Period.C_Year_ID DESC, C_Period.PeriodNo DESC";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, period.getC_Calendar_ID());
pstmt.setInt(2, period.getC_Year_ID());
pstmt.setInt(3, period.getPeriodNo());
rs = pstmt.executeQuery();
if (rs.next()) newPeriod = new MPeriod(ctx, rs, trx);
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
return newPeriod;
}
public void deleteSecForm(CFSecurityAuthorization Authorization, CFSecuritySecFormBuff Buff) {
final String S_ProcName = "deleteSecForm";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
long ClusterId = Buff.getRequiredClusterId();
int SecFormId = Buff.getRequiredSecFormId();
final String sql =
"CALL sp_delete_secform( ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " )";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setLong(argIdx++, ClusterId);
stmtDeleteByPKey.setInt(argIdx++, SecFormId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
resultSet = stmtDeleteByPKey.executeQuery();
if (resultSet.next()) {
int deleteFlag = resultSet.getInt(1);
if (resultSet.next()) {
resultSet.last();
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Did not expect multi-record response, " + resultSet.getRow() + " rows selected");
}
} else {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(
getClass(),
S_ProcName,
"Expected 1 record result set to be returned by delete, not 0 rows");
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* Elimina al registro a través de la entidad dada por vData.
*
* <p><b> delete from GRLRegistroPNC where iEjercicio = ? AND iConsecutivoPNC = ? </b>
*
* <p><b> Campos Llave: iEjercicio,iConsecutivoPNC, </b>
*
* @param vData TVDinRep - VO Dinámico que contiene a la entidad a Insertar.
* @param cnNested Connection - Conexión anidada que permite que el método se encuentre dentro de
* una transacción mayor.
* @throws DAOException - Excepción de tipo DAO
* @return boolean - En caso de ser o no eliminado el registro.
*/
public boolean delete(TVDinRep vData, Connection cnNested) throws DAOException {
DbConnection dbConn = null;
Connection conn = cnNested;
PreparedStatement lPStmt = null;
boolean lSuccess = true;
String cMsg = "";
try {
if (cnNested == null) {
dbConn = new DbConnection(dataSourceName);
conn = dbConn.getConnection();
conn.setAutoCommit(false);
conn.setTransactionIsolation(2);
}
// Ajustar Where de acuerdo a requerimientos...
String lSQL = "delete from GRLRegistroPNC where iEjercicio = ? AND iConsecutivoPNC = ? ";
// ...
lPStmt = conn.prepareStatement(lSQL);
lPStmt.setInt(1, vData.getInt("iEjercicio"));
lPStmt.setInt(2, vData.getInt("iConsecutivoPNC"));
lPStmt.executeUpdate();
if (cnNested == null) {
conn.commit();
}
} catch (SQLException sqle) {
lSuccess = false;
cMsg = "" + sqle.getErrorCode();
} catch (Exception ex) {
warn("delete", ex);
if (cnNested == null) {
try {
conn.rollback();
} catch (Exception e) {
fatal("delete.rollback", e);
}
}
lSuccess = false;
} finally {
try {
if (lPStmt != null) {
lPStmt.close();
}
if (cnNested == null) {
if (conn != null) {
conn.close();
}
dbConn.closeConnection();
}
} catch (Exception ex2) {
warn("delete.close", ex2);
}
if (lSuccess == false) throw new DAOException(cMsg);
return lSuccess;
}
}
int create_war(
Channel channel,
String starter,
String name,
long base_duration,
long duration,
long remaining,
long time_to_start,
int total_chains,
int current_chain,
int break_duration,
boolean do_randomness) {
int id = 0;
Connection con = null;
try {
con = pool.getConnection(timeout);
PreparedStatement s =
con.prepareStatement(
"INSERT INTO `wars` (`channel`, `starter`, `name`, `base_duration`, `randomness`, `delay`, `duration`, `remaining`, `time_to_start`, `total_chains`, `current_chain`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
Statement.RETURN_GENERATED_KEYS);
s.setString(1, channel.getName().toLowerCase());
s.setString(2, starter);
s.setString(3, name);
s.setLong(4, base_duration);
s.setBoolean(5, do_randomness);
s.setLong(6, break_duration);
s.setLong(7, duration);
s.setLong(8, remaining);
s.setLong(9, time_to_start);
s.setInt(10, total_chains);
s.setInt(11, current_chain);
s.executeUpdate();
ResultSet rs = s.getGeneratedKeys();
if (rs.next()) {
id = rs.getInt(1);
}
rs.close();
s.close();
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
} finally {
try {
if (con != null) {
con.close();
}
} catch (SQLException ex) {
Logger.getLogger(DBAccess.class.getName()).log(Level.SEVERE, null, ex);
}
}
return id;
}
public boolean update(Local_dept local_dept) throws SQLException {
PreparedStatement stmt =
conexao.prepareStatement("update local_dept set cod_local = ? where num_dept = ? ");
stmt.setInt(1, local_dept.getCod_local());
stmt.setInt(2, local_dept.getNum_dept());
int linhas = stmt.executeUpdate();
stmt.close();
return linhas > 0;
}
public void insertIntoDB(PreparedStatement ps, int id) throws SQLException {
if (dbid != -1) {
throw new IllegalArgumentException("Already in DB!");
}
ps.setInt(1, id);
ps.setInt(2, mapping.getDBID());
ps.setString(3, name1);
ps.setInt(4, genome1.getDBID());
ps.setString(5, name2);
ps.setInt(6, genome2.getDBID());
ps.executeUpdate();
dbid = id;
}
/** Test batched prepared statements. */
public void testPrepStmtBatch() throws Exception {
Statement stmt = con.createStatement();
stmt.execute("create table #testbatch (id int, data varchar(255))");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO #testbatch VALUES (?, ?)");
for (int i = 0; i < 5; i++) {
if (i == 2) {
pstmt.setString(1, "xxx");
} else {
pstmt.setInt(1, i);
}
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
int x[];
try {
x = pstmt.executeBatch();
} catch (BatchUpdateException e) {
x = e.getUpdateCounts();
}
if (con.getMetaData().getDatabaseProductName().toLowerCase().startsWith("microsoft")) {
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
} else {
// Sybase - Entire batch fails due to data conversion error
// detected in statement 3
assertEquals(5, x.length);
assertEquals(EXECUTE_FAILED, x[0]);
assertEquals(EXECUTE_FAILED, x[1]);
assertEquals(EXECUTE_FAILED, x[2]);
assertEquals(EXECUTE_FAILED, x[3]);
assertEquals(EXECUTE_FAILED, x[4]);
}
// Now without errors
stmt.execute("TRUNCATE TABLE #testbatch");
for (int i = 0; i < 5; i++) {
pstmt.setInt(1, i);
pstmt.setString(2, "This is line " + i);
pstmt.addBatch();
}
x = pstmt.executeBatch();
assertEquals(5, x.length);
assertEquals(1, x[0]);
assertEquals(1, x[1]);
assertEquals(1, x[2]);
assertEquals(1, x[3]);
assertEquals(1, x[4]);
}
public void deleteTSecGroup(CFSecurityAuthorization Authorization, CFSecurityTSecGroupBuff Buff) {
final String S_ProcName = "deleteTSecGroup";
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
long TenantId = Buff.getRequiredTenantId();
int TSecGroupId = Buff.getRequiredTSecGroupId();
String sql =
"call "
+ schema.getLowerDbSchemaName()
+ ".sp_delete_tsecgrp( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtDeleteByPKey == null) {
stmtDeleteByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtDeleteByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtDeleteByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtDeleteByPKey.setLong(argIdx++, TenantId);
stmtDeleteByPKey.setInt(argIdx++, TSecGroupId);
stmtDeleteByPKey.setInt(argIdx++, Buff.getRequiredRevision());
;
stmtDeleteByPKey.executeUpdate();
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
private static Map saveSites(ExchSite3[] sites, int siteType, int sourceID, Connection c)
throws SQLException {
PreparedStatement insert = null;
PreparedStatement delete = null;
try {
insert =
c.prepareStatement(
"insert into dat_customer_sites (site_id, site_type, source_id, internal_name, display_name) "
+ "values (?, ?, ?, ?, ?)");
delete = c.prepareStatement("delete from dat_customer_sites where site_id = ?");
Map siteIDs = new HashMap(sites.length * 2 + 1);
for (int i = 0; i < sites.length; i++) {
ExchSite3 site = sites[i];
int siteID = queryLookupSiteId(sourceID, site.getInternalName(), siteType, c);
if (siteID == 0) {
// if we couldn't find an existing siteID, grab the next one from the sequence
siteID = getNextFromSequence("seq_site_id", c);
} else {
// if there is an existing siteID, delete it so we can insert the changes
delete.setInt(1, siteID);
int deleted = delete.executeUpdate();
if (deleted != 1) {
throw new SQLException("Delete for siteID " + siteID + " returned " + deleted);
}
}
siteIDs.put(site.getInternalName(), siteID);
insert.setInt(1, siteID);
insert.setInt(2, siteType);
insert.setInt(3, sourceID);
insert.setString(
4, DirectoryUtils.truncateString(site.getInternalName(), DB_SITE_INTERNALNAME_LENGTH));
insert.setString(
5, DirectoryUtils.truncateString(site.getDisplayName(), DB_SITE_DISPLAYNAME_LENGTH));
insert.executeUpdate();
}
return siteIDs;
} finally {
if (delete != null) {
delete.close();
}
if (insert != null) {
insert.close();
}
}
}
/**
* Get Restriction Lines
*
* @param reload reload data
* @return array of lines
*/
public MGoalRestriction[] getRestrictions(boolean reload) {
if (m_restrictions != null && !reload) return m_restrictions;
ArrayList<MGoalRestriction> list = new ArrayList<MGoalRestriction>();
//
String sql =
"SELECT * FROM PA_GoalRestriction "
+ "WHERE PA_Goal_ID=? AND IsActive='Y' "
+ "ORDER BY Org_ID, C_BPartner_ID, M_Product_ID";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, get_Trx());
pstmt.setInt(1, getPA_Goal_ID());
rs = pstmt.executeQuery();
while (rs.next()) list.add(new MGoalRestriction(getCtx(), rs, get_Trx()));
} catch (Exception e) {
log.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
//
m_restrictions = new MGoalRestriction[list.size()];
list.toArray(m_restrictions);
return m_restrictions;
} // getRestrictions
public CFSecurityTSecGroupBuff lockBuff(
CFSecurityAuthorization Authorization, CFSecurityTSecGroupPKey PKey) {
final String S_ProcName = "lockBuff";
if (!schema.isTransactionOpen()) {
throw CFLib.getDefaultExceptionFactory()
.newUsageException(getClass(), S_ProcName, "Transaction not open");
}
ResultSet resultSet = null;
try {
Connection cnx = schema.getCnx();
long TenantId = PKey.getRequiredTenantId();
int TSecGroupId = PKey.getRequiredTSecGroupId();
String sql =
"SELECT * FROM "
+ schema.getLowerDbSchemaName()
+ ".sp_lock_tsecgrp( ?, ?, ?, ?, ?"
+ ", "
+ "?"
+ ", "
+ "?"
+ " )";
if (stmtLockBuffByPKey == null) {
stmtLockBuffByPKey = cnx.prepareStatement(sql);
}
int argIdx = 1;
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString());
stmtLockBuffByPKey.setString(
argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId());
stmtLockBuffByPKey.setLong(
argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId());
stmtLockBuffByPKey.setLong(argIdx++, TenantId);
stmtLockBuffByPKey.setInt(argIdx++, TSecGroupId);
resultSet = stmtLockBuffByPKey.executeQuery();
if (resultSet.next()) {
CFSecurityTSecGroupBuff buff = unpackTSecGroupResultSetToBuff(resultSet);
if (resultSet.next()) {
throw CFLib.getDefaultExceptionFactory()
.newRuntimeException(getClass(), S_ProcName, "Did not expect multi-record response");
}
return (buff);
} else {
return (null);
}
} catch (SQLException e) {
throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
} finally {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
}
resultSet = null;
}
}
}
/**
* ************************************************************************ 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 static ArrayList<String> getPasser_zone(String zone) throws SQLException {
ArrayList<String> passerArray = new ArrayList<String>();
Connection conn = DBConn.GetConnection();
PreparedStatement st =
conn.prepareStatement(
"SELECT * FROM cs_conversion LEFT JOIN cs_user ON cs_user.username=cs_conversion.username WHERE cs_conversion.isstart=? AND cs_user.zone=? ");
st.setInt(1, 0);
st.setString(2, zone);
ResultSet rs = st.executeQuery();
while (rs.next()) {
if (rs.getString("username").length() != 0) {
if (passerArray.size() == 0) {
passerArray.add(rs.getString("username"));
} else {
if (!passerArray.contains(rs.getString("username"))) {
passerArray.add(rs.getString("username"));
}
}
}
}
rs.close();
st.close();
conn.close();
return passerArray;
}
/**
* Get waiting User name;
*
* @return An waiter ArrayList
*/
public static ArrayList<String> getWaiter() throws SQLException {
ArrayList<String> waiterArray = new ArrayList<String>();
Connection conn = DBConn.GetConnection();
PreparedStatement st = conn.prepareStatement("SELECT * FROM cs_conversion where isstart=?");
st.setInt(1, 1);
ResultSet rs = st.executeQuery();
while (rs.next()) {
if (rs.getString("username").length() != 0) {
if (waiterArray.size() == 0) {
waiterArray.add(rs.getString("username"));
} else {
for (int i = 0; i < waiterArray.size(); i++) {
if (!rs.getString("username").equals(waiterArray.get(i).toString())) {
waiterArray.add(rs.getString("username"));
}
}
}
}
}
rs.close();
st.close();
conn.close();
return waiterArray;
}
/**
* Test that <code>Clob.getCharacterStream(long,long)</code> works on CLOBs that are streamed from
* store. (DERBY-2891)
*/
public void testGetCharacterStreamLongOnLargeClob() throws Exception {
getConnection().setAutoCommit(false);
// create large (>32k) clob that can be read from store
final int size = 33000;
StringBuilder sb = new StringBuilder(size);
for (int i = 0; i < size; i += 10) {
sb.append("1234567890");
}
final int id = BlobClobTestSetup.getID();
PreparedStatement ps =
prepareStatement("insert into blobclob(id, clobdata) values (?,cast(? as clob))");
ps.setInt(1, id);
ps.setString(2, sb.toString());
ps.executeUpdate();
ps.close();
Statement s = createStatement();
ResultSet rs = s.executeQuery("select clobdata from blobclob where id = " + id);
assertTrue(rs.next());
Clob c = rs.getClob(1);
// request a small region of the clob
BufferedReader r = new BufferedReader(c.getCharacterStream(4L, 3L));
assertEquals("456", r.readLine());
r.close();
c.free();
rs.close();
s.close();
rollback();
}
private static int queryLookupServerId(int sourceID, String serverInternalName, Connection c)
throws SQLException {
PreparedStatement idSelect = null;
ResultSet rs = null;
try {
int serverId = 0;
idSelect =
c.prepareStatement(
"select server_id from dat_customer_servers "
+ "where source_id = ? and internal_name = ?");
idSelect.setInt(1, sourceID);
idSelect.setString(2, serverInternalName);
rs = idSelect.executeQuery();
if (rs.next()) {
serverId = rs.getInt(1);
}
return serverId;
} finally {
if (rs != null) {
rs.close();
}
if (idSelect != null) {
idSelect.close();
}
}
}
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();
}
}
/**
* Find all the year records in a Calendar, it need not be a standard period (used in MRP)
*
* @param C_Calendar_ID calendar
* @param ctx context
* @param trx trx
* @return MYear[]
*/
public static MYear[] getAllYearsInCalendar(int C_Calendar_ID, Ctx ctx, Trx trx) {
List<MYear> years = new ArrayList<MYear>();
String sql = "SELECT * FROM C_Year WHERE " + "IsActive='Y' AND C_Calendar_ID = ? ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Calendar_ID);
rs = pstmt.executeQuery();
while (rs.next()) years.add(new MYear(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MYear[] retValue = new MYear[years.size()];
years.toArray(retValue);
return retValue;
}
/**
* Find the periods in a calendar year it need not be a standard period (used in MRP)
*
* @param C_Year_ID Year
* @param periodType Period Type
* @param ctx context
* @param trx trx
* @return MPeriod[]
*/
public static MPeriod[] getAllPeriodsInYear(int C_Year_ID, String periodType, Ctx ctx, Trx trx) {
List<MPeriod> periods = new ArrayList<MPeriod>();
String sql = "SELECT * FROM C_Period WHERE IsActive='Y'";
sql = sql + " AND C_Year_ID = ?";
if (periodType != null) sql = sql + " AND PeriodType = ? ";
sql = sql + " order by StartDate ";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, trx);
pstmt.setInt(1, C_Year_ID);
if (periodType != null) pstmt.setString(2, periodType);
rs = pstmt.executeQuery();
while (rs.next()) periods.add(new MPeriod(ctx, rs, trx));
} catch (Exception e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeResultSet(rs);
DB.closeStatement(pstmt);
}
MPeriod[] retValue = new MPeriod[periods.size()];
periods.toArray(retValue);
return retValue;
}
/**
* Find first Year Period of DateAcct based on Client Calendar
*
* @param ctx context
* @param C_Calendar_ID calendar
* @param DateAcct date
* @return active first Period
*/
public static MPeriod getFirstInYear(Ctx ctx, int C_Calendar_ID, Timestamp DateAcct) {
MPeriod retValue = null;
String sql =
"SELECT * "
+ "FROM C_Period "
+ "WHERE C_Year_ID IN "
+ "(SELECT p.C_Year_ID "
+ "FROM C_Year y"
+ " INNER JOIN C_Period p ON (y.C_Year_ID=p.C_Year_ID) "
+ "WHERE y.C_Calendar_ID=?"
+ " AND ? BETWEEN StartDate AND EndDate)"
+ " AND IsActive='Y' AND PeriodType='S' "
+ "ORDER BY StartDate";
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
pstmt = DB.prepareStatement(sql, (Trx) null);
pstmt.setInt(1, C_Calendar_ID);
pstmt.setTimestamp(2, DateAcct);
rs = pstmt.executeQuery();
if (rs.next()) // first only
retValue = new MPeriod(ctx, rs, null);
} catch (SQLException e) {
s_log.log(Level.SEVERE, sql, e);
} finally {
DB.closeStatement(pstmt);
DB.closeResultSet(rs);
}
return retValue;
} // getFirstInYear
