Re: Connecting to a remote H2 PgServer (Postgres) mode doesn't work, but to a remote TcpServer does work and a local PG Server also works, why?

[Thomas Mueller]

Hi,

What problem do you want to solve by using the PgServer? The PgServer is not needed if you have Java on the client side. If you have Java on the client side, just use the TcpServer and (database URL jdbc:h2:tcp://localhost/~/test).

If you really, really want to connect to the PgServer using a Java client, you would need to use the PostgreSQL driver. That means the driver class is "org.postgresql.Driver" and the database URL is something like "jdbc:postgresql:test". This driver is not included in H2: you would need to download it from http://postgresql.org. But there is no point really in doing that, and I don't even know if it really works.

Regards,

Thomas

On Thu, May 9, 2013 at 11:30 AM, <hamm...@googlemail.com> wrote:

Hi,

I've been able to set up a TcpServer and connect to it remotely (when I say remotely, I still mean on the same machine, but using tcp://localhost:port etc from a different thread) no problem at all. Where I have difficulty is connecting to a PgServer "remotely". I can connect to it locally without a problem, but not "remotely".

So to connect to the TcpServer, I have tried jdbc:h2:tcp://localhost:8192/TestDatabase;PAGE_SIZE=1024;CACHE_SIZE=8192; and it works perfectly.

So then I start the PgServer with the usual parameters, -pgPort, -baseDir, -pgAllowOthers etc.

The URL jdbc:h2:pg://localhost:5435/TestDatabase;PAGE_SIZE=1024;MODE=POSTGRES_MODE;CACHE_SIZE=8192; does not work, so it's obviously interpreting everything after h2: as a filename, so pg:// is not a recognized protocol.

org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: The filename, directory name, or volume label syntax is incorrect"; "pg://localhost:5435/TestDatabase.h2.db" [90031-166]

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

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

at org.h2.message.DbException.convertIOException(DbException.java:315)

at org.h2.store.fs.FilePathDisk.toRealPath(FilePathDisk.java:181)

at org.h2.store.fs.FilePathDisk.toRealPath(FilePathDisk.java:34)

at org.h2.store.fs.FileUtils.toRealPath(FileUtils.java:82)

at org.h2.engine.ConnectionInfo.getName(ConnectionInfo.java:373)

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

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

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

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

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

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

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

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

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

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

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

I've also tried jdbc:h2:tcp://localhost:5435/TestDatabase;PAGE_SIZE=1024;MODE=POSTGRES_MODE;CACHE_SIZE=8192; also does not work.

org.h2.jdbc.JdbcSQLException: Connection is broken: "java.io.EOFException: localhost:5435" [90067-166]

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

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

at org.h2.engine.SessionRemote.connectServer(SessionRemote.java:397)

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

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

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

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

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

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

Finally I tried leaving out the protocol completely, jdbc:h2:localhost:5435/TestDatabase;PAGE_SIZE=1024;MODE=POSTGRES_MODE;CACHE_SIZE=8192;

Same problem as the first case, as it has no protocol, it interprets the whole thing as a filename

org.h2.jdbc.JdbcSQLException: IO Exception: "java.io.IOException: The filename, directory name, or volume label syntax is incorrect"; "localhost:5435/TestDatabase.h2.db" [90031-166]

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

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

at org.h2.message.DbException.convertIOException(DbException.java:315)

at org.h2.store.fs.FilePathDisk.toRealPath(FilePathDisk.java:181)

at org.h2.store.fs.FilePathDisk.toRealPath(FilePathDisk.java:34)

at org.h2.store.fs.FileUtils.toRealPath(FileUtils.java:82)

at org.h2.engine.ConnectionInfo.getName(ConnectionInfo.java:373)

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

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

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

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

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

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

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

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

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

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

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

What *does* work is jdbc:h2:TestDatabase;PAGE_SIZE=1024;CACHE_SIZE=8192;

which has no host and no port.

I'm running on Windows 7, 64 bit, Java 1.6. Version of H2 is 1.3.166

In terms of the exception trace I get, here it is: Any pointers appreciated, I just need the correct format of the URL to connect from a remote client to a PgServer in H2, i.e. include a host and a port?

Paul

--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.