10

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
Mibac
  • 8,990
  • 5
  • 33
  • 57
rustyfinger
  • 199
  • 2
  • 7
  • The exception basicaly says that you do not have a table CITIES in database. Are you sure you h2 database created it for you? – pokemzok Aug 05 '17 at 10:38
  • 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. – rustyfinger Aug 05 '17 at 10:43
  • I look here http://www.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? – pokemzok Aug 05 '17 at 11:18
  • i am using it as in-memory DB: jdbc:h2:mem:test – rustyfinger Aug 05 '17 at 11:25

3 Answers3

17

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

James A Wilson
  • 14,611
  • 5
  • 29
  • 31
3

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)

diagram

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

Mibac
  • 8,990
  • 5
  • 33
  • 57
2

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

Mibac
  • 8,990
  • 5
  • 33
  • 57
rustyfinger
  • 199
  • 2
  • 7