 |
AppletTalk.com
Java discussions newsgroups
|
| View previous topic :: View next topic |
| Author |
Message |
Mike
Guest
|
 Posted: Mon Dec 08, 2003 3:52 pm Post subject: newbie: jsp / ms sql - nested queries problem |
 |
|
Hi all,
I'm writing html in a while loop in a jsp page. Unfortunately, though
I'm getting 3 results back from my first database query (I tested for
this), only the first one is output. The other iterations in the loop
aren't performed, so i only get one set of html tags output instead of
three.
Is this something to do with my nested queries?
Thanks for any tips,
Mike
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
StringTokenizer token = new StringTokenizer(dataInfo);
Connection myConnect = DriverManager.getConnection("JDBC:ODBC:" +
token.nextToken(),token.nextToken(),token.nextToken());
Statement myStatement = myConnect.createStatement();
String myQuery = "select * from forums";
myStatement.executeQuery(myQuery);
ResultSet myResults = myStatement.getResultSet();
while (myResults.next()){
int forumID = myResults.getInt("forumID");
String forumTitle = myResults.getString("forumTitle");
String forumDescr = myResults.getString("forumDescr");
%>
<TR>
<TD width="41" height=50 align=middle vAlign=center
class=row1><IMG height=25 alt="Bullet Point" src="pics/bullet.gif"
width=32></TD>
<TD width="495" height=50 class=row1><SPAN
class=forumlink><a href="forum.jsp?f=<%=forumID%>"><%=forumTitle%></a><BR></SPAN>
<SPAN class=genmed><%=forumDescr%></SPAN></TD>
<%
String threadQuery = "select threadID from threads where forumID =
'"+forumID+"'";
myStatement.executeQuery(threadQuery);
ResultSet threadResult = myStatement.getResultSet();
int threadCount = 0;
while (threadResult.next()) threadCount++;
String postQuery = "select postID from posts";
myStatement.executeQuery(postQuery);
ResultSet postResult = myStatement.getResultSet();
int postCount = 0;
while (postResult.next()) postCount++;
String lastPostQuery = "select top 1 userName, submitDate from
posts where forumID = '"+forumID+"' order by submitDate desc";
myStatement.executeQuery(lastPostQuery);
ResultSet lastPostResult = myStatement.getResultSet();
String userNm = null;
Timestamp submitDate = null;
while (lastPostResult.next()){
userNm = lastPostResult.getString("userName");
submitDate = lastPostResult.getTimestamp("submitDate");
}
SimpleDateFormat sdf = new SimpleDateFormat("EEE, dd MMM yyyy
HH:mm:ss z");
String readableDate = sdf.format((java.util.Date)submitDate);
%>
<TD class=row2 vAlign=center align=middle height=50><SPAN
class=gensmall><%= threadCount %></SPAN></TD>
<TD class=row2 vAlign=center align=middle height=50><SPAN
class=gensmall><%= postCount %></SPAN></TD>
<TD class=row2 vAlign=center noWrap align=left
height=50><SPAN class=gensmall><%= readableDate %><BR><%= userNm
%></SPAN></TD>
<%
if (canEdit){ // modify and remove buttons
%>
<TD class=row2 vAlign=center noWrap align=center><img
src="pics/modify.gif" width="44" height="15"> <img
src="pics/remove.gif" width="44" height="15"></TD>
<%
}
%>
</TR>
<%
} // while
%>
|
|
| Back to top |
|
 |
Lee Fesperman
Guest
|
| Posted: Mon Dec 08, 2003 8:52 pm Post subject: Re: newbie: jsp / ms sql - nested queries problem |
|
|
Mike wrote:
| Quote: |
Hi all,
I'm writing html in a while loop in a jsp page. Unfortunately, though
I'm getting 3 results back from my first database query (I tested for
this), only the first one is output. The other iterations in the loop
aren't performed, so i only get one set of html tags output instead of
three.
Is this something to do with my nested queries?
Thanks for any tips,
|
You're using the same Statement object (myStatement) to create the nested queries. A
Statement can only have one active ResultSet at a time. When you call
myStatement.executeQuery() for the inner query, it automatically closes the outer
ResultSet. Read the JDBC docs.
Use a separate Statement object for the nested queries. Note also that Sql Server has
limits on simultaneous ResultSets. You will need to set the server configuration to use
server-side cursors.
--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
Mike
Guest
|
| Posted: Tue Dec 09, 2003 9:00 am Post subject: Re: newbie: jsp / ms sql - nested queries problem |
|
|
Lee Fesperman <firstsql (AT) ix (DOT) netcom.com> wrote
| Quote: | You're using the same Statement object (myStatement) to create the nested queries. A
Statement can only have one active ResultSet at a time. When you call
myStatement.executeQuery() for the inner query, it automatically closes the outer
ResultSet. Read the JDBC docs.
Use a separate Statement object for the nested queries.
|
I'll try that.
| Quote: | Note also that Sql Server has
limits on simultaneous ResultSets. You will need to set the server configuration to use
server-side cursors.
|
I don't fully understand this and how it affects this example, but
I'll check the documentation.
Many Thanks!
Mike
|
|
| Back to top |
|
|
Mike
Guest
|
| Posted: Tue Dec 09, 2003 12:17 pm Post subject: Re: newbie: jsp / ms sql - nested queries problem |
|
|
Lee Fesperman <firstsql (AT) ix (DOT) netcom.com> wrote
| Quote: | Note also that Sql Server has
limits on simultaneous ResultSets. You will need to set the server configuration to use
server-side cursors.
|
OK, I see what you mean - I can only have one ResultSet at a time
using the jdbc-odbc bridge.
My solution at the moment is to save the contents of my first
ResultSet to an ArrayList, so that I can use them in subsequent
queries - thus avoiding nested queries. Does this seem a reasonable
solution in terms of efficiency? I don't want to change server
settings, and opening two connections at once seems a like a bad idea.
Thanks,
Mike
|
|
| Back to top |
|
|
Lee Fesperman
Guest
|
| Posted: Tue Dec 09, 2003 9:41 pm Post subject: Re: newbie: jsp / ms sql - nested queries problem |
|
|
Mike wrote:
| Quote: |
Lee Fesperman <firstsql (AT) ix (DOT) netcom.com> wrote
Note also that Sql Server has
limits on simultaneous ResultSets. You will need to set the server
configuration to use server-side cursors.
OK, I see what you mean - I can only have one ResultSet at a time
using the jdbc-odbc bridge.
My solution at the moment is to save the contents of my first
ResultSet to an ArrayList, so that I can use them in subsequent
queries - thus avoiding nested queries. Does this seem a reasonable
solution in terms of efficiency? I don't want to change server
settings, and opening two connections at once seems a like a bad idea.
|
Yes, storing the outer ResultSet in an ArrayList is a reasonable solution. Opening two
connections is also ok; I've used that solution in the past.
--
Lee Fesperman, FirstSQL, Inc. (http://www.firstsql.com)
==============================================================
* The Ultimate DBMS is here!
* FirstSQL/J Object/Relational DBMS (http://www.firstsql.com)
|
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
