3

I am fairly new to H2 Database. As a part of a PoC, I am using H2 database(version : 1.4.187) for mocking the MS SQL Server DB. I have one application, say app1 which generates the data and save into H2. Another application, app2, needs to read from the H2 database and process the data it reads. I am trying to use Auto Server mode so that even if one of the application is down, other one is able to read/write to/from the database.

After reading multiple examples, i found how to build the h2 url and shown as below:

jdbc:h2:~/datafactory;MODE=MSSQLServer;AUTO_SERVER=TRUE;

Enabled the tcp and remote access as Below:

org.h2.tools.Server.createTcpServer("-tcpAllowOthers","-webAllowOthers").start()

With this, I am able to write to the database. Now, I want to read the data using the h2-web-console application. I am able to do that from my local machine. However, I am not able to understand how I can connect to this database remotely from another machine.

My plant is to run these two apps in an ubuntu machine and I can monitor the data using the web console from my machine. Is it not possible with this approach? How can I solve this ?

Or do I need to use server mode and explicitly start the h2 server? Any help would be appreciated.

Yadu Krishnan
  • 3,492
  • 5
  • 41
  • 80
  • Found the following line in H@ documentation, _Explicit client/server connections (using jdbc:h2:tcp:// or ssl://) are not supported. This mode is not supported for in-memory databases._. See [Automatic Mixed Mode](http://www.h2database.com/html/features.html#auto_mixed_mode). Maybe that is the cause you can't connect to it. It seems that the port is also randomly picked. When using this feature, by default the server uses any free TCP port. The port can be set manually using `AUTO_SERVER_PORT`. – uniknow Apr 08 '16 at 20:55
  • Although the following question starts with an exception [how-to-run-h2-database-in-server-mode](http://stackoverflow.com/questions/9318116/how-to-run-h2-database-in-server-mode) it seems that is is making a connection so it could give you a hint on how the URL should look. – uniknow Apr 08 '16 at 21:10

3 Answers3

3

By default, remote connections are disabled for H2 database for protection. To enable remote access to the TCP server, you need to start the TCP server using the option -tcpAllowOthers or the other flags -webAllowOthers, -pgAllowOthers .

To start both the Web Console server (the H2 Console tool) and the TCP server with remote connections enabled, you will have to use something like below

java -jar /path/to/h2.jar -web -webAllowOthers -tcp -tcpAllowOthers -browser

More information can be found in the docs here and console settings can be configured from here

Saif Asif
  • 5,516
  • 3
  • 31
  • 48
  • Yes, I did that. My confusion is how to access them from a remote machine on web-console. Not getting the Url right – Yadu Krishnan Apr 04 '16 at 10:55
  • My h2 application is running in an ubuntu machine (no UI access, only ssh). The application will insert data to H2, which is a local file in that ubuntu machine. I want to view the data in web-console from my machine. – Yadu Krishnan Apr 04 '16 at 11:01
  • Did you try the url `jdbc:h2:tcp://host_of_server:port/~/datafactory` ? Is firewall properly configured on the server system? Is the port open to the other machine ? – Saif Asif Apr 04 '16 at 11:01
  • Tried that, but not accessible. I am not using Server Mode, using Auto-Mixed-Mode, http://www.h2database.com/html/features.html#auto_mixed_mode. It says u can access from any machine with same url, but unable to understand how to build the url – Yadu Krishnan Apr 04 '16 at 11:06
  • jdbc:h2:tcp://192.168.1.50/datafactory;IGNORECASE=TRUE;AUTO_SERVER=TRUE – Yadu Krishnan Apr 04 '16 at 11:12
  • Its listening on port `80` ? `AUTO_SERVER` mode uses `9090` port by default. – Saif Asif Apr 04 '16 at 11:14
  • tried that, i fixed the port to 8281, and tried below url jdbc:h2:tcp://192.168.1.50:8281/~/datafactory;AUTO_SERVER_PORT=8281. Getting the error as Connection is broken: "java.net.SocketTimeoutException: connect timed out: 192.168.1.50:8281" [90067-191] 90067/90067 – Yadu Krishnan Apr 04 '16 at 12:04
  • This question is also applying to connection problems with H@ automixed mode [h2-mixed-mode-connection-problem](http://stackoverflow.com/questions/4525586/h2-mixed-mode-connection-problem) – uniknow Apr 08 '16 at 21:02
0

Not entirely sure but looking at the documentation and other questions answered previously regarding the same topic the url should be something like this:

jdbc:h2:tcp://<host>:<port>/~/datafactory;MODE=MSSQLServer;AUTO_SERVER=TRUE;

It seems that the host may not be localhost and the database may not be in memory

uniknow
  • 938
  • 6
  • 5
0

Is there a need for the H2 web console?

You can use a different SQL tool using the TCP server you have already started. I use SQuirreL SQL Client (http://squirrel-sql.sourceforge.net/) to connect to different databases.

If you need a web interface you could use Adminer (https://www.adminer.org/) which can connect to different database vendors, including MS SQL, which happens to be mode you're running H2. There is an Adminer Debian package that should work for Ubuntu.

jschiavon
  • 527
  • 5
  • 10