Tags
People
Badges
Help-
CORE FRAMEWORK
FEATURED COMPONENTS
-
RESOURCES
-
LEARN ZK
RESOURCES
I am trying to insert some stuff into a database. I can query perfectly fine. I format the insert statement well.
i can print it out to the screen, copy and paste, and have it enter the data correctly into the database.
For some reason, when i use the update function, i get this error"The server is temporarily out of service.
Would you like to try again?".
And it also shuts down my Apache Tomcat service.
In the orderInsert function, if i comment out the update function call then I have no problems.
Any idea what might be causing this?
public void update(String Qstatement) throws ClassNotFoundException, SQLException { //also for insert
//load driver and get a database connection
//Note: It is usually better to use connection pool. Please consult
//the manual of your Web server. Or, refer to the Developer's Guide
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://"+DB_host+":"+DB_port+"/"+DB_database;
Connection con = DriverManager.getConnection(url,"user", "");
PreparedStatement stmt = con.prepareStatement(Qstatement);
//execute the statement
try {
int rs = stmt.executeUpdate();
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
}
//close the jdbc connection
con.close();
Qstatement="";
}
public String orderInsert(String[] insertOrderArray) throws ClassNotFoundException, SQLException{
Qstatement = "INSERT INTO weborders (SessionID, Confirmed, ClientID, BioRxID, OrderDate, OrderTime, PhysicianID, OrderedByID, ProductID, ProcedureID, PatientName, Weight, DeliveryDate, DeliveryTime, CalibrationDate, CalibrationTime, Amount, Units, Quantity, PONumber, Note, LastModified, Deleted) VALUES (";
for (Integer num = 0; num < insertOrderArray.length; num++){
Qstatement += insertOrderArray;
if (insertOrderArray.length != 1 + num){
Qstatement += ", ";
}
}
Qstatement += ")";
String sQstatement = Qstatement;
update();
return sQstatement;
}There are various issues in this code :
1) In orderInsert instead of String concatenation use a StringBuilder or a StringBuffer. For example:
public String orderInsert(String[] insertOrderArray) throws ClassNotFoundException, SQLException {
StringBuilder sb = new StringBuilder(1024);
sb.append("INSERT INTO weborders (SessionID, Confirmed, ClientID, BioRxID, OrderDate, OrderTime, PhysicianID, OrderedByID, ProductID, ProcedureID, PatientName, Weight, DeliveryDate, DeliveryTime, CalibrationDate, CalibrationTime, Amount, Units, Quantity, PONumber, Note, LastModified, Deleted) VALUES (");
for (String s : insertOrderArray) {
sb.append(s).append(", ");
}
sb.setLength(sb.length()-1);
sb.append(")");
update(sb.toString());
2) To avoid sql injection use PreparedStament instead a Statement or better a Stored procedure
Okay... so I still can't figure this out. I tried your code Yamil, using StringBuilder, and nothing has changed.
User side, everything is exactly the same, the sql statement ends up the same, and when the update function is called the tomcat service still crashes.
Also, I am using PreparedStament, not Statement
(quote)
PreparedStatement stmt = con.prepareStatement(Qstatement);
I you test the SQL code in the sb variable, it works ?
I am printing out the variable (getting the whole sql statement that it's using ie. "INSERT INTO weborders (SessionID, Confirmed...").
If i copy and paste that into the sql command line, it inserts the data correctly. And i can query it correctly from the sql command line, or query and display it correctly from the website.
So yes, the variable is containing the correct string to use.
Although i might add that when it doesn't include the semi-colon. So i have to put that at the end of the statement when i put it into the sql command line. But that's it.
Ok. Let's see which exception is thrown...
Please add a add a catch lock to the update() method.
try {
int rs = stmt.executeUpdate();
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
} catch (ClassNotFoundException cnfe) {
System.out.println("Class not found exception");
} catch (Exception ex) {
ex.printStackTrace();
}
Test and tell me what happened ...
This was the first one
Unresolved compilation problem: Unreachable catch block for ClassNotFoundException. This exception is never thrown from the try statement body
Please place the ClassNotFoundException as first catch exception and removes throws ClassNotFoundException.
It looks like this is the thrown exception...
My code
public void update(String editStatement) throws SQLException { //also for insert
//load driver and get a database connection
//Note: It is usually better to use connection pool. Please consult
//the manual of your Web server. Or, refer to the Developer's Guide
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://"+DB_host+":"+DB_port+"/"+DB_database;
Connection con = DriverManager.getConnection(url,"user", "");
PreparedStatement stmt = con.prepareStatement(editStatement);
//execute the statement
try {
int rs = stmt.executeUpdate();
} catch (ClassNotFoundException cnfe) {
System.out.println("Class not found exception");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
} catch (Exception ex) {
ex.printStackTrace();
}
//close the jdbc connection
con.close();
}
error
Unresolved compilation problems: Unhandled exception type ClassNotFoundException Unreachable catch block for ClassNotFoundException. This exception is never thrown from the try statement body
Is there something wrong with the way i'm doing the try/catch?
Change to
public void update(String editStatement) throws SQLException { //also for insert
Connection conn = null;
PreparedStatement stmt = null;
try {
//load driver and get a database connection
//Note: It is usually better to use connection pool. Please consult
//the manual of your Web server. Or, refer to the Developer's Guide
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://"+DB_host+":"+DB_port+"/"+DB_database;
con = DriverManager.getConnection(url,"user", "");
stmt = con.prepareStatement(editStatement);
//execute the statement
int rs = stmt.executeUpdate();
} catch (ClassNotFoundException cnfe) {
System.out.println("Class not found exception");
} catch (SQLException se) {
System.out.println("We got an exception while executing our query:" +
"that probably means our SQL is invalid");
se.printStackTrace();
System.exit(1);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
if (stmt != null) {
try {
stmt.close()
} catch (Exception ex) {}
}
if (conn != null) {
try {
conn.close();
} catch (Exception ex) {}
}
}
}
Asked: 2009-04-17 18:40:24 +0800
Seen: 479 times
Last updated: Apr 21 '09