CODEWITHSUNDEEP
javamysqlsyntaxjdbcprepared-statement
Mike
Mike

Reputation: 2434

Java PreparedStatement complaining about SQL syntax on execute()

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?

Upvotes: 4

Views: 3684

Answers (6)

Justin Guertin
Justin Guertin

Reputation: 503

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.

Upvotes: 1

Landei
Landei

Reputation: 54574

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.

Upvotes: 1

musiKk
musiKk

Reputation: 15189

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.

Upvotes: 1

gmhk
gmhk

Reputation: 15940

Try using the following

pStmt.executeUpdate();

pStmt.close();

Upvotes: -1

dogbane
dogbane

Reputation: 274522

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.

Upvotes: 11

Talha Ahmed Khan
Talha Ahmed Khan

Reputation: 15433

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

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

Upvotes: -1

Related Questions