db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From dag.wan...@oracle.com (Dag H. Wanvik)
Subject Re: Calling updateRow() after insertRow() leads to "java.sql.SQLException: Invalid cursor state"
Date Fri, 26 Nov 2010 00:03:14 GMT

Hi,

I think there are some issues with this code:

Clemens Eisserer <linuxhippy@gmail.com> writes:


> Table definition:
>> CREATE TABLE testtable (ikey INTEGER PRIMARY KEY  NOT NULL GENERATED BY DEFAULT AS
IDENTITY, name VARCHAR(127))
>
> JdbcRowSetImpl    rowset = new JdbcRowSetImpl(ssConnection.getConnection());
> rowset.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
> rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);
> rowset.setCommand("SELECT * FROM testtable");
> rowset.execute();
>
> rowset.next();

You need to do rowset.moveToInsertRow() first, I think. Without it you
would see java.sql.SQLException: Invalid cursor state - no current row.

> rowset.updateString("name", "1");
> rowset.updateString("short", "2");

There is no column called "short" in the table, should be "ikey".

> rowset.insertRow();
>
> rowset.last();
> rowset.updateRow();

Also, since you are using a scroll insensitive result set to support the
rowset, you'd need to do another query to see the inserted row (in
general, although you might get lucky, depending on whether the
underlying result set has already been inserted into the Derby cache
used to implement the insensitive semantics). 

The following test program ran without error for me.

Thanks,
Dag

---------------------------------------------------
import java.sql.*;
import com.sun.rowset.JdbcRowSetImpl;

public class Foo {
    public static void main(String[] args) throws Exception {

        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        Connection c = DriverManager.getConnection("jdbc:derby:foo_test;create=true");
        Statement s = c.createStatement();

        try {
            s.executeUpdate("DROP TABLE testtable");
        } catch (SQLException e) {
        }

        s.executeUpdate(
            "CREATE TABLE testtable " +
            "(ikey INTEGER PRIMARY KEY  NOT NULL GENERATED BY DEFAULT AS IDENTITY," +
            " name VARCHAR(127))");
            
        JdbcRowSetImpl    rowset = new JdbcRowSetImpl(c);
        rowset.setType(ResultSet.TYPE_SCROLL_INSENSITIVE);
        rowset.setConcurrency(ResultSet.CONCUR_UPDATABLE);
        rowset.setCommand("SELECT * FROM testtable");
        rowset.execute();
        
        rowset.last();    // not needed, but exposes need for another
                          // execute below.

        rowset.moveToInsertRow();
        rowset.updateString("name", "1");
        rowset.updateString("ikey", "2");
        rowset.insertRow();

        rowset.execute(); // Since we have insensitive result set we
                          // need to refresh to see the inserted row
        rowset.last();
        rowset.updateRow();
    }
}

Mime
View raw message