verification on delete statement
Hi
i need a verification on delete statement and am trying to execute this delete using prepareStatement and through parameter passing
the code is given below.plz do let me know the problem with the statement
=============================================================
public int delete(String tableName,String empName){
int n=0;
System.out.println("Starting!!!delete");
try{
/*stmnt=con.createStatement();
stmnt.executeUpdate("delete from Employees where Name= 'jaggu'");*/
stmt=con.prepareStatement("delete from ? where Name = ? ");
stmt.setString(1, tableName);
stmt.setString(2, empName);
n=stmt.executeUpdate();
System.out.println("Deletion Done!!!");
}catch(Exception e){
System.out.println("Error on deletion :"+e);
}
return n;
}
===========================================================
the error being displayed is:
Error on deletion :java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' where Name = ''' at line 1"
PLZ DO LET ME KNOW WHAT IS WRONG HERE!!!
# 1
I believe your problem has to do with your first question mark in your sql.
I believe preparedStatement requires you to hard code in the table name and not pass it in as a question mark. This is because (I believe) the preparedStatement pre-compiles the statement for optomization and cant do so without the tableName. Solution is to use a statement or preparedStatement where the sql is built up before being given to the statement or preparedStatement.
Example:
String sqlStatement="delete from Person where Name='Joe' ";
stmt=con.prepareStatement(sqlStatement);
or
String sqlStatement="delete from Person where Name='? ";
stmt=con.prepareStatement(sqlStatement);
stmt.setString("Joe");
Also, you should get a connection in a try block, close the connection in a finally block of a try/catch/finally block.
# 2
yes tat was the problem and now i got it rectified.. for more dynamisation u can use the following code. If so u can delete any row based on the column value from any table...plz tell whether its looking fine or not:
public int delete(String tableName,String empName){
int n=0;
System.out.println("Starting!!!delete");
try{
stmt=con.prepareStatement("delete from "+tableName+" where name = ?");
stmt.setString(1, empName);
n=stmt.executeUpdate();
System.out.println("Deletion Done!!!");
}catch(Exception e){
System.out.println("Error on deletion :"+e);
}
return n;
}
