Geek Answers Handbook

Mysql Drop Table as PreparedStatement not working for me

This prepared statement seems correct to me for SQL.

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

But when I run this, I get an error:

An exception in the stream "main" com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: you have an error in the SQL syntax; check the manual corresponding to the MySQL server version for the correct syntax for use next to the `` functions '' in line 1 on sun.reflect.NativeConstructorAccessorImpl.newInstance0 (native method) in sun.reflect.NativeConstructorAccessorImpl.newInstance (NativeConstructorAccessorImpl.java:57) in sun.reflect.DelegatingConstructorAccessorImpl.newInstance (DelegatingConstructorAccessorImpl.java:45) in java.lang.reflect.Constructor.newInstance (Constructor.java∗32) at com.mysql.jdbc.Util.handleNewInstava40 (Util) .mysql.jdbc.Util.getInstance (Util.javahaps81) in com.mysql.jdbc.SQLError.createSQLException (SQLError.java:1031) in com.mysql.jdbc.SQLError.createSQLException (SQ95rror.j com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.javahaps558) at com.mysql.jdbc.MysqlIO.checkErrorPacket (MysqlIO.java:3490) in com.mysql.jdbc. MysqlIO.sendCommand (MysqlIO.java:1959) in com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2109) in com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2648) in com.mqq .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 a problem here? I'm at a dead end.

+8
java mysql jdbc prepared-statement
source share
4 answers

MySQL does not support prepared statements with table variable names, so you must do this in the old-fashioned way by creating SQL:

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

In this case, you can also use regular operators, since you are not getting anything using prepared statements.

+7
source share

I think your code prepares this statement:

 DROP TABLE IF EXISTS 'features'

while you want:

 DROP TABLE IF EXISTS features
+1
source share

PreparedStatements are used to create a database for compiling a query (executing an execution plan) once, therefore, executing the same query with different parameters is faster.

In short, in PreparedStatement you cannot have a template for database objects. Including table name, column name, other where clauses and ....

+1
source share

You cannot use a prepared statement to delete tables with dynamic table names. However, if you do not know the names of the tables before runtime, and they may have spaces in them, then you will need to wrap them in reverse ticks:

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

More articles:


All Articles