You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@struts.apache.org by Mindaugas Idzelis <ma...@ritvax.isc.rit.edu> on 2001/06/15 18:57:13 UTC
General DATABASE programming question
This may be a little off topic, please forgive me. One of my webapplications
displays a group of results from a database query. These can be hundreds of
rows long. I would like to break these up into smaller sections to fit on a
page.
My problem is, I don't want to run the entire query at once and save it to
memory.
PostgreSQL and MySQL have the LIMIT num,offset qualifier which does exactly
what I want. The problem is that I'm using MS SQL and it doesn't use the
LIMIT keyword. Instead, it uses TOP and you can't supply an offset.
My quetion: Is there anyway to specify an offset into a query using MS SQL
Server? Thank you.
--min
Re[2]: General DATABASE programming question
Posted by Oleg V Alexeev <go...@penza.net>.
Hello Mindaugas,
Saturday, June 16, 2001, 10:36:45 PM, you wrote:
MI> My original goal is to ask the database for a page of information at a time.
MI> However, MS SQL server does not let you display your results starting at an
MI> offset.
MI> In my web app's search method, I load the ResultSet into a LinkedList and
MI> use the struts iterate taglib to display the results to the JSP tags pager
MI> taglib.
MI> So, I see two ways of resolving this. 1) Do the query one time and store the
MI> LinkedList in the session -- effectively caching the entire results in
MI> memory. 2) Query the database each time and skip over the first n results
MI> and discarding the last x results.
MI> Neither of these solutions are really ideal since the the ammount of results
MI> I expect to return are in 1,000 range.
MI> So, is it time to pick a better database? Thanks.
I think you have next variants -
1. Cach results in session (if you have large amount of memory - it is
your way) or in application scope (if you use static data set).
2. Write stored proc in SQL Server -
by the stored proc you can query database, scroll to the target row
and return limited count of rows
3. Query database directly -
you can scroll directly - via ResultSet.absolute(int) - if your
JDBC driver supports scrollable result sets, or scroll manually -
via ResultSet.next().
--
Best regards,
Oleg mailto:gonza@penza.net
Web design with taglibs?
Posted by Thomas Corte <th...@hp9000.do.knipp.de>.
Hi,
the Struts template taglib obviously addresses the design
of a web page at large, e.g. menu/header/content/footer,
and does a good job here.
However, I'd like to use templates at a smaller scale,
e.g. use a template for a small HTML fragment containing an
anchor tag with an image which occurs on many pages.
As the struts templates are not built for this task,
I find myself writing my own tags which merely print out
little or huge amounts of literal HTML code in the doStartTag and doEndTag
methods.
Is this the preferred method for my task? It's not really elegant,
even if I'd replace the print("<...></..>") stuff by equivalent
ECS method calls.
Regards,
_____________________________________________________________________
| |
| knipp | Knipp Medien und Kommunikation GmbH
------- Technologiepark
Martin-Schmeisser-Weg 9
D-44227 Dortmund
Dipl.-Inform. Thomas Corte Fon: 0231-9703-0
thomas@knipp.de Fax: 0231-9703-200
Re: General DATABASE programming question
Posted by Martin Cooper <ma...@tumbleweed.com>.
We use SQL Server, and we use your option (2). As long as you use scrollable
cursors, you're not actually retrieving everything, just what you need. You
can also use TOP to help limit the query. Some of our queries are against
tables with many millions of rows, and the result sets can contain millions
of entries. Still, as long as the database is set up properly, the queries
and retrieval are fast.
--
Martin Cooper
----- Original Message -----
From: "Mindaugas Idzelis" <ma...@ritvax.isc.rit.edu>
To: <st...@jakarta.apache.org>
Sent: Saturday, June 16, 2001 11:36 AM
Subject: RE: General DATABASE programming question
>
> > I know it sounds wasteful, but personally I would think about getting a
> > page at a time. This is more work on the DBMS as they go to later pages,
> > but many times they don't go past the first page. Otherwise you will
> > need to keep the resultset in the session and tie up memory.
> >
> > A lot of people like to use the pager tag from JSP tags for this sort of
> > thing.
>
> My original goal is to ask the database for a page of information at a
time.
> However, MS SQL server does not let you display your results starting at
an
> offset.
>
> In my web app's search method, I load the ResultSet into a LinkedList and
> use the struts iterate taglib to display the results to the JSP tags pager
> taglib.
>
> So, I see two ways of resolving this. 1) Do the query one time and store
the
> LinkedList in the session -- effectively caching the entire results in
> memory. 2) Query the database each time and skip over the first n results
> and discarding the last x results.
>
> Neither of these solutions are really ideal since the the ammount of
results
> I expect to return are in 1,000 range.
>
> So, is it time to pick a better database? Thanks.
>
>
> > -- Ted Husted, Husted dot Com, Fairport NY USA.
> > -- Custom Software ~ Technical Services.
> > -- Tel 716 737-3463.
> > -- http://www.husted.com/about/struts/
> >
> >
> > Mindaugas Idzelis wrote:
> > >
> > > Thanks for all your answers. This has been very nerve wrecking.
> > I think I've
> > > come up with a solution that may work in a DB independent way.
> > I'll use a
> > > scrollable resultset. I won't iterate through all of it, only
> > portions at a
> > > time. I think most underlying JDBC drivers use "cursors" to
> > implement the
> > > scrollable resultset. Is this a valid approach?
> > >
> > > --min
>
RE: General DATABASE programming question
Posted by Mindaugas Idzelis <ma...@ritvax.isc.rit.edu>.
> I know it sounds wasteful, but personally I would think about getting a
> page at a time. This is more work on the DBMS as they go to later pages,
> but many times they don't go past the first page. Otherwise you will
> need to keep the resultset in the session and tie up memory.
>
> A lot of people like to use the pager tag from JSP tags for this sort of
> thing.
My original goal is to ask the database for a page of information at a time.
However, MS SQL server does not let you display your results starting at an
offset.
In my web app's search method, I load the ResultSet into a LinkedList and
use the struts iterate taglib to display the results to the JSP tags pager
taglib.
So, I see two ways of resolving this. 1) Do the query one time and store the
LinkedList in the session -- effectively caching the entire results in
memory. 2) Query the database each time and skip over the first n results
and discarding the last x results.
Neither of these solutions are really ideal since the the ammount of results
I expect to return are in 1,000 range.
So, is it time to pick a better database? Thanks.
> -- Ted Husted, Husted dot Com, Fairport NY USA.
> -- Custom Software ~ Technical Services.
> -- Tel 716 737-3463.
> -- http://www.husted.com/about/struts/
>
>
> Mindaugas Idzelis wrote:
> >
> > Thanks for all your answers. This has been very nerve wrecking.
> I think I've
> > come up with a solution that may work in a DB independent way.
> I'll use a
> > scrollable resultset. I won't iterate through all of it, only
> portions at a
> > time. I think most underlying JDBC drivers use "cursors" to
> implement the
> > scrollable resultset. Is this a valid approach?
> >
> > --min
Re: General DATABASE programming question
Posted by Ted Husted <hu...@apache.org>.
If you use a scollable resultset, you will have to keep an open
connection to the database. This can be an issue with a Web application.
With a Cacheable RowSet you would not need to keep the connection open,
but you would need to store the entire set in memory.
Either way, you would need a mechanism to change the settings for the
interator, which probably means round trips to the server.
I know it sounds wasteful, but personally I would think about getting a
page at a time. This is more work on the DBMS as they go to later pages,
but many times they don't go past the first page. Otherwise you will
need to keep the resultset in the session and tie up memory.
A lot of people like to use the pager tag from JSP tags for this sort of
thing.
-- Ted Husted, Husted dot Com, Fairport NY USA.
-- Custom Software ~ Technical Services.
-- Tel 716 737-3463.
-- http://www.husted.com/about/struts/
Mindaugas Idzelis wrote:
>
> Thanks for all your answers. This has been very nerve wrecking. I think I've
> come up with a solution that may work in a DB independent way. I'll use a
> scrollable resultset. I won't iterate through all of it, only portions at a
> time. I think most underlying JDBC drivers use "cursors" to implement the
> scrollable resultset. Is this a valid approach?
>
> --min
RE: General DATABASE programming question
Posted by Mindaugas Idzelis <ma...@ritvax.isc.rit.edu>.
Thanks for all your answers. This has been very nerve wrecking. I think I've
come up with a solution that may work in a DB independent way. I'll use a
scrollable resultset. I won't iterate through all of it, only portions at a
time. I think most underlying JDBC drivers use "cursors" to implement the
scrollable resultset. Is this a valid approach?
--min
Re: General DATABASE programming question
Posted by Ted Husted <hu...@apache.org>.
Sun's cached rowset has a MaxRows property that you can use to limit the
number of rows returned.
http://developer.java.sun.com/developer/Books/JDBCTutorial/chapter5.html
I'm not aware of a general way to offset a particular number of rows in
the a query that will work with everyting, which leaves fetching block 1
and block 2 to get to block 3. (Hoping 1 and 2 haven't changed in the
meantime).
-- Ted Husted, Husted dot Com, Fairport NY USA.
-- Custom Software ~ Technical Services.
-- Tel 716 737-3463.
-- http://www.husted.com/about/struts/
Mindaugas Idzelis wrote:
>
> This may be a little off topic, please forgive me. One of my webapplications
> displays a group of results from a database query. These can be hundreds of
> rows long. I would like to break these up into smaller sections to fit on a
> page.
>
> My problem is, I don't want to run the entire query at once and save it to
> memory.
>
> PostgreSQL and MySQL have the LIMIT num,offset qualifier which does exactly
> what I want. The problem is that I'm using MS SQL and it doesn't use the
> LIMIT keyword. Instead, it uses TOP and you can't supply an offset.
>
> My quetion: Is there anyway to specify an offset into a query using MS SQL
> Server? Thank you.
>
> --min
Re: General DATABASE programming question
Posted by Stephen Welds <sw...@internet.look.ca>.
If you want to be able to restrict the number of rows returned from a query
to a MS SQL Server database then you can use the "set rowcount xx" command.
This command can be used as follows to return 100 rows from a query:
set rowcount 100
select * from mytable where myvariable = 'mycriteria';
set rowcount 0
The set rowcount command affects all queries executed after the command.
Hence the set rowcount 0 is required to return things to normal.
Stephen Welds
----- Original Message -----
From: "Mindaugas Idzelis" <ma...@ritvax.isc.rit.edu>
To: "struts" <st...@jakarta.apache.org>
Sent: Friday, June 15, 2001 12:57 PM
Subject: General DATABASE programming question
> This may be a little off topic, please forgive me. One of my
webapplications
> displays a group of results from a database query. These can be hundreds
of
> rows long. I would like to break these up into smaller sections to fit on
a
> page.
>
> My problem is, I don't want to run the entire query at once and save it to
> memory.
>
> PostgreSQL and MySQL have the LIMIT num,offset qualifier which does
exactly
> what I want. The problem is that I'm using MS SQL and it doesn't use the
> LIMIT keyword. Instead, it uses TOP and you can't supply an offset.
>
> My quetion: Is there anyway to specify an offset into a query using MS SQL
> Server? Thank you.
>
> --min
>
RE: General DATABASE programming question
Posted by Mindaugas Idzelis <ma...@ritvax.isc.rit.edu>.
I've tried using the rownum before, but this is the error i get from sql
server 2000....
"rownum is not a valid column name"
Re: General DATABASE programming question
Posted by Spencer Smith <sp...@newdestiny.net>.
Sure. Just use something like this:
SELECT * FROM (Select * from sometable
Order by sortcolumn)
WHERE rownum>=1 and rownum<=10
This would return the first 1-10th rows of the query (sorted properly)
If you were not interested in sorting properly, you could just do:
SELECT * from sometable
WHERE rownum>=1 and rownum<=10
Of course, normally you would use ? instead of hardcoded values...
:-) Hope this helps
----- Original Message -----
From: "Mindaugas Idzelis" <ma...@ritvax.isc.rit.edu>
To: "struts" <st...@jakarta.apache.org>
Sent: Friday, June 15, 2001 9:57 AM
Subject: General DATABASE programming question
> This may be a little off topic, please forgive me. One of my
webapplications
> displays a group of results from a database query. These can be hundreds
of
> rows long. I would like to break these up into smaller sections to fit on
a
> page.
>
> My problem is, I don't want to run the entire query at once and save it to
> memory.
>
> PostgreSQL and MySQL have the LIMIT num,offset qualifier which does
exactly
> what I want. The problem is that I'm using MS SQL and it doesn't use the
> LIMIT keyword. Instead, it uses TOP and you can't supply an offset.
>
> My quetion: Is there anyway to specify an offset into a query using MS SQL
> Server? Thank you.
>
> --min
>
>
Re: General DATABASE programming question
Posted by Dan Marina <md...@sybase.ro>.
an idea
you have to know all the time exactly where you are in result set
( start, end | step)
run query, skip first first rows -
do what you want with next "step" rows [ for ( int i = start; i <
start + step; i ++) ...]
close result set
create a template with parameters for this page ...
( it works on any db)
br,
dan
hope this help
At 12:57 PM 6/15/2001 -0400, you wrote:
>This may be a little off topic, please forgive me. One of my webapplications
>displays a group of results from a database query. These can be hundreds of
>rows long. I would like to break these up into smaller sections to fit on a
>page.
>
>My problem is, I don't want to run the entire query at once and save it to
>memory.
>
>PostgreSQL and MySQL have the LIMIT num,offset qualifier which does exactly
>what I want. The problem is that I'm using MS SQL and it doesn't use the
>LIMIT keyword. Instead, it uses TOP and you can't supply an offset.
>
>My quetion: Is there anyway to specify an offset into a query using MS SQL
>Server? Thank you.
>
>--min