Problem facing while inserting data into dynamically created column of table.

Question

Hi all, I am dynamically adding column to table in db through code using alter table query. But i am facing problem wen i am trying to insert values in that column. it throws an exception column does not exists. And wen i clean and rebuild my project through netbeans it works fine.

I am using java and mysql as databse .

Is there any body who know the solution for this problem.

Following is my alter table query Code

     String alterTableQuery ="alter table `test` add `abc` varchar(50) NOT NULL default ''";
     stmt = conn.prepareStatement(alterTableQuery);
     boolean val = stmt.execute();

And I am trying to insert data using following code.

      String sqlQuery = "insert into `test` (`id`,`abc`) values (?)" ;
      stmt = conn.prepareStatement(sqlQuery);
      boolean val = stmt.execute();

Answer 1

I dont know about Java but in .net after performing a change on a table you need to call dataAdapter.AcceptChanges(); which essentially commits the change to the table.

In your codedo you need to make a similar call after you have added the column to the table,for the insert to be able to work.

Answer 2

This may be because Data Description Language (DDL) is often executed outside of transactions. Perhaps a commit/rollback, or even reconnect would sort the problem. Just a guess.

Answer 3

You might also rethink your design. In general it is a poor practice for the user interface to add columns to tables. Perhaps you need a more normalized design. Database structural changes should not come from the user. You could create a real mess if different users were making changes at the same time. Additionally users should not have the security rights to add columns. This is a major risk for your system.