org.h2.jdbc.JdbcSQLException: IO Exception: "java.net.UnknownHostException

[dmic...@googlemail.com]

Hi all,

I have been using the H2 database on various occasions and I recently
just installed H2 on a new linux machine but I get a
UnknownHostException as I try to launch the web console by running the
h2.sh:

org.h2.jdbc.JdbcSQLException: IO Exception:
"java.net.UnknownHostException: venustus: venustus" [90028-143]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:
327)
at org.h2.message.DbException.get(DbException.java:156)
at org.h2.message.DbException.convert(DbException.java:271)
at org.h2.util.NetUtils.getLocalAddress(NetUtils.java:250)
at org.h2.server.web.WebServer.updateURL(WebServer.java:274)
at org.h2.server.web.WebServer.init(WebServer.java:264)
at org.h2.tools.Server.<init>(Server.java:48)
at org.h2.tools.Server.createWebServer(Server.java:301)
at org.h2.tools.Console.runTool(Console.java:142)
at org.h2.tools.Console.main(Console.java:89)
Caused by: java.net.UnknownHostException: venustus: venustus
at java.net.InetAddress.getLocalHost(InetAddress.java:1354)
at org.h2.util.NetUtils.getLocalAddress(NetUtils.java:248)
... 6 more
Exception in thread "main" java.lang.NullPointerException
at org.h2.tools.Console.runTool(Console.java:169)
at org.h2.tools.Console.main(Console.java:89)


Where Venustus is the host machine name.

Has anyone ever encountered that?

Thanks
David

[Thomas Mueller]

Hi,

The call to "java.net.InetAddress.getLocalHost()" throws an exception
on your machine. It looks like your computer doesn't support TCP/IP,
or the TCP/IP configuration is broken. See also the javadocs for
InetAddress.getLocalHost(). I'm afraid there is not much H2 can do to
work around this problem.

Regards,
Thomas

[FORGEARD Thierry]

Hi,

I have a problem with the following SQL code (see below: SQL_CODE) that I use for a large set of tables of the same type with PostgreSQL.
The aim is to retrieve for each (mmat, mitem) the records in S1ADR table where "mversion = 0" or, if there is no version 0 for (mmat, mitem), with the max version number for (mmat, mitem).

The error message with H2 is:
org.h2.jdbc.JdbcSQLException: Unsupported outer join condition: "((S1ADR.MMAT = S1IDE.MMAT) AND ((S1ADR.MVERSION = 0) OR (NOT EXISTS(SELECT ''
FROM PUBLIC.S1ADR SA /* PUBLIC.S1ADR.tableScan */
WHERE ((SA.MVERSION = 0) OR (SA.MVERSION > S1ADR.MVERSION)) AND ...

I generate the SQL code by program for many tables of this kind and I'd like to know if you plan to give H2 the possibility to execute this type of LEFT OUTER JOIN condition or if you know a bypassing because I'd like to migrate from PostgreSQL to H2.

Regards.

Thierry Forgeard

/**************************************************************************************************************/

SQL_CODE

-- Employee identity
CREATE TABLE IF NOT EXISTS s1ide
(
mmat integer NOT NULL, -- employee key

name varchar,
sexe varchar,

CONSTRAINT pk_s1ide PRIMARY KEY(mmat)
);

-- salaree adresses
CREATE TABLE IF NOT EXISTS s1adr

(
mmat integer NOT NULL, -- employee key
mitem integer NOT NULL, -- item number (sequential for a date interval)
mversion integer NOT NULL, -- version number

ddeb date, -- date of beginning
dfin date, -- date of end

adr1 varchar,
adr2 varchar,
-- ...

CONSTRAINT pk_s1adr PRIMARY KEY(mmat, mitem, mversion)
);

SELECT * FROM s1ide
LEFT OUTER JOIN s1adr
ON s1adr.mmat = s1ide.mmat
AND (s1adr.mversion=0 OR NOT EXISTS (SELECT '' FROM s1adr sa WHERE
(sa.mmat, sa.mitem) = (s1adr.mmat, s1adr.mitem)
AND (sa.mversion=0 OR sa.mversion>s1adr.mversion)));

/**************************************************************************************************************/

[FORGEARD Thierry]

Hi,

Regards.

Thierry Forgeard

/**************************************************************************************************************/

SQL_CODE

name varchar,
sexe varchar,

/**************************************************************************************************************/

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To post to this group, send email to h2-da...@googlegroups.com.
To unsubscribe from this group, send email to h2-database...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.

[Thomas Mueller]

Hi,

Unfortunately, this outer join condition is not supported yet. In
version 1.3.x they will be supported. Support for such conditions is
already implemented, but disabled by default (only used when the
system property h2.nestedJoins is set to true). However, your test
case doesn't work as expected yet even in this case (H2 returns too
many rows). This will be fixed in the next release. My simplified test
case is:

drop table test;
create table test(id int primary key);
insert into test values(1);
select b.id from test a left outer join test b
on not exists (select * from test c where c.id = b.id);

Regards,
Thomas

[FORGEARD Thierry]

Hi,
Thank you Thomas.
Regards.
Thierry

-----Message d'origine-----
De : h2-da...@googlegroups.com [mailto:h2-da...@googlegroups.com] De la part de Thomas Mueller
Envoyé : samedi 23 octobre 2010 12:24
À : h2-da...@googlegroups.com
Objet : Re: LEFT OUTER JOIN error

Hi,

Regards,
Thomas

--