H2 database in memory mode cannot be accessed by Console

Question

I am creating an in memory database in H2 database by the following code on servlet context startup

 void initDb() { try { webserver = Server.createWebServer().start(); Class.forName("org.h2.Driver"); Connection conn = DriverManager.getConnection("jdbc:h2:mem:db1;DB_CLOSE_DELAY=-1","SA",""); InputStream in = getClass().getResourceAsStream("script.sql"); if (in == null) { System.out.println("Please add the file script.sql to the classpath, package " + getClass().getPackage().getName()); } else { RunScript.execute(conn, new InputStreamReader(in)); Statement stat = conn.createStatement(); ResultSet rs = stat.executeQuery("SELECT TO_CHAR(bday,'DD/MM/yyyy hh24:mi') FROM TEST2"); while (rs.next()) { System.out.println(rs.getString(1)); } rs.close(); stat.close(); conn.commit(); conn.close(); } //accessed using url jdbc:h2:tcp://localhost/mem:db1 try{ CachedRowSet crs = new DBConnector().executeQuery("select * from test2"); while(crs.next()){ System.out.println("ARGUMENT_NAME:"+crs.getString(1)); // System.out.println(",DATA_TYPE:"+crs.getString("DATA_TYPE")); } crs.close(); }catch(SQLException e){ e.printStackTrace(); } } catch (Exception e) { //this exception gets throws connection failed! System.out.println("Exception initializing memory H2 database"+e); } }

I am later on accessing by url jdbc:h2:mem:db1 in the same JVM, which is working too. But when i want to access it by jdbc:h2:tcp://localhost/mem:db1 it is not working either in the same JVM or in different JVM.

I actually want to run the system in embedded mode and see the contents using the console. If I start the webserver in the same servlet context startup method I am able to see the Console but it is still not connecting to the in memory DB with url jdbc:h2:tcp://localhost/mem:db1.

If I start the server using Command line using

java -cp "WebContent/WEB-INF/lib/h2-1.3.148.jar;hsqldb.jar;%H2DRIVERS%;%CLASSPATH%" org.h2.tools.Console %* and url as 'jdbc:h2:tcp://localhost/mem:db1'

And then try to connect, surprisingly it connects but with no data. Seems like it is creating a seperate server on its own and its a different db. So there is no data.

Thomas Mueller · Accepted Answer · 2011-02-22 15:54:43Z

To make the in-memory database available for another process, you need to start a TCP server in the same process as the database was opened. Example:

package db; import java.sql.Connection; import java.sql.DriverManager; import org.h2.tools.Server; public class TestMem { public static void main(String... args) throws Exception { // open the in-memory database within a VM Class.forName("org.h2.Driver"); Connection conn = DriverManager.getConnection("jdbc:h2:mem:test"); conn.createStatement().execute("create table test(id int)"); // start a TCP server // (either before or after opening the database) Server server = Server.createTcpServer().start(); // .. use in embedded mode .. // or use it from another process: System.out.println("Server started and connection is open."); System.out.println("URL: jdbc:h2:" + server.getURL() + "/mem:test"); // now start the H2 Console here or in another process using // java org.h2.tools.Console -web -browser System.out.println("Press [Enter] to stop."); System.in.read(); System.out.println("Stopping server and closing the connection"); server.stop(); conn.close(); } }

Yes I already got this one up and running. The only difference from your suggestion is the Console part for which I just started the web server webserver = Server.createWebServer().start(); I guess the catch was to start the tcp server in the same process. Same process is very crucial.

Collectives™ on Stack Overflow

H2 database in memory mode cannot be accessed by Console

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related