CODEWITHSUNDEEP
javasqlitejdbc
ejfilip
ejfilip

Reputation: 75

jdbc: get date from sqlite database

I have saved the date in sqlite database as unixepoch value and while trying to fetch the date I'm getting the error: 'No such column exists 'Date'".

Here is how I create the table: 

public void createActivityTable()   {
    Statement stmt = null;

    try (Connection connection = connectionClass.connectToMainDb(true)) {
        stmt = connection.createStatement();
        String table = "CREATE TABLE IF NOT EXISTS EMPLOYEE_ACTIVITY (" +
                "ActivityIndex INTEGER PRIMARY KEY AUTOINCREMENT," +
                "employeeID INTEGER NOT NULL," +
                "Date INTEGER NOT NULL," +
                "PCName TEXT NOT NULL," +
                "Amount REAL," +
                "Activity TEXT NOT NULL," +
                "FOREIGN KEY (employeeID) REFERENCES EMPLOYEES(employeeID) ON DELETE NO ACTION ON UPDATE CASCADE," +
                "FOREIGN KEY (PCName) REFERENCES COMPUTERAVERAGE(PCName) ON DELETE NO ACTION ON UPDATE CASCADE" +
                ");";
        stmt.executeUpdate(table);

        stmt.close();
        connection.close();
    } catch (SQLException e) {
        System.err.println(e.getClass().getName() + ": " + e.getMessage());
        System.exit(0);
    }
}

Here is the insert method:

public void insertProductivity(int employeeID, double amount, String activityType, String pieceType)    {
    Statement stmt = null;

    try (Connection connection = connectionClass.connectToMainDb(true)) {
        stmt = connection.createStatement();
        String query = "INSERT INTO EMPLOYEE_ACTIVITY (employeeID, Date, PCName, Amount, Activity, PieceType) " +
                "VALUES ('" + employeeID + "', strftime('%s', 'now'), '" + User.getLoggedPC().replaceAll("[^a-zA-Z0-9]", "") +
                "', '" + amount + "', '" + activityType + "', '" + pieceType + "');";
        stmt.executeUpdate(query);

        stmt.close();
        connection.close();
    } catch (SQLException e)    {
        e.printStackTrace();
    }
}

Here is the way I try to fetch the date where I get the exception in the rs.next while loop:

public void getStats()    {
    PreparedStatement stmt = null;

    try (Connection connection = connectionClass.connectToMainDb(false))    {
        String query = "SELECT datetime(Date, 'unixepoch') FROM EMPLOYEE_ACTIVITY;";
        stmt = connection.prepareStatement(query);
        ResultSet rs = stmt.executeQuery();


        while (rs.next())   {
            System.out.println(rs.getInt("Date"));
        }

        rs.close();
        stmt.close();
        connection.close();
    } catch (SQLException e)    {
        e.printStackTrace();
    }
}

And i get the next error: 

java.sql.SQLException: no such column: 'Date'
at org.sqlite.jdbc3.JDBC3ResultSet.findColumn(JDBC3ResultSet.java:48)
at org.sqlite.jdbc3.JDBC3ResultSet.getInt(JDBC3ResultSet.java:401)
at employee.stats.StatsHandler.getStatsByYear(StatsHandler.java:24)
at gui.Main.start(Main.java:20)
at com.sun.javafx.application.LauncherImpl.lambda$launchApplication1$162(LauncherImpl.java:863)
at com.sun.javafx.application.PlatformImpl.lambda$runAndWait$175(PlatformImpl.java:326)
at com.sun.javafx.application.PlatformImpl.lambda$null$173(PlatformImpl.java:295)
at java.security.AccessController.doPrivileged(Native Method)
at com.sun.javafx.application.PlatformImpl.lambda$runLater$174(PlatformImpl.java:294)
at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
at com.sun.glass.ui.win.WinApplication.lambda$null$148(WinApplication.java:191)
at java.lang.Thread.run(Thread.java:748)

Upvotes: 0

Views: 519

Answers (1)

Henry
Henry

Reputation: 43738

Your select statement specifies SELECT datetime(Date, 'unixepoch') so there is indeed no column called Date in the result set. You may want to give your column a name, for example:

SELECT datetime(Date, 'unixepoch') AS d

and then fetch column d.

Upvotes: 1

Related Questions