SQL error

[Ravi Harishchandra]

Hi... I'm trying to retrieve the records whose check out dates match with the system date(A hotel room reservation software). But I'm getting the following error java.sql.SQLException: ORA-01722: invalid number

The CODE:::

public abc() throws ParseException
    {
    Connection con = null;
    try
    {
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd/MM/yy");
        Class.forName("oracle.jdbc.driver.OracleDriver");
        con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/XE","system","123");
        Statement st = con.createStatement();
        System.out.println("STAGE !");
        Date date = new Date();
        String dt = dateFormat.format(date);
          
        
        System.out.println(" The date is " + dt);
        ResultSet res = st.executeQuery("select * from CUSTOMER where CUST_TO ="+ dt);// this line is not executing
        while(res.next()){
        String nm = res.getString("CUST_NAME");
        System.out.println(" The name of the customer is  " + nm);
        }
        
    }catch(Exception e)
    {
     System.out.println(e);
    }

[Martin Vashko]

I've added code tags to your post (it does look much better this way) - you can do so yourself, but avoid using other formatting tools (such as colors) in code, it is not supported.

What is the data type of the CUST_TO column? Based on the exception, I'd say it is a NUMBER, so it doesn't even make sense to compare it to a date.

If you really want to put in a date, you should use PreparedStatement. Actually, Rob has already told you so, so please follow the good advice when given to you. This page says why using PreparedStatement is critical, and the JDBC tutorial can help you doing so.

Please read both of these pages (and the complete JDBC tutorial, if you haven't done so yet) before you try to write another database code - it will help you avoid many database-related problems. Of course, if something is unclear to you in the tutorial, feel free to ask questions here - we'll happily answer questions from people that read tutorials

[chris webster]

Just one extra point to add to Martin's excellent advice:

You appear to be using your SYSTEM schema to hold these user tables. Do not do this. You should create a separate user schema for your application tables, and connect to the DB using that. The SYSTEM user has all kinds of extra privileges that you really do not want your application users to have access to. Also, until your run your application with the right user you cannot be sure you have set up access privileges etc properly.