0

Can you help me with my problem of inserting rows to a table whose columns/ fields change dynamically. I have a table named schedule, in which depending the fields increase or decrease dynamically. My requirement is to insert data into this table. The problem is that, I am unsure of how many fields are there, because, it increases or decreases dynamically.

My idea: I have used ResultSetMetaData to dynamically know the field names. With this, I can use the insert query which wil be like:

ResultSet res = stat.executeUpdate("insert into scheduletab (name,date,shift,'"+col_name[i]+"') values (....., ...., .....)");

The problem here is that how will I initialize or specify the value of "i" ?

My code is :

Note: b34 is the button to submit.

if (e.getSource() == b34)
{
    String[] col_name = new String[25];
    try
    {
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection(
           "jdbc:mysql://localhost/nursedb", "root", "123456");
        try
        {
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT * FROM scheduletab");
            ResultSetMetaData md = rs.getMetaData();
            int col = md.getColumnCount();
            System.out.println("Number of Column : " + col);
            System.out.println("Columns Name: ");
            for (int i = 1; i <= col; i++)
            {
                col_name[i] = md.getColumnName(i);
                System.out.println(col_name[i]);
            }
        }
        catch (SQLException s)
        {
            System.out.println("SQL statement is not executed!");
        }

        // Start Inserting data into Schedule Tab by specifying the
        // column names.
        Statement stat = con.createStatement();
        // ResultSet res =
        // stat.executeUpdate("insert into scheduletab (name,date,shift,'"values();
        // End Inserting data into Schedule Tab by specifying the column
        // names.

    }
    catch (Exception ex)
    {
        ex.printStackTrace();
    }
}
7
  • You can make use of StringBuilder and build the query according to your need. I will also suggest that make your connection in some other class than putting everything into some button listener. Commented Jan 26, 2013 at 2:00
  • Can you explain this a bit more? Commented Jan 26, 2013 at 2:43
  • Sorry for late reply. I hope my answer will help resolve your issue. Let me know if you have any problems while doing this. Commented Jan 29, 2013 at 20:23
  • Are you saying that the database schema is modified while the application is running? Commented Jan 29, 2013 at 22:47
  • @Dave Thats what his sql query is suggesting Commented Jan 29, 2013 at 23:17

1 Answer 1

1

If your database schema is changing dynamically, you are almost certainly going down a bad path.

Check out this older question for some cautionary advice.

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.