Failed to create a table on in-memory sqlite database without any error message

Question

I am trying to create a table on in-memory sqlite database. I have a method called createTable to create a table which initializes a connection to the database with getConnection method.

createTable:

private void createTable() {
    try {
        final Connection connection = getConnection();
        final Statement statement = connection.createStatement();
        statement.execute(
            "CREATE TABLE videos (id INTEGER PRIMARY KEY, title VARCHAR(255), description TEXT, path TEXT, category VARCHAR(255), published INTEGER DEFAULT 0, created TIMESTAMP DEFAULT CURRENT_TIMESTAMP);");
        statement.close();
        connection.close();
    } catch (final SQLException e) {
        LOGGER.warn("Failed to create table.", e);
    }
}

getConnection

private static Connection getConnection() {
    try {
        Class.forName("org.sqlite.JDBC");
        return DriverManager.getConnection("jdbc:sqlite:");
    } catch (final Exception e) {
        LOGGER.warn("Failed to get connection.", e);
        throw new RuntimeException(e);
    }
}

To test the database I wrote a simple insert method:

public void insert() {
    try {
        final Connection connection = getConnection();
        final Statement statement = connection.createStatement();
        statement.executeUpdate(
            "INSERT INTO videos (title, path, category) VALUES ('test title', 'test path', 'test category');");
        statement.close();
        connection.close();
    } catch (final SQLException e) {
        LOGGER.warn("Failed to insert.", e);
    }
}

When I do

this.createTable();
this.insert();

I got the following error:

2017-09-17 22:38:02 WARN  Database:128 - Failed to insert.
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: videos)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.throwex(DB.java:886)
    at org.sqlite.core.NativeDB._exec_utf8(Native Method)
    at org.sqlite.core.NativeDB._exec(NativeDB.java:87)
    at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)...

For connections I am using org.xerial:sqlite-jdbc's v3.20.0.

Any ideas why table is not getting created? I am not seeing any exception or anything. SQLFiddle for the example above seems to be fine.

Does it work if you use the same database connection?

Thilo
– Thilo

2017-09-18 05:45:59 +00:00
Commented Sep 18, 2017 at 5:45 — Thilo
– Thilo, Commented Sep 18, 2017 at 5:45

Thilo · Accepted Answer · 2017-09-18 05:53:07Z

7

If I read the documentation correctly, the database only exists within its database connection by default:

Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

The easiest fix would be to not open a second connection, but keep using the connection that you used to create the table for the other database queries as well.

If you want to open multiple connections, you can set the ?cache=shared parameter on the database url.

answered Sep 18, 2017 at 5:53

Thilo

264k107 gold badges527 silver badges674 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mert Over a year ago

Thanks for a quick response. Changing connection string from jdbc:sqlite: to jdbc:sqlite:?cache=shared solved the problem.

Collectives™ on Stack Overflow

Failed to create a table on in-memory sqlite database without any error message

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related