Java PreparedStatement and Alter Table syntax error

Question

I have to create an index on a field of a table using PreparedStatement. The query that I've to perform is the following:

ALTER TABLE esa_matrix ADD INDEX doc_index (id_doc)

So, I've create a PreparedStatement instance with the same text of the query and perform executeUpdate() method on it. But at execution time I get a SQL syntax error. This is creation of the PreparedStatement instance:

PreparedStatement ps = conn.prepareStatement("ALTER TABLE "+ESATable+ "ADD INDEX doc_index ("+idDocLabel+")");                                  
ps.executeUpdate();
ps.close();

This SQLException I get:

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 'doc_index (id_doc)' at line 1

How can I solve this?
Thanks in advance,
Antonio

i encourage you to use String.format() for this kind of "stringconstruction" not just for DB-querys. String query = String.format("ALTER TABLE %s ADD INDEX doc_index (%s)", ESATable, idDocLabel); — dustin.b
– dustin.b, Commented Oct 24, 2012 at 12:21

user7094 · Accepted Answer · 2011-03-24 09:13:33Z

3

You've forgotten a space before the "ADD".

answered Mar 24, 2011 at 9:13

user7094

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

3 Comments

user7094 Over a year ago

Easy mistake to make, if I had a pound for every time I'd done that I'd be a rich man :)

DaveH Over a year ago

As an aside, as it's a prepared statement, couldn't you use ps = conn.prepareStatement("ALTER TABLE ? ADD INDEX doc_index ?"); and then use ps.setString(1,"ESATable") etc. Not sure if it's permissible, but if it is, it certainly makes the statement a lot easier to read and debug.

user7094 Over a year ago

@user53300: Parameters won't work when you're specifying table names, sadly.

Collectives™ on Stack Overflow

Java PreparedStatement and Alter Table syntax error

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related