• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

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

0
act1ve
Asked:
act1ve
  • 7
  • 6
  • 3
  • +2
2 Solutions
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
TimYatesCommented:
you need to include java.text

or just do:

<%= new java.text.SimpleDateFormat("MM/dd/yy").format(rs.getDate("DateIn")) %>
0
 
act1veAuthor Commented:
thanks tim that worked treat ill split points because ye both helped
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now