How to add rows to a table after the columns were altered? (SQLite, JAVA)

Question

I am trying to add rows to a table. Initially, I had this code but it creates an error that there are 8 columns (additional columns were altered, initially I only had 2 columns) but I am only adding 2 values:

PreparedStatement addDate = 
con.prepareStatement("insert into contributions values(?,?)");

           addDate.setString(2, string);
           addDate.execute();
     }

And then I tried this:

  public void addDateToContributionsTable(String string) throws ClassNotFoundException, SQLException {
         if(con == null) {
             // get connection
             getConnection();
         }
          Statement st = con.createStatement();
          ResultSet rs = st.executeQuery("Select * from contributions");
          ResultSetMetaData rsmd = rs.getMetaData();
          int columnsNumber = rsmd.getColumnCount();
          StringBuilder sb = new StringBuilder();
          for(int i= 0; i < columnsNumber; i++){
              sb.append("?");
              sb.append(",");
          }

          System.out.println(sb.toString());
          System.out.println("insert into contributions values('"+sb.toString()+"')");
          PreparedStatement addDate = con
                    .prepareStatement("insert into contributions values('"+sb.toString()+"')");


          addDate.setString(2, string);
          addDate.execute();
     }

But I am still having the same error:

  [SQLITE_ERROR] SQL error or missing database (table contributions has 8 columns but 1 values were supplied)

Any help would be appreciated. :)

If you can change your INSERT query to have the column names against which you are passing the values, wouldn't raise such issues - considering the others are not required! — N00b Pr0grammer, Nov 03 '16 at 08:17

score 0 · Answer 1 · answered Nov 03 '16 at 08:18

0

The values don't know where they should be inserted, so I suggest writing the column names for each value.

reparedStatement addDate = 
con.prepareStatement("insert into contributions (COLUMN_NAMES) values (?,?)");

       addDate.setString(2, string);
       addDate.execute();
 }

answered Nov 03 '16 at 08:18

Radu Maftei

81
9

My problem is that I have a method of changing the column numbers. So if I set values as (?,?). I would have problems in the future because the number of columns could increase. – DJ Tan Nov 03 '16 at 08:45

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

You added multiple bind parameters in the SQL, but you only ever bound one of them:

addDate.setString(2, string);

You need to call setXXX() per bind parameter.

If you are really sure you want to use the same value, you can instead use named parameters instead, then you won't have to call setXXX() multiple times. You can refer to this: Named parameters in JDBC

Also, questionMarksList and StringBuilder sb are doing the same thing over two loops.

Edit

If your SQL has 2 question marks, you have 2 bind parameters, then you need to set two bind parameters.

e.g. For SQL with 3 bind parameters:

INSERT INTO MYTABLE VALUES(?, ?, ?)

You need to provide 3 values:

addDate.setString(1, "String1"); // Bind to first question mark
addDate.setString(2, "String2"); // etc.
addDate.setString(3, "String3");

edited May 23 '17 at 10:32

Community

1
1

answered Nov 03 '16 at 08:19

KC Wong

2,410
1
18
26

I edited my code and removed questionMarksList. :) I'm not sure how to use setXXX() but my main problem is that the columns could change anytime. My database has member's names as columns. So every time the user adds a member, the column number would change. Would your method solve my problem? Sorry I'm just a newbie when it comes to programming. – DJ Tan Nov 03 '16 at 08:43
See if my edit clears it up for you. Also, if the no. of columns changed, your problem becomes if your program knows what those columns mean. How can you tell what to put in them? – KC Wong Nov 03 '16 at 08:48

score 0 · Accepted Answer · answered Nov 04 '16 at 01:10

Thanks to all that responded to my question. The problem was with the number of ? I had on my statement every time I increase or decrease the column numbers. The only problem with my statement is having this expression '' . When I changed it to "+questionMarks+" instead of '"+questionMarks+"', it worked. Does not matter how many setXXX() method I use as long as the number of ? are the same with the number of columns on the table, it will work. Having the code below, I did not encounter any errors anymore.

  PreparedStatement addmembers = con
                    .prepareStatement("insert into membersdata values "+questionMarks+"");
                  addmembers.setString(2, name);
                  addmembers.setString(3, accounts);
                  addmembers.setString(4, email);
                  addmembers.execute();

How to add rows to a table after the columns were altered? (SQLite, JAVA)

3 Answers3