Creating MySQL table columns dynamically using Java

Question

I have an error while trying to alter a MySQL table by adding columns to it dynamically where the column names are values stored in a Java array variable.

 try
{  
Class.forName("com.mysql.jdbc.Driver");        
Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql","root","123");  
Statement stmt=con.createStatement();        
stmt.executeUpdate("create table distable (patient integer)");  
PreparedStatement pst=con.prepareStatement("alter table distable add ? varchar(50)");  
for(i=1;i<=col;i++)
{
pst.setString (1,out[i]);
pst.executeUpdate();
}        
con.close();        
}
catch(Exception e)
{
 System.out.println(e);
}

Here con is the variable which holds the connection. Using the connection I have created a table in MySQL and the name of the table is "distable". The table "distable" initially contains one integer column named Patient.

I need to alter the table "distable" and add new columns to it in run time. As I said the name of each column has to be the values stored in an array in Java. Hence I have used PreparedStatement, which contains a '?' which is a substitute for column names.

The number of columns to be inserted at runtime is stored in the variable "col". I have used setString() to set the column name at run time. The column names are stored in the array named "out". I have used executeUpdate() to execute the query.

When I run this code, the error i get is:

com.mysql.jdbc.exceptions.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 ''GSM22449' varchar(50)' at line 1

'GSM22449' is the first value stored in the array [i.e. out[1]=GSM22449]. Therefore it is actually fetched and substituted at the place of '?' in the query. But the query is not executed.

Where does the error lies? Is there any problem with syntax?

Possible duplicate of Java PreparedStatement complaining about SQL syntax on execute() — Arnaud
– Arnaud, Commented Feb 23, 2016 at 11:03
you can't bind a column name, only value could be a bind parameter. — mmuzahid
– mmuzahid, Commented Feb 23, 2016 at 11:07

rahimv · Accepted Answer · 2016-02-23 11:17:14Z

1

I think you do not need to specify "COLUMN" keyword while adding a column. Here is the right syntax to add the column

ALTER TABLE table_name
ADD column_name datatype

So your statement creation line should look like this,

PreparedStatement pst=con.prepareStatement("alter table distable add ? varchar(50)");

Update: Just figured out that ? parameters works only for data, and does not work for the column names. If at all you need to generate the column names this way, you may use string concatenation. For example,

"alter table distable add " + COLUMN_NAME + " varchar(50)"

Make sure that the column names are not user inputs, otherwise it will invite XSS (cross site scripting).

edited Feb 23, 2016 at 11:17

answered Feb 23, 2016 at 11:03

rahimv

5794 silver badges12 bronze badges

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

3 Comments

AJOY Over a year ago

Now i noticed it and changed it. But still the same error exists

AJOY Over a year ago

Yep changed the '?' to '+Column Name+ and now it works. BTW thanks

rahimv Over a year ago

Glad it worked. Please accept the answer if it solved your problem.

Collectives™ on Stack Overflow

Creating MySQL table columns dynamically using Java

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related