Persistent to In-memory Database

[Jason]

I'm currently using H2 as a persistent datastore, but would like the ability to use in-memory mode.

I tried updating the connection string from jdbc:h2:[filename] to jdbc:h2:mem:[filename] but this doesn't work.

Do you always have to script from the persistent store to in-memory or is there another way to do this?

[Thomas Mueller]

Hi,

> but this doesn't work.

We would need more details.

Regards,
Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Jason]

Here's the test code I've setup:

        public static final String STD_CONNECTION_STRING = "jdbc:h2:%s;MV_STORE=FALSE";

public static final String MEM_CONNECTION_STRING = "jdbc:h2:mem:%s;MV_STORE=FALSE";

public void run(String connectionString) {

String filepath = new File("./src/main/resources/gpfp").getAbsolutePath();

//setup initial datasource

JdbcDataSource datasource = new JdbcDataSource();

datasource.setUrl(String.format(connectionString, filepath));

datasource.setUser("sa");

//initialize transaction factory & environment

TransactionFactory transactionFactory = new JdbcTransactionFactory();

Environment environment = new Environment("production", transactionFactory, datasource);

//initialize configuration, aliases, and mappers

Configuration configuration = new Configuration(environment);

configuration.addMapper(SelectAllTester.class);

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);

SqlSession session = sqlSessionFactory.openSession();

//do stuff

}

I've tested the STD_CONNECTION_STRING and everything is working just fine, however when I switch to the MEM string I get the following exception:

Caused by: org.h2.jdbc.JdbcSQLException: Error while creating file "mem:C:" [90062-178]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:178)

at org.h2.message.DbException.get(DbException.java:154)

at org.h2.store.fs.FilePathDisk.createDirectory(FilePathDisk.java:264)

at org.h2.store.fs.FileUtils.createDirectory(FileUtils.java:43)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:298)

On Thursday, March 12, 2015 at 1:50:08 PM UTC-4, Thomas Mueller wrote:

Hi,

> but this doesn't work.

We would need more details.

Regards,
Thomas

On Thursday, March 12, 2015, Jason <flor...@gmail.com> wrote:

I'm currently using H2 as a persistent datastore, but would like the ability to use in-memory mode.

I tried updating the connection string from jdbc:h2:[filename] to jdbc:h2:mem:[filename] but this doesn't work.

Do you always have to script from the persistent store to in-memory or is there another way to do this?

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Noel Grandin]

You trimmed off part of the exception. We need that part :-)

[Jason]

Sorry, here's the entire trace.  I'm using Mybatis for object-relational mapping.

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Error while creating file "mem:C:" [90062-178]

### The error may exist in com/mmm/his/cer/framework/pfp/test/access/SelectAllTester.java (best guess)

### The error may involve com.mmm.his.cer.framework.pfp.test.access.SelectAllTester.allApgDx

### The error occurred while executing a query

### Cause: org.h2.jdbc.JdbcSQLException: Error while creating file "mem:C:" [90062-178]

at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:111)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:102)

at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:119)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63)

at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)

at com.sun.proxy.$Proxy2.allApgDx(Unknown Source)

at com.mmm.his.cer.framework.pfp.test.access.TestDatasourceModes.run(TestDatasourceModes.java:57)

at com.mmm.his.cer.framework.pfp.test.access.TestDatasourceModes.main(TestDatasourceModes.java:119)

Caused by: org.h2.jdbc.JdbcSQLException: Error while creating file "mem:C:" [90062-178]

at org.h2.message.DbException.getJdbcSQLException(DbException.java:344)

at org.h2.message.DbException.get(DbException.java:178)

at org.h2.message.DbException.get(DbException.java:154)

at org.h2.store.fs.FilePathDisk.createDirectory(FilePathDisk.java:264)

at org.h2.store.fs.FileUtils.createDirectory(FileUtils.java:43)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:298)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.fs.FileUtils.createDirectories(FileUtils.java:297)

at org.h2.store.FileStore.<init>(FileStore.java:74)

at org.h2.store.FileStore.open(FileStore.java:130)

at org.h2.store.FileStore.open(FileStore.java:111)

at org.h2.engine.Database.openFile(Database.java:523)

at org.h2.store.PageStore.openNew(PageStore.java:303)

at org.h2.store.PageStore.open(PageStore.java:292)

at org.h2.engine.Database.getPageStore(Database.java:2369)

at org.h2.engine.Database.open(Database.java:674)

at org.h2.engine.Database.openDatabase(Database.java:262)

at org.h2.engine.Database.<init>(Database.java:256)

at org.h2.engine.Engine.openSession(Engine.java:57)

at org.h2.engine.Engine.openSession(Engine.java:164)

at org.h2.engine.Engine.createSessionAndValidate(Engine.java:142)

at org.h2.engine.Engine.createSession(Engine.java:125)

at org.h2.engine.Engine.createSession(Engine.java:27)

at org.h2.engine.SessionRemote.connectEmbeddedOrServer(SessionRemote.java:335)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:107)

at org.h2.jdbc.JdbcConnection.<init>(JdbcConnection.java:91)

at org.h2.Driver.connect(Driver.java:74)

at org.h2.jdbcx.JdbcDataSource.getJdbcConnection(JdbcDataSource.java:191)

at org.h2.jdbcx.JdbcDataSource.getConnection(JdbcDataSource.java:162)

at org.apache.ibatis.transaction.jdbc.JdbcTransaction.openConnection(JdbcTransaction.java:134)

at org.apache.ibatis.transaction.jdbc.JdbcTransaction.getConnection(JdbcTransaction.java:61)

at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:279)

at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:72)

at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:59)

at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:267)

at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:137)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:96)

at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:77)

at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:108)

... 7 more

[Fred&Dani&Pandora&Aquiles]

Hi,

This test worked for me with the lastest version.

    private void testPersistentToInMemoryDatabase() throws SQLException {

        deleteDb(System.getProperty("user.home"), "test");

        String[] urls = {

            "jdbc:h2:%s;MV_STORE=FALSE",

            "jdbc:h2:mem:%s;MV_STORE=FALSE"

        };

        JdbcDataSource datasource = new JdbcDataSource();

        datasource.setUrl(String.format(urls[0], System.getProperty("user.home") + File.separator+ "test"));

        datasource.setUser("sa");

        Connection conn = datasource.getConnection();

        JdbcDataSource datasource2 = new JdbcDataSource();

        datasource2.setUrl(String.format(urls[1], System.getProperty("user.home") + File.separator + "test"));

        datasource2.setUser("sa");

        Connection conn2 = datasource2.getConnection();

        conn.close();

        conn2.close();

        deleteDb(System.getProperty("user.home"), "test");

    }

Apparently, you are using an older version (1.4-178, right?), can you test with a more recent version? If the error persists, would be possible to you provide an simple test without external libraries (like mybatis)?

Regards,

Fred

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Thomas Mueller]

Hi,

Could you find out what the final database URL is? It looks like some kind of formatting problem, but I'm not sure.

Regards,

Thomas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Jason]

The datasource path resolves to

jdbc:h2:mem:C:\Users\a2jagzz\workspace\Miscellaneous\.\src\main\resources\gpfp;MV_STORE=FALSE

I was originally using 1.4.178.  I've updated to use 1.4.186 and now it seems to be able to connect, but there is no content within.  The exception I have is

0    [main] DEBUG org.apache.ibatis.logging.LogFactory  - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.

93   [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction  - Opening JDBC Connection

280  [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction  - Setting autocommit to false on JDBC Connection [conn2: url=jdbc:h2:mem:C:\Users\a2jagzz\workspace\Miscellaneous\.\src\main\resources\gpfp user=SA]

296  [main] DEBUG com.mmm.his.h2.SelectAllTester.allApgDx  - ==>  Preparing: select * from APG_DX_DATA 

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 

### Error querying database.  Cause: org.h2.jdbc.JdbcSQLException: Table "APG_DX_DATA" not found; SQL statement:

select * from APG_DX_DATA [42102-186]

### The error may exist in com/mmm/his/h2/SelectAllTester.java (best guess)

### The error may involve com.mmm.his.h2.SelectAllTester.allApgDx

### The error occurred while executing a query

### SQL: select * from APG_DX_DATA

### Cause: org.h2.jdbc.JdbcSQLException: Table "APG_DX_DATA" not found; SQL statement:

select * from APG_DX_DATA [42102-186]

I've tested the connections with and without mem using the web client and found that the standard connection works just fine, but when I add "mem" I get the error I noted above - Error while creating file "mem:C:" [90062-178]

 

[Thomas Mueller]

Hi,

Sorry I only tested with the latest version of H2. I think in previous versions, paths were not supported for in-memory databases. That means, you would need to use a simple name, for example "jdbc:h2:mem:gpfp" or similar.

Regards,

Thomas

--

[Jason]

Not a problem... I rebuilt my database using MEMORY on each table.

What is the difference between mem in the url and MEMORY on the tables?  Is there an advantage to one over the other?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.

[Thomas Mueller]

Hi,

See the documentation at http://h2database.com/html/grammar.html#create_table

Regards,

Thomas

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.