iterate and delete through a HEAP table - updatable result sets

[Alan Ezust]

I used to have an ordinary table that looked like this:
    static String createTable1 =
        "CREATE TABLE " + tableName +       
        " (state int, tlid int, " +
//        "  CONSTRAINT " + tableName + "_PK PRIMARY KEY(tlid, state), " +
        "  cost int, configid int," +
        " xid INT PRIMARY KEY AUTO_INCREMENT," +
        " INDEX CIDIND(configid)) " ;

The "xid" field is there only so I can get an updatable result set

So I have this HEAP table that looks like this:

    static String createTable =
        "CREATE TABLE " + tableName +
        " (state int, tlid int, "  +
        "  cost INT,  " +
        "  configid int, " +
        "  INDEX COSTIND(cost) " + 
        "  ) TYPE = HEAP; ";

I had to get rid of the "xid" PRIMARY KEY because
HEAP tables do not allow AUTO_INCREMENT, and I couldn't think of a way
of assigning unique IDs to each of these rows.

But now I am unable to do a query I wanted to do:
      final String queryStr =
                "select state, tlid, cost from "+tableName + ";";

                PreparedStatement pstmt = c.prepareStatement(
                        queryStr,  ResultSet.TYPE_SCROLL_SENSITIVE,
                        ResultSet.CONCUR_UPDATABLE);
                ResultSet nextResult = pstmt.executeQuery();
                while (nextResult.first())  {
                    int sid = nextResult.getInt("state");
                    int tlid = nextResult.getInt("tlid");
                    int cost = nextResult.getInt("cost");
                    nextResult.deleteRow();
                    SearchState ss = new SearchState(sid, tlid, cost, 0, 0);
                    priorityQueue.add(ss);
                    nextResult = nextResult = pstmt.executeQuery();
                }
                nextResult.close();
                pstmt.close();
    
I try to execute the statement and I get this:


16-May-2005 9:35:52 AM nfa.DistQuery infiniteLoop
 SEVERE: result set not updatable.this result set must come from a
 statement that was created with a result set type of
 resultset.concur_updatable, the query must select only one table, and
 must select all primary keys from that

 table. see the jdbc 2.1 api specification, section 5.6 for more details.
 Exception in thread "main" java.lang.RuntimeException: get() failed
 at db.ProcessingQueue.get(ProcessingQueue.java:124)
 at nfa.DistQuery.infiniteLoop(DistQuery.java:73)
 at nfa.DistQuery.main(DistQuery.java:186)
Caused by: com.mysql.jdbc.NotUpdatable: Result Set not updatable.This
result

So I need to have a primary key and query on it, in order to get an
updatable result set. I could create a unique string which is the
integers converted into strings and concatenated. It would be nice if
I could make a "primary - secondary " key because the state/tlid combo
uniquely identifies each row.

But I'm getting side-tracked. What's the most straightforward way of
iterating through each row of a HEAP table, grabbing the values and
removing the rows?




-- 
University of Victoria
Department of Computer Science
Victoria, BC, Canada