Thanks everybody,
With all your help, I finally made my code work! Here is a summary about
what I did in case anybody is interested:
In Oracle, I can use this query:
Select id, name, date from (select id, name, date from mytable order by
date desc) where rownum<=100;
In sql Server, I can use this query:
Select top 100 id, name, date from mytable order by date desc
If I was worked on only one datebase platform, either of these two would
work fine and I would not need to do any extra work. But I have to make my
java code work on both databases (in other words, make it portable), so
finally I use the query "Select id, name, date from mytable order by date
desc;". Then as John suggested, I used setMaxRows=100. Fortunately it
worked!(Maybe because I have an index on date, so it sorts before
setmaxrows?)
Does anybody has to do the same thing as I do which is make sure the jsp
page works on both Oracle and sql Server? Now I have another query which
will deal with date. I post it here so somebody please help me.
Suppose I have a table holidays on both Oracle and sql Server as following:
HOLIDAYNAME HOLIDAYDATE
------------------------------ ---------
New Years Day 01-JAN-01
Valentines Day 14-FEB-01
St. Patricks Day 17-MAR-01
Memorial Day 28-MAY-01
Independence Day 04-JUL-01
Labor Day 03-SEP-01
Columbus Day 08-OCT-01
Halloween 31-OCT-01
Thanksgiving Day 22-NOV-01
Day After Thanksgiving 23-NOV-01
Christmas Day 25-DEC-01
New Years Eve 31-DEC-01
Now I need to be able to do a query which will tell the user how many
holidays are on Sunday and how many on Monday, etc.. like following:
DayOfWeek numberOfOccurrences
MONDAY 5
TUESDAY 1
WEDNESDAY 3
THURSDAY 1
FRIDAY 1
SATURDAY 1
In oracle, I can use the query: "select dayOfWeek, sum(numberOfOccurrences)
from ( select to_char holidaydate, 'DAY') dayOfWeek, to_char( holidaydate,
'D') DD, count(*) numberOfOccurrences from holidays group by
holidaydate)group by dayOfWeek, DD order by DD;".
And in sql server, I can use: "select datename(dw, HolidayDate) dayOfWeek,
count(*) numberOfOccurrences from holidays group by datename(dw,
HolidayDate)" except the dayOfWeek is not in order.
But how do I use java(jsp) code to form the query string and make it
portable for both databases, I have totally no idea. Since both databases
use totally different date functions. So how can I make the query
transferable to each other?
Happy coding! :)
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
===========================================================================
To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST".
For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST".
Some relevant FAQs on JSP/Servlets can be found at:
http://java.sun.com/products/jsp/faq.html
http://www.esperanto.org.nz/jsp/jspfaq.html
http://www.jguru.com/jguru/faq/faqpage.jsp?name=JSP
http://www.jguru.com/jguru/faq/faqpage.jsp?name=Servlets
