This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Reference a Table Field Value in a SQL Statement using JSP

 
Bernard Bobinski
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a TABLE called rpdnotification. Within rpdnotification is an INTEGER Field entitled rundate. rundate is the last date that the notification is supposed to run. In the TABLE rpdbullein is an INTEGER Field entitled notedate. notedate is the current entry day of the bulletin. Both dates are converted to and stored as Integers in the following format yyyymmdd.

i.e.

Field Value Table

Notedate 20040827 rpdbulletin
Rundate 20040830 rpdnotification


I need to generate the following SQL Statement: SELECT * FROM rpdnotification WHERE rundate >= notedate;

The code that I am trying to use is as follows:

NOTE - rsLog1 is the query results for rpdbulletin


<% // Get Notifications from rpdnotification

while (rsLog1.next()) {
int getnotedate = rsLog1.getInt("notedate");
rsLog1.cose();
// int getnotedate = 20040826; Test for my SQL Statement � Grayed out
sql2 = "select * from rpdnotification where rundate >= " + getnotedate;
Statement stmtLog2 = qedsys.createStatement();
ResultSet rsLog2 = stmtLog2.executeQuery(sql2);
DBResultSet drsLog2 = new DBResultSet(rsLog2);
while(rsLog2.next()) {
String subject = rsLog2.getString("subject");
String authority = rsLog2.getString("authority");
String narrative = rsLog2.getString("narrative");
String startdate = rsLog2.getString("startdate");
String rundays = rsLog2.getString("rundays");
%>
<tr>
<td height="25" colspan="6"><table width="760" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="123" height="25"><strong> Entered:</strong> <%=startdate%></td>
<td width="485" height="25"><strong> Subject: <%=subject%></strong></td>
<td width="152"><strong># of Days to Run:</strong> <%=rundays%></td>
</tr>
<tr>
<td height="25" colspan="3"><%=narrative%><%=getnotedate%> </td>
</tr>
<tr>
<td height="25" colspan="3"><strong> Authority: </strong><%=authority%></strong>  </td>
</tr>
<tr>
<td height="25" colspan="3"> </td>
</tr>
<%
} // end of rsLog2 WHILE Loop
} // end of rsLog1 WHILE Loop
%>


With the following code I get no errors but nothing shows up.

Thanks.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Bernard,

Welcome to JavaRanch!

I can't see anything obviously wrong with your code.

Does your query return any rows when you run "select * from rpdnotification where rundate >= 20040826" directly against the DB (i.e. not with Java)? Try sticking some debug logging in there to see what's happening along the way. You're not doing anything daft like catching Exceptions and ignoring them are you?

A couple of tips: it's considered best practice (safer, more secure, etc.) to use PreparedStatement instead of just plain old Statement. Are you aware that you're not using the DBResultSet?

When you post code please surround it with the UBB [C0DE] and [/C0DE] tags to preserve your formatting. Thanks.

Jules
 
Bernard Bobinski
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Julian,

Thank you very much for the reply. I solved the problem today by adjusting my sql statement to

SELECT * FROM rpdnotification WHERE rundate >= (SELECT MAX(notedate) FROM rpdbulletin)
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic