Re: Limiting Large Result Sets

"Ed Prochak" <edprochak_at_gmail.com> writes:

> smartnhandsome_at_gmail.com wrote:
> > Thanks for every ones replies to make the question more clear this
> > what i wanted from the code
> > I have a method which can accept a SQL query as input parameter and
> > number of results the user wants
> >
> > public RowSetDynaClass getResults(String sql,int resultNumber)
> > {
> > prepareStatment= conn.prepareStatement(sql.toString()),
> > ResultSet.TYPE_SCROLL_INSENSITIVE,java.sql.ResultSet.CONCUR_READ_ONLY);
> > prepareStatment.setMaxRows(resultNumber);
> > resultset = prepareStatment.executeQuery();
> > rowsdc = new RowSetDynaClass(resultset);
> >
> > }
> >
> > So the user gets back a result for the first 10 rows, now the user
> > again requests another set of 10 rows. This method is again called and
> > here is the real problem starts he now wants to get results from 11-20.
> > Simply put the question is how to maintain the state of the results
> > with out modifying the query the user inputs??
>
> You need to split your method into two methods (well three actually)
>
> 1: this one prepares the statement and opens the cursor
> 2: this one fetches the next set of 10 rows
> 3: don't forget to close the cursor.
>
> This is fundamental database query operations.
> Ed

This might do it

select * from (
  select a.*, rownum r from (
    <passed_sql_statement_here>
  ) a
  where rownum < :record_count + 10
)
where r >= :record_count

--
Dave Newman