Connecting remote H2 embedded database over SSH

[ArchangelAurora]

Is there a way to connect to an H2 database, which is running in automatic mixed mode (AUTO_SERVER=TRUE) inside a web application, from another machine over SSH tunnel?

Or do I have to use client-server mode and then tunnel, like I would with MySQL server etc.?

[Noel Grandin]

We don't support that at the moment, but it sounds like a reasonable feature, and one that should not be hard to add
(since the JRE already provides most of the building blocks).

Patches are welcome :-)

> --
> 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 <mailto:h2-database...@googlegroups.com>.
> To post to this group, send email to h2-da...@googlegroups.com <mailto:h2-da...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/h2-database.
> For more options, visit https://groups.google.com/d/optout.

[ArchangelAurora]

jdbc:h2:/dbPath/dbName;AUTO_SERVER=TRUE;AUTO_SERVER_PORT=60031

60031 is the port I mapped to remote h2 database over SSH (port number in dbName.lock.db file).

I think this still connects to a local database on my own machine. Something is missing here, I'm getting something wrong.

[rado]

Hello,
for tunnel to work- the client must connect to the port of the tunnel, and the tunnel must connect to the h2 server

[christof...@finaris.de]

Hi,

The following statement returns 0 rows as result, obviously, the result should be one row containing the value 17:

SELECT
 ZEILE_RELATIV
FROM
 (
   SELECT
     ZEILE_RELATIV
   FROM
     (
       SELECT 16 AS ZEILE_RELATIV
       UNION ALL
       SELECT 17 AS ZEILE_RELATIV
       UNION ALL
       SELECT 18 AS ZEILE_RELATIV
       UNION ALL
       SELECT 19 AS ZEILE_RELATIV
     )
   WHERE
     ZEILE_RELATIV BETWEEN 16 AND 18
 )  
WHERE
  ZEILE_RELATIV = 17

The same query using an equivalent IN clause works as expected (1 record as result):

 SELECT
 ZEILE_RELATIV
FROM
 (
   SELECT
     ZEILE_RELATIV
   FROM
     (
       SELECT 16 AS ZEILE_RELATIV
       UNION ALL
       SELECT 17 AS ZEILE_RELATIV
       UNION ALL
       SELECT 18 AS ZEILE_RELATIV
       UNION ALL
       SELECT 19 AS ZEILE_RELATIV
     )
   WHERE
     ZEILE_RELATIV IN (16,17,18)
 )  
WHERE
  ZEILE_RELATIV = 17



Kind regards

Christoff Schmitz

F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main

Fon:      +49 (0)69  / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax:       +49 (0)69  / 254 98 - 50
eMail:    mailto:Christof...@finaris.de
www:      http://www.finaris.de und http://www.rapidrep.com

================================================================================================================
Disclaimer
The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee only and
may contain confidential and/or privileged information. If you have received the message by mistake please delete it and notify
the sender and do not copy or distribute it or disclose its contents to anyone.

FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany
Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl. Inf. Werner Märkl
================================================================================================================

[ArchangelAurora]

Secondary processes that connect to DB has to have direct access to DB files. That's the reason why the tunnel does not work I think :

http://www.h2database.com/html/features.html#auto_mixed_mode  Says all processes has to have access to files.

[rado]

Yes, indeed. AUTO_MIXED mode needs access directly to the file. You can play some tricks by telling it to use samba share and redirecting the samba traffic thru the tunnel, but it is not a simple task at all.

You will be much better if you just use in your client a network mode url:
jdbc:h2:tcp://localhost:<tunel_port>/<database>

If you feel more adventures

[Noel Grandin]

Hmmm, this does not seem to be a BETWEEN issue, since it also fails with
ZEILE_RELATIV >=16 AND ZEILE_RELATIV <= 18

On 2014-12-03 05:21 PM, christof...@finaris.de wrote:
> Hi,
>
> The following statement returns 0 rows as result, obviously, the result should be one row containing the value 17:
>

> *SELECT
> *ZEILE_RELATIV*
> FROM
> **(
> **SELECT
> *ZEILE_RELATIV
> *FROM
> **(
> **SELECT **16 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **17 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **18 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **19 **AS *ZEILE_RELATIV
> *)
> **WHERE
> *ZEILE_RELATIV *BETWEEN **16 **AND **18
> **) **
> WHERE
> *ZEILE_RELATIV *= **17*

>
> The same query using an equivalent IN clause works as expected (1 record as result):
>

> *SELECT
> *ZEILE_RELATIV*
> FROM
> **(
> **SELECT
> *ZEILE_RELATIV
> *FROM
> **(
> **SELECT **16 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **17 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **18 **AS *ZEILE_RELATIV
> *UNION ALL
> SELECT **19 **AS *ZEILE_RELATIV
> *)
> **WHERE
> *ZEILE_RELATIV *IN **(**16*,*17*,*18**)
> ) **
> WHERE
> *ZEILE_RELATIV *= **17*

>
>
>
> Kind regards
>
> Christoff Schmitz
>
> F I N A R I S
> Financial Software Partner GmbH
> Sömmerringstrasse 23
> 60322 Frankfurt am Main
>
> Fon: +49 (0)69 / 254 98 - 24
> Mobile: +49 (0)176 / 206 34 186
> Fax: +49 (0)69 / 254 98 - 50
> eMail: mailto:Christof...@finaris.de

> www: http://www.finaris.de <http://www.finaris.de/>und http://www.rapidrep.com <http://www.rapidrep.com/>

>
> ================================================================================================================
> Disclaimer
> The information contained in this e - mail and any attachments ( together the "message") is intended for the addressee
> only and
> may contain confidential and/or privileged information. If you have received the message by mistake please delete it and
> notify
> the sender and do not copy or distribute it or disclose its contents to anyone.
>
> FINARIS Financial Software Partner GmbH, Sömmerringstr. 23, 60322 Frankfurt/Main, Germany
> Registered at Frankfurt/Main, HRB 52873, Managing Directors: Dipl. Inf. Hermann Friebel, Dipl. Ing. Kai Bächle, Dipl.
> Inf. Werner Märkl
> ================================================================================================================
>

[Thomas Mueller]

Hi,

I couldn't find analyze the problem yet, but I found a simpler case:

This results in 0 rows (should be 1):

select x from (select x from (select 1 as x) 

where x > 0 and x < 2) where x = 1;

select x from (select x from (select x from system_range(1, 9)) 

where x > 2 and x < 4) where x = 3;

This works:

select x from (select x from (select 1 as x) 

where x > 0 and x < 2) where x > 0;

select x from (select x from dual 

where x > 0 and x < 2) where x = 1;

I think this is related to parameters in view. The explain plan for the queries is quite strange.

Regards,

Thomas

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.

[Thomas Mueller]

Hi,

I found the problem, it is relatively easy to fix. Views and derived tables with equality and range conditions on the same columns don't currently work properly. The fix and new test case is now committed. It also makes the code a bit more readable, which is good :-) 

Regards,

Thomas

[christof...@finaris.de]

Hi,

Is there a way to obtain an updated 1.3.176 version containing this fix with help of the svn-repository?

Kind regards

Christoff Schmitz

F I N A R I S
Financial Software Partner GmbH
Sömmerringstrasse 23
60322 Frankfurt am Main

Fon:      +49 (0)69  / 254 98 - 24
Mobile: +49 (0)176 / 206 34 186
Fax:       +49 (0)69  / 254 98 - 50
eMail:    mailto:Christof...@finaris.de

www:      http://www.finaris.de und http://www.rapidrep.com

[Thomas Mueller]

Hi,

I guess you mean a version without the MVStore? Could you append ";MV_STORE=FALSE" to the database URL?

Regards,

Thomas