Example #1
2
  // Takes and image_id, pulls in the image from cache, and goes about
  // encoding it to put it into the database in a transaction.  The last
  // query in the transaction records the image having been stored.
  protected void storeImage(int image_id) throws Exception {
    PhotoImage p = new PhotoImage(image_id);
    SpyDB pdb = getDB();
    Connection db = null;
    Statement st = null;
    Vector v = p.getImage();
    System.err.println(
        "Storer: Got image for " + image_id + " " + v.size() + " lines of data to store.");
    try {
      int i = 0, n = 0;
      db = pdb.getConn();
      db.setAutoCommit(false);
      st = db.createStatement();
      BASE64Encoder base64 = new BASE64Encoder();
      String data = "";

      for (; i < v.size(); i++) {
        String tmp = base64.encodeBuffer((byte[]) v.elementAt(i));
        tmp = tmp.trim();

        if (data.length() < 2048) {
          data += tmp + "\n";
        } else {
          storeQuery(image_id, n, st, data);
          data = tmp;
          n++;
        }
      }
      // OK, this is sick, but another one right now for the spare.
      if (data.length() > 0) {
        System.err.println("Storer:  Storing spare.");
        storeQuery(image_id, n, st, data);
        n++;
      }
      System.err.println("Storer:  Stored " + n + " lines of data for " + image_id + ".");
      st.executeUpdate(
          "update upload_log set stored=datetime(now())\n" + "\twhere photo_id = " + image_id);
      db.commit();
      // Go ahead and generate a thumbnail.
      p.getThumbnail();
    } catch (Exception e) {
      // If anything happens, roll it back.
      if (st != null) {
        try {
          db.rollback();
        } catch (Exception e3) {
          // Nothing
        }
      }
    } finally {
      if (db != null) {
        try {
          db.setAutoCommit(true);
        } catch (Exception e) {
          System.err.println("Error:  " + e);
        }
      }
      pdb.freeDBConn();
    }
  }
Example #2
1
 static void attributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     Statement meanQuery = con.createStatement();
     ResultSet mean = meanQuery.executeQuery("select AVG(sal) from employ where sal IS NOT NULL");
     if (mean.next()) {
       fill = con.createStatement();
       fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + mean.getInt(1) + ")");
     }
     /*fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = COALESCE(sal,AVG(sal))");*/
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
  /**
   * Delete a student from the database. Also deletes the student's folders. Throws
   * InvalidDBRequestException if any error in database connection.
   *
   * @param username student's user name
   * @throws InvalidDBRequestException
   */
  private void purgeStudent(String username) throws InvalidDBRequestException {
    try {
      Class.forName(GaigsServer.DBDRIVER);
      db =
          DriverManager.getConnection(GaigsServer.DBURL, GaigsServer.DBLOGIN, GaigsServer.DBPASSWD);

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

      // delete from scores
      count = stmt.executeUpdate("delete from scores where user_login = '******'");

      // delete from student
      count = stmt.executeUpdate("delete from student where login = '******'");

      // delete student's folder
      File studentDir = new File("./StudentQuizzes/" + username);
      if (!(studentDir.delete())) {
        System.err.println("Error in deleting folder for student: " + username);
      }

      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");
    }
  }
  public void doSQL() throws Exception {
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");
    Statement stat = conn.createStatement();
    stat.executeUpdate("drop table if exists people;");
    stat.executeUpdate("create table people (name, occupation);");
    PreparedStatement prep = conn.prepareStatement("insert into people values (?, ?);");

    prep.setString(1, "Gandhi");
    prep.setString(2, "politics");
    prep.addBatch();
    prep.setString(1, "Turing");
    prep.setString(2, "computers");
    prep.addBatch();
    prep.setString(1, "Wittgenstein");
    prep.setString(2, "smartypants");
    prep.addBatch();

    conn.setAutoCommit(false);
    prep.executeBatch();
    conn.setAutoCommit(true);

    ResultSet rs = stat.executeQuery("select * from people;");
    while (rs.next()) {
      System.out.println("name = " + rs.getString("name"));
      System.out.println("job = " + rs.getString("occupation"));
    }
    rs.close();
    conn.close();
  }
  /**
   * 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");
    }
  }
  public void updateTables(String updateQuery1, String updateQuery2, String updateQuery3)
        // PRE:  updateQuery1,updateQuery2, updateQuery3 must be initialized
        // POST: Updates the list of Items based on the querys.
      {
    String driver = "org.apache.derby.jdbc.ClientDriver"; // Driver for DB
    String url = "jdbc:derby://localhost:1527/ShopDataBase"; // Url for DB
    String user = "******"; // Username for db
    String pass = "******"; // Password for db
    Connection myConnection; // Connection obj to db
    Statement stmt; // Statement to execute a result appon
    ResultSet results; // A set containing the returned results
    int i; // Index for the array
    try // Try connection to db
    {

      Class.forName(driver).newInstance(); // Create our db driver

      myConnection = DriverManager.getConnection(url, user, pass); // Initalize our connection

      stmt =
          myConnection.createStatement(
              ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      stmt.executeUpdate(updateQuery1);

      stmt.executeUpdate(updateQuery2);

      stmt.executeUpdate(updateQuery3);

      results = stmt.executeQuery(previous_query); // Call the previous query

      i = 0;
      while (results.next()) // Itterate through the results set
      {
        itemsArray[i] =
            new Item(
                results.getInt("item_id"),
                results.getString("item_name"),
                results.getString("item_type"),
                results.getInt("price"),
                results.getInt("owner_id"),
                results.getString("item_path"));
        i++;
      }
      results.close(); // Close the ResultSet
      stmt.close(); // Close the statement
      myConnection.close(); // Close the connection to db

    } catch (Exception e) // Cannot connect to db
    {
      System.err.println(e.toString());
      System.err.println("Error, something went horribly wrong! in updateTables");
    }
  }
Example #7
0
  public void CrearTablas() {
    Connection con = null;
    Statement st = null;
    String USER =
        "******"
            + "apellido VARCHAR(45),actividad varchar(20),comuna varchar(30),carrera varchar(10) ,"
            + "telefono varchar(15),celular varchar(20),direccion VARCHAR(45),email varchar(50));";

    String ADMIN =
        "CREATE TABLE ADMIN (run_admin VARCHAR(20) NOT NULL PRIMARY KEY, nombre VARCHAR(45),"
            + "apellido VARCHAR(45),actividad varchar(20),comuna varchar(30),carrera varchar(10) ,"
            + "telefono varchar(15),celular varchar(20),direccion VARCHAR(45),email varchar(50),contraseƱa varchar(50));";

    String ARTICULO =
        "CREATE TABLE ARTICULO (codigo VARCHAR(40) NOT NULL PRIMARY KEY,codigoutem VARCHAR(40), categoria VARCHAR(45),"
            + "producto VARCHAR(50), descripcion varchar(50),estado VARCHAR(12));";

    String PEDIDO =
        "CREATE TABLE PEDIDO (id_pedido VARCHAR(40) NOT NULL PRIMARY KEY,"
            + "id_producto VARCHAR(20),id_usuario varchar (20), "
            + "FOREIGN KEY (id_producto) REFERENCES ARTICULO(codigo),"
            + "FOREIGN KEY(id_usuario) REFERENCES USUARIO(run_usuario)"
            + ",fecha_pedido varchar(15) ,fecha_entrega varchar(15));";
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", pass);
      try {
        st = con.createStatement();

        st.executeUpdate(USER);

        st.executeUpdate(ADMIN);

        st.executeUpdate(ARTICULO);

        st.executeUpdate(PEDIDO);

      } catch (SQLException s) {

        SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, "error al crear tablas");

      } finally {
        con.close();
        st.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #8
0
  /*
   * addAuto method. parse the Automobile object and add them to three tables.
   */
  public void addAuto(Automobile auto) {
    String name = null;
    float price = 0;
    int autoid = 0;
    String sql = null;
    try {
      String autoName = auto.getMake() + " " + auto.getModel();
      price = auto.getBasePrice();
      sql =
          "insert into automobile (name, base_price) values('"
              + autoName
              + "',"
              + price
              + ")"; // MySQL command
      statement.executeUpdate(sql); // add to automobile table

      sql = "select id from automobile where name ='" + autoName + "';";
      ResultSet rs = statement.executeQuery(sql); // use ResultSet object to receive
      while (rs.next()) {
        autoid = rs.getInt("id"); // get auto_id
      }
      for (int i = 0; i < auto.getOptionSetNum(); i++) {
        name = auto.getOptionSetName(i);
        sql = "insert into optionset (name, auto_id) values('" + name + "'," + autoid + ")";
        statement.executeUpdate(sql); // add to optionset table
        int opsid = 0;
        sql = "select id from optionset where name ='" + name + "';";
        ResultSet rrs = statement.executeQuery(sql);
        while (rrs.next()) {
          opsid = rrs.getInt("id"); // get optionset id
        }
        for (int j = 0; j < auto.getOptionNum(i); j++) {
          name = auto.getOptionName(i, j);
          price = auto.getOptionPrice(i, j);
          sql =
              "insert into options (name, price, option_id) values('"
                  + name
                  + "',"
                  + price
                  + ","
                  + opsid
                  + ")";
          statement.executeUpdate(sql); // add to options table
        }
      }

    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Example #9
0
  public void carga_default() {

    Connection con = null;
    Statement st = null;
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", pass);
      try {
        st = con.createStatement();
        st.executeUpdate(
            "INSERT INTO ADMIN VALUES ('174183767','msX','msx','estudiante','santiago',"
                + "'2130','3030303','30303030','los valdios 3030','*****@*****.**',12345);");

      } catch (SQLException s) {

        SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, "error al ingresar datos");

        // SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, s, "alert", JOptionPane.ERROR_MESSAGE);

      } finally {
        con.close();
        st.close();
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #10
0
  public static void recieveTrafficRates(String IP, float upload, float download) {
    System.out.println("Insertion of Data into Database!");
    Connection con = null;
    String url = "jdbc:mysql://localhost:3306/";
    String dbName = "traffic_report";
    String driverName = "com.mysql.jdbc.Driver";
    String userName = "******";
    String password = "******";
    String IP_address = IP;

    float up = upload, down = download;

    try {
      Class.forName(driverName).newInstance();
      con = DriverManager.getConnection(url + dbName, userName, password);
      try {
        Statement st = con.createStatement();

        int val =
            st.executeUpdate(
                "INSERT current_traffic VALUES('" + IP + "','" + up + "','" + down + "')");

        System.out.println("Data Entry process successfully!");
      } catch (SQLException s) {
        System.out.println("Table all ready exists!" + s);
      }
      con.close();
    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #11
0
 static void specificAttributeMean() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate(
         "UPDATE employ a set sal = (select AVG(sal) from employ where sal IS NOT NULL and grade = (select grade from employ b where sal IS NULL and a.grade=b.grade and ROWNUM <= 1)) where sal IS NULL");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #12
0
 static void globalConstant() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     fill = con.createStatement();
     fill.executeUpdate("UPDATE employ set sal = " + GlobalConstant + " where sal IS NULL");
     // fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + GlobalConstant + ")");
     // System.out.println("Entered..!");
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println("Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #13
0
 static void probableValue() {
   try {
     Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
     con = DriverManager.getConnection("jdbc:odbc:jbdb", "system", "tiger");
     Statement freqQuery = con.createStatement();
     ResultSet freq =
         freqQuery.executeQuery(
             "select sal,count(*) as total from employ group by sal order by total desc");
     if (freq.next()) {
       fill = con.createStatement();
       fill.executeUpdate("UPDATE employ set sal = COALESCE(sal," + freq.getInt(1) + ")");
     }
     st = con.createStatement();
     rs = st.executeQuery("select * from employ");
     System.out.println(">>Sno\tName\tSalary\tGPF\tGrade");
     while (rs.next()) {
       System.out.println(
           ">>"
               + rs.getString(1)
               + "\t"
               + rs.getString(2)
               + "\t"
               + rs.getString(3)
               + "\t"
               + rs.getString(4)
               + "\t"
               + rs.getString(5));
     }
   } catch (Exception e) {
     System.out.println("Error: " + e);
   }
 }
Example #14
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());
      }
    }
  }
Example #15
0
  public static void main(String args[]) {
    int myport = 9090;
    Socket s;
    ServerSocket ss;
    BufferedReader fromSoc, fromKbd;
    PrintStream toSoc;
    String line, msg;
    try {
      s = new Socket("169.254.63.10", 6016);
      System.out.println("enter line");
      System.out.println("connected");
      toSoc = new PrintStream(s.getOutputStream());

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

      Connection con = DriverManager.getConnection(url, "", "");
      Statement st = con.createStatement();
      String ip = "169.254.63.10";
      int port = 6016;
      String video = "rooney";

      st.executeUpdate(
          " insert into p3 values('" + ip + "' , '" + port + "' , '" + video + "' );  ");

      fun();

    } catch (Exception e) {

      System.out.println("exception" + e);
    }
  }
  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();
  }
Example #17
0
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws ServletException, IOException {

    PrintWriter writer = response.getWriter();
    String noteName = request.getParameter("noteName");
    String note = request.getParameter("note");

    try {
      Class.forName("com.mysql.jdbc.Driver");
      Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/evernoteDB");

      Statement st = con.createStatement();

      st.executeUpdate(
          "INSERT INTO note (noteName, note) VALUES ('" + noteName + "','" + note + "')");

      response.sendRedirect("/userNotes.jsp");
    } catch (ClassNotFoundException e) {
      writer.println("Couldn't load database driver: " + e.getMessage());
    } catch (SQLException e) {
      writer.println("SQLException caught: " + e.getMessage());
    } catch (Exception e) {
      writer.println(e);
    }
  }
Example #18
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 #19
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 #20
0
  public static void failJobs(Connection conn) throws SQLException {
    String method = "failJobs";
    int location = 1000;
    try {
      Statement stmt = conn.createStatement();

      String strSQL =
          "INSERT INTO os.ao_queue (queue_id, status, queue_date, start_date, end_date, "
              + "error_message, num_rows, id, refresh_type, target_schema_name, target_table_name, target_append_only, "
              + "target_compressed, target_row_orientation, source_type, source_server_name, source_instance_name, "
              + "source_port, source_database_name, source_schema_name, source_table_name, source_user_name, "
              + "source_pass, column_name, sql_text, snapshot) "
              + "SELECT queue_id, 'failed' as status, queue_date, start_date, now() as end_date, "
              + "'Outsourcer stop requested' as error_message, num_rows, id, refresh_type, target_schema_name, "
              + "target_table_name, target_append_only, target_compressed, target_row_orientation, source_type, "
              + "source_server_name, source_instance_name, source_port, source_database_name, source_schema_name, "
              + "source_table_name, source_user_name, source_pass, column_name, sql_text, snapshot "
              + "FROM os.queue WHERE status = 'queued'";

      stmt.executeUpdate(strSQL);

    } catch (SQLException ex) {
      throw new SQLException(
          "(" + myclass + ":" + method + ":" + location + ":" + ex.getMessage() + ")");
    }
  }
  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.");
    }
  }
Example #22
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() + ")");
    }
  }
  /**
   * 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");
    }
  }
Example #24
0
  // Query to store an image
  protected void storeQuery(int image_id, int line, Statement st, String data) throws Exception {
    String query = "insert into image_store values(" + image_id + ", " + line + ", '" + data + "')";

    // Print out the query for debug.
    // System.err.println(query);

    st.executeUpdate(query);
  }
Example #25
0
 public void setNumero(int ID, int numero) {
   try {
     String s = "UPDATE Edicion SET Numero = " + numero + " WHERE IDEdicion = " + ID;
     stmt.executeUpdate(s);
   } catch (SQLException e) {
     System.out.println("Cannot execute disposicion()" + e);
   }
 }
Example #26
0
  /*
   * delete methods. select fisrt and then delete.
   */
  public void deleteAuto(String modelName) {

    try {
      String sql = "delete from automobile where name= '" + modelName + "';";
      statement.executeUpdate(sql); // delete direcly as it can be accessed in automobile
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
Example #27
0
  public void CrearTrashTablas() {
    Connection con = null;
    Statement st = null;
    String USER =
        "******"
            + "apellido VARCHAR(45),actividad varchar(20),comuna varchar(30),carrera varchar(10) ,"
            + "telefono varchar(15),celular varchar(20),direccion VARCHAR(45),email varchar(50));";

    String ADMIN =
        "CREATE TABLE Trash_ADMIN (run_admin VARCHAR(20), nombre VARCHAR(45),"
            + "apellido VARCHAR(45),actividad varchar(20),comuna varchar(30),carrera varchar(10) ,"
            + "telefono varchar(15),celular varchar(20),direccion VARCHAR(45),email varchar(50),contraseƱa varchar(50));";

    String ARTICULO =
        "CREATE TABLE Trash_ARTICULO (codigo VARCHAR(40),codigoutem VARCHAR(40),categoria VARCHAR(45),"
            + "producto VARCHAR(50), descripcion varchar(50),estado VARCHAR(12));";

    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();
      con = DriverManager.getConnection("jdbc:mysql://localhost/rsu_inventario", "root", pass);
      try {
        st = con.createStatement();
        st.executeUpdate(USER);

        st.executeUpdate(ADMIN);

        st.executeUpdate(ARTICULO);
      } catch (SQLException s) {

        SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, "error al crear tablas");

        // SQL_FORM error = new SQL_FORM();
        JOptionPane.showMessageDialog(error, s, "alert", JOptionPane.ERROR_MESSAGE);

      } finally {
        con.close();
        st.close();
      }

    } catch (Exception e) {
      e.printStackTrace();
    }
  }
Example #28
0
  /*
   * update method. select the objects and then update.
   */
  public void updateAutoName(String oldName, String newName) {

    try {
      String sql = "update automobile set name = '" + newName + "'where name= '" + oldName + "';";
      statement.executeUpdate(
          sql); // update directly as it can be accessed directly from automobile table
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
 private static void doUpdate(String cmd) {
   try {
     Statement stmt = conn.createStatement();
     int howmany = stmt.executeUpdate(cmd);
     System.out.println(howmany + " records processed");
   } catch (SQLException e) {
     System.out.println("SQL Exception: " + e.getMessage());
     e.printStackTrace();
   }
 }
Example #30
0
  public static void clearTable() {
    connectDb();
    String sql = "delete from " + tablename + ";";
    try {
      query.executeUpdate(sql);

    } catch (SQLException e) {
      System.out.println("SQL Exception: " + e);
      System.exit(1);
    }
    System.out.println("Table Cleared Successfully");
  }