This discussion is archived
6 Replies Latest reply: Sep 10, 2012 6:50 AM by dsurber RSS

SCROLL_SENSITIVE result set can't see the data inserted.

955481 Newbie
Currently Being Moderated
hi all ,

I am trying to display all the latest data available in the table through SCROLL_SENSITIVE and UPDATABLE result set after inserting a new record in the table.
But the result set obtained after executing the query initially is not able to see the newly inserted record in the table and hence same result is getting printed out in both the cases.
Can u explain me what's happening in this case ?? And how can i get the updated record also without executing the statement query twice to get the latest result set.

My full code is given below.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Misc3 {

public static void main(String[] args) {
Connection con = null;
Statement stmt = null;
ResultSet rs = null;
try {
int empid;
String lname;
String fname;
int deptno;
int mngrid;
con = JDBCUtil.getOracleConnection();
stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
String query = "select employee_id , last_name , first_name , department_number , manager_id from employees ";
rs = stmt.executeQuery(query);
System.out.println("Before inserting the new record.....");
while (rs.next()) {
empid = rs.getInt(1);
lname = rs.getString(2);
fname = rs.getString(3);
deptno = rs.getInt(4);
mngrid = rs.getInt(5);
System.out.println(empid + "\t" + lname + "\t" + fname + "\t" + deptno + "\t" + mngrid);
}
System.out.println("Going to insert the new record.....");
rs.moveToInsertRow();
rs.updateInt(1, 10);
rs.updateString(2, "Clark");
rs.updateString(3, "John");
rs.updateInt(4, 2);
rs.updateInt(5, 2);
rs.insertRow();
System.out.println("New record inserted successfully.....");
System.out.println("After inserting the new record.....");
rs.beforeFirst();
while (rs.next()) {
empid = rs.getInt(1);
lname = rs.getString(2);
fname = rs.getString(3);
deptno = rs.getInt(4);
mngrid = rs.getInt(5);
System.out.println(empid + "\t" + lname + "\t" + fname + "\t" + deptno + "\t" + mngrid);
}
} catch (SQLException ex) {
System.out.println("error code : " + ex.getErrorCode());
System.out.println("error message : " + ex.getMessage());
} finally {
JDBCUtil.cleanUp(con, stmt);
}
}
}


*** JDBCUtil Class ****

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JDBCUtil {
public static Connection getOracleConnection(){
Connection con = null;
try{
// Load the driver
Class.forName("oracle.jdbc.driver.OracleDriver");
//Establish Connection
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","ex","ex");
}catch(Exception ex){
ex.printStackTrace();
}
return con;
}

public static void cleanUp (Connection con , Statement stmt){
// Release the resource
try{
if(con != null){
con.close();
}
if(stmt != null){
stmt.close();
}
}catch(Exception ex){
ex.printStackTrace();
}
}
}

Edited by: user12848632 on Aug 13, 2012 2:06 PM
  • 1. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    rp0428 Guru
    Currently Being Moderated
    >
    Can u explain me what's happening in this case ?? And how can i get the updated record also without executing the statement query twice to get the latest result set.
    >
    Sure - but you could have answered your own question if you had read the doc link I gave you in your other thread and next time you post code use \
     tags on the lines before and after the code - see the FAQ for info
    17076 : Invalid operation for read only resultset
    {quote}
    •Internal INSERT operations are never visible, regardless of the result set type.
    {quote}
    See •Seeing Database Changes Made Internally and Externally in the JDBC Dev doc I pointed you to
     http://docs.oracle.com/cd/B28359_01/java.111/b31224/resltset.htm#i1024720
    
    Did you notice the words 'never visible'? You won't see them as part of the result set unless you requery.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 2. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    dsurber Explorer
    Currently Being Moderated
    What rp0428 wrote.

    Plus SCROLL_SENSITIVE result sets are fake in Oracle JDBC. The query is in fact executed multiple times to refresh the rows. It would be hugely faster to just re-execute the query. Other databases have server support for sensitive result sets but the Oracle Database does not. So to fake it the JDBC driver re-executes the query for every fetchSize number of rows to get the most recent updates.
  • 3. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    rp0428 Guru
    Currently Being Moderated
    Why are you addressing those comments to me instead of to OP?
    >
    Plus SCROLL_SENSITIVE result sets are fake in Oracle JDBC. The query is in fact executed multiple times to refresh the rows. It would be hugely faster to just re-execute the query. Other databases have server support for sensitive result sets but the Oracle Database does not. So to fake it the JDBC driver re-executes the query for every fetchSize number of rows to get the most recent updates.
    >
    I assume you are referring to the Note at the end of the link I provided OP
    >
    Note:

    Because this kind of refetching is not a highly efficient or optimized methodology, there are significant performance concerns. Consider carefully before using scroll-sensitive result sets as currently implemented. There is also a significant trade-off between sensitivity and performance. The most sensitive result set is one with a fetch size of 1, which would result in the new current row being refetched every time you move between rows. However, this would have a significant impact on the performance of your application.
    >
    I wouldn't call that 'faking it'. It's a client-side implementation rather that server-side if that's what you mean. As the note you need to be careful to use scroll-sensitive sets carefully.

    A large part of the issue with using server-side methodology with Oracle is maintaining the read consistency that Oracle is famous for and that other databases don't provide. Part of that read consistency is related to what I (and the doc) told OP about why new inserts were not visible. Table 17-1 'Visibility of Internal and External Changes for Oracle JDBC summarizes some of the issues and shows why internal/external INSERTs cannot be seen and why even external DELETEs cannot be seen.

    Scroll-sensitive result sets certainly aren't designed to be used for extensive back-and-forth scrolling through large numbers of records.
  • 4. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    DrClap Expert
    Currently Being Moderated
    rp0428 wrote:
    Scroll-sensitive result sets certainly aren't designed to be used for extensive back-and-forth scrolling through large numbers of records.
    I'm curious what they are designed for, then. I've never encountered a situation where I thought they might be useful. (Of course that doesn't mean they are useless, it just means I don't get around enough.)
  • 5. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    rp0428 Guru
    Currently Being Moderated
    >
    I'm curious what they are designed for, then.
    >
    The best example is a data browser in a GUI (sql developer, toad, etc). A data grid is displayed that shows a 'window' of rows. The underlying query will 'fetch' a number of rows (based on fetchsize) and display a subset in the 'window'.

    The issue is how to get the data needed when the user scrolls down (forward) or up.

    Forward-only isn't a problem; that just uses the normal 'fetch' to get the next set of rows if a new fetch is needed. The original cursor is still open and fetch operations just return records as normal. Oracle's read consistency is maintained to allow the query to only fetch records that the user's transaction should see based on the isolation level. That is easily done using the SCN that is part of the data blocks and the UNDO segment that allows old versions of data to be reconstructed even if there have been multiple committed updates or deletes.

    The problem is reverse scrolling - retrieving records that had previously been retrieved. Oracle rows in a table or query have no inherent order. Rows in a table are like balls in a basket; there is no 'first' row. You have to use an ORDER BY to ensure that you get the order you want.

    So if the fetch size is the standard 10 rows and the last fetch was for rows 1001 - 1010 how do you now fetch rows 991 - 1000? Rerun the query and then spin through it throwing away rows until you reach row 991 and then fetch those 10 rows.

    Now if the user wants to scroll backward again you rerun the query again throwing away rows until you reach the ones you want.

    That is very expensive. You can often get by with it in a GUI when the user takes several seconds to look at the data grid anyway.

    Recent versions of Oracle support RESULT SET CACHING and if that is enabled then Oracle can just use the cache to get previous records. But as the doc showed for JDBC if the refreshRow function is used (even under the covers) then the result set needs to include commited updates to rows that were in the result set to begin with.

    Oracle now also supports client-side caching and this makes things even faster. But the larger the result set the bigger the problem with memory and storage.
  • 6. Re: SCROLL_SENSITIVE result set can't see the data inserted.
    dsurber Explorer
    Currently Being Moderated
    Oracle JDBC has supported client side scrolling for at least ten years. The JDBC spec includes (optional) support for scrollable read-only result sets. Since the server has no support for this the JDBC driver has had client side support since the feature was added to the JDBC spec. As you say though, it can be memory intensive.

    The use case you describe, supporting a scrolling list in a GUI is perfect for scrolling insensitive. While there are use cases for scrolling updateable or scrolling sensitive, because of the overhead required by the client side support, I don't think they are very useful with Oracle. They work if you don't mind paying the cost, but in general, I wouldn't use them. Scrolling sensitive reads every row in the result (except for the first fetch block) twice, at least. It executes <number of rows> / <fetch size> + 1 queries at least to view the whole result set. If you start scrolling around the number goes up.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points