CODEWITHSUNDEEP
javasqljdbcnetbeansdb2
newbie
newbie

Reputation: 23

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.

Upvotes: 1

Views: 200

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

Out of interest, another solution would be to make the identity column IMPLICITLY HIDDEN

Upvotes: 1

GMB
GMB

Reputation: 222432

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.

Upvotes: 1

Related Questions