How Get a scrollable resultset in Java

2k views Asked by At

I am using Java for my application and Oracle database in the back-end

ResultSet GetCar()
{
CallableStatement cs;
ResultSet rs;

try{
    //conn = dbConnector.getConnection();
conn = dbConnection.getStaticConnection();
cs = conn.prepareCall("begin select_all_car(?); end;",
              ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
rs = ((OracleCallableStatement)cs).getCursor(1);
return rs;
}
}

void foo()
{
    ResultSet rs = GetCar();
    rs.beforeFirst();
}

In foo rs.beforeFirst is giving me this error: "Invalid operation for forward only resultset : beforeFirst"

And this is my query for ORACLE database:

CREATE OR REPLACE PROCEDURE
SELECT_ALL_CAR 
(
  pCar_Recordset  OUT   SYS_REFCURSOR
)
AS

BEGIN

OPEN pCar_Recordset FOR
  SELECT  ID, MANUFACTURER, MAKE, YEAR, MODEL
  FROM    CAR
  ORDER BY ID;

END SELECT_ALL_CAR;

what am I doing wrong? is it oracle cursor? how can I make my resultset scrollable?

1

There are 1 answers

0
Elliott Frisch On

As @Przemyslaw pointed out you could use a PreparedStatement to call your stored procedure (no need to move your implementation to the front end, and your front end should not be making SQL calls directly anyway) you can define an ad hoc procedure call to then call your existing (packaged right?) stored procedures. If that isn't acceptable, you can create a view (or a materialized view) and query from that instead.