How to insert a row into a that table has an increment id with CachedRowSet?

Question

I'm trying to insert a row into a table that has an increment id with CachedRowSet(I'm using Java wit Java DB), but I got the following SQLException:
java.sql.SQLException: Failed on insert row
at...
SQLState: null
Error Code: 0
Message: Failed on insert row
Here's my code snippet:

private static String urlString = "jdbc:derby:testdb;create=true";
private static String userName = "testUser";
private static String password = "testPassword";
...

CachedRowSet crs = new CachedRowSetImpl();
            crs.setUrl(urlString);
            crs.setUsername(userName);
            crs.setPassword(password);
            crs.setCommand("SELECT * FROM test_table");
            crs.execute();

            crs.moveToInsertRow();
            crs.updateString("str_value", "testValue");
            crs.insertRow();
            crs.moveToCurrentRow();
            crs.acceptChanges();

The SQLException is thrown from crs.inertRow().
The CachedRowSet works well if the table does not have an auto increment id.
How can I successfully do this?

other details:
I use the following code to create my table:

conn = DriverManager.getConnection(urlString, userName, password);
            Statement stmt = conn.createStatement();
            stmt.execute("CREATE TABLE test_table("
                    + "id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),"
                    + "str_value VARCHAR(20) NOT NULL,"
                    + "CONSTRAINT primary_key PRIMARY KEY (id))");
            System.out.println("Talbe test_table created.");

score 1 · Answer 1 · edited May 23 '17 at 12:15

1

This is a duplicate of Failed on insert row using CachedRowSet.

Unfortunately, that happens because of the API definition:

Throws: SQLException - if a database access error occurs; the result set concurrency is CONCUR_READ_ONLY, this method is called on a closed result set, if this method is called when the cursor is not on the insert row, or if not all of non-nullable columns in the insert row have been given a non-null value

You may be better off using JdbcRowSetImpl.
You can only stick with the CachedRowSet if you remove the NOT NULL constraint of the id column. Unfortunately, this would also mean to remove the PK constraint. I'd assume that is too big a sacrifice, but it's either that or no CachedRowSet.

edited May 23 '17 at 12:15

Community

1
1

answered Apr 04 '13 at 11:25

skirsch

1,640
12
24

I check that question already before post,seems it really doesn't have a 'perfect' solution. – SamWang Apr 05 '13 at 05:10
Well, sometimes you can't have 'perfect'. ;) I had another idea, but I guess it's even less perfect. :( – skirsch Apr 05 '13 at 06:01

score 1 · Answer 2 · answered Sep 01 '13 at 15:34

1

try this crs.updateNull(1); crs.insertRow();

where 1 is your PK. Worcked for me in MySQL

answered Sep 01 '13 at 15:34

raSTy

11
1

How to insert a row into a that table has an increment id with CachedRowSet?

2 Answers2