14 Replies - 3769 Views - Last Post: 05 March 2012 - 12:52 PM Rate Topic: -----

#1 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Java SQL Error - Expected Date got Number

Posted 04 March 2012 - 06:24 PM

Hi. I have a quick and simple, self explanatory method here:

    public int noOfReservations(Date date) throws SQLException {
        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) "
                + "FROM RESERVATIONS WHERE RESERVATION_DATE = " + date);
        // Get the number of rows from the result set
        resultSet.next();
        int rowCount = resultSet.getInt(1);
        return rowCount;
    }



It throws this error when I try to use it.
SEVERE: null
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

I don't see how it could expect date and get number. Surely if I'm counting I want a number right?

Please help me to see the error of my ways. Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Java SQL Error - Expected Date got Number

#2 blackcompe  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 1155
  • View blog
  • Posts: 2,533
  • Joined: 05-May 05

Re: Java SQL Error - Expected Date got Number

Posted 04 March 2012 - 06:47 PM

Are you using java.sql.Date?
Was This Post Helpful? 0
  • +
  • -

#3 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 04 March 2012 - 06:57 PM

http://www.dreaminco...-into-database/
Was This Post Helpful? 0
  • +
  • -

#4 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 09:15 AM

Yeah it is a Java.sql.date. Pbl, this link doesn't tell me much as I'm not inserting, i'm selecting. Thanks
Was This Post Helpful? 0
  • +
  • -

#5 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 09:22 AM

View PostSaiLee, on 05 March 2012 - 11:15 AM, said:

Pbl, this link doesn't tell me much as I'm not inserting, i'm selecting. Thanks

Was for the java.sql.Date and java.sql.Date question. Selecting or Inserting is irrelevant

Add a System.out.println(date); at the beginning of your method and let see what it prints
Was This Post Helpful? 0
  • +
  • -

#6 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 09:27 AM

it prints 2012-03-01 which is the date I select from the DatePicker.

I'm calling it from here:

private void showReservationsOnDate() throws SQLException, ClassNotFoundException{
      db.createConnection();
          JOptionPane.showMessageDialog(this, "Dates"
                + db.noOfReservations(new java.sql.Date(txtDate.getDate().getTime())));
      db.closeConnection();
    }




Here is more of the error:
Mar 5, 2012 4:25:46 PM restaurantreservationapplications.MainSystemMenu itmReservationNoActionPerformed
SEVERE: null
java.sql.SQLException: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

This post has been edited by SaiLee: 05 March 2012 - 09:29 AM

Was This Post Helpful? 0
  • +
  • -

#7 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 09:34 AM

At least we now now that it is a sql date :)

I always use prepared statement so I do not have to worry about these details but may be your statement should have ' ' to enclose the date
ResultSet resultSet = stmt.executeQuery("SELECT COUNT(*) " 
             + "FROM RESERVATIONS WHERE RESERVATION_DATE = '" + date + "';");  


Was This Post Helpful? 0
  • +
  • -

#8 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 10:05 AM

Changed it. Now I get this

SEVERE: null
java.sql.SQLException: ORA-01861: literal does not match format string
Was This Post Helpful? 0
  • +
  • -

#9 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 10:09 AM

Sorry,
Use a PreparedStatement you won't have to worry about the exact syntax, it will do it for you.
Was This Post Helpful? 0
  • +
  • -

#10 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 11:05 AM

thanks I will try

Still not working. I get this error:

SEVERE: null
java.sql.SQLException: Missing IN or OUT parameter at index:: 1


    public int noOfReservations(Date date) throws SQLException {
        int records = 0;
        String sql = "SELECT COUNT(*) FROM RESERVATIONS WHERE RESERVATION_DATE = ?";
        PreparedStatement prest = conn.prepareStatement(sql);
        ResultSet rs = prest.executeQuery();
        while (rs.next()) {
            records = rs.getInt(1);
        }
        return records;
    }



I can see why this is happening, its cause of this.... records = rs.getInt(1);

but which method do I use and how do I get the number of records

This post has been edited by SaiLee: 05 March 2012 - 10:59 AM

Was This Post Helpful? 0
  • +
  • -

#11 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 11:08 AM

you have

prest.setDate(1, date);

missing :)
Was This Post Helpful? 0
  • +
  • -

#12 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 11:13 AM

Nope, get this same error

java.sql.SQLException: Missing IN or OUT parameter at index:: 1

New Code:
    public int noOfReservations(Date date) throws SQLException {
        int records = 0;
        String sql = "SELECT COUNT(*) FROM RESERVATIONS WHERE RESERVATION_DATE = ?";
        PreparedStatement prest = conn.prepareStatement(sql);
        ResultSet rs = prest.executeQuery();
        prest.setDate(1, date);
        while (rs.next()) {
            records = rs.getInt(1);
        }
        return records;
    }

Was This Post Helpful? 0
  • +
  • -

#13 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 11:18 AM

You have to fill the ? before doing the query or it will try
WHERE RESERVATION_DATE = ?

public int noOfReservations(Date date) throws SQLException {
    int records = 0;
    String sql = "SELECT COUNT(*) FROM RESERVATIONS WHERE RESERVATION_DATE = ?";
    PreparedStatement prest = conn.prepareStatement(sql);
    prest.setDate(1, date);

    ResultSet rs = prest.executeQuery();
    while (rs.next()) {
        records = rs.getInt(1);
    }
    return records;
}


Was This Post Helpful? 1
  • +
  • -

#14 SaiLee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 37
  • Joined: 19-February 12

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 12:49 PM

Ah yes, I should have noticed this.

Works fine now. Though the Date class is still quite tricky to use and understand
Was This Post Helpful? 0
  • +
  • -

#15 pbl  Icon User is offline

  • There is nothing you can't do with a JTable
  • member icon

Reputation: 8332
  • View blog
  • Posts: 31,857
  • Joined: 06-March 08

Re: Java SQL Error - Expected Date got Number

Posted 05 March 2012 - 12:52 PM

java.sql.Date extends java.utilDate. Beside the fact that its default representation is YYYY-MM-DD there is not much difference and all java.util.Date methods are available
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1