Why is my SQL query failing?

Question

I have this table:

sportman
{
    code int primary key,
    date Date.
}

containing values

code 10,          30,         50.
date 1990-02-15,  1999-02-15, 2010-02-15.

I wrote this query in NetBeans:

 resultSet = statement.executeQuery("select code from sportman "
                                    + "where date =  1990-02-15";

but the resultset is empty. What is the problem and how can I solve it?

 while(resultSet.next())
                      {
                             x = resultSet.getString("code");
                      }

score 1 · Accepted Answer · answered Mar 29 '11 at 14:30

1

1990-02-15 is 1973 and not February 15th 1990.

A useful debugging approach in those situations is to print the entire SQL statement prior to executing it.

A good approach would be to use a PreparedStatement:

PreparedStatement stmt = con.prepareStatement("select code from sportman where start = ?");
stmt.setDate(1, java.sql.Date.valueOf("1990-02-15");
resultSet = stmt.executeQuery();

answered Mar 29 '11 at 14:30

Joachim Sauer

302,674
57
556
614

2

Didn't it? In what way? Be more precise, please. – Joachim Sauer Mar 29 '11 at 14:50
1

@mehdi: no, it most definitely is not. `executeQuery()` will **never** return `null`. It might return an empty `ResultSet` or it might throw an exception, but it will not return `null`. Please post some actual code (= **the code you are running**, not something that "looks similar"). Ideally you'd want to post a [SSCCE](http://sscce.org/). – Joachim Sauer Mar 29 '11 at 14:56
my purpose was same you said.it didn't true any exception and didn't enter into while loop. – Mahdi_Nine Mar 29 '11 at 15:01

score 1 · Answer 2 · answered Mar 29 '11 at 14:43

1

Are you sure that's your actual code? You should be getting a compilation error from the missing parenthesis to your executeQuery() call. Also, 1990-02-15 is not a String. Is that error what you meant by "but resultset is empty"?

answered Mar 29 '11 at 14:43

Pops

30,199
37
136
151

no it's true and when we put **+** ,**1990-02-15** convert to string. – Mahdi_Nine Mar 29 '11 at 14:49
@mehdi, no, it doesn't. It converts to 1973, which _then_ gets converted into a `String`. Which I now see that Joachim pointed out before me, while I was doing something in another tab. – Pops Mar 29 '11 at 14:54
@mehdi, that still clearly won't compile, but let's look at this from another angle. What kind of database do you have? Are you sure it's been populated correctly? Is it a true database, or do you just have a large number of Java objects somewhere else in the program? I'm a little confused because you used that pseudo-Java syntax for representing the columns instead of the more traditional ASCII art table style. – Pops Mar 29 '11 at 15:12

score 0 · Answer 3 · answered Mar 29 '11 at 15:14

You are not telling us which DBMS you are using, but if you are using Oracle, that you probably need to cut off the time part that is part of an Oracle DATE column:

PreparedStatement stmt = con.prepareStatement("select code from sportman where trunc(start) = ?");
stmt.setDate(1, java.sql.Date.valueOf("1990-02-15");
resultSet = stmt.executeQuery();

RollingBoy · Answer 4 · 2011-03-29T16:15:31.593

0

resultSet = statement.executeQuery("select code from sportman "
                                + " where date ='1990-02-15'");

This works.

edited Mar 29 '11 at 16:15

answered Mar 29 '11 at 16:02

RollingBoy

2,767
1
14
8

Why is my SQL query failing?

4 Answers4