Kotlin Exposed does not commit transaction

Question

Following the example provided on Exposed I am not able to read the created tables/data outside the transaction creating it. I am using h2-in-memory database.

The exception is:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Table "CITIES" not found; SQL statement:

I have added a call to commit but this does not help. If I read the data within the transaction creating the data, as in the example on the link to github, it works fine. Here the a simplified version of it:

fun main(args: Array<String>) {
    Database.connect("jdbc:h2:mem:test", driver = "org.h2.Driver")

    transaction {
        create(Cities)

        City.new {
            name = "St. Petersburg"
        }

        println("Cities: ${City.all().joinToString { it.name }}")
        //I have added this commit here
        commit()
    }
    //I want to read the data outside the transaction, but it does not work
    transaction {
        println("Cities: ${City.all().joinToString { it.name }}")

    }
}

How can I persist the data?

Adding logger.addLogger(StdOutSqlLogger) gives the following output:

SQL: CREATE TABLE IF NOT EXISTS CITIES (ID INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(50) NOT NULL)
SQL: INSERT INTO CITIES (NAME) VALUES ('St. Petersburg')
SQL: SELECT CITIES.ID, CITIES.NAME FROM CITIES

Delphinus

The exception basicaly says that you do not have a table CITIES in database. Are you sure you h2 database created it for you? – Dyeline

Yes, the first read within the transaction creating the table and the row works fine and prints out Cities: St. Petersburg. Table and row were created correctly but for some reason can't be accessed after closing the first transaction. – Delphinus

I look here h2database.com/html/faq.html for some answers. Interesting part for you is part titled "Where are the Database Files Stored?". Can you find these files on your computer? – Dyeline

i am using it as in-memory DB: jdbc:h2:mem:test – Delphinus

Answer 1

It looks like you moved away from in-memory H2 to solve your problem. Be advised that the root of your initial problem was probably because H2 needs to be told to keep your tables for the life of the JVM:

jdbc:h2:mem:test;DB_CLOSE_DELAY=-1

further explained here: H2 in-memory database. Table not found

Adorne

Answer 2

It does commit the transaction. The problem is that when the in memory database is closed then it's deleted and the in memory database is closed under the following circumstances:

By default, H2 closes the database when the last connection is closed

_Source

Here is a diagram so it's easier to understand what happens step by step (when the database is closed it's deleted altogether)

The easiest solution is to simply use a actual file database instead of a in memory one.

Deter

Good to know, thanks for sharing. The file based has been work well for me. – Delphinus

Answer 3

Changing the DB from in memory to Database.connect("jdbc:h2:~/test", driver = "org.h2.Driver") fixed the problem.

Delphinus