[SaiLee]

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

[blackcompe]

Are you using java.sql.Date?

[pbl]

http://www.dreaminco...-into-database/

[SaiLee]

Yeah it is a Java.sql.date. Pbl, this link doesn't tell me much as I'm not inserting, i'm selecting. Thanks

[pbl]

SaiLee, 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

[SaiLee]

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

[pbl]

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 + "';");  

[SaiLee]

Changed it. Now I get this

SEVERE: null
java.sql.SQLException: ORA-01861: literal does not match format string

[pbl]

Sorry,
Use a PreparedStatement you won't have to worry about the exact syntax, it will do it for you.

[SaiLee]

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

[pbl]

you have

prest.setDate(1, date);

missing

[SaiLee]

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;
    }

[pbl]

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;
}

[SaiLee]

Ah yes, I should have noticed this.

Works fine now. Though the Date class is still quite tricky to use and understand

[pbl]

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