File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JSP and the fly likes Reference a Table Field Value in a SQL Statement using JSP Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Make it so: Java DB Connections & Transactions this week in the JDBC forum!
JavaRanch » Java Forums » Java » JSP
Bookmark "Reference a Table Field Value in a SQL Statement using JSP" Watch "Reference a Table Field Value in a SQL Statement using JSP" New topic

Reference a Table Field Value in a SQL Statement using JSP

Bernard Bobinski

Joined: Aug 30, 2004
Posts: 14
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.


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 ( {
int getnotedate = rsLog1.getInt("notedate");
// 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( {
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");
<td height="25" colspan="6"><table width="760" border="0" cellspacing="0" cellpadding="0">
<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>
<td height="25" colspan="3"><%=narrative%><%=getnotedate%> </td>
<td height="25" colspan="3"><strong> Authority: </strong><%=authority%></strong>  </td>
<td height="25" colspan="3"> </td>
} // end of rsLog2 WHILE Loop
} // end of rsLog1 WHILE Loop

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


Respectfully,<br /> <br />Bernard J. Bobinski
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
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.

Bernard Bobinski

Joined: Aug 30, 2004
Posts: 14

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)
I agree. Here's the link:
subject: Reference a Table Field Value in a SQL Statement using JSP
It's not a secret anymore!