java.sql.SQLException: no such column and false Period getDays result

Question

I'm a java beginner and struggle with two problems.

1) The SQL Exception: no such column 'Ofen'

This is my Code and I want to get specific data from a SQLite Database called "kleintest.db" with 2 tables "maindata" and "Zahlwertuntertable". maindata contains the 'Ofen' entry as TEXT. The ResultSet rs should generally take all Data from maindata and the ResultSet rs2 should take the weight from Zahlwertuntertable. But running the programm now shows the mentioned error.

public static void readDB() {

    try {
        Statement stmt = connection.createStatement();
        //ResultSet rs = stmt.executeQuery("SELECT * FROM Gewichtsabnahme;");
        ResultSet rs = stmt.executeQuery("SELECT * FROM maindata;");
        ResultSet rs2 = stmt.executeQuery("SELECT * FROM Zahlwertuntertable;");

        while (rs.next()) {
            System.out.println("Ofen = " + rs.getString("Ofen"));
            System.out.println("Platznummer = " + rs.getInt("Zahlwert"));
            System.out.println("Startdatum = " + rs.getString("Startdatum"));
            LocalDate heute = LocalDate.now();
            String Datum = rs.getString("Startdatum");
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("dd.MM.yyyy");
            LocalDate Wägetag = LocalDate.parse(Datum, formatter);
            Period DiffTag = Period.between(heute, Wägetag);
            System.out.format("Tage = " + DiffTag.getDays() + "\n");            //

            System.out.println("Gewicht = " + rs2.getInt("Startgewicht")); 

        }
        rs.close();           
        rs2.close();
        connection.close();

    } catch (SQLException e) {
        System.err.println("Zugriff auf DB nicht möglich.");
        e.printStackTrace();
    }
}

The table maindata contains the following items: Laufnummer Ofen Zahlwert Startdatum

But Laufnummer is just a primary key and should not be retrieved.

2) Next Question is the thing with the Period function. This worked well but as a printed result I'll get P 1D or P 1M 2D what looks slightly confusing. I like to print just the simple amount of days like 45 or 45D and added the getDays() to my DiffTag. Now my result is -1 what makes no sense at all. What's wrong here?

Period DiffTag = Period.between(heute, Wägetag);
        System.out.format("Tage = " + DiffTag.getDays() + "\n");

Thanks for suggestions and links I may have missed. But everything I looked so far didn't point out my specific questions.

Edit your question and include your table definitions, formatted in a minimal way. The error is saying that your `maindata` table does not have a column called `Ofen`. Also, you need to call `rs2.next()` if you want to access that second result set. You probably should not be looping over both at the same time. — Tim Biegeleisen, Mar 06 '18 at 10:46
How was the table `maindata` created/altered? What does `ResultSetMetaData meta = rs.getMetaData(); for (int i = 1; i <= meta.getColumnCount(); i++) System.out.println(meta.getColumnLabel(i));` print? — fabian, Mar 06 '18 at 11:06
BTW: The first parameter of `Period.between` is the start of the period not the end. assuming the dates saved to the db are in the past, you need to swap the parameters. Note though that this `Period` will not allow you to calculate the total number of days, see https://stackoverflow.com/questions/30833582/how-to-calculate-the-number-of-days-in-a-period — fabian, Mar 06 '18 at 12:00
Okay, I added the content of my maindata table and I will change the call for rs2. Will see to the Period thing as well. Thanks for the input. — Sewjin, Mar 06 '18 at 12:10
Please ask a single question per question. Move your second question to a separate question. — Mark Rotteveel, Mar 06 '18 at 12:12
Don’t you just need `ChronoUnit.DAYS.between(heute, wägetag)`? (or the other way around, as @fabian suggested) — Ole V.V., Mar 06 '18 at 12:59

score 1 · Accepted Answer · answered Mar 06 '18 at 12:52

1

You can only have one result set open at a time for a Statement object so when you execute the second query agains "Zahlwertuntertable" the first one gets closed.

So either add another statement or handle one query at a time.

Also, right now it looks strange that you call rs.next() but never rs2.next()

answered Mar 06 '18 at 12:52

Joakim Danielson

43,251
5
22
52

Ahh many thanks. That was the point. I set ResultSet rs2 = stmt.executeQuery("SELECT * FROM Zahlwertuntertable;"); beneath rs.close() and now it works again. And the ChronoUnit.DAYS works as well. – Sewjin Mar 06 '18 at 13:18

java.sql.SQLException: no such column and false Period getDays result

1 Answers1