• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Question

 
smitha verghese
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi can i use 2 result sets and compare them?
ResultSet rs,rs1;
Statement st;
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =DriverManager.getConnection("jdbc dbc msdb","admin","");
st=con.createStatement();
rs=st.executeQuery("SELECT DATE1,NAME,SUM(LINE_COUNT) AS LCOUNT,AVG(QUALITY) AS ACCURACY FROM Production GROUP BY DATE1,NAME");
rs1=st.executeQuery("SELECT DATE1,NAME,SUM(LINE_COUNT) AS LB FROM PRODUCTION WHERE QUALITY < 98 GROUP BY DATE1,NAME");
while (rs.next())
{
while (rs1.next())
{%>

<tr bgColor="#FFFFFF">
<td><p><%=rs.getString("DATE1")%> </p>
</td>
<td><p><%="NULL"%>   </p>
</td>
<td><p><%=rs.getString("NAME")%>  </p>
</td>
<td><p><%=rs.getString("LCOUNT")%>  </p>
</td>
<td><p><%=rs.getString("ACCURACY")%>  </p>
</td>

<%if ((rs.getString("NAME") == rs1.getString("NAME")) && (rs.getString("DATE1") == rs1.getString("DATE1")))
{%>
<td><p><%=rs1.getString("LB")%>   </p>
</td>

<%}%>
</tr>

<%}}%>
</table>
</center>
</div>

<%
con.close();

I get error resultset closed! Y?
PLS REPLY
 
SJ Adnams
Ranch Hand
Posts: 925
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
2 points.
1) read about "model-view-controller" architecture.
2) roll up your 2 queries into 1.

SELECT
DATE1,
NAME,
SUM(LINE_COUNT) AS LCOUNT,
AVG(QUALITY) AS ACCURACY
SUM(UNDER98) AS UNDER98
FROM (
SELECT
DATE1,
NAME,
QUALITY,
CASE WHEN
QUALITY < 98 THEN 1
ELSE
0
END AS UNDER98
FROM
PRODUCTION )
GROUP BY DATE1, NAME;
 
Anwar Hossain
Ranch Hand
Posts: 35
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If u really wanna test a jdbc and odbc then u've to download the actual driver.

So go ahead......
Anwar
SCJP2
 
Dorothy Finkel-Laverty
Ranch Hand
Posts: 51
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I read that if you want 2 result sets opened at the same time, that you need 2 Statement objects. I haven't had a chance to try it out yet, but it seems to fit with your error message. Let me know if it works.
 
Monde Hans
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Although your code is not desinged *perfectly*. Try to change create a second statement and use that one for the second resultSet.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic