Mysql Drop Table as PreparedStatement not working for me

Question

This prepared statement seems like valid SQL to me.

PreparedStatement dropTable = cnx.prepareStatement( "DROP TABLE IF EXISTS ?"); dropTable.setString(1, "features"); dropTable.execute();

But when I run this, I get the error:

Exception in thread "main" 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 ''features'' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:532) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1031) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1356) at doriangray.db.TestSetup.main(TestSetup.java:62)

Does anyone see the problem here? I'm stumped.

I'm not into Java, but all prepared statements libraries I know don't support dynamic table names. — Pekka
– Pekka, Commented Feb 12, 2012 at 20:32

Joni · Accepted Answer · 2012-02-12 20:44:43Z

MySQL doesn't support prepared statements with variable table names, so you have to do this the old fashioned way, by generating SQL:

PreparedStatement dropTable = cnx.prepareStatement( String.format("DROP TABLE IF EXISTS %s", "features")); dropTable.execute();

In this case you might as well use regular statements since you don't gain anything by using prepared statements.

ypercubeᵀᴹ · Accepted Answer · 2012-02-12 20:34:42Z

I think your code prepares this statement:

DROP TABLE IF EXISTS 'features'

while you want:

DROP TABLE IF EXISTS features

Amir Pashazadeh · Accepted Answer · 2012-02-12 20:43:51Z

PreparedStatements are used for making database compile the query (make the execution plan) once, so executing the same query with different parameters happens faster.

In short in a PreparedStatement you can not have a wildcard for database objects. Including table name, column name, different where clauses and ....

whistling_marmot · Accepted Answer · 2017-12-01 10:47:26Z

You can't use a prepared statement to drop tables with a dynamic table names. However, if you don't know the table names until run-time, and they could have spaces in them, then you'll need to enclose them in back-ticks:

for (String tableName : tableNames) { statement.executeUpdate("DROP TABLE IF EXISTS `" + tableName + "`"); }

Collectives™ on Stack Overflow

Mysql Drop Table as PreparedStatement not working for me

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related