CODEWITHSUNDEEP
javamysqljdbcprepared-statement
Malwinder Singh
Malwinder Singh

Reputation: 7060

Creating table dynamically by SQL in Servlet

I am getting error in this: prepared statement.

Prepared Statement:

PreparedStatement pStatement=connection.prepareStatement
("CREATE TABLE `details`.?(`ID` VARCHAR(255) NOT NULL,`Score` VARCHAR(255) NULL);
INSERT INTO `testdetails`.? (`ID`) VALUES (?);");
pStatement.setString(1, "usrname");
pStatement.setString(2, "usrname");
pStatement.setString(3, "001");
pStatement.executeUpdate();

Error details:

Severe:   com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
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
''usrname'(`ID` VARCHAR(255) NOT NULL,`Score` VARCHAR(255) NULL);INSER' at line 1

How to fix this error?

Upvotes: 1

Views: 902

Answers (2)

pbespechnyi
pbespechnyi

Reputation: 2291

This error appears because, you use #setString, and the string gets wrapped by ''.

To fix this, you can use one of this snippets:

"CREATE TABLE `details`.#tableName#(`ID` VARCHAR(255) NOT NULL,`Score` VARCHAR(255) NULL); INTO `testdetails`.#tableName# (`ID`) VALUES (?);".replaceAll("#tableName#", "usrname");

or 

new StringBuilder("CREATE TABLE `details`.").append("usrname").append("(`ID` VARCHAR(255) NOT NULL,`Score` VARCHAR(255) NULL); INTO `testdetails`.").append("usrname").append("(`ID`) VALUES (?);").toString();

and pass resulted string into #prepareStatement() method.

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1500805

JDBC parameters aren't usually usable for table and column names - they're only for values.

Unfortunately, I think this is one of those cases where you will need to be build the SQL dynamically. You will want to be very careful about which values you allow, of course.

Upvotes: 1

Related Questions