// 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();
}
}
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");
}
}
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();
}
}
/*
* 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();
}
}
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();
}
}
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();
}
}
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);
}
}
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);
}
}
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);
}
}
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 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();
}
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);
}
}
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();
}
}
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();
}
}
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.");
}
}
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");
}
}
// 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);
}
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);
}
}
/*
* 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();
}
}
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();
}
}
/*
* 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();
}
}
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");
}