Thanks for the tips...I'm actually using the SP approach right now (SQL
Server 7). What I'm trying to accomplish (as well as the originator of this
thread, I believe) is to get away from having to re-execute the query for
every subsequent page retrieval.
--Gary
> -----Original Message-----
> From: A mailing list for Enterprise JavaBeans development
> [mailto:[EMAIL PROTECTED]]On Behalf Of Matt Randle
> Sent: Monday, July 31, 2000 1:58 AM
> To: [EMAIL PROTECTED]
> Subject: Re: Statefull session bean to handle queries
>
>
> Depends on what .db you are using.
>
> Oracle has SQL extensions that allow you to specify exactly
> which rows of a
> query to return i.e. between 100 and 200 (I forget the exact syntax).
>
> SQL Server + others only allow you to do the TOP x rows.
>
> >From your mail I assume you not using Oracle.
Correct...I'm using SQL Server 7.
>
> Two database solutions. Assuming you want rows X to X + n
>
> Write an SP that does a query for TOP (X + n) rows, opens a
> cursor on the
> result set and navigates through the cursor returning the
> required rows.
>
> Write out the entire query to a temporary table with a query ID and
> sequential row id's. You can then query the table for the
> required page.
>
> Obviously you need to store the current page number (in the
> session context
> or a statefull session bean ?).
>
> Hope this helps,
>
> Matt.
>
>
> > -----Original Message-----
> > From: Gary Windham [mailto:[EMAIL PROTECTED]]
> > Sent: Friday, July 28, 2000 8:02 PM
> > To: [EMAIL PROTECTED]
> > Subject: Re: Statefull session bean to handle queries
> >
> >
> > This is an interesting problem that I've been wrestling with
> > in the context
> > of master-detail lists. I have a "master list view" in our
> > application that
> > is implemented as pages, which can be navigated sequentially
> > or randomly.
> > My current approach is to use a stateless session bean which
> > performs a JDBC
> > query using a "SELECT TOP ..." statement and then cursoring
> > through the
> > resultset (e.g., if a page size is 20 rows, and you want the
> > 5th page, you
> > use a "TOP 100", then cursor down 80 rows). Obviously this
> > approach is
> > fraught with problems. I haven't been able to find an
> > approach that is a
> > complete "win". Stateful session beans have scalability
> and resource
> > consumption problems. One approach that I've been
> > investigating is using
> > "transient entity beans", which live only in the EJB object
> cache (see
> > http://www.c2.com/cgi/wiki?SearchResultAsEntityBean and
> > http://www.c2.com/cgi/wiki?TransientEntityBean). The risk
> > here is that if
> > you have a large result set, and/or lots of client "think
> > time" between
> > "chunk" retrievals, your entity bean could be flushed from
> > the cache before
> > you're done with it...in which case you would have to
> > re-execute your query.
> >
> > --Gary
> >
> > -----Original Message-----
> > From: A mailing list for Enterprise JavaBeans development
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Chris Brown
> > Sent: Friday, July 28, 2000 8:50 AM
> > To: [EMAIL PROTECTED]
> > Subject: Statefull session bean to handle queries
> >
> >
> > Has anyone had success with or have suggestions on the following?
> >
> > Use a statefull session bean to handle queries from a client
> > (servlet). The
> > statefull session bean will create and hold on to the
> > resultset on behalf of
> > the client. The client controls the display of "result
> > pages" to the user
> > and calls methods on the statefull session bean to move through the
> > resultset. The results are passed back to the client as a
> > collection of
> > "state" objects. The servlet will store a handle to the
> > session bean in the
> > session.
> >
> > Concerns:
> > Is this the proper design?
> > How long is the statefull session bean tied to the client?
> > Passivate/Activate - will the resultset be
> > serialized/deserialized properly?
> > Can it handle large result sets? 20,000+ rows?
> >
> > Thanks in advance,
> > Chris
> >
> > ==============================================================
> > =============
> > To unsubscribe, send email to [EMAIL PROTECTED] and
> > include in the body
> > of the message "signoff EJB-INTEREST". For general help,
> > send email to
> > [EMAIL PROTECTED] and include in the body of the message "help".
> >
> > ==============================================================
> > =============
> > To unsubscribe, send email to [EMAIL PROTECTED] and
> > include in the body
> > of the message "signoff EJB-INTEREST". For general help,
> > send email to
> > [EMAIL PROTECTED] and include in the body of the message "help".
> >
>
> ==============================================================
> =============
> To unsubscribe, send email to [EMAIL PROTECTED] and
> include in the body
> of the message "signoff EJB-INTEREST". For general help,
> send email to
> [EMAIL PROTECTED] and include in the body of the message "help".
>
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
