Problem with CREATE TABLE statement
Here is the code which tries to create a table using prepare statement
the code compiles and i get the error:
Error on Creating Table :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 ''dept_no' varchar(25) NOT NULL,'dept_name' varchar(25) NULL,PRIMARY KEY('dept_no' at line 1"
Am enclosing my code here. Plz let me know where does i fail?
public int create(String table_name,String column1,String column2){
System.out.println("Starting!!!create");
int n=0;
try{
connect();
stmt=con.prepareStatement("CREATE TABLE "+table_name+" ( ? varchar(25) NOT NULL,? varchar(25) NULL,PRIMARY KEY(?))");
stmt.setString(1,column1);
stmt.setString(2,column2);
stmt.setString(3,column1);
n=stmt.executeUpdate();
}catch(Exception e){
System.out.println("Error on Creating Table :"+e);
}
return n;
}
Message was edited by:
Abhi_The_Software_Nerd
# 1
try this:
public int create(String table_name,String column1,String column2){
System.out.println("Starting!!!create");
int n=0;
try{
connect();
stmt=con.prepareStatement("CREATE TABLE "+table_name+" ( "+column1+" varchar(25) NOT NULL,"+column2+" varchar(25) NULL,PRIMARY KEY("+column1+"))");
n=stmt.executeUpdate();
}catch(Exception e){
System.out.println("Error on Creating Table :"+e);
}
return n;
}
hth
# 2
Don't put quotes around your table and column names.
Try the query in the MySQL client tool. If it doesn't work there, it won't work in Java.
It's not common to create tables in Java. Usually an application that needs a database will have the schema created and ready to go when it starts. Perhaps this isn't the right thing for you to be doing in Java.
%
# 4
Does that statement (with the single quotes around the field names) work in MySQL, directly? I doubt it, seeing as how the single quote (') is the first character contained in the error message, which is then, the first "problem" character encountered while parsing the statement.
Also, DBs, in general, have a problem with sing quotes (') around a field name. Double quotes (") are okay (and even required when the field name is also a reserved word), b ut single quotes (') are usually a problem.
Since setString will always place single quotes(') around the argument provided, you cannot make an SQL create statement this way.
# 5
read this:http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
and http://java.sun.com/j2se/1.4.2/docs/api/java/sql/PreparedStatement.html
PreparedStatement must be used to update/retrieve data in/from the db table rows.
The PreparedStatement setter methods (setShort, setString, and so on) for setting IN parameter values must specify types that are compatible with the defined SQL type of the input parameter. For instance, if the IN parameter has SQL type INTEGER, then the method setInt should be used.
That's means that the IN parameters must be column values
