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;
  }
Example #2
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;
  }
Example #3
0
  public static void customStartAll(Connection conn) throws SQLException {
    String method = "customStartAll";
    int location = 1000;
    try {
      Statement stmt = conn.createStatement();

      int id = 0;
      int gpfdistPort = 0;
      String strSQL = "SELECT id\n";
      strSQL += "FROM os.custom_sql";

      ResultSet rs = stmt.executeQuery(strSQL);
      while (rs.next()) {
        id = rs.getInt(1);
        gpfdistPort = GpfdistRunner.customStart(OSProperties.osHome);

        strSQL = "INSERT INTO os.ao_custom_sql\n";
        strSQL +=
            "(id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, gpfdist_port)\n";
        strSQL +=
            "SELECT id, table_name, columns, column_datatypes, sql_text, source_type, source_server_name, source_instance_name, source_port, source_database_name, source_user_name, source_pass, "
                + gpfdistPort
                + "\n";
        strSQL += "FROM os.custom_sql\n";
        strSQL += "WHERE id = " + id;

        stmt.executeUpdate(strSQL);
      }
    } catch (SQLException ex) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
    }
  }
Example #4
0
  public static String getVariable(Connection conn, String name) throws SQLException {
    String method = "getVariable";
    int location = 1000;
    try {
      location = 2000;
      String value = "";

      location = 2100;
      Statement stmt = conn.createStatement();
      String strSQL = "SELECT os.fn_get_variable('" + name + "')";

      if (debug) Logger.printMsg("Getting Variable: " + strSQL);

      location = 2200;
      ResultSet rs = stmt.executeQuery(strSQL);

      while (rs.next()) {
        value = rs.getString(1);
      }

      location = 2300;
      return value;

    } catch (SQLException ex) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
    }
  }
Example #5
0
  public static String getVersion(Connection conn) throws SQLException {
    String method = "getVersion";
    int location = 1000;
    try {
      location = 2000;
      String value = "";

      location = 2100;
      Statement stmt = conn.createStatement();
      String strSQL =
          "SELECT CASE "
              + "WHEN POSITION ('HAWQ 2.0.1' in version) > 0 THEN 'HAWQ_2_0_1' "
              + "WHEN POSITION ('HAWQ 2.0.0' in version) > 0 THEN 'HAWQ_2_0_0' "
              + "WHEN POSITION ('HAWQ 1' in version) > 0 THEN 'HAWQ_1' "
              + "WHEN POSITION ('HAWQ' in version) = 0 AND POSITION ('Greenplum Database' IN version) > 0 THEN 'GPDB' "
              + "ELSE 'OTHER' END "
              + "FROM version()";
      if (debug) Logger.printMsg("Getting Variable: " + strSQL);

      location = 2200;
      ResultSet rs = stmt.executeQuery(strSQL);

      while (rs.next()) {
        value = rs.getString(1);
      }

      location = 2300;
      return value;

    } catch (SQLException ex) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
    }
  }
Example #6
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;
  }
Example #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;
  }
Example #8
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;
  }
  private static void dropCourse(Connection conn, String stu_no) throws SQLException {
    String cno = readEntry("Course number: ");
    String sno = readEntry("Section number : ");

    String chks =
        "SELECT g.Section_identifier FROM SECTION s, GRADE_REPORT g where s.Section_identifier=g.Section_identifier AND s.Course_number='"
            + cno
            + "' AND g.Section_identifier="
            + sno
            + " AND s.Semester='Spring' AND s.Year=TO_DATE('2007','YYYY') AND g.Student_number="
            + stu_no;
    Statement s3 = conn.createStatement();
    ResultSet r3 = s3.executeQuery(chks);

    if (r3.next()) {
      String del =
          "DELETE FROM GRADE_REPORT WHERE Student_number="
              + stu_no
              + " AND Section_identifier="
              + sno;
      Statement s4 = conn.createStatement();
      try {
        s4.executeUpdate(del);
        System.out.println("Course dropped");
      } catch (SQLException e) {
        System.out.println("Cannot drop a course from table");
      }
    } else {
      System.out.println("Enrollment data not found. Check the input and try again.");
    }
  }
  /**
   * 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");
    }
  }
  /**
   * 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");
    }
  }
  // execute and get results
  private void execute(Connection conn, String text, Writer writer, boolean commaSeparator)
      throws SQLException {

    BufferedWriter buffer = new BufferedWriter(writer);
    Statement stmt = conn.createStatement();
    stmt.execute(text);
    ResultSet rs = stmt.getResultSet();
    ResultSetMetaData metadata = rs.getMetaData();
    int nbCols = metadata.getColumnCount();
    String[] labels = new String[nbCols];
    int[] colwidths = new int[nbCols];
    int[] colpos = new int[nbCols];
    int linewidth = 1;

    // read each occurrence
    try {
      while (rs.next()) {
        for (int i = 0; i < nbCols; i++) {
          Object value = rs.getObject(i + 1);
          if (value != null) {
            buffer.write(value.toString());
            if (commaSeparator) buffer.write(",");
          }
        }
      }
      buffer.flush();
      rs.close();
    } catch (IOException ex) {
      if (Debug.isDebug()) ex.printStackTrace();
      // ok, exit from the loop
    } catch (SQLException ex) {
      if (Debug.isDebug()) ex.printStackTrace();
    }
  }
  /**
   * 使用 ResultSet 中的第 i 行给 Schema 赋值
   *
   * @param: rs ResultSet
   * @param: i int
   * @return: boolean
   */
  public boolean setSchema(ResultSet rs, int i) {
    try {
      // rs.absolute(i);		// 非滚动游标
      if (rs.getString("CalCode") == null) this.calCode = null;
      else this.calCode = rs.getString("CalCode").trim();

      if (rs.getString("RiskCode") == null) this.riskCode = null;
      else this.riskCode = rs.getString("RiskCode").trim();

      if (rs.getString("Type") == null) this.type = null;
      else this.type = rs.getString("Type").trim();

      if (rs.getString("CalSQL") == null) this.calSQL = null;
      else this.calSQL = rs.getString("CalSQL").trim();

      if (rs.getString("Remark") == null) this.remark = null;
      else this.remark = rs.getString("Remark").trim();

    } catch (SQLException sqle) {
      System.out.println(
          "数据库中的LEPCalMode表字段个数和Schema中的字段个数不一致,或者执行db.executeQuery查询时没有使用select * from tables");
      // @@错误处理
      CError tError = new CError();
      tError.moduleName = "LEPCalModeSchema";
      tError.functionName = "setSchema";
      tError.errorMessage = sqle.toString();
      this.mErrors.addOneError(tError);
      return false;
    }
    return true;
  }
  static void task1() throws FileNotFoundException, IOException, SQLException {
    // Read Input
    System.out.println("Task1 Started...");
    BufferedReader br = new BufferedReader(new FileReader(inputFile));
    br.readLine();
    String task1Input = br.readLine();
    br.close();
    double supportPercent =
        Double.parseDouble(task1Input.split(":")[1].split("=")[1].split("%")[0].trim());
    if (supportPercent >= 0) {
      System.out.println("Task1 Support Percent :" + supportPercent);
      // Prepare query
      String task1Sql =
          "select  temp.iname,(temp.counttrans/temp2.uniquetrans)*100 as percent"
              + " from (select i.itemname iname,count(t.transid) counttrans from trans t, items i"
              + " where i.itemid = t.itemid group by i.itemname having count(t.transid)>=(select count(distinct transid)*"
              + supportPercent / 100
              + " from trans)"
              + ") temp , (select count(distinct transid) uniquetrans from trans) temp2 order by percent";

      PreparedStatement selTask1 = con.prepareStatement(task1Sql);
      ResultSet rsTask1 = selTask1.executeQuery();

      BufferedWriter bw = new BufferedWriter(new FileWriter("system.out.1"));
      while (rsTask1.next()) {
        bw.write("{" + rsTask1.getString(1) + "}, s=" + rsTask1.getDouble(2) + "%");
        bw.newLine();
      }
      rsTask1.close();
      bw.close();
      System.out.println("Task1 Completed...\n");
    } else System.out.println("Support percent should be a positive number");
  }
Example #15
0
  private synchronized void init() throws SQLException {
    if (isClosed) return;

    // do tables exists?
    Statement stmt = conn.createStatement();
    ResultSet rs = stmt.executeQuery(TABLE_NAMES_SELECT_STMT);

    ArrayList<String> missingTables = new ArrayList(TABLES.keySet());
    while (rs.next()) {
      String tableName = rs.getString("name");
      missingTables.remove(tableName);
    }

    for (String missingTable : missingTables) {
      try {
        Statement createStmt = conn.createStatement();
        // System.out.println("Adding table "+ missingTable);
        createStmt.executeUpdate(TABLES.get(missingTable));
        createStmt.close();

      } catch (Exception e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
      }
    }
  }
 public void actionPerformed(ActionEvent evt) {
   // 删除原来的JTable(JTable使用scrollPane来包装)
   if (scrollPane != null) {
     jf.remove(scrollPane);
   }
   try (
   // 根据用户输入的SQL执行查询
   ResultSet rs = stmt.executeQuery(sqlField.getText())) {
     // 取出ResultSet的MetaData
     ResultSetMetaData rsmd = rs.getMetaData();
     Vector<String> columnNames = new Vector<>();
     Vector<Vector<String>> data = new Vector<>();
     // 把ResultSet的所有列名添加到Vector里
     for (int i = 0; i < rsmd.getColumnCount(); i++) {
       columnNames.add(rsmd.getColumnName(i + 1));
     }
     // 把ResultSet的所有记录添加到Vector里
     while (rs.next()) {
       Vector<String> v = new Vector<>();
       for (int i = 0; i < rsmd.getColumnCount(); i++) {
         v.add(rs.getString(i + 1));
       }
       data.add(v);
     }
     // 创建新的JTable
     JTable table = new JTable(data, columnNames);
     scrollPane = new JScrollPane(table);
     // 添加新的Table
     jf.add(scrollPane);
     // 更新主窗口
     jf.validate();
   } catch (Exception e) {
     e.printStackTrace();
   }
 }
  /**
   * 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");
    }
  }
  private static void addCourse(Connection conn, String stu_no) throws SQLException {
    String cno = readEntry("Course number : ");
    String sno = readEntry("Section number : ");

    String course_query =
        "SELECT Section_identifier FROM SECTION WHERE Section_identifier="
            + sno
            + " AND Course_number='"
            + cno
            + "' AND Semester='Spring' AND YEAR=TO_DATE('2007','YYYY')";
    // System.out.println(course_query);
    Statement s1 = conn.createStatement();
    ResultSet r1 = s1.executeQuery(course_query);
    if (r1.next()) {
      String addc =
          "INSERT INTO GRADE_REPORT (Student_number, Section_identifier, Grade) VALUES ("
              + stu_no
              + ", "
              + sno
              + ", 'NULL')";
      Statement s2 = conn.createStatement();
      System.out.println(addc);
      try {
        s2.executeUpdate(addc);
        System.out.println("Enrollment successfull");
      } catch (SQLException e) {
        System.out.println("Cannot add course into table");
      }
    } else {
      System.out.println("Course not found");
    }
    s1.close();
  }
  /**
   * 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;
  }
Example #20
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;
  }
  /**
   * 使用 ResultSet 中的第 i 行给 Schema 赋值
   *
   * @param: rs ResultSet
   * @param: i int
   * @return: boolean
   */
  public boolean setSchema(ResultSet rs, int i) {
    try {
      // rs.absolute(i);		// 非滚动游标
      if (rs.getString("RiskCode") == null) this.riskCode = null;
      else this.riskCode = rs.getString("RiskCode").trim();

      if (rs.getString("RiskVer") == null) this.riskVer = null;
      else this.riskVer = rs.getString("RiskVer").trim();

      if (rs.getString("DutyCode") == null) this.dutyCode = null;
      else this.dutyCode = rs.getString("DutyCode").trim();

      if (rs.getString("ChoFlag") == null) this.choFlag = null;
      else this.choFlag = rs.getString("ChoFlag").trim();

      if (rs.getString("SpecFlag") == null) this.specFlag = null;
      else this.specFlag = rs.getString("SpecFlag").trim();

    } catch (SQLException sqle) {
      System.out.println(
          "数据库中的LEPRiskDuty表字段个数和Schema中的字段个数不一致,或者执行db.executeQuery查询时没有使用select * from tables");
      // @@错误处理
      CError tError = new CError();
      tError.moduleName = "LEPRiskDutySchema";
      tError.functionName = "setSchema";
      tError.errorMessage = sqle.toString();
      this.mErrors.addOneError(tError);
      return false;
    }
    return true;
  }
Example #22
0
  public void setQuery(String q) {
    cache = new Vector();
    try {
      ResultSet rs = statement.executeQuery(q);
      ResultSetMetaData meta = rs.getMetaData();
      colCount = meta.getColumnCount();
      headers = new String[colCount];
      for (int h = 1; h <= colCount; h++) {
        headers[h - 1] = meta.getColumnName(h);
      }
      while (rs.next()) {
        String[] record = new String[colCount];
        for (int i = 0; i < colCount; i++) {
          record[i] = rs.getString(i + 1);
        }
        cache.addElement(record);
      } // while sonu

      fireTableChanged(null);
    } // try sonu
    catch (Exception e) {
      cache = new Vector();
      e.printStackTrace();
    }
  } // setQuery sonu
Example #23
0
  public String checkValidLogin(String myUserName, String myPW) {

    try {
      Class.forName(javaSQLDriverPath);
      Connection conn =
          (Connection) DriverManager.getConnection(ConnectionPath, ConnectionUser, ConnectionPW);
      Statement st = conn.createStatement();
      String query = "Select * from User";

      ResultSet rs = st.executeQuery(query);
      while (rs.next()) {
        // return rs.getString("Username");
        if (myUserName.equals(rs.getString("Username"))) {
          if (myPW.equals(rs.getString("Password"))) {
            setUserVariables(myUserName);
            return "success";
          } else {
            return "wrongPassword";
          }
        }
      }

      rs.close();
      st.close();
      conn.close();

      return "userNotFound";
    } catch (Exception e) {

      return e.getMessage();
    }
  }
  private boolean login(String[] part) {
    ResultSet rs = null;

    try {
      rs = beanOracle.selection("PASSWORD", "UTILISATEURS", "LOGIN = '******'");
    } catch (SQLException e) {
      System.err.println(e.getStackTrace());
    }

    String pwd = null;

    try {
      if (!rs.next()) {
        SendMsg("ERR#Login invalide");
      } else pwd = rs.getString("PASSWORD");
    } catch (SQLException ex) {
      System.err.println(ex.getStackTrace());
    }

    if (pwd.equals(part[2])) {
      SendMsg("ACK");
      return true;
    } else SendMsg("ERR#Mot de passe incorrecte");

    return false;
  }
Example #25
0
    public MetadataBlob[] select(String selectString) {
      PreparedStatement stmt = null;
      try {
        stmt =
            conn.prepareStatement(
                "select writingKey, mapkey, blobdata from metadatablobs " + selectString + ";");
        ResultSet rs = stmt.executeQuery();
        List<MetadataBlob> list = new ArrayList<MetadataBlob>();
        while (rs.next()) {
          MetadataBlob f =
              new MetadataBlob(
                  rs.getString("writingkey"), rs.getString("mapkey"), rs.getString("blobdata"));
          list.add(f);
        }

        return list.toArray(new MetadataBlob[0]);
      } catch (SQLException sqe) {
        System.err.println("Error selecting: " + selectString);
        sqe.printStackTrace();
        return null;
      } finally {
        if (stmt != null)
          try {
            stmt.close();
          } catch (SQLException sqe2) {
            sqe2.printStackTrace();
          }
      }
    }
Example #26
0
  /* to search the DB table for req. video*/
  boolean search(String file) {

    boolean found = false;

    Connection con = null;
    String url =
        "jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};  DBQ=D://db.mdb; DriverID=22;READONLY=true;";

    try {
      con = DriverManager.getConnection(url, "", "");
      try {
        Statement st = con.createStatement();
        ResultSet rs = st.executeQuery("SELECT video FROM p3");
        while (rs.next()) {
          String colvalue = rs.getString("video");
          if (colvalue.equalsIgnoreCase(file)) found = true;
        }

      } catch (SQLException s) {
        System.out.println("SQL statement is not executed!");
      }
    } catch (Exception e) {
      System.out.println(e);
    }
    return found;
  }
Example #27
0
 public void deleteOption(String modelName, String optionSetName, String option) {
   try {
     int autoid = 0;
     String sql = "select id from automobile where name ='" + modelName + "';";
     ResultSet rs;
     rs = statement.executeQuery(sql);
     while (rs.next()) {
       autoid = rs.getInt("id"); // get auto_id
     }
     int opsid = 0;
     sql =
         "select id from optionset where name ='"
             + optionSetName
             + "' and auto_id= "
             + autoid
             + ";";
     rs = statement.executeQuery(sql);
     while (rs.next()) {
       opsid = rs.getInt("id"); // get option_id
     }
     sql = "delete from options where name= '" + option + "' and option_id= " + opsid;
     statement.executeUpdate(sql); // delete it using name and option_id
   } catch (SQLException e) {
     e.printStackTrace();
   }
 }
Example #28
0
  public void updateOptionPrice(
      String modelName, String optionSetName, String optionName, float newPrice) {
    try {
      int autoid = 0;
      String sql = "select id from automobile where name ='" + modelName + "';";
      ResultSet rs = statement.executeQuery(sql);
      while (rs.next()) {
        autoid = rs.getInt("id"); // get auto_id
      }
      int opsid = 0;
      sql =
          "select id from optionset where name ='"
              + optionSetName
              + "' and auto_id= "
              + autoid
              + ";";
      rs = statement.executeQuery(sql);
      while (rs.next()) {
        opsid = rs.getInt("id"); // get option_id
      }
      sql =
          "update options set price = "
              + newPrice
              + " where name= '"
              + optionName
              + "' and option_id= "
              + opsid
              + ";";
      statement.executeUpdate(sql); // update it with name and option_id

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Example #29
0
  public void doPost(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {
    PrintWriter writer = response.getWriter();
    HttpSession session = request.getSession();

    String username = request.getParameter("username");
    String password = request.getParameter("password");
    String type = request.getParameter("type");
    System.out.println(username + password + type);

    session.setAttribute("user", username);

    try {
      writer.println("<html>");
      writer.println("<body bgcolor=green>");
      writer.println("<center>");
      ps.setString(1, username);
      ps.setString(2, password);
      ps.setString(3, type);
      ResultSet rs = ps.executeQuery();

      if (rs.next()) {
        writer.println("<h1>LOGIN SUCCESSFUL</h1><br><br>");
        writer.println("<a href=account.html>click here to see your account</a>");
      } else {
        writer.println("<h1>LOGIN FAILED</h1><br><br>");
        writer.println("<a href=login.html>click here to login again</a>");
      }
      writer.println("</center>");
      writer.println("</body>");
      writer.println("</html>");
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #30
-1
  public List<Empleado> getAllEmpleados() {
    List ll = new LinkedList();
    Statement st;
    ResultSet rs;
    String url = "jdbc:mysql://localhost:3306/food";
    String user = "******";
    String pass = "******";
    String driver = "com.mysql.jdbc.Driver";

    try (Connection connection = DriverManager.getConnection(url, user, pass)) {
      Class.forName(driver);
      st = connection.createStatement();
      String recordQuery = ("Select * from empleados");
      rs = st.executeQuery(recordQuery);
      while (rs.next()) {
        Integer id = rs.getInt("idEmpleados");
        String name = rs.getString("Nombre");
        String telf = rs.getString("Telefono");
        ll.add(new Empleado(id, name, telf));
        /// System.out.println(id +","+ name +","+ apellido +","+ cedula +","+ telf +"
        // "+direccion+"");
      }

    } catch (ClassNotFoundException | SQLException ex) {
      Logger.getLogger(MenuEmpleados.class.getName()).log(Level.SEVERE, null, ex);
    }
    return ll;
  }