Problem facing while inserting data into dynamically created column of table

Question

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();

Also post a "DESCRIBE <table>" of the table and point out the offending column, if possible. — Stefan Sveidqvist
– Stefan Sveidqvist, Commented Mar 6, 2009 at 13:56

HLGEM · Accepted Answer · 2009-03-06 21:17:46Z

1

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.

answered Mar 6, 2009 at 21:17

HLGEM

97k15 gold badges120 silver badges191 bronze badges

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

Comments

user47008 · Accepted Answer · 2009-03-06 14:01:11Z

0

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.

answered Mar 6, 2009 at 14:01

user47008

733 bronze badges

Comments

Tom Hawtin - tackline · Accepted Answer · 2009-03-06 14:16:14Z

0

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.

answered Mar 6, 2009 at 14:16

Tom Hawtin - tackline

148k30 gold badges222 silver badges314 bronze badges

Collectives™ on Stack Overflow

Problem facing while inserting data into dynamically created column of table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related