Strange SQLException: Column not found

Question

I'm getting a weird SQLException on a function I run against a database using JDBC. SQLException: Column 'Message' not found.

I have this in my function:

    st = con.prepareStatement("SELECT NotificationID,UserIDFrom,UserIDTo,Message,Timestamp,isNotified FROM notification WHERE UserIDTo=? AND isNotified=?");
    st.setInt(1, _UserID);
    st.setBoolean(2, false);
    System.out.println("st is: " + st);
    rs = st.executeQuery();

And I got that error, so I added this after the st.executeQuery() :

    ResultSetMetaData meta = rs.getMetaData();
    for (int index = 1; index <= meta.getColumnCount(); index++) {
        System.out.println("Column " + index + " is named " + meta.getColumnName(index));
        }

And when I run my code again this is what I get as a result:

Column 1 is named NotificationID
Column 2 is named UserIDFrom
Column 3 is named UserIDTo
Column 4 is named Message
Column 5 is named TimeStamp
Exception in thread "main" java.sql.SQLException: Column 'Message' not found.
Column 6 is named isNotified

And here is a screenshot of my table's design, from MySQL Workbench

And the data in the table

I really can't figure out what's going one here.... Anyone can help out?

EDIT
I've replaced the * in the SELECT statement just to add something to the question that I just noticed.
If I remove the Message column from the select then I get the same error for the TimeStamp column. And if I remove both columns I get no errors then.

EDIT2
OK,this is the part i get the errors, i get both on Message and Timestamp:

while (rs.next()) {
        NotificationID = rs.getInt("NotificationID");
        System.out.println("NotificationID: " + NotificationID);

        SenderID = rs.getInt("UserIDFrom");
        System.out.println("SenderID: " + SenderID);
        From = findUserName(SenderID);

        try {
            body = rs.getString("Message");
            System.out.println("body: " + body);
        } catch (Exception e) {
            System.out.println("Message error: " + e);
            e.printStackTrace();
        }

        try {
            time = rs.getString("Timestamp");
            System.out.println("time: " + time);
        } catch (Exception e) {
            System.out.println("Timestamp error: " + e);
            e.printStackTrace();
        }
    }

I get the error on the getString() methods for each column
StackTrace for TimeStamp(the same for Message):

java.sql.SQLException: Column 'TimeStamp' not found.
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
    at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1167)
    at com.mysql.jdbc.ResultSetImpl.getString(ResultSetImpl.java:5733)
    at NotifyMe_Server.Database.getUnNotified(Database.java:444)
    at tests.Tests.main(Tests.java:39)

Kaufman

do you have any triggers on the table? – Allo

Do you get the error if you manually set the Int/Boolean in the SQL statement? I.e., try "SELECT * FROM notification WHERE UserIDTo=1 AND isNotified=0" and execute it without setting the PreparedStatement variables. – Petrel

could you have some whitespace or weird character in the column name? – Dizzy

@abmitchell I've tried it the way way you said but I still got the same error – Kaufman

@Allo I have set 2 foreign Keys UsersIDFrom,UserIDTo and set CASCADE both on update and on delete, if that's what your are asking – Kaufman

@Dizzy I've thought of that so I went and deleted the name, and write it again but even workbench prompted me with "no changes detected" – Kaufman

can you run "select Message from notification limit 10" in workbench successfully? If so it's something in the application layer, so try debugging into the jdbc (or whatever you're using). – Dizzy

@Dizzy I can run the query you proposed successfully, so you suggest I take a look at the rest of my code? – Kaufman

I suggest you debug into jdbc code to where the exception is thrown and try to track down the comparison that is failing that you expect to succeed. The problem is likely in your code/config but debugging into jdbc will help you understand what's not lining up. – Dizzy

Did you ever solve this? Did you try using a different get method on the date column to see if that changed anything? – Hesson

I would say that the "getString" function cases your excceptions. It's an invalid object cast exception or so, but since you are using the SQL lib the original exception is truncated. Try to cast the correct object to your field. – Pseudonymous

Answer 1

If you observe your code

try {
        time = rs.getString("Timestamp");
        System.out.println("time: " + time);
    } catch (Exception e) {
        System.out.println("Timestamp error: " + e);
        e.printStackTrace();
    }
}

you have used "Timestamp" in this format but if you changed it to "TimeStamp" as specified in your database, hopefully it will work.

Min

Answer 2

Change datatype of your isNotified column as TINYINT in database and retry to insert

isNotified TINYINT(1)

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

Fascinator

I don't see a boolean option in the datatype, in MySQL EDIT btw, I have the same type of field in another table, and i use the 'setBoolean()' in my java code and it works fine – Kaufman

Which version of MSQL you are using? – Fascinator

Not sure why you suggest this, I use Bit(1) all over the place and haven't had problems. – Dizzy

From the MySQL command line: Server version: 5.6.13-log MySQL Community Server (GPL) – Kaufman

Answer 3

Can you change

System.out.println("Column " + index + " is named " + meta.getColumnName(index));

to

System.out.println("Column " + index + " is named '" + meta.getColumnName(index) + "'");

so that we can see if there is whitespace in the "Message" column name?

The fact that the error message comes between column 5 and 6 is not important I think, because one is Standard Output and the other one Standard Error, these are not synchronized output streams.

(Also see the previous answer about Timestamp vs TimeStamp.)

Uganda

Answer 4

It sounds like the table metadata is corrupt. You should be able to correct this by dropping and recreating the table, although if the metadata is really borked you may not be able to drop the table. If that's the case or you need to keep the data, backing up and restoring the whole database is the way to go, but check the SQL dump file before restoring and/or restore to another database name before dropping the broken database. Depending on exactly what's wrong, your problem columns may be missing from the dump.

If refreshing the database is not an option there are ways to perform targetted repairs, but I'm no expert so I can't advise you on that. Again, back up your database AND verify that the backup is complete (i.e. it has all your columns) before proceeding. If this is a production database, I would be very wary about taking advice from the internet on manipulating metadata. Minor differences in version, storage engine and environment can make or break you with this stuff, and given the nature of the problem you can't do a dry run.

Mulkey