CODEWITHSUNDEEP
javajavadbcachedrowset
SamWang
SamWang

Reputation: 11

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

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.");

Upvotes: 1

Views: 1317

Answers (2)

raSTy
raSTy

Reputation: 11

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

where 1 is your PK. Worcked for me in MySQL

Upvotes: 1

skirsch
skirsch

Reputation: 1737

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.

Upvotes: 1

Related Questions