public Vector getUsacFiles(String invoice) {
    Vector fileList = new Vector();
    String query = "";

    query =
        query
            + " SELECT distinct filename, '  Date:'||to_char(usac_prcs_dat,'MM/DD/YYYY'),trunc(usac_prcs_dat) usac_prcs_dat ";
    query = query + " FROM stage_usac_form ";
    query = query + " WHERE rtrim(ltrim(sdc_inv_no)) = ?";
    query = query + " ORDER BY usac_prcs_dat ";

    USFEnv.getLog().writeDebug("getUsacFiles() Query :" + query, this, null);

    PreparedStatement pstmt = null;
    ResultSet rs = null;
    try {
      pstmt = cConn.prepareStatement(query);
      pstmt.setString(1, invoice);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        fileList.addElement(rs.getString(1));
        fileList.addElement(rs.getString(2));
      }

      // rs.close();
      // stmt.close();
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();

    } catch (SQLException ex) {
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (Exception e) {
        USFEnv.getLog().writeCrit("Unable to close ResultSet and statement", this, e);
      }
      USFEnv.getLog().writeCrit("getUsacFiles() Failed Query:" + query, this, ex);
    }

    /*	try
    {
    	if( rs != null)
    		rs.close();
    }
    catch(Exception e)
    {
    	USFEnv.getLog().writeCrit("Unable to close ResultSet",this,null);
    }
    try
    {
    	if( pstmt != null)
    		pstmt.close();
    }
    catch(Exception e)
    {
    USFEnv.getLog().writeCrit("Unable to close Prepared Statement",this,null);
    }*/
    return fileList;
  }
  private void initFromDatabase() throws SQLException, BlockStoreException {
    Statement s = conn.get().createStatement();
    ResultSet rs;

    rs = s.executeQuery("SELECT value FROM settings WHERE name = '" + CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException("corrupt Postgres block store - no chain head pointer");
    }
    Sha256Hash hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    this.chainHeadBlock = get(hash);
    this.chainHeadHash = hash;
    if (this.chainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - head block not found");
    }

    rs =
        s.executeQuery(
            "SELECT value FROM settings WHERE name = '" + VERIFIED_CHAIN_HEAD_SETTING + "'");
    if (!rs.next()) {
      throw new BlockStoreException(
          "corrupt Postgres block store - no verified chain head pointer");
    }
    hash = new Sha256Hash(rs.getBytes(1));
    rs.close();
    s.close();
    this.verifiedChainHeadBlock = get(hash);
    this.verifiedChainHeadHash = hash;
    if (this.verifiedChainHeadBlock == null) {
      throw new BlockStoreException("corrupt Postgres block store - verified head block not found");
    }
  }
Exemple #3
1
  private String getMonth(String month) {
    String query;
    String i_month = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;
    query = "select to_number(to_char(to_date(?,'Month'),'MM')) from dual";

    USFEnv.getLog().writeDebug("Dinvjrnl:Get Month - Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        i_month = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Month Conversion Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return i_month;
  }
  @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;
  }
  /**
   * This method queries the database to get the SPIN associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getSPINname(String invid) {
    String query;
    String spinnm = "";
    Statement stmt = null;
    ResultSet rs = null;

    //		query =  "select slc_nm from usw_co,inv where uc_id=uc_id_fk and inv_id="+invid;
    query =
        "select srv_provr_nm from srv_provr,rhcc_inv where spin = rhcc_inv.spin_fk and rhcc_inv_id="
            + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        spinnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The SPIN Name not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return spinnm;
  }
Exemple #6
0
  /**
   * This method queries the database to get the list of the Billing Systems.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getYears(String year) {
    String query;
    Vector years = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select yr||'-'||(yr+1),yr from fung_yr where yr > ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        years.addElement(rs.getString(1));
        years.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Years List not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return years;
  }
Exemple #7
0
  /**
   * This method queries the database to get the sequence for the BP_ID.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBpid() {
    String query;
    String bpid = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select bp_id_seq.nextval from dual ";

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        bpid = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Sequence Value for BP_ID not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return bpid;
  }
Exemple #8
0
  /**
   * This method queries the database to get the details related to the bp_id passed.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getBpdet(String bpid) {
    String query;
    Vector bpdet = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select rtrim(bs_id_fk||bp_rgn),bp_month from blg_prd where bp_id = ?";

    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, bpid);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        bpdet.addElement(rs.getString(1));
        bpdet.addElement(rs.getString(2));
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: BP_ID details not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return bpdet;
  }
  /**
   * This method queries the database to validate the passed in FRN for the selected Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean validateFRN(String frn, String year) {
    String query;
    boolean validfrn = false;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select wo_id from wrk_ordr where wrk_ordr_no='" + frn + "' and yr_fk=" + year;

    USFEnv.getLog().writeCrit("RhccDinvview: FRN query " + query, this, null);
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        validfrn = true;
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: FRN not valid for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return false;
  }
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public long getInvID(String inv_no) {
    String query;
    long invID = 0;
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_id from inv where inv_no=" + inv_no;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invID = rs.getLong(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvview: The Invoice ID not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }

    return (invID);
  }
  /**
   * This method queries the database to get the Invoice Number associated with the passes Invid
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getInvno(String invid) {
    String query;
    String invno = "";
    Statement stmt = null;
    ResultSet rs = null;

    query = "select inv_no from rhcc_inv where rhcc_inv_id=" + invid;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery(query);
      if (rs.next()) {
        invno = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (stmt != null) stmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("RhccDinvview: The Invoice Number not retreived", this, ex);
      try {
        if (rs != null) rs.close();
        if (stmt != null) stmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/statement", this, e);
      }
    }
    return invno;
  }
Exemple #12
0
  /**
   * This method queries the database to get the name of the Billing System.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public String getBlgsysnm(String blgsys) {
    String query;
    String blgsysnm = "";
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select bs_nm from blg_sys where bs_id = ?";

    USFEnv.getLog().writeDebug("Dinvjrnl: Billing System Name Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, blgsys);

      rs = pstmt.executeQuery();
      if (rs.next()) {
        blgsysnm = rs.getString(1);
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Billing System Name not Retreived ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepare statement", this, e);
      }
    }

    return blgsysnm;
  }
Exemple #13
0
  /**
   * This method queries the database to check if the passed start date for the Journal Month starts
   * exactly after the Previous end date.
   *
   * @exception SQLException, if query fails
   * @author
   */
  public boolean check(String strtdat, String blgsys, String year, String month, String rgn) {
    if (month.length() > ((new Integer("2")).intValue())) {
      month = getMonth(month);
    }

    String query;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query = "select 'true' from (select bp_end_dat,bp_year from blg_prd ";
    query = query + "where bp_month= decode(to_number(?),1,12,to_number(?)-1) and bs_id_fk = ?";

    if ((rgn != null) && !(rgn.equals(""))) {
      query = query + " and bp_rgn = ?";
    } else {
      query = query + " and bp_rgn is null";
    }

    query = query + " and bp_year=decode(to_number(?),1,to_number(?)-1,to_number(?) ) ) A ";

    query = query + " where to_date(?,'MM/DD/YYYY')-A.bp_end_dat=1";

    USFEnv.getLog().writeDebug("Dinvjrnl:check Date- Query" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, month);
      pstmt.setString(2, month);
      pstmt.setString(3, blgsys);

      int m = 4;
      if ((rgn != null) && !(rgn.equals(""))) {
        pstmt.setString(m, rgn);
        m = m + 1;
      }

      pstmt.setString(m, month);
      pstmt.setString(m + 1, year);
      pstmt.setString(m + 2, year);
      pstmt.setString(m + 3, strtdat);

      rs = pstmt.executeQuery();

      if (rs.next()) {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
        return true;
      }
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: Date Check Failed ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    return false;
  }
  public ExptLocatorTree(Genome g) {
    super();
    try {
      java.sql.Connection c = DatabaseFactory.getConnection(ExptLocator.dbRole);
      int species = g.getSpeciesDBID();
      int genome = g.getDBID();

      Statement s = c.createStatement();
      ResultSet rs = null;

      rs =
          s.executeQuery(
              "select e.name, e.version from experiment e, exptToGenome eg where e.active=1 and "
                  + "e.id=eg.experiment and eg.genome="
                  + genome);
      while (rs.next()) {
        String name = rs.getString(1);
        String version = rs.getString(2);
        ChipChipLocator loc = new ChipChipLocator(g, name, version);
        this.addElement(loc.getTreeAddr(), loc);
      }
      rs.close();

      rs =
          s.executeQuery(
              "select ra.name, ra.version from rosettaanalysis ra, rosettaToGenome rg where "
                  + "ra.id = rg.analysis and ra.active=1 and rg.genome="
                  + genome);
      while (rs.next()) {
        String name = rs.getString(1);
        String version = rs.getString(2);
        MSPLocator msp = new MSPLocator(g, name, version);
        this.addElement(msp.getTreeAddr(), msp);
      }
      rs.close();

      rs =
          s.executeQuery(
              "select ra.name, ra.version from bayesanalysis ra, bayesToGenome rg where "
                  + "ra.id = rg.analysis and ra.active=1 and rg.genome="
                  + genome);
      while (rs.next()) {
        String name2 = rs.getString(1);
        String version2 = rs.getString(2);
        ExptLocator loc2 = new BayesLocator(g, name2, version2);
        addElement(loc2.getTreeAddr(), loc2);
      }
      rs.close();
      s.close();

      DatabaseFactory.freeConnection(c);
    } catch (SQLException se) {
      se.printStackTrace(System.err);
      throw new RuntimeException(se);
    } catch (UnknownRoleException e) {
      e.printStackTrace();
    }
  }
Exemple #15
0
  /**
   * This method queries the database to get the Journal Dates for the passed in Funding Year
   *
   * @exception SQLException, if query fails
   * @author
   */
  public Vector getJrnldts(String year) {
    String query;
    Vector jrnllst = new Vector();
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    query =
        "select bp_id,decode(bs_nm,'CRIS','1','IABS','2','BART','3','SOFI','4','INFRANET','5','6') ord, ";
    query = query + "rtrim(bs_nm||' '||bp_rgn) nm, bp_month,decode(bp_month,1,'January',";
    query =
        query
            + "2,'February',3,'March',4,'April',5,'May',6,'June',7,'July',8,'August',9,'September',";
    query =
        query
            + "10,'October',11,'November',12,'December'), to_char(bp_strt_dat,'MM/DD/YYYY'),to_char(bp_end_dat,'MM/DD/YYYY') ";
    query = query + "from blg_prd,blg_sys ";
    query = query + "where bs_id=bs_id_fk and ( bp_strt_dat >= ";
    query = query + "(select strt_dat from fung_yr where yr = ?) and ";
    query = query + "bp_end_dat <= (select end_dat from fung_yr where yr = ?) ";
    query = query + "or ( bp_year =to_number(?) and bp_month=7) ) ";
    query = query + "order by ord,nm,bp_strt_dat ";

    USFEnv.getLog().writeDebug("Dinvjrnl: JRNL Dates Query :" + query, this, null);
    try {
      pstmt = conn.prepareStatement(query);
      pstmt.setString(1, year);
      pstmt.setString(2, year);
      pstmt.setString(3, year);

      rs = pstmt.executeQuery();
      while (rs.next()) {
        Dinvjrnl jrnldts = new Dinvjrnl(null);

        jrnldts.strBpid = rs.getString(1);
        jrnldts.strBlgsys = rs.getString(3);
        jrnldts.strBlgmnth = rs.getString(5);
        jrnldts.strBlgstrtdt = rs.getString(6);
        jrnldts.strBlgenddt = rs.getString(7);

        jrnllst.addElement(jrnldts);
        jrnldts = null;
      }
      if (rs != null) rs.close();
      if (pstmt != null) pstmt.close();
    } catch (SQLException ex) {
      USFEnv.getLog().writeCrit("Dinvjrnl: JRNL Dates not retreived for the Year ", this, ex);
      try {
        if (rs != null) rs.close();
        if (pstmt != null) pstmt.close();
      } catch (SQLException e) {
        USFEnv.getLog().writeCrit("Unable to close the resultset/prepared statement", this, e);
      }
    }

    USFEnv.getLog()
        .writeDebug("Dinvjrnl: Journal Dates Vector size :" + jrnllst.size(), this, null);
    return jrnllst;
  }
 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;
     }
   }
 }
Exemple #17
0
 /**
  * ************************************************************************ 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
Exemple #18
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;
  }
Exemple #19
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;
  }
Exemple #20
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);
      }
    }
  }
Exemple #21
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);
      }
    }
  }
Exemple #22
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 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;
      }
    }
  }
  /**
   * Check the user's name and password and verify that the user is an instructor. Throws
   * InvalidDBRequestException if user is not an instructor, wrong password, or if any error occured
   * to the database connection.
   *
   * @param name user's user name
   * @param pass user's password
   * @throws InvalidDBRequestException
   */
  public void instructorLogin(String name, String pass) throws InvalidDBRequestException {
    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // check if instructor
      rs = stmt.executeQuery("select password from instructor where login = '******'");
      if (!rs.next()) {
        if (debug) System.out.println("User not found in the instructor table");
        throw new InvalidDBRequestException("Instructor not registered");
      }

      // check password
      if (!rs.getString(1).equals(pass)) {
        if (debug) System.out.println("Invalid password for user: "******"Invalid password for user: "******"Invalid SQL in instructor login: "******"???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }
  }
  /**
   * Remove a student from a particular course. Also Deletes all the quiz vizualisation files in the
   * student's directory which relates to the course. Caution: vizualisation file will be deleted
   * eventhough it also relates to another course if the student is also registered to that course.
   * (FIX ME!) Throws InvalidDBRequestException if the student is not registered in the course,
   * error occured during deletion, or other exception occured.
   *
   * @param username student's user name
   * @param courseID course id (course number + instructor name)
   * @throws InvalidDBRequestException
   */
  public void deleteStudent(String username, String courseID) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      int count = 0;

      // check if student registered to the course
      rs =
          stmt.executeQuery(
              "select * from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (!rs.next())
        throw new InvalidDBRequestException("Student is not registered to the course");

      // remove student from the course
      count =
          stmt.executeUpdate(
              "delete from courseRoster where course_id = '"
                  + courseID
                  + "' and user_login = '******'");
      if (count != 1) throw new InvalidDBRequestException("Error occured during deletion!");

      // delete the quiz visualization files
      rs =
          stmt.executeQuery(
              "select distinct unique_id, s.test_name from scores s, courseTest t "
                  + "where s.test_name = t.test_name "
                  + "and course_id = '"
                  + courseID
                  + "' "
                  + "and user_login = '******'");
      while (rs.next()) {
        deleteVisualization(rs.getString(1), username, rs.getString(2));
        count =
            stmt.executeUpdate("delete from scores where unique_id = " + rs.getString(1).trim());
      }

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addstudent: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Deletes students who are older than a certain number of years and not registered to any course.
   *
   * @param year students older than year are candidates to be deleted
   * @throws InvalidDBRequestException
   */
  public void deleteOldStudent(int year) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();

      // query all student who have been in the database longer than a number of years and not
      // registered to any course
      ResultSet rs =
          stmt.executeQuery(
              "select login, count(course_id) "
                  + "from student s left join courseRoster r on login = user_login "
                  + "where date_entered < SUBDATE(now(), INTERVAL "
                  + new String().valueOf(year).trim()
                  + " YEAR) "
                  + "group by login, date_entered");
      // delete them
      while (rs.next()) if (rs.getInt(2) == 0) purgeStudent(rs.getString(1).trim());

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Deletes an instructor from the database. Deletes the instructor's courses by invoking the
   * deleteCourse method. Throws InvalidDBRequestException if instructor not in the database or
   * other database connection problems.
   *
   * @see deleteCourse
   * @param instructor instructor's user name
   * @throws InvalidDBRequestException
   */
  public void deleteInstructor(String instructor) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      int count;

      // delete the instructor's courses
      ResultSet rs =
          stmt.executeQuery(
              "select course_num from course where instructor = '" + instructor + "'");
      while (rs.next()) deleteCourse(rs.getString(1).trim(), instructor);

      // delete the instructor's record
      count = stmt.executeUpdate("delete from instructor where login ='******'");

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in addCourse: " + e.getMessage());
      throw new InvalidDBRequestException("??? ");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Internal Server Error");
    }
  }
  /**
   * Gets a list of courses that belongs to an instructor. Throws InvalidDBRequestException if any
   * error occured to the database connection.
   *
   * @param name the instructor's user name
   * @return a vector containing the list of courses
   * @throws InvalidDBRequestException
   */
  public Vector getCourseList(String name) throws InvalidDBRequestException {
    Vector courseList = new Vector();

    try {
      Connection db;

      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

      Statement stmt = db.createStatement();
      ResultSet rs;

      // get the course list
      rs =
          stmt.executeQuery(
              "select course_num, course_name from course where instructor = '"
                  + name
                  + "' order by course_num");
      while (rs.next()) courseList.add(rs.getString(1) + " - " + rs.getString(2));

      rs.close();
      stmt.close();
      db.close();
    } catch (SQLException e) {
      System.err.println("Invalid SQL in getCourseList: " + e.getMessage());
      throw new InvalidDBRequestException("???");
    } catch (ClassNotFoundException e) {
      System.err.println("Driver Not Loaded");
      throw new InvalidDBRequestException("Server Error");
    }

    return courseList;
  }
 private boolean columnExists(Connection connection, String table, String column)
     throws SQLException {
   ResultSet columns = connection.getMetaData().getColumns(null, null, table, column);
   boolean exists = columns.next();
   columns.close();
   return exists;
 }
  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();
    }
  }