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