NetWallah has asked for the wisdom of the Perl Monks concerning the following question:

Revered Monks,

I'm looking for working code that shows how to DBI connect to a H2 database.

I have found a few references that claim that the Ppstgres DBD driver (DBD::Pg) can be used for this purpose, but all my attempts to use a H2 database file name with that driver failed.

I believe I need to start the H2 server, and somehow configure it to listen for Postgres protocol on a TCP port, then use the DBD:Pg driver to connect to it .. but I could not figure out the necessary parameters to start the H2 engine to listen for PG protocol.

I would appreciate either an explanation of how this is supposed to work, or known working syntax on both starting H2 appropriately, if necessary, and the connection string.

Peace and seasonal thanks to all.

        "You're only given one little spark of madness. You mustn't lose it."         - Robin Williams

Replies are listed 'Best First'.
Re: H2 database connect in perl
by erix (Prior) on Nov 26, 2014 at 08:38 UTC

    I have found a few references that claim that the Postgres DBD driver (DBD::Pg) can be used for this purpose

    Can you give those references?

    UPDATE:

    I find on the h2 site that the postgres jdbc driver can be used and that there are so-called "compatibility modes". And that one of those compatibility modes is a postgres compatibility mode:

    H2 compatibility modes

    Just for reference, here is the postgres jdbc driver

    P.S.
    Funny how they 'qualify' database features: postgresql gets a glaring red "No" for "Pure Java". Fair enough, but it still made me laugh :)

    I also think that the "No" for postgres' "Linked tables" is wrong. Do not the myriad FDW drivers amount to the same thing?

    The 'In-Memory Mode' and 'Embedded Mode (Java)' seems to be the interesting part of H2.

[reply]
Re: H2 database connect in perl
by poj (Abbot) on Nov 26, 2014 at 16:48 UTC

    On windows 8.1, I manage to connect to a H2 server on locahost with PostgreSQL ODBC driver Unicode(x64) (port 5435) http://www.h2database.com/html/advanced.html#odbc_driver. I had to untick the 'Server side prepare' on Options Datasource->page 2 to execute a simple statement though.

    poj
[reply]
Re: H2 database connect in perl
by NetWallah (Canon) on Nov 27, 2014 at 00:14 UTC
    I have made what I'd like to call some progress, but the connection still hangs.

    I installed the jdbc Postgres driver (postgresql-9.3-1102.jdbc4.jar) from http://jdbc.postgresql.org/download.html.

    Modified the run BAT script to : 

    SET H2DRIVERS= -pg -tool
SET CLASSPATH=-cp "D:\Program Files (x86)\H2\bin\h2-1.3.175.jar
SET CLASSPATH=%CLASSPATH%;D:\some_path\postgresql-9.3-1102.jdbc4.jar"
SET H2FLAGS= -webAllowOthers -tcpAllowOthers -url jdbc:h2:file:AvCloud
+LiteDB -driver org.postgresql.Driver

java  %CLASSPATH% org.h2.tools.Console %H2DRIVERS% %H2FLAGS%

    [download]
    This brings up the h2 server, and apparently listens on some random port. I find the port using "netstat-an".

    Then try to run this perl - which now hangs.: 

    use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:Pg:dbname=AvCloudLiteDB;host=127.0.0.1;por
+t=60077;",
                      ,, #$username,      $password,
                      {AutoCommit => 0, RaiseError => 1, PrintError =>
+ 1}
                     )
  or die "Cannot  - db:$DBI::errstr";
print "Connected..\n";
my  $sth = $dbh->prepare("SELECT * from GRIDS");

while (my $h = $sth->fetchrow_hashref){
   print "$_=$h->{$_}; " for keys %$h;
   print "\n";
}
close $dbh;

    [download]

            "You're only given one little spark of madness. You mustn't lose it."         - Robin Williams

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom