URGENT cant get sql statement to work

using oracle and jsp. trying to show all records greated than todays date but wont show any records. the query i have at moment is:

 ResultSet rs = stmt.executeQuery("select * from TblPatient_TblBed where DateOut > sysdate");

It works when i enter the query in sql* worksheety

act1veAsked:
Who is Participating?
 
TimYatesCommented:
you need to include java.text

or just do:

<%= new java.text.SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn")) %>
0
 
TimYatesCommented:
is it throwing an exception?

can you post your actual code?
0
 
objectsCommented:
Are you sure the problems with your quesry and not some other issue like a missing driver, or being unable to find database for example?
Does "select * from TblPatient_TblBed" return all the rows in the table?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
bloodredsunCommented:
You'll need to make sure that:

All the columns exist and are the correct name including CAPS
That your comparison is using the correct data types, and if not convert them
0
 
act1veAuthor Commented:
<%
                              
//String user = request.getParameter("patientid");
//session.setAttribute( "Name", user );
       // Create a result set containing all data from my_table
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery("select * from TblPatient_TblBed where DateOut > sysdate");


%>
                   
                    </span><br>
                    </font></p>

                  <table width="100%" border="0">
                    <!--DWLayoutTable-->
                    <tr>
                      <td width="1" height="22">&nbsp;</td>
                      <td width="127" valign="top"><strong>Patient ID</strong></td>
                      <td width="121" valign="top"><strong>Bed ID</strong></td>
                      <td width="118" valign="top"><strong>Assigned ID</strong></td>
                      <td width="114" valign="top"><strong>Date In</strong></td>
                      <td width="56" valign="top"><strong>Date Out</strong></td>
                    </tr>
                              </table>
                    <!--DWLayoutTable-->
                              <%while (rs.next ()){%>
                              
                  <table>
                    <!--DWLayoutTable-->
                    <tr>
                      <td width="1" height="39">&nbsp;</td>
                      <td width="124" valign="top"><%=rs.getString("PatientID")%>&nbsp;</td>
                      <td width="115" valign="top"><%=rs.getString("BedID")%>&nbsp;</td>
                      <td width="1">&nbsp;</td>
                      <td width="117" valign="top"><%=rs.getString("Aid")%>&nbsp;</td>
                      <td width="107" valign="top"><%=rs.getString("DateIn")%>&nbsp;</td>
                      <td width="36" valign="top"><%=rs.getString("DateOut")%>&nbsp;</td>
                    </tr>
                    <tr>
                      <td height="2"></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                    </tr>
                  </table>
                          <%}%>
                  <%
    connection.close();
%>

There should be two rows that are greated than sysdate but just showing nothing. It is actually going on to the next page. select * from TblPatient_TblBed gives everything. The above query does work in SQL*worksheet
0
 
KuldeepchaturvediCommented:
try
ResultSet rs = stmt.executeQuery("select * from TblPatient_TblBed where DateOut >TO_DATE(SYSDATE,'DD-MMM-YY')");

I was about to post this same thing in your previous question as well
0
 
TimYatesCommented:
Ok, try:

-------------------

<%
    //String user = request.getParameter("patientid");
    //session.setAttribute( "Name", user );
    // Create a result set containing all data from my_table
    Statement stmt = null ;
    ResultSet rs = null ;
    try
    {
        stmt = connection.createStatement();
        rs = stmt.executeQuery("select * from TblPatient_TblBed where DateOut > sysdate");
%>
                   
                    </span><br>
                    </font></p>

                  <table width="100%" border="0">
                    <!--DWLayoutTable-->
                    <tr>
                      <td width="1" height="22">&nbsp;</td>
                      <td width="127" valign="top"><strong>Patient ID</strong></td>
                      <td width="121" valign="top"><strong>Bed ID</strong></td>
                      <td width="118" valign="top"><strong>Assigned ID</strong></td>
                      <td width="114" valign="top"><strong>Date In</strong></td>
                      <td width="56" valign="top"><strong>Date Out</strong></td>
                    </tr>
                         </table>
                    <!--DWLayoutTable-->
                         <%while (rs.next ()){%>
                         
                  <table>
                    <!--DWLayoutTable-->
                    <tr>
                      <td width="1" height="39">&nbsp;</td>
                      <td width="124" valign="top"><%=rs.getString("PatientID")%>&nbsp;</td>
                      <td width="115" valign="top"><%=rs.getString("BedID")%>&nbsp;</td>
                      <td width="1">&nbsp;</td>
                      <td width="117" valign="top"><%=rs.getString("Aid")%>&nbsp;</td>
                      <td width="107" valign="top"><%=rs.getString("DateIn")%>&nbsp;</td>
                      <td width="36" valign="top"><%=rs.getString("DateOut")%>&nbsp;</td>
                    </tr>
                    <tr>
                      <td height="2"></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                      <td></td>
                    </tr>
                  </table>
                      <%}%>
<%
    }
    catch( SQLException ex )
    {
        out.println( "<h1>SQL Exception " + ex.getMessage() +"</h1>" ) ;
        ex.printStackTrace() ;
    }
    finally
    {
        try { if( rs != null ) rs.close() ; } catch( SQLException ex ) {}
        try { if( stmt != null ) stmt.close() ; } catch( SQLException ex ) {}
        try { if( connection != null ) connection.close() ; } catch( SQLException ex ) {}
    }
%>
0
 
TimYatesCommented:
I reckon you're getting an exception thrown...  THat code should tell you if you are...
0
 
KuldeepchaturvediCommented:
and I am telling you most of the time Tim is correct..... ( thats my past experience speaking....:-))
0
 
TimYatesCommented:
>>  and I am telling you most of the time Tim is correct.....

Hehehe, I'm wrong more than I'm right ;-)  I just tend to keep quiet about the wrong answers, and brag about the correct ones ;-)

hehehehe
0
 
act1veAuthor Commented:
it says date format not recognised for:

ResultSet rs = stmt.executeQuery("select * from TblPatient_TblBed where DateOut >TO_DATE(SYSDATE,'DD-MMM-YY')");
0
 
act1veAuthor Commented:
well it worked when i got rid of one of the m. but out put is like thiis:

2005-10-27 00:00:00.0

How can i get rid of time and change date around to dd-mm-yyyy

I use the following to display date:

<%=rs.getString("DateIn")%>

0
 
KuldeepchaturvediCommented:
you can use simple date format to print it properly..

new SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn"))
0
 
TimYatesCommented:
hehe, see, it looks like I was wrong ;-)
0
 
act1veAuthor Commented:
Do i do something like this:

<%=rs.getString(new SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn")))
0
 
TimYatesCommented:
>>  Do i do something like this:

No, like this:

<%= new SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn")) %>
0
 
act1veAuthor Commented:
Getting this mistake:

An error occurred at line: 118 in the jsp file: /sad/patientsduedischarge.jsp

Generated servlet error:
G:\Apache\work\Standalone\localhost\_\sad\patientsduedischarge$jsp.java:134: Class org.apache.jsp.SimpleDateFormat not found.
                out.print( new SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn")) );
0
 
act1veAuthor Commented:
thanks tim that worked treat ill split points because ye both helped
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.