Reference a Table Field Value in a SQL Statement using JSP

[BBobinski]

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
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>&nbsp;Entered:</strong> <%=startdate%></td>
<td width="485" height="25"><strong>&nbsp;Subject: <%=subject%></strong></td>
<td width="152"><strong># of Days to Run:</strong> <%=rundays%></td>
</tr>
<tr>
<td height="25" colspan="3"><%=narrative%><%=getnotedate%>&nbsp;</td>
</tr>
<tr>
<td height="25" colspan="3"><strong>&nbsp;Authority: </strong><%=authority%></strong> &nbsp;</td>
</tr>
<tr>
<td height="25" colspan="3">&nbsp;</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.

 

[stefanwagner]

I don't see something obvious on the first look.

Did you

System.out.println (sql2);

to check, if the statement is as expected at this point?

Why don't you use a single query:

SELECT n.* 
FROM rpdnotification n
    , rpdbulletin b
WHERE n.rundate >= b.notedate;

seeking a job as java-programmer in Berlin:

http://home.arcor.de/hirnstrom/bewerbung

 

[toolkit]

You should also consider using the JavaServer Pages Standard Tag Library (JSTL) from Sun, which contains tags specifically for running and iterating through SQL:

http://java.sun.com/products/jsp/jstl/

Alternatively, you could move the SQL calls into a bean, which would unclutter your JSP, and make code reuse a bit easier.

Cheers, Neil

 

[toolkit]

So for example:

<%@ taglib prefix="c" uri="[URL unfurl="true"]http://java.sun.com/jsp/jstl/core"[/URL] %>
<%@ taglib prefix="sql" uri="[URL unfurl="true"]http://java.sun.com/jsp/jstl/sql"[/URL] %>

<sql:setDataSource
  driver="your driver goes here"
  url="your JDBC url does here"
  user="your user goes here"
  password="your password goes here"
/>

<sql:query var="result">
    SELECT foo, bar FROM mytable
</sql:query>

<table cellspacing="0" cellpadding="3px">
  <tr>
    <th>Foo</th>
    <th>Bar</th>
  </tr>
  <c:forEach var="row" items="${result.rows}">
    <tr>
        <td><c:out value="${row.foo}"/></td>
        <td><c:out value="${row.bar}"/></td>
    </tr>
  </c:forEach>
</table>

Cheers, Neil

 

[BBobinski]

Thanks for the help Stefan and Toolkit. I ended up using the following SQL Statement. You guys pointed me in the right direction.

SELECT * FROM rpdnotification WHERE rundate >= (SELECT MAX(notedate) FROM rpdbulletin)