Getting Exception With DB2 Auto Increment

I have created the following table:

"CREATE TABLE ParsonCollection "
                + "(id integer not null GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                + "name varchar(20),"
                + "eye varchar(20),"
                + "hair varchar(20),"
                + "height varchar(20),"
                + "weight varchar(20),"
                + "PRIMARY KEY (id))";

I am then trying to insert into the table and this is where I am running into issues. When I try to alter the “id” column, I get an error saying “java.sql.SQLSyntaxErrorException: Attempt to modify an identity column ‘ID’. ” Here is what the insert statement looks like:

"insert into ParsonCollection values(" + q_surround(Name) + ","
            + q_surround(Eye) + "," + q_surround(Hair) + "," + q_surround(Height) + "," + q_surround(Weight) + ",1" + ")";

However, when I take away the field that is inserting into “id”, I get the following error: “java.sql.SQLSyntaxErrorException: The number of values assigned is not the same as the number of specified or implied columns.” Here is what this insert statement looks like:

"insert into ParsonCollection values(" + q_surround(Name) + ","
            + q_surround(Eye) + "," + q_surround(Hair) + "," + q_surround(Height) + "," + q_surround(Weight) + ")";

How do I get past this? It seems that when I solve one exception, the other one pops up and vice versa. Thanks.

Answer

You can’t assign to an identity column. Since you cannot pass all values for insert, you need to enumerate the columns (omitting the identity column):

"insert into ParsonCollection (
    name,
    eye,
    hair,
    height
    weight
) values(" 
    + q_surround(Name) 
    + "," + q_surround(Eye) 
    + "," + q_surround(Hair) 
    + "," + q_surround(Height)
    + "," + q_surround(Weight)
+ ")";

Side note: your code is opened to SQL injection. You should seriously consider using prepared statements and bind parameters instead of concatenating the query string.