Example #1
1
  @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;
  }
  /**
   * Test importing an unsupported field e.g. datetime, the program should continue and assign null
   * value to the field.
   *
   * @throws Exception
   */
  @Test
  public void testImportUnsupportedField() throws Exception {
    ConnectionProperties p = getConnectionProperties();
    Connection cn = DatabaseConnection.getConnection(p);
    try {
      List<Field> verifiedFields = new Vector<Field>();
      String[] fields = "ListingId, Title".split(",");
      String tableName = "Listings";
      DatabaseConnection.verifyTable(p, tableName, fields, verifiedFields);

      Collection<JsonNode> importNodes = new LinkedList<JsonNode>();
      JsonNodeFactory f = JsonNodeFactory.instance;

      int listingId = 1559350;

      ObjectNode n;
      n = new ObjectNode(f);
      n.put("ListingId", listingId);
      importNodes.add(n);

      JsonArrayImporter importer = new JsonArrayImporter(p);
      importer.doImport(tableName, verifiedFields, importNodes);

      Statement st = cn.createStatement();
      ResultSet rs = st.executeQuery("SELECT ListingId, Title FROM Listings");
      assertTrue("Expected result set to contain a record", rs.next());
      assertEquals(listingId, rs.getInt("ListingId"));
      assertEquals(null, rs.getString("Title"));
    } finally {
      cn.close();
    }
  }
 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;
     }
   }
 }
  /**
   * Get organisation's nomination rater status
   *
   * @param iOrgID
   * @return
   * @throws SQLException
   * @throws Exception
   * @author Desmond
   */
  public boolean getNomRater(int iOrgID) throws SQLException, Exception {
    String query = "SELECT NominationModule FROM tblOrganization WHERE PKOrganization =" + iOrgID;
    boolean iNomRater = true;

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(query);

      if (rs.next()) {
        iNomRater = rs.getBoolean(1);
      }

    } catch (Exception E) {
      System.err.println("Organization.java - getNomRater - " + E);
    } finally {
      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }

    return iNomRater;
  } // End getNomRater()
  /**
   * Get Company ID by OrganisationID
   *
   * @param OrgID
   * @return PKCompany
   * @throws SQLException
   * @throws Exception
   */
  public int getCompanyID(int OrgID) throws SQLException, Exception {
    String query = "Select FKCompanyID from tblOrganization WHERE PKOrganization = " + OrgID;

    /*db.openDB();
    Statement stmt = db.con.createStatement();
    ResultSet rs = stmt.executeQuery(query);

    if(rs.next())
    	return rs.getInt(1);*/
    int iCompanyID = 0;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {

      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(query);

      if (rs.next()) {
        iCompanyID = rs.getInt(1);
      }

    } catch (Exception E) {
      System.err.println("Organization.java - getCompanyID - " + E);
    } finally {

      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }

    return iCompanyID;
  }
  /* Method Name : isConsulting
   * Checks whether login organisation is a Consulting Company
   * @param sOrgName
   * @param orgCode
   * @author Mark Oei
   * @since v.1.3.12.63 (09 Mar 2010)
   */
  public boolean isConsulting(String orgName) {
    String sOrgName = "";
    orgName = "\'" + orgName + "\'";
    String querySql = "SELECT * FROM tblConsultingCompany WHERE CompanyName = " + orgName;
    // Change to disable print statement. Used for debugging only
    // Mark Oei 19 Mar 2010
    // System.out.println("testing " + orgName);
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(querySql);

      if (rs.next()) sOrgName = rs.getString("CompanyName");
    } catch (Exception E) {
      System.err.println("Organization.java - isConsulting - " + E);
    } finally {

      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }
    // Change to disable print statement. Used for debugging only
    // Mark Oei 19 Mar 2010
    // System.out.println("testing " + sOrgName);
    if ((sOrgName == null) || (sOrgName == "")) return false;
    else return true;
  } // End of isConsulting
Example #7
0
 public void deleteUser(String username) {
   if (isReadOnly()) {
     // Reject the operation since the provider is read-only
     throw new UnsupportedOperationException();
   }
   Connection con = null;
   PreparedStatement pstmt = null;
   boolean abortTransaction = false;
   try {
     // Delete all of the users's extended properties
     con = DbConnectionManager.getTransactionConnection();
     pstmt = con.prepareStatement(DELETE_USER_PROPS);
     pstmt.setString(1, username);
     pstmt.execute();
     pstmt.close();
     // Delete the actual user entry
     pstmt = con.prepareStatement(DELETE_USER);
     pstmt.setString(1, username);
     pstmt.execute();
   } catch (Exception e) {
     Log.error(e);
     abortTransaction = true;
   } finally {
     DbConnectionManager.closeTransactionConnection(pstmt, con, abortTransaction);
   }
 }
Example #8
0
  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);
      }
    }
  }
  public long nextTSecGroupMemberIdGen(
      CFSecurityAuthorization Authorization, CFSecurityTenantPKey PKey) {
    final String S_ProcName = "nextTSecGroupMemberIdGen";
    if (!schema.isTransactionOpen()) {
      throw CFLib.getDefaultExceptionFactory()
          .newUsageException(getClass(), S_ProcName, "Not in a transaction");
    }
    Connection cnx = schema.getCnx();
    long Id = PKey.getRequiredId();

    CallableStatement stmtSelectNextTSecGroupMemberIdGen = null;
    try {
      String sql = "{ call sp_next_tsecgroupmemberidgen( ?" + ", " + "?" + " ) }";
      stmtSelectNextTSecGroupMemberIdGen = cnx.prepareCall(sql);
      int argIdx = 1;
      stmtSelectNextTSecGroupMemberIdGen.registerOutParameter(argIdx++, java.sql.Types.BIGINT);
      stmtSelectNextTSecGroupMemberIdGen.setLong(argIdx++, Id);
      stmtSelectNextTSecGroupMemberIdGen.execute();
      long nextId = stmtSelectNextTSecGroupMemberIdGen.getLong(1);
      return (nextId);
    } catch (SQLException e) {
      throw CFLib.getDefaultExceptionFactory().newDbException(getClass(), S_ProcName, e);
    } finally {
      if (stmtSelectNextTSecGroupMemberIdGen != null) {
        try {
          stmtSelectNextTSecGroupMemberIdGen.close();
        } catch (SQLException e) {
        }
        stmtSelectNextTSecGroupMemberIdGen = null;
      }
    }
  }
Example #10
0
 /** Entity Capabilities */
 public void setEntityCapsManager(EntityCapsManager manager) {
   capsManager = manager;
   if (connection.getCapsNode() != null && connection.getHost() != null) {
     capsManager.addUserCapsNode(connection.getHost(), connection.getCapsNode());
   }
   capsManager.addPacketListener(connection);
 }
Example #11
0
  /**
   * Returns the discovered items of a given XMPP entity addressed by its JID and note attribute.
   * Use this message only when trying to query information which is not directly addressable.
   *
   * @param entityID the address of the XMPP entity.
   * @param node the attribute that supplements the 'jid' attribute.
   * @return the discovered items.
   * @throws XMPPException if the operation failed for some reason.
   */
  public DiscoverItems discoverItems(String entityID, String node) throws XMPPException {
    // Discover the entity's items
    DiscoverItems disco = new DiscoverItems();
    disco.setType(IQ.Type.GET);
    disco.setTo(entityID);
    disco.setNode(node);

    // Create a packet collector to listen for a response.
    PacketCollector collector =
        connection.createPacketCollector(new PacketIDFilter(disco.getPacketID()));

    connection.sendPacket(disco);

    // Wait up to 5 seconds for a result.
    IQ result = (IQ) collector.nextResult(SmackConfiguration.getPacketReplyTimeout());
    // Stop queuing results
    collector.cancel();
    if (result == null) {
      throw new XMPPException("No response from the server.");
    }
    if (result.getType() == IQ.Type.ERROR) {
      throw new XMPPException(result.getError());
    }
    return (DiscoverItems) result;
  }
 /**
  * Sets the user to run as. This is for the case where the request was generated by the user and
  * so the worker must set this value later.
  *
  * @param cq_id id of this entry in the queue
  * @param user user to run the jobs as
  */
 public void setRunAs(long cq_id, String user) throws MetaException {
   try {
     Connection dbConn = getDbConn();
     try {
       Statement stmt = dbConn.createStatement();
       String s = "update COMPACTION_QUEUE set cq_run_as = '" + user + "' where cq_id = " + cq_id;
       LOG.debug("Going to execute update <" + s + ">");
       if (stmt.executeUpdate(s) != 1) {
         LOG.error("Unable to update compaction record");
         LOG.debug("Going to rollback");
         dbConn.rollback();
       }
       LOG.debug("Going to commit");
       dbConn.commit();
     } catch (SQLException e) {
       LOG.error("Unable to update compaction queue, " + e.getMessage());
       try {
         LOG.debug("Going to rollback");
         dbConn.rollback();
       } catch (SQLException e1) {
       }
       detectDeadlock(e, "setRunAs");
     } finally {
       closeDbConn(dbConn);
     }
   } catch (DeadlockException e) {
     setRunAs(cq_id, user);
   } finally {
     deadlockCnt = 0;
   }
 }
  public void closeConnections() throws SQLException {
    if (itemPreparedStatement != null) {
      itemPreparedStatement.close();
    }

    if (itemConnection != null) {
      itemConnection.close();
    }

    if (holdingsPreparedStatement != null) {
      holdingsPreparedStatement.close();
    }

    if (holdingsConnection != null) {
      holdingsConnection.close();
    }

    if (bibResultSet != null) {
      bibResultSet.close();
    }
    if (bibStatement != null) {
      bibStatement.close();
    }

    if (bibConnection != null) {
      bibConnection.close();
    }
    if (connection != null) {
      connection.close();
    }
  }
Example #14
0
  public void saveSentMessage(COutgoingMessage message) throws Exception {
    Statement sqlCmd;

    if (connection != null) {
      sqlCmd =
          connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
      sqlCmd.executeUpdate(
          "insert into sms_out (recipient, text, dispatch_date, flash_sms, status_report, src_port, dst_port, validity_period) values ('"
              + message.getRecipient()
              + "', '"
              + message.getText().replaceAll("'", "''")
              + "', "
              + escapeDate(message.getDate(), true)
              + ", "
              + (message.getFlashSms() ? 1 : 0)
              + ", "
              + (message.getStatusReport() ? 1 : 0)
              + ", "
              + message.getSourcePort()
              + ", "
              + message.getDestinationPort()
              + ", "
              + message.getValidityPeriod()
              + ")");
      connection.commit();
      sqlCmd.close();
    }
  }
Example #15
0
 SqlDialect get(DataSource dataSource) {
   Connection connection = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     String productName = metaData.getDatabaseProductName();
     String productVersion = metaData.getDatabaseProductVersion();
     List key = Arrays.asList(productName, productVersion);
     SqlDialect dialect = map.get(key);
     if (dialect == null) {
       final SqlDialect.DatabaseProduct product =
           SqlDialect.getProduct(productName, productVersion);
       dialect = new SqlDialect(product, productName, metaData.getIdentifierQuoteString());
       map.put(key, dialect);
     }
     connection.close();
     connection = null;
     return dialect;
   } catch (SQLException e) {
     throw new RuntimeException(e);
   } finally {
     if (connection != null) {
       try {
         connection.close();
       } catch (SQLException e) {
         // ignore
       }
     }
   }
 }
Example #16
0
  void joinChannel(Channel channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s = con.prepareStatement("INSERT INTO `channels` (`channel`) VALUES (?)");
      s.setString(1, channel.getName().toLowerCase());
      s.executeUpdate();
      s.close();

      if (!this.channel_data.containsKey(channel.getName().toLowerCase())) {
        ChannelInfo new_channel = new ChannelInfo(channel.getName().toLowerCase());
        new_channel.setDefaultOptions();

        this.channel_data.put(channel.getName().toLowerCase(), new_channel);
      }

      this.saveChannelSettings(this.channel_data.get(channel.getName().toLowerCase()));
    } 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);
      }
    }
  }
Example #17
0
  String getRandomChannelWithVelociraptors(String exclude) {
    Connection con = null;
    String value = "";

    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "SELECT `channel` FROM `channels` WHERE `channel` != ? AND `active_velociraptors` > 0 ORDER BY (RAND() * active_velociraptors) DESC LIMIT 1;");
      s.setString(1, exclude);
      s.executeQuery();

      ResultSet rs = s.getResultSet();
      while (rs.next()) {
        value = rs.getString("channel");
      }

      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 value;
  }
  public void deleteISOTimezone(
      CFSecurityAuthorization Authorization, CFSecurityISOTimezoneBuff Buff) {
    final String S_ProcName = "deleteISOTimezone";
    ResultSet resultSet = null;
    try {
      Connection cnx = schema.getCnx();
      short ISOTimezoneId = Buff.getRequiredISOTimezoneId();

      String sql =
          "SELECT "
              + schema.getLowerDbSchemaName()
              + ".sp_delete_isotz( ?, ?, ?, ?, ?"
              + ", "
              + "?"
              + ", "
              + "?"
              + " ) 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.setShort(argIdx++, ISOTimezoneId);
      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;
      }
    }
  }
Example #19
0
 private ImmutableMap<String, JdbcTable> computeTables() {
   Connection connection = null;
   ResultSet resultSet = null;
   try {
     connection = dataSource.getConnection();
     DatabaseMetaData metaData = connection.getMetaData();
     resultSet = metaData.getTables(catalog, schema, null, null);
     final ImmutableMap.Builder<String, JdbcTable> builder = ImmutableMap.builder();
     while (resultSet.next()) {
       final String tableName = resultSet.getString(3);
       final String catalogName = resultSet.getString(1);
       final String schemaName = resultSet.getString(2);
       final String tableTypeName = resultSet.getString(4);
       // Clean up table type. In particular, this ensures that 'SYSTEM TABLE',
       // returned by Phoenix among others, maps to TableType.SYSTEM_TABLE.
       // We know enum constants are upper-case without spaces, so we can't
       // make things worse.
       final String tableTypeName2 = tableTypeName.toUpperCase().replace(' ', '_');
       final TableType tableType = Util.enumVal(TableType.class, tableTypeName2);
       final JdbcTable table = new JdbcTable(this, catalogName, schemaName, tableName, tableType);
       builder.put(tableName, table);
     }
     return builder.build();
   } catch (SQLException e) {
     throw new RuntimeException("Exception while reading tables", e);
   } finally {
     close(connection, null, resultSet);
   }
 }
Example #20
0
  @SuppressWarnings("unchecked")
  @Test
  public void testFindCustomersWithConnection() throws Exception {
    CustomerDAO dao =
        EasyMock.createMockBuilder(CustomerDAO.class)
            .addMockedMethod("readNextCustomer")
            .addMockedMethod("getCustomerQuery")
            .createStrictMock();
    ResultSet resultSet = EasyMock.createStrictMock(ResultSet.class);
    Connection connection = EasyMock.createStrictMock(Connection.class);
    Statement statement = EasyMock.createStrictMock(Statement.class);
    List<SearchConstraint> constraints = new LinkedList<SearchConstraint>();

    EasyMock.expect(dao.getCustomerQuery(constraints)).andReturn("aQuery");
    EasyMock.expect(connection.createStatement()).andReturn(statement);
    EasyMock.expect(statement.executeQuery("aQuery")).andReturn(resultSet);

    EasyMock.expect(resultSet.next()).andReturn(true);
    EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
        .andReturn(true);
    EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
        .andReturn(true);
    EasyMock.expect(dao.readNextCustomer(EasyMock.eq(resultSet), EasyMock.isA(List.class)))
        .andReturn(false);
    resultSet.close();
    EasyMock.expectLastCall();
    statement.close();
  }
  public String getOrganisationName(int iFKOrg) {
    String sOrgName = "";
    String querySql = "SELECT * FROM tblOrganization WHERE PKOrganization = " + iFKOrg;

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(querySql);

      if (rs.next()) sOrgName = rs.getString("OrganizationName");

    } catch (Exception E) {
      System.err.println("Organization.java - getOrganizationName - " + E);
    } finally {

      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }

    return sOrgName;
  }
Example #22
0
  public void removeFromChannelGroup(String group, ChannelInfo channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "DELETE `channel_groups`.* FROM `channel_groups` INNER JOIN `channels` ON (`channel_groups`.`channel_id` = `channels`.`id`) WHERE `channels`.`channel` = ? AND `channel_groups`.`name` = ?");
      s.setString(1, channel.channel);
      s.setString(2, group);
      s.executeUpdate();
      s.close();

      // Will do nothing if the channel is not in the list.
      this.channel_groups.get(group.toLowerCase()).remove(channel);
    } 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);
      }
    }
  }
  /** Get Organisation ID by User email */
  public int getOrgIDbyEmail(String UserEmail) throws SQLException, Exception {
    String query = "Select COUNT(*) as TotRecord from tblEmail";
    int count = 0;
    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(query);

      if (rs.next()) {
        count = rs.getInt(1);
      }

    } catch (Exception E) {
      System.err.println("Organization.java - editRecord - " + E);
    } finally {
      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }

    return count;
  }
Example #24
0
  public void addToChannelGroup(String group, ChannelInfo channel) {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      PreparedStatement s =
          con.prepareStatement(
              "REPLACE INTO `channel_groups` SET `name` = ?, `channel_id` = (SELECT `id` FROM `channels` WHERE `channel` = ?)");
      s.setString(1, group.toLowerCase());
      s.setString(2, channel.channel.toLowerCase());
      s.executeUpdate();
      s.close();

      // Will do nothing if the channel is not in the list.
      if (!this.channel_groups.containsKey(group.toLowerCase())) {
        this.channel_groups.put(group.toLowerCase(), new HashSet<>());
      }

      this.channel_groups.get(group.toLowerCase()).add(channel);
    } 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);
      }
    }
  }
  /**
   * Get organisation's name sequence
   *
   * @param iOrgID
   * @return
   * @throws SQLException
   * @throws Exception
   * @author Maruli
   */
  public int getNameSeq(int iOrgID) throws SQLException, Exception {
    String query = "SELECT NameSequence FROM tblOrganization WHERE PKOrganization =" + iOrgID;
    int iNameSeqe = 0;

    Connection con = null;
    Statement st = null;
    ResultSet rs = null;

    try {
      con = ConnectionBean.getConnection();
      st = con.createStatement();
      rs = st.executeQuery(query);

      if (rs.next()) {
        iNameSeqe = rs.getInt(1);
      }

    } catch (Exception E) {
      System.err.println("Organization.java - getNameSeq - " + E);
    } finally {
      ConnectionBean.closeRset(rs); // Close ResultSet
      ConnectionBean.closeStmt(st); // Close statement
      ConnectionBean.close(con); // Close connection
    }

    return iNameSeqe;
  }
Example #26
0
  private void getPokemon() {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);
      try (Statement s = con.createStatement()) {
        ResultSet rs;

        s.executeQuery("SELECT `name` FROM `pokemon`");
        rs = s.getResultSet();
        this.pokemon.clear();
        while (rs.next()) {
          this.pokemon.add(rs.getString("name"));
        }
        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);
      }
    }
  }
  /**
   * Method called by the Form panel to delete existing data.
   *
   * @param persistentObject value object to delete
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response deleteRecord(ValueObject persistentObject) throws Exception {
    PreparedStatement stmt = null;
    try {
      EmpVO vo = (EmpVO) persistentObject;

      // delete from WORKING_DAYS...
      stmt = conn.prepareStatement("delete from WORKING_DAYS where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      stmt.close();

      // delete from EMP...
      stmt = conn.prepareStatement("delete from EMP where EMP_CODE=?");
      stmt.setString(1, vo.getEmpCode());
      stmt.execute();
      gridFrame.reloadData();

      frame.getGrid().clearData();

      return new VOResponse(vo);
    } catch (SQLException ex) {
      ex.printStackTrace();
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        stmt.close();
        conn.commit();
      } catch (SQLException ex1) {
      }
    }
  }
Example #28
0
  private void getIgnoreList() {
    Connection con = null;
    try {
      con = pool.getConnection(timeout);

      Statement s = con.createStatement();
      s.executeQuery("SELECT `name`, `type` FROM `ignores`");

      ResultSet rs = s.getResultSet();
      this.ignore_list.clear();
      this.soft_ignore_list.clear();
      while (rs.next()) {
        if (rs.getString("type").equals("hard")) {
          this.ignore_list.add(rs.getString("name").toLowerCase());
        } else {
          this.soft_ignore_list.add(rs.getString("name").toLowerCase());
        }
      }
      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);
      }
    }
  }
 private void handleKeepAlive() {
   Connection c = null;
   try {
     netCode = ois.readInt();
     c = checkConnectionNetCode();
     if (c != null && c.getState() == Connection.STATE_CONNECTED) {
       oos.writeInt(ACK_KEEP_ALIVE);
       oos.flush();
       System.out.println("->ACK_KEEP_ALIVE"); // $NON-NLS-1$
     } else {
       System.out.println("->NOT_CONNECTED"); // $NON-NLS-1$
       oos.writeInt(NOT_CONNECTED);
       oos.flush();
     }
   } catch (IOException e) {
     System.out.println("handleKeepAlive: " + e.getMessage()); // $NON-NLS-1$
     if (c != null) {
       c.setState(Connection.STATE_DISCONNECTED);
       System.out.println(
           "Connection closed with "
               + c.getRemoteAddress()
               + //$NON-NLS-1$
               ":"
               + c.getRemotePort()); // $NON-NLS-1$
     }
   }
 }
Example #30
0
  String getSetting(String key) {
    Connection con = null;
    String value = "";

    try {
      con = pool.getConnection(timeout);

      PreparedStatement s = con.prepareStatement("SELECT `value` FROM `settings` WHERE `key` = ?");
      s.setString(1, key);
      s.executeQuery();

      ResultSet rs = s.getResultSet();
      while (rs.next()) {
        value = rs.getString("value");
      }
      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 value;
  }