Connect remotely to an H2 Database using a Java Application

Question

I'm having the following problem: When I try to createTcpServer with my external IP address (the PC's IP and not my local IP = the one we see as an output after running ipconfig in cmd.exe) the following error occurs:

Error with Server: Exception opening port "9092" (port may be in use), cause: "java.net.BindException: Cannot assign requested address: JVM_Bind" [90061-169]

However, the port is not in use. I've checked that using netstat -a -n . I have enabled my external IP and I have disabled the firewall from the router. My external IP can now be pinged.

Please help me.

Update: Here is my code to start the tcp server.

package businessApp;

import org.h2.tools.Server; //imports the server utility

public class startTcpServerForH2 {

    Server server; //the server's instance variable

    private static final String SERVER_IP = "192.168.1.101"; //fixed IP of the server
    private static final String SERVER_PORT = "9092"; //fixed port the server is listening to

    public void tcpServer() { //method responsible to create the tcp server

        optionPane optPane = new optionPane(); //option pane for debugging purposes, shows the server's status

        try { //catches any server related errors, if the connection is broken etc.

            //server uses the IP and port defined earlier, allows other computers in the LAN to connect and implements the secure socket layer (SSL) feature
            server = Server.createTcpServer( //create tcp server
                new String[] { "-tcpPort" , SERVER_PORT , "-tcpAllowOthers" , "-tcpSSL" }).start();

            System.out.println(server.getStatus()); //prints out the server's status
            optPane.checkServerStatus(server.getStatus()); //prints out the server's status on the option pane as well

        } catch(Exception ex){
            System.out.println("Error with Server: " + ex.getMessage());
        }
    }

    public static void main(String[] args){

        startTcpServerForH2 tcpServ = new startTcpServerForH2(); //create a new server object
        tcpServ.tcpServer(); //starts the tcp server
    }
}

Second Update: here is the h2Connection code.

package businessApp;

import java.sql.*; //imports sql features

//Class responsible for connection with H2 Database Engine public class h2Connection {

Connection conn;        //connection variable
DatabaseMetaData dbmd;  /** Metadata variable which include methods such as the following:
                         * 1) Database Product Name
                         * 2) Database Product Version
                         * 3) URL where the database files are located (in TCP mode)
                        */
Statement stm;          //statements variable
ResultSet rst;          //result sets variable

private static final String SERVER_IP = "..."; //here I enter my WAN_IP
private static final String SERVER_PORT = "9092";

public Connection connectionToH2(Connection connt) {

    optionPane optPane = new optionPane(); //create new option pane object
    String outputConn = null; //declare & initialize string which will hold important messages

    try {

        Class.forName("org.h2.Driver"); //Driver's name
        /** The String URL is pertained of the following:
         *  1) jdbc which java implements so that it can take advantage of the SQL features
         *  2) Which Database Engine will be used
         *  3) URL where the files will be stored (as this is a TCP connection)
         *  4) Schema: businessApp
         *  5) Auto server is true means that other computers can connect with the same databse at any time
         *  6) Port number of the server is also defined
         */

        String url = "jdbc:h2:tcp://" + SERVER_IP + ":" + SERVER_PORT + "/C:/Databases/businessApp;IFEXISTS=TRUE";
        System.out.println(url); //prints out the url the database files are located as well as the h2 features used (SSL)
        connt = DriverManager.getConnection(url, "sa", ""); //Driver Manager defines the username & password of the database
        System.out.println(connt.getCatalog()); //prints out the database schema
        optPane.checkServerStatus(connt.getCatalog()); //prints out the database schema on the option pane as well
        connt.setAutoCommit(false); //set AutoCommit to false to control commit actions manually

        //outputs H2 version and the URL of the database files which H2 is reading from, for confirmation
        dbmd = connt.getMetaData(); //get MetaData to confirm connection

        outputConn = "Connection to "+dbmd.getDatabaseProductName()+" "+
                   dbmd.getDatabaseProductVersion()+ " with the URL " + dbmd.getURL()+" was successful.\n";
        System.out.println(outputConn);  //outputs the message on the system (NetBeans compiler)
        optPane.checkH2Connection(outputConn); //outputs the message on top of the frame


    } catch (ClassNotFoundException ex){ //In case there is an error for creating the class for the Driver to be used
        System.out.println("Error creating class: " + ex.getMessage());
    } catch(SQLException ex){ //Any error associated with the Database Engine
        System.out.println("SQL error: " + ex.getMessage());
        optPane.checkServerStatus("SQL error: " + ex.getMessage());
    }
    return connt; //As the method is not void, a connection variable must be returned
}

}

When I want to connect to the h2 database, I make a new h2Connection object and use it to connect. I have followed the H2 manual word by word. What more do you need?

Answer 1

As suggested in the command line help shown below, Protection against Remote Access advises the following:

By default this database does not allow connections from other machines when starting the H2 Console, the TCP server, or the PG server. Remote access can be enabled using the command line options -webAllowOthers, -tcpAllowOthers, -pgAllowOthers.

See the documentation for important caveats regarding these options.

Addendum: Works for me, as long as I restart the Server after opening the firewall; you don't need the setProperty() line at all; the LAN IP to which your WAN_IP forwards port 9092 should be your host IP address; then you can open a shell via your WAN_IP:

java -cp h2.jar org.h2.tools.Shell -url 
    jdbc:h2:tcp://WAN_IP/~/path/to/test;ifexists=true"

Command line help:

$ java -cp .:/opt/h2/bin/h2.jar org.h2.tools.Shell -?
Interactive command line tool to access a database using JDBC.
Usage: java org.h2.tools.Shell 
Options are case sensitive. Supported options are:
[-help] or [-?]        Print the list of options
[-url ""]         The database URL (jdbc:h2:...)
[-user ]         The user name
[-password ]      The password
[-driver ]      The JDBC driver class to use (not required in most cases)
[-sql ""]  Execute the SQL statements and exit
[-properties ""]  Load the server properties from this directory
If special characters don't work as expected, you may need to use
 -Dfile.encoding=UTF-8 (Mac OS X) or CP850 (Windows).
See also http://h2database.com/javadoc/org/h2/tools/Shell.html

$ java -cp /opt/h2/bin/h2.jar org.h2.tools.Server -?
Starts the H2 Console (web-) server, TCP, and PG server.
Usage: java org.h2.tools.Server 
When running without options, -tcp, -web, -browser and -pg are started.
Options are case sensitive. Supported options are:
[-help] or [-?]         Print the list of options
[-web]                  Start the web server with the H2 Console
[-webAllowOthers]       Allow other computers to connect - see below
[-webDaemon]            Use a daemon thread
[-webPort ]       The port (default: 8082)
[-webSSL]               Use encrypted (HTTPS) connections
[-browser]              Start a browser connecting to the web server
[-tcp]                  Start the TCP server
[-tcpAllowOthers]       Allow other computers to connect - see below
[-tcpDaemon]            Use a daemon thread
[-tcpPort ]       The port (default: 9092)
[-tcpSSL]               Use encrypted (SSL) connections
[-tcpPassword ]    The password for shutting down a TCP server
[-tcpShutdown ""]  Stop the TCP server; example: tcp://localhost
[-tcpShutdownForce]     Do not wait until all connections are closed
[-pg]                   Start the PG server
[-pgAllowOthers]        Allow other computers to connect - see below
[-pgDaemon]             Use a daemon thread
[-pgPort ]        The port (default: 5435)
[-properties ""]   Server properties (default: ~, disable: null)
[-baseDir ]        The base directory for H2 databases (all servers)
[-ifExists]             Only existing databases may be opened (all servers)
[-trace]                Print additional trace information (all servers)
The options -xAllowOthers are potentially risky.
For details, see Advanced Topics / Protection against Remote Access.
See also http://h2database.com/javadoc/org/h2/tools/Server.html