public CFSecuritySecDeviceBuff[] readBuffByUserIdx( CFSecurityAuthorization Authorization, UUID SecUserId) { final String S_ProcName = "readBuffByUserIdx"; ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_read_secdev_by_useridx( ?, ?, ?, ?, ?" + ", " + "?" + " )"; if (stmtReadBuffByUserIdx == null) { stmtReadBuffByUserIdx = cnx.prepareStatement(sql); } int argIdx = 1; stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByUserIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtReadBuffByUserIdx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByUserIdx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtReadBuffByUserIdx.setString(argIdx++, SecUserId.toString()); try { resultSet = stmtReadBuffByUserIdx.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } List<CFSecuritySecDeviceBuff> buffList = new LinkedList<CFSecuritySecDeviceBuff>(); while ((resultSet != null) && resultSet.next()) { CFSecuritySecDeviceBuff buff = unpackSecDeviceResultSetToBuff(resultSet); buffList.add(buff); } int idx = 0; CFSecuritySecDeviceBuff[] retBuff = new CFSecuritySecDeviceBuff[buffList.size()]; Iterator<CFSecuritySecDeviceBuff> iter = buffList.iterator(); while (iter.hasNext()) { retBuff[idx++] = iter.next(); } return (retBuff); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public void display(ResultSet rs) { try { boolean recordNumber = rs.next(); if (recordNumber) { payNo = rs.getString(1); pasNo = rs.getString(2); pasName = rs.getString(3); mode = rs.getString(4); dt = rs.getString(5); amount = rs.getString(6); rev = rs.getString(7); text1.setText(payNo); combo1.setSelectedItem(pasNo); combo2.setSelectedItem(pasName); combo4.setSelectedItem(mode); p_date.setText(dt); combo8.setSelectedItem(amount); combo3.setSelectedItem(rev); } else { JOptionPane.showMessageDialog( null, "Record Not found", "ERROR", JOptionPane.DEFAULT_OPTION); } } catch (SQLException sqlex) { sqlex.printStackTrace(); } }
public CFSecurityISOCountryLanguageBuff[] readAllBuff(CFSecurityAuthorization Authorization) { final String S_ProcName = "readAllBuff"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_read_iso_cntrylng_all( ?, ?, ?, ?, ? )"; if (stmtReadAllBuff == null) { stmtReadAllBuff = cnx.prepareStatement(sql); } int argIdx = 1; stmtReadAllBuff.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadAllBuff.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtReadAllBuff.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtReadAllBuff.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadAllBuff.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); try { resultSet = stmtReadAllBuff.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } List<CFSecurityISOCountryLanguageBuff> buffList = new LinkedList<CFSecurityISOCountryLanguageBuff>(); while ((resultSet != null) && resultSet.next()) { CFSecurityISOCountryLanguageBuff buff = unpackISOCountryLanguageResultSetToBuff(resultSet); buffList.add(buff); } int idx = 0; CFSecurityISOCountryLanguageBuff[] retBuff = new CFSecurityISOCountryLanguageBuff[buffList.size()]; Iterator<CFSecurityISOCountryLanguageBuff> iter = buffList.iterator(); while (iter.hasNext()) { retBuff[idx++] = iter.next(); } return (retBuff); } catch (SQLException e) { throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e); } finally { if (resultSet != null) { try { resultSet.close(); } catch (SQLException e) { } resultSet = null; } } }
public votblOrganization getAllOrganizations(int iFKCompanyID, int iOrganisationID) { Vector v = new Vector(); Connection con = null; Statement st = null; ResultSet rs = null; votblOrganization vo = new votblOrganization(); String sql = ""; if (iOrganisationID != 0) { sql = "SELECT * FROM tblConsultingCompany a, tblOrganization b"; sql = sql + " WHERE a.CompanyID = b.FKCompanyID AND a.CompanyID = " + iFKCompanyID + " AND b.PKOrganization =" + iOrganisationID; } else { sql = "SELECT * FROM tblConsultingCompany WHERE CompanyID = " + iFKCompanyID; } try { con = ConnectionBean.getConnection(); st = con.createStatement(); rs = st.executeQuery(sql); if (rs.next()) { // 27 May 2008 by Hemilda - cause iOrganisationID == 0 the query only retrieve // tblConsultingCompany column if (iOrganisationID != 0) { vo.setEmailNom(rs.getString("EmailNom")); vo.setEmailNomRemind(rs.getString("EmailNomRemind")); vo.setEmailPart(rs.getString("EmailPart")); vo.setEmailPartRemind(rs.getString("EmailPartRemind")); vo.setExtraModule(rs.getInt("ExtraModule")); vo.setFKCompanyID(rs.getInt("FKCompanyID")); vo.setNameSequence(rs.getInt("NameSequence")); vo.setOrganizationCode(rs.getString("OrganizationCode")); vo.setOrganizationLogo(rs.getString("OrganizationLogo")); vo.setOrganizationName(rs.getString("OrganizationName")); vo.setPKOrganization(rs.getInt("PKOrganization")); } vo.setCompanyName(rs.getString("CompanyName")); } } catch (SQLException SE) { System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage()); } finally { ConnectionBean.closeRset(rs); // Close ResultSet ConnectionBean.closeStmt(st); // Close statement ConnectionBean.close(con); // Close connection } return vo; }
// @karen not completed public Vector getRecord_Sorted(String sSQL, int type) { Vector v = new Vector(); Connection con = null; Statement st = null; ResultSet rs = null; try { // int SortType = getSortType(); sSQL = sSQL + " ORDER BY "; if (type == 1) { if (SortType == 1) sSQL = sSQL + "CompanyName"; else if (SortType == 2) sSQL = sSQL + "CompanyDesc"; if (Toggle[SortType - 1] == 1) sSQL = sSQL + " DESC"; } else { if (SortType_org == 1) sSQL = sSQL + "OrganizationName"; else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode"; else if (SortType_org == 3) sSQL = sSQL + "NameSequence"; if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC"; } con = ConnectionBean.getConnection(); st = con.createStatement(); rs = st.executeQuery(sSQL); while (rs.next()) { votblConsultingCompany vo = new votblConsultingCompany(); vo.setCompanyDesc(rs.getString("CompanyDesc")); vo.setCompanyName(rs.getString("CompanyName")); vo.setCompanyID(rs.getInt("CompanyID")); v.add(vo); } } catch (SQLException SE) { System.err.println("Organization.java - getRecord_Sorted - " + SE.getMessage()); } finally { ConnectionBean.closeRset(rs); // Close ResultSet ConnectionBean.closeStmt(st); // Close statement ConnectionBean.close(con); // Close connection } return v; }
/** * Get Organization * * @param iFKCompanyID * @return * @author James */ public votblOrganization getOrganization(int iOrgID) { votblOrganization vo = new votblOrganization(); Connection con = null; Statement st = null; ResultSet rs = null; String command = "SELECT * FROM tblOrganization WHERE PKOrganization= " + iOrgID; try { con = ConnectionBean.getConnection(); System.out.println("con:" + con); st = con.createStatement(); rs = st.executeQuery(command); if (rs.next()) { vo.setEmailNom(rs.getString("EmailNom")); vo.setEmailNomRemind(rs.getString("EmailNomRemind")); vo.setEmailPart(rs.getString("EmailPart")); vo.setEmailPartRemind(rs.getString("EmailPartRemind")); vo.setExtraModule(rs.getInt("ExtraModule")); vo.setFKCompanyID(rs.getInt("FKCompanyID")); vo.setNameSequence(rs.getInt("NameSequence")); vo.setOrganizationCode(rs.getString("OrganizationCode")); vo.setOrganizationLogo(rs.getString("OrganizationLogo")); vo.setOrganizationName(rs.getString("OrganizationName")); vo.setPKOrganization(rs.getInt("PKOrganization")); // Added by DeZ, 18/06/08, to add function to enable/disable Nominate Rater vo.setNomRater(rs.getBoolean("NominationModule")); } } catch (SQLException SE) { System.err.println("Organization.java - getOrganization - " + SE.getMessage()); } finally { ConnectionBean.closeRset(rs); // Close ResultSet ConnectionBean.closeStmt(st); // Close statement ConnectionBean.close(con); // Close connection } return vo; }
public Vector getAllOrganizations(int iFKCompanyID) { Vector v = new Vector(); Connection con = null; Statement st = null; ResultSet rs = null; /* * Change(s) : Changed the SQL statement to get the company name as well * Reason(s) : Able to set the selected company name when company selection is changed in OrganizationList.jsp * Updated By: Gwen Oh * Updated On: 26 Sep 2011 */ // String sSQL="SELECT * FROM tblOrganization WHERE FKCompanyID= "+ iFKCompanyID; String sSQL = "SELECT tblOrganization.*, tblConsultingCompany.CompanyName FROM " + "tblOrganization INNER JOIN tblConsultingCompany ON tblOrganization.FKCompanyID = tblConsultingCompany.CompanyID " + "WHERE tblOrganization.FKCompanyID=" + iFKCompanyID; try { /* * Re-edited by Eric Lu 15-May-08 * * Added sort and toggle functionality for getting organizations */ sSQL = sSQL + " ORDER BY "; if (SortType_org == 1) sSQL = sSQL + "OrganizationName"; else if (SortType_org == 2) sSQL = sSQL + "OrganizationCode"; else if (SortType_org == 3) sSQL = sSQL + "NameSequence"; if (Toggle_org[SortType_org - 1] == 1) sSQL = sSQL + " DESC"; con = ConnectionBean.getConnection(); st = con.createStatement(); rs = st.executeQuery(sSQL); while (rs.next()) { votblOrganization vo = new votblOrganization(); vo.setEmailNom(rs.getString("EmailNom")); vo.setEmailNomRemind(rs.getString("EmailNomRemind")); vo.setEmailPart(rs.getString("EmailPart")); vo.setEmailPartRemind(rs.getString("EmailPartRemind")); vo.setExtraModule(rs.getInt("ExtraModule")); vo.setFKCompanyID(rs.getInt("FKCompanyID")); vo.setNameSequence(rs.getInt("NameSequence")); vo.setOrganizationCode(rs.getString("OrganizationCode")); vo.setOrganizationLogo(rs.getString("OrganizationLogo")); vo.setOrganizationName(rs.getString("OrganizationName")); vo.setPKOrganization(rs.getInt("PKOrganization")); // Gwen Oh - 26/09/2011: Set the company name vo.setCompanyName(rs.getString("CompanyName")); v.add(vo); } } catch (SQLException SE) { System.err.println("Organization.java - getAllOrganizations - " + SE.getMessage()); } finally { ConnectionBean.closeRset(rs); // Close ResultSet ConnectionBean.closeStmt(st); // Close statement ConnectionBean.close(con); // Close connection } return v; }
/* * Add a new record to the Organization table, * creates an admin account when SA creates a new consulting company * @param OrganizationCode (based on Company Name) * @param OrganizationName (based on Company Description) * @param FKCompanyID * @param NameSequence * @param PKUser * @param nomRater * @throws SQLException * @throws Exception * @author: Mark Oei * @since v.1.3.12.63 09 Mar 2010 */ public boolean addOrganisationByCons( String OrganizationCode, String OrganizationName, int FKCompanyID, int NameSequence, int PKUser, String nomRater) throws SQLException, Exception { Connection con = null; Statement st = null; boolean bIsAdded = false; String sql = "INSERT INTO tblOrganization (OrganizationCode, OrganizationName, FKCompanyID, NameSequence, NominationModule)"; sql = sql + " VALUES ('" + OrganizationCode + "', '" + OrganizationName + "', " + FKCompanyID + ", " + NameSequence + ", '" + Boolean.parseBoolean(nomRater) + "')"; try { con = ConnectionBean.getConnection(); st = con.createStatement(); int iSuccess = st.executeUpdate(sql); System.out.println(iSuccess); if (iSuccess != 0) bIsAdded = true; } catch (Exception E) { System.err.println("Organization.java - AddRecord - " + E); } finally { ConnectionBean.closeStmt(st); // Close statement ConnectionBean.close(con); // Close connection } System.out.println("1. Add Organization"); // add default under the organization. String defaultName = "NA"; int FKOrganization = checkOrgExist(OrganizationCode, OrganizationName, FKCompanyID); // Change to disable print statement. Used for debugging only // Mark Oei 19 Mar 2010 // System.out.println("testing " + FKOrganization); System.out.println("2. Check Organization Exist"); if (FKOrganization != 0) { // Add Division div.addRecord(defaultName, FKOrganization, PKUser); System.out.println("3. Add Division"); // Add Department dept.addRecord(defaultName, FKOrganization, PKUser); System.out.println("4. Add Department"); // Add Group G.addRecord(defaultName, FKOrganization, PKUser); System.out.println("5. Add Group"); // Check whether exists int FKDivision = div.checkDivExist(defaultName, FKOrganization); int FKDepartment = dept.checkDeptExist(defaultName, FKOrganization); int FKGroup = G.checkGroupExist(defaultName, FKOrganization); // Create links dept.linkDepartment(FKDivision, FKDepartment); G.linkGroup(FKDepartment, FKGroup); // Establish new admin account and password Date timeStamp = new java.util.Date(); SimpleDateFormat dFormat = new SimpleDateFormat("ddMMyyHHmmss"); String temp = dFormat.format(timeStamp); String loginName = OrganizationCode + "admin"; String password = OrganizationCode + temp; int userType = 6; // Insert record into database U.addRecord( FKDepartment, FKDivision, userType, "Admin", "Admin", loginName, "NA", "NA", FKGroup, password, 1, FKCompanyID, FKOrganization, "NA", PKUser); System.out.println("6. Add User"); int userExist = U.checkUserExist( FKDepartment, FKDivision, userType, "Admin", "Admin", loginName, "NA", "NA", FKGroup, password, 1, FKCompanyID, FKOrganization); System.out.println( "FKDivision = " + FKDivision + ", FKDepartment = " + FKDepartment + ", FKGroup = " + FKGroup + " and User Exist = " + userExist); if (userExist != 0) { try { U.insertRelation(userExist, userExist, 0); } catch (SQLException SE) { System.out.println(SE.getMessage()); } // Send email notification String content = template.ForgotPass_temp(loginName, password); String email = "*****@*****.**"; // Edited By Roger 13 June 2008 Email.sendMail( server.getAdminEmail(), email, "New Admin Assignment for " + OrganizationName, content, FKOrganization); } System.out.println("8. Add User Relation"); } sDetail = detail.getUserDetail(PKUser); ev.addRecord("Insert", itemName, OrganizationName, sDetail[2], sDetail[11], sDetail[10]); return bIsAdded; } // End Method for addOrganisationByCons
public void updateISOLanguage( CFSecurityAuthorization Authorization, CFSecurityISOLanguageBuff Buff) { final String S_ProcName = "updateISOLanguage"; ResultSet resultSet = null; try { short ISOLanguageId = Buff.getRequiredISOLanguageId(); String ISO6392Code = Buff.getRequiredISO6392Code(); String ISO6391Code = Buff.getOptionalISO6391Code(); String EnglishName = Buff.getRequiredEnglishName(); int Revision = Buff.getRequiredRevision(); Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_update_iso_lang( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )"; 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++, "ISLN"); stmtUpdateByPKey.setShort(argIdx++, ISOLanguageId); stmtUpdateByPKey.setString(argIdx++, ISO6392Code); if (ISO6391Code != null) { stmtUpdateByPKey.setString(argIdx++, ISO6391Code); } else { stmtUpdateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR); } stmtUpdateByPKey.setString(argIdx++, EnglishName); stmtUpdateByPKey.setInt(argIdx++, Revision); try { resultSet = stmtUpdateByPKey.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } if ((resultSet != null) && resultSet.next()) { CFSecurityISOLanguageBuff updatedBuff = unpackISOLanguageResultSetToBuff(resultSet); if ((resultSet != null) && resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredISO6392Code(updatedBuff.getRequiredISO6392Code()); Buff.setOptionalISO6391Code(updatedBuff.getOptionalISO6391Code()); Buff.setRequiredEnglishName(updatedBuff.getRequiredEnglishName()); 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 CFSecurityISOLanguageBuff readBuffByCode3Idx( CFSecurityAuthorization Authorization, String ISO6392Code) { final String S_ProcName = "readBuffByCode3Idx"; ResultSet resultSet = null; try { Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_read_iso_lang_by_code3idx( ?, ?, ?, ?, ?" + ", " + "?" + " )"; if (stmtReadBuffByCode3Idx == null) { stmtReadBuffByCode3Idx = cnx.prepareStatement(sql); } int argIdx = 1; stmtReadBuffByCode3Idx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByCode3Idx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtReadBuffByCode3Idx.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtReadBuffByCode3Idx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtReadBuffByCode3Idx.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtReadBuffByCode3Idx.setString(argIdx++, ISO6392Code); try { resultSet = stmtReadBuffByCode3Idx.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } if ((resultSet != null) && resultSet.next()) { CFSecurityISOLanguageBuff buff = unpackISOLanguageResultSetToBuff(resultSet); if ((resultSet != null) && resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } 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; } } }
public CFSecurityISOLanguageBuff lockBuff( CFSecurityAuthorization Authorization, CFSecurityISOLanguagePKey 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(); short ISOLanguageId = PKey.getRequiredISOLanguageId(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_lock_iso_lang( ?, ?, ?, ?, ?" + ", " + "?" + " )"; 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.setShort(argIdx++, ISOLanguageId); try { resultSet = stmtLockBuffByPKey.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } if ((resultSet != null) && resultSet.next()) { CFSecurityISOLanguageBuff buff = unpackISOLanguageResultSetToBuff(resultSet); if ((resultSet != null) && resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } 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; } } }
public void createSecDevice(CFSecurityAuthorization Authorization, CFSecuritySecDeviceBuff Buff) { final String S_ProcName = "createSecDevice"; if (!schema.isTransactionOpen()) { throw CFLib.getDefaultExceptionFactory() .newUsageException(getClass(), S_ProcName, "Transaction not open"); } ResultSet resultSet = null; try { UUID SecUserId = Buff.getRequiredSecUserId(); String DevName = Buff.getRequiredDevName(); String PubKey = Buff.getOptionalPubKey(); Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_create_secdev( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + " )"; if (stmtCreateByPKey == null) { stmtCreateByPKey = cnx.prepareStatement(sql); } int argIdx = 1; stmtCreateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtCreateByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecUserId().toString()); stmtCreateByPKey.setString( argIdx++, (Authorization == null) ? "" : Authorization.getSecSessionId().toString()); stmtCreateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecClusterId()); stmtCreateByPKey.setLong( argIdx++, (Authorization == null) ? 0 : Authorization.getSecTenantId()); stmtCreateByPKey.setString(argIdx++, "SDEV"); stmtCreateByPKey.setString(argIdx++, SecUserId.toString()); stmtCreateByPKey.setString(argIdx++, DevName); if (PubKey != null) { stmtCreateByPKey.setString(argIdx++, PubKey); } else { stmtCreateByPKey.setNull(argIdx++, java.sql.Types.VARCHAR); } try { resultSet = stmtCreateByPKey.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } if ((resultSet != null) && resultSet.next()) { CFSecuritySecDeviceBuff createdBuff = unpackSecDeviceResultSetToBuff(resultSet); if ((resultSet != null) && resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredSecUserId(createdBuff.getRequiredSecUserId()); Buff.setRequiredDevName(createdBuff.getRequiredDevName()); Buff.setOptionalPubKey(createdBuff.getOptionalPubKey()); Buff.setRequiredRevision(createdBuff.getRequiredRevision()); Buff.setCreatedByUserId(createdBuff.getCreatedByUserId()); Buff.setCreatedAt(createdBuff.getCreatedAt()); Buff.setUpdatedByUserId(createdBuff.getUpdatedByUserId()); Buff.setUpdatedAt(createdBuff.getUpdatedAt()); } 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 void updateTSecGroup(CFSecurityAuthorization Authorization, CFSecurityTSecGroupBuff Buff) { final String S_ProcName = "updateTSecGroup"; ResultSet resultSet = null; try { long TenantId = Buff.getRequiredTenantId(); int TSecGroupId = Buff.getRequiredTSecGroupId(); String Name = Buff.getRequiredName(); int Revision = Buff.getRequiredRevision(); Connection cnx = schema.getCnx(); String sql = "call " + schema.getLowerDbSchemaName() + ".sp_update_tsecgrp( ?, ?, ?, ?, ?, ?" + ", " + "?" + ", " + "?" + ", " + "?" + ", " + "?" + " )"; 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++, "TGRP"); stmtUpdateByPKey.setLong(argIdx++, TenantId); stmtUpdateByPKey.setInt(argIdx++, TSecGroupId); stmtUpdateByPKey.setString(argIdx++, Name); stmtUpdateByPKey.setInt(argIdx++, Revision); try { resultSet = stmtUpdateByPKey.executeQuery(); } catch (SQLException e) { if (e.getErrorCode() != 1329) { throw e; } resultSet = null; } if ((resultSet != null) && resultSet.next()) { CFSecurityTSecGroupBuff updatedBuff = unpackTSecGroupResultSetToBuff(resultSet); if ((resultSet != null) && resultSet.next()) { resultSet.last(); throw CFLib.getDefaultExceptionFactory() .newRuntimeException( getClass(), S_ProcName, "Did not expect multi-record response, " + resultSet.getRow() + " rows selected"); } Buff.setRequiredName(updatedBuff.getRequiredName()); 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 void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); ResultSet rs = null; try { // SET UP Context environment, to look for Data Pooling Resource Context initCtx = new InitialContext(); Context envCtx = (Context) initCtx.lookup("java:comp/env"); DataSource ds = (DataSource) envCtx.lookup("jdbc/TestDB"); Connection dbcon = ds.getConnection(); // ########### SEARCH INPUT PARAMETERS, EXECUTE search // ##################################################### Vector<String> params = new Vector<String>(); params.add(request.getParameter("fname")); params.add(request.getParameter("lname")); params.add(request.getParameter("title")); params.add(request.getParameter("year")); params.add(request.getParameter("director")); List<Movie> movies = new ArrayList<Movie>(); movies = SQLServices.getMovies(params.toArray(new String[params.size()]), dbcon); // ########## SET DEFAULT SESSION() PARAMETERS #################################### request.getSession().removeAttribute("movies"); request.getSession().removeAttribute("linkedListMovies"); request.getSession().removeAttribute("hasPaged"); request.getSession().setAttribute("hasPaged", "no"); request.getSession().setAttribute("movies", movies); request.getSession().setAttribute("currentIndex", "0"); request.getSession().setAttribute("defaultN", "5"); // ########## IF MOVIES FROM SEARCH NON-EMPTY ########################################### List<String> fields = Movie.fieldNames(); int count = 1; if (!movies.isEmpty()) { request.setAttribute("movies", movies); for (String field : fields) { request.setAttribute("f" + count++, field); } request.getRequestDispatcher("../movieList.jsp").forward(request, response); } else { out.println("<html><head><title>error</title></head>"); out.println("<body><h1>could not find any movies, try your search again.</h1>"); out.println("<p> we are terribly sorry, please go back. </p>"); out.println("<table border>"); out.println("</table>"); } dbcon.close(); } catch (SQLException ex) { while (ex != null) { System.out.println("SQL Exception: " + ex.getMessage()); ex = ex.getNextException(); } } catch (java.lang.Exception ex) { out.println( "<html>" + "<head><title>" + "moviedb: error" + "</title></head>\n<body>" + "<p>SQL error in doGet: " + ex.getMessage() + "</p></body></html>"); return; } out.close(); }