In-Memory Database and TCP Server Queries

[Derek Price]

I am new to H2 and was testing it out using a JUnit test and an in-memory database. I would like to be able to query the in-memory database during debugging and I saw another post or the help mentioning to use the TCP Server.

In my test, I have some very basic code like this:

Class.forName("org.h2.Driver");

conn = DriverManager.getConnection("jdbc:h2:mem:test_mem;DB_CLOSE_DELAY=-1", "sa", "sa");

server = Server.createTcpServer(new String[] { "-baseDir", 

     "jdbc:h2:tcp://localhost/mem:test_mem;DB_CLOSE_ON_EXIT=FALSE" }).start();

preparedStatement = conn.prepareStatement("drop table if exists Test;");

preparedStatement.executeUpdate();

preparedStatement = conn.prepareStatement("create table Test(Id int primary key, Name varchar(255));");

preparedStatement.executeUpdate();

preparedStatement = conn.prepareStatement("insert into Test values(1, 'Hello');");

preparedStatement.executeUpdate();

resultSet.next();

assertEquals(resultSet.getInt("Id"), 1);

assertEquals(resultSet.getString("Name"), "Hello");

If I set a breakpoint on the one of the assertEquals(), is it possible to query the database via the TCP server from either the command line, web or external tool like SQL Workbench?

Thanks,

Derek

[Thomas Mueller]

Hi,

The is not the correct way to use the server. You can't supply the database URL when starting the server. Try:

server = Server.createTcpServer().start();

and then connect to the database using the database URL "jdbc:h2:tcp://localhost/mem:test_mem".

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/groups/opt_out.

[Derek Price]

Thanks, Thomas! That worked perfectly. I was able to connect to it via SQL Workbench. 

The only issue I had was keeping the connection open for SQL Workbench to connect to. I ended up just adding a Thread.sleep() where I wanted to debug so I could query the database. If I set a breakpoint, it paused the VM (I'm assuming) which paused access to H2, which is why I tried the sleep() method. Is there a better way?

[Christoph Läubrich]

Why do you assuming this? Of course it depends on your debugging tools,
but most of them should only suspend the responsible Thread(s)

[Thomas Mueller]

Hi,

In Eclipse, you can decide whether you want to suspend all thread or only the current thread. I guess with other IDEs it's the same.

Possibly easier is to add the following line in your code, at the place where you want to look at the database:

    <your code>

    org.h2.tools.Server.startWebServer(conn);

    <your code>

This will start the web server and open a browser window that is connected to the database. The current thread will continue once you disconnect from the database in the browser. See also:

http://h2database.com/javadoc/org/h2/tools/Server.html#startWebServer_Connection

Regards,

Thomas

On Fri, Aug 16, 2013 at 7:36 AM, Christoph Läubrich <lae...@googlemail.com> wrote:

Why do you assuming this? Of course it depends on your debugging tools, but most of them should only suspend the responsible Thread(s)

Am 15.08.2013 21:12, schrieb Derek Price:

Thanks, Thomas! That worked perfectly. I was able to connect to it via SQL Workbench.

The only issue I had was keeping the connection open for SQL Workbench to connect to. I ended up just adding a Thread.sleep() where I wanted to debug so I could query the database. If I set a breakpoint, it paused the VM (I'm assuming) which paused access to H2, which is why I tried the sleep() method. Is there a better way?

--
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.

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/groups/opt_out.

--
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.

[Derek Price]

I'm using IntelliJ with this code:

@Test

public void testH2() throws SQLException {

  Connection conn = null;

  PreparedStatement preparedStatement = null;

  ResultSet resultSet = null;

  Server server = null;

  try {

    Class.forName("org.h2.Driver");

    conn = DriverManager.getConnection("jdbc:h2:mem:test_mem;DB_CLOSE_DELAY=-1", "sa", "sa");

    server = Server.createTcpServer().start();

    preparedStatement = conn.prepareStatement("drop table if exists Test;");

    preparedStatement.executeUpdate();

    preparedStatement = conn.prepareStatement("create table Test(Id int primary key, Name varchar(255));");

    preparedStatement.executeUpdate();

    preparedStatement = conn.prepareStatement("insert into Test values(1, 'Hello');");

    preparedStatement.executeUpdate();

    //debuggingDatabaseSleep(30);

    preparedStatement = conn.prepareStatement("select * from Test order by Id;");

    resultSet = preparedStatement.executeQuery();

    resultSet.next();

    assertEquals(resultSet.getInt("Id"), 1);

    assertEquals(resultSet.getString("Name"), "Hello");

  }

  catch(ClassNotFoundException | SQLException | InterruptedException e) {

    fail(e.getLocalizedMessage());

  }

  finally {

    try {

      if(resultSet != null)

        resultSet.close();

      if(preparedStatement != null)

        preparedStatement.close();

    } catch(Exception e) {

    }

  }

  conn.close();

}

If I stop on a breakpoint and try to access my in-memory database from SQL Workbench, it never connects. If I uncomment  debuggingDatabaseSleep(30) and let the test run, then I can connect from SQL Workbench as long as the test is running with my Thread.sleep() call. 

Should the database still be available after the test has completed? It doesn't seem like it should since once the test is complete, the app is no longer running. So is there a better method? 

[Thomas Mueller]

Hi,

What you could try is replace "debuggingDatabaseSleep(30)" with "org.h2.tools.Server.startWebServer(conn)".

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.

[Derek Price]

That was exactly what I was looking for!

Thanks,

Derek

Thanks,
Derek

--
You received this message because you are subscribed to a topic in the Google Groups "H2 Database" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/h2-database/I4sh2-jhPuQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to h2-database...@googlegroups.com.