Java PreparedStatement complaining about SQL syntax on execute()

Question

This is driving me nuts... What am I doing wrong here?

ArrayList<String> toAdd = new ArrayList<String>();
toAdd.add("password");
try{
    PreparedStatement pStmt = conn.prepareStatement("ALTER TABLE testTable ADD ? varchar(100)");
        for (String s : toAdd) {
            pStmt.setString(1, s);
            pStmt.execute();
        }
} catch (SQLException e) {
    e.printStackTrace();
}

Results in...

02:59:12,885 ERROR [STDERR] 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 ''password' varchar(100)' at line 1

but...

ArrayList<String> toAdd = new ArrayList<String>();
toAdd.add("password");
try{
    Statement stmt = conn.prepareStatement();
        for (String s : toAdd) {
            stmt.execute("ALTER TABLE testTable ADD "+s+" varchar(100)");
        }
} catch (SQLException e) {
    e.printStackTrace();
}

works perfectly... So does directly entering the text directly into the MySQL command line client.

mysql> alter table testTable add stringGoesHere varchar(100);
Query OK, 1 row affected (0.23 sec)
Records: 1  Duplicates: 0  Warnings: 0

What am I doing wrong?

dogbane · Accepted Answer · 2011-06-14 07:26:45Z

11

The MySQL manual clearly says that ? (parameter markers) are for binding data values only, not for column names.

Parameter markers can be used only where data values should appear, not for SQL keywords, identifiers, and so forth.

So you will have to use your second approach.

answered Jun 14, 2011 at 7:26

dogbane

276k77 gold badges407 silver badges415 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mike Over a year ago

Ahh, thanks. I'd give you an upvote, but it won't let me yet. ;)

spencer7593 Over a year ago

This same restriction is found in Oracle, SQL Server, DB2, Teradata, and every other relational database I've worked with. The restriction has to do with how a SQL statement is processed by the optimizer. The bind values are supplied too late in the processing, when the query plan is executed. The keywords need to be available earlier, for the syntax check. And tables, columns, functions, etc. have to identified during the semantics check, before the query plan is developed/chosen. The bind values aren't supplied until the query plan is executed.

musiKk · Accepted Answer · 2011-06-14 07:26:53Z

1

Placeholders in JDBC are for data, not for table, column, view or function names. And for good reason. The DB schema of an application is static most of the time and only changes rarely. There are no benefits making them dynamic.

answered Jun 14, 2011 at 7:26

musiKk

15.3k4 gold badges60 silver badges85 bronze badges

1 Comment

spencer7593 Over a year ago

Yes, bind parameters/placeholders can only be used for values. Not for identitfiers or keywords. The limitation is primarily due to the SQL optimizer (how a SQL statement is processed on the database). The limitation is not a specific to JDBC. (The reason given in this answer is specious.)

Landei · Accepted Answer · 2011-06-14 07:26:55Z

1

Prepared statements need to define a fixed structure so they can be precompiled. That means you can have variable values, but never variable table names, column names, function names etc.

answered Jun 14, 2011 at 7:26

Landei

54.6k13 gold badges105 silver badges195 bronze badges

Comments

Justin Guertin · Accepted Answer · 2011-06-14 07:28:08Z

1

When using a prepared statement, your parameter is treated similarily to a string literal. As a result, your statement is equivalent to "ALTER TABLE testTable ADD \'"+s+"\' varchar(100)". Notice the single quotations around the field name in the error message.

I would suggest building a literal statement in this case, and not attempting to used a prepared statement.

answered Jun 14, 2011 at 7:28

Justin Guertin

5031 gold badge4 silver badges7 bronze badges

Comments

Talha Ahmed Khan · Accepted Answer · 2011-06-14 07:26:38Z

-1

You cannot submit an ALTER TABLE statement using parameters like this.

I guess it is not permissible to execute DDL statements in Java PreparedStatement.

answered Jun 14, 2011 at 7:26

Talha Ahmed Khan

15.6k10 gold badges46 silver badges49 bronze badges

Comments

gmhk · Accepted Answer · 2011-06-14 07:26:51Z

-1

Try using the following

pStmt.executeUpdate();

pStmt.close();

answered Jun 14, 2011 at 7:26

gmhk

16k29 gold badges91 silver badges113 bronze badges

Collectives™ on Stack Overflow

Java PreparedStatement complaining about SQL syntax on execute()

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related