Bug 360487

Comment 1 Joseph Vincens 2011-10-10 21:20:38 EDT

From The ResultSet JavaDoc
cancelRowUpdates ()

Cancels the updates made to the current row in this ResultSet object. This method may be called after calling an updater method(s) and before calling the method updateRow to roll back the updates made to a row. If no updates have been made or updateRow has already been called, this method has no effect. 

Throws:
    SQLException - if a database access error occurs; this method is called on a closed result set; the result set concurrency is CONCUR_READ_ONLY or if this method is called when the cursor is on the insert row 

What happens will depend on the currency, with the you have currency is CONCUR_READ_ONLY so I would expect you to get an exception.

Comment 2 Joseph Vincens 2011-10-10 21:24:14 EDT

From The ResultSet JavaDoc
refreshRow()

    Refreshes the current row with its most recent value in the database. This method cannot be called when the cursor is on the insert row.

    The refreshRow method provides a way for an application to explicitly tell the JDBC driver to refetch a row(s) from the database. An application may want to call refreshRow when caching or prefetching is being done by the JDBC driver to fetch the latest value of a row from the database. The JDBC driver may actually refresh multiple rows at once if the fetch size is greater than one.

    All values are refetched subject to the transaction isolation level and cursor sensitivity. If refreshRow is called after calling an updater method, but before calling the method updateRow, then the updates made to the row are lost. Calling the method refreshRow frequently will likely slow performance.

    Throws:
        SQLException - if a database access error occurs; this method is called on a closed result set; the result set type is TYPE_FORWARD_ONLY or if this method is called when the cursor is on the insert row 

What happens will depend on the scroll and row type, with the code you have scroll is
TYPE_FORWARD_ONLY so I would expect you to get an exception.

Comment 3 Joseph Vincens 2011-10-10 21:40:16 EDT

deleteRow()
The idea is get a resultset, set a position, then call deleteRow. Positioning could be done using SQLResultSet.setNext(), or any of the positioning functions, setAbsolute(), setRelative(), etc. 
Using your miniTblRec record and database:
 	rs1 SQLResultSet?{@SQLResultSetControl{scrollablity = SQLResultSetScrollablity.TYPE_SCROLL_INSENSITIVE, concurrency = SQLResultSetConcurrency.CONCUR_UPDATABLE}};
	set row empty ;
	try
		open rs1 from ds with #sql { 
			select id, aChar, aString, aFloat, aInt, aSmallInt, aBigInt 
				from MINITBL 
				where id > ?
				order by id asc
			}  		
			using row.id;
		if(rs1.setNext())
			log ( "Set Absolute/Set Relative Compile Errors" ) ;
			setVariation ( "set relative plus" ) ;
			rs1.setRelative(2);
			get row from rs1;
			if(row.id == 3)
				rs1.deleteRow();
			end
		end	
Until I release the code that allows you to change the scrollability, concurrency, and holdability (360260) you will get an exception because deleteRow throws an exception when the concurrency CONCUR_READ_ONLY is which is the default.

Comment 4 Joseph Vincens 2011-10-10 21:44:41 EDT

From The ResultSet JavaDoc

setFetchSize(int rows)

    Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. The default value is set by the Statement object that created the result set. The fetch size may be changed at any time. 

So fetchsize is a hint when more rows are needed, it is not set before you get a ResultSet.

Comment 5 Paul Hoffman 2011-10-11 12:16:50 EDT

Q1:

In tests so far, I started with a null result set and open instantiated a new result set and assigned it to the result set variable named on the open statement.

I'm assuming from the example you posted that open will instantiate a new result set object only if the result set is null.   Correct?

Q2:  Update

I can see that scrollability and holdability are tied to the cursor / result set and do not require any change to the SELECT statement used for opening the result set.

However, update is another story.  What is the relationship of rs.update to the "for update of " clause in the SQL SELECT statement?    Can I update a row without the "for update of " clause?   How are the column values replaced in current row of the result set?  Do I need both a replace to rs and rs.update() to update a row in a result set?

Q3:  Delete

From your example, I assume rs.delete() by itself is sufficient to delete a row in the result set from the table.  Correct?    If auto-commit is true, I'm guessing the row gets physically deleted from the table at the point where the result set is closed, not when the rs.delete() is invoked.   Correct?

Comment 6 Joseph Vincens 2011-10-11 15:15:16 EDT

SQLResultSet.updateRow()
before calling updateRow() you would do 
replace row to rs;
rs.updateRow();

Comment 7 Joseph Vincens 2011-10-11 15:25:14 EDT

(In reply to comment #5)
> Q1:
> 
> In tests so far, I started with a null result set and open instantiated a new
> result set and assigned it to the result set variable named on the open
> statement.
> 
> I'm assuming from the example you posted that open will instantiate a new
> result set object only if the result set is null.   Correct?
> 

Correct, the open creates the resultset.

> Q2:  Update
> 
> I can see that scrollability and holdability are tied to the cursor / result
> set and do not require any change to the SELECT statement used for opening the
> result set.
> 
> However, update is another story.  What is the relationship of rs.update to the
> "for update of " clause in the SQL SELECT statement?    Can I update a row
> without the "for update of " clause?   How are the column values replaced in
> current row of the result set?  Do I need both a replace to rs and rs.update()
> to update a row in a result set?
> 

See comment 6.

> Q3:  Delete
> 
> From your example, I assume rs.delete() by itself is sufficient to delete a row
> in the result set from the table.  Correct?    If auto-commit is true, I'm
> guessing the row gets physically deleted from the table at the point where the
> result set is closed, not when the rs.delete() is invoked.   Correct?

That sounds correct.
The following link describes when an autocommit occurs.
http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/apxtips.htm 
section Disabling Auto-Commit Mode.

Comment 8 Paul Hoffman 2011-10-17 13:38:03 EDT

All questions answered.