[Date Prev][Date Next][Thread Prev][Thread Next] [Search] [Date Index] [Thread Index]

[MacPerl] When Perl meets Java ...



Hi all,

I'd like to post some insights into Java-based RDBMSs and DBI with 
DBD::JDBC on the Mac, which may be of general interest.  And here is 
the story:


*** About  Java-based RDBMSs

I recently got aware of the existence of 4 (!) Java based RDBMSs, 
which can all be run on our favorite platform. These DBs are free or 
at least freely available for evaluation. The following quoted 
article was originally posted on the MRJ-Dev mailing list:

---

"        Date: Sat, 19 Aug 2000 17:45:46 -0500
           Subject: [OFF] Java-based RDBMS Reviews & Insight
           From: Philip Weaver <philmaker@earthlink.net>
           To: <mrj-dev@public.lists.apple.com>

           August 19, 2000

           I'm writing to discuss Java-based RDBMSs, offer some advice 
and possibly get
           some feedback. Under Mac OS 9.0.4/MRJ 2.2, I've setup four leading
           Java-based RDBMSs and they all run.

               HyperSonic SQL <http://hsql.oron.ch/>
               PointBase <http://www.pointbase.com/products/>
               Cloudscape <http://www.cloudscape.com>
               Instant DB <http://instantdb.enhydra.org/>

           I currently just need a standards compliant database to use 
for development.
           And that may ultimately mean that I must hand over money - 
no big problem.
           But, the point of this article is really about finding a fully JDBC
           compliant RDBMS _server_ that performs well during 
evaluation. I don't want
           to have to startup the database each time I test my 
project. At the moment,
           entry-level SQL-92 vs. full SQL-92 or SQL-99 is a non-issue for me.

           HyperSonic SQL is very fast but v1.42 does not yet 
implement small details
           of JDBC. In my case, I need to call 
DatabaseMetaData.getColumns(). The
           returned COLUMN_SIZE column returns null and I need this information.

           Cloudscape seems to support the JDBC appropriately - at least
           DatabaseMetaData.getColumns() returns meaningful 
information. They have a
           sixty day evaluation for download. This download includes a 
lightweight
           server framework using RmiJDBD. I have reasoned that this 
RMI JDBC driver is
           very slow. Cloudscape does _sell_ another server framework called
           "CloudConnector" which you apparently must buy to evaluate. BTW,
           Cloudscape's documentation is poorly written, too 
ambitious, and is a total
           organizational nightmare.

           InstantDB is a single-user, embedded database. In order to 
make it act as a
           server, you use the RMI JDBC driver (wrapper) as above. 
Using the RMI JDBC
           driver is slow with InstantDB which re-inforces that this 
RMI driver is
           causing the overhead.

           PointBase appears to support the full JDBC and they have a thirty day
           evaluation for download. You may download a network edition 
that has it's
           own networking code. The company and the web site appear to 
be "with it". I
           have no affiliation.

           In summary:
           - HyperSonic SQL does not currently fully implement details 
of JDBCs but it
           is FAST, FAST as a server by default, relatively easy to 
get running after
           switching the code to 1.1, and is open source.
           - Basic server configurations of Cloudscape and InstantDB 
seem to support
           JDBC adequately but are slow using the RMI JDBC driver. You can buy
           CloudConnector for Cloudscape but I have not tested.
           - PointBase is working briskly running in server mode and 
supports JDBC
           adequately.

           I might have continued to use HyperSonic SQL but can not 
currently. If they
           complete the JDBC interface, I may move back. My second 
choice is PointBase
           which is "affordable". I am hopeful.

           Philip J. Weaver   "

---

In the meantime, Cloudscape (owned by Informix) has released a 
personal version 3.5 (single user), which is free for development 
purposes (<http://www.cloudscape.com/FreeDev/fdkreg.html>). It's a 
hefty 10,2 MB download (you have been warned ;), including the full 
documentation. The documentation is indeed hard to understand, 
because it's disorganized. Cloudscape comes with a GUI tool called 
"Cloudview" for retrieving and manipulating data (in standalone 
mode). If you want to use it, you will also need to download the 
JFC/Swing 1.1.1 classes from Sun 
(<http://java.sun.com/products/jfc/download.html>), which is another 
hefty ~ 12 MB download :( (see "What you will need" below). 
Cloudscape is entry-level SQL-92 compliant (with enhancements).

HyperSonic SQL, a 655 KB download including the full documentation, 
is indeed fast. You will get a nice little (albeit simple) GUI tool 
called "DatabaseManager" for retrieving and manipulating data (in 
different modes). The DatabaseManager is very useful, because it can 
be used with all the other databases and doesn't need JFC/Swing. The 
only thing I have to complain about HyperSonic SQL is its limited set 
of SQL statements. The ALTER statement for example isn't supported :(.

InstantDB, a 323 KB download, comes without documentation. The docs 
can be found at 
(<http://instantdb.enhydra.org/software/documentation/index.html>). 
Features supported include joins, transactions, triggers, 
sub-selects, table aliasing. InstantDB comes with two GUI tools 
called "DBBrowser" and "SQLBuilder". DBBrowser isn't very useful, 
it's better to use "DatabaseManager" from HyperSonic instead. 
SQLBuilder needs the JFC/Swing 1.1.1 classes from Sun, which I 
haven't downloaded yet, so I can't say much about this tool.

I haven't downloaded PointBase (and probably I will not), so I can't 
say much about this DB.


*** Back to Perl

As Philip J. Weaver pointed out, accessing the DBs from Java via JDBC 
(Javas ODBC) is possible. But we all like to speak Perl, not Java (or 
at least we prefer Perl over Java :). I was well surprised to see 
that a module/driver DBD::JDBC 0.63, written by Gennis Emerson 
(<gemerson@vizdom.com>), already exits. From the docs: "DBD::JDBC is 
a Perl module which works in conjunction with a server written in 
Java to provide a DBI front end to a JDBC driver. The Perl module and 
Java server may be installed on different machines, as long as socket 
connections are allowed. The Java server portion is multi-threaded 
and supports multiple simultaneous connections."

I was able to set up the Java server and have successfully retrieved 
data from the HypersonicSQL and InstantDB DBs via the DBI API. I 
haven't tried this with Cloudscape yet, but I see no reason why it 
schouldn't work. It's sloooow (a G3 or G4 processor may change that), 
but it works and may be sufficient for development, prototyping or 
educational purposes. Please note: First change your TCP/IP 
configuration to an offline/intranet config (not PPP), then start up 
the Java server by hand and run your Perl script. If the server comes 
up with the "[Server] accepting connections" message in the Java 
console, everything is fine -- if not, something went wrong.


*** What you will need

  + A Macintosh, of course :-))   -- System 8.6 will work
  + Perl 5.004 or higher    -- we've got MacPerl
  + DBI 1.13 or higher   -- here is a catch, see below
  + Convert::BER 1.31   -- available from CPAN
  + Java Virtual Machine compatible with JDK 1.1  -- Apple's MRJ, 
currently at 2.2.2 (JDK 1.1.8),
     2.1.x (JDK 1.1.7) will also work, available from Apple at 
<http://developer.apple.com/java/>
  + A JDK for compiling and running the Java applications   -- Apple's 
MRJ SDK, currently at 2.2.2,
     available from Apple (see above); particularly JBindery will be needed
  + A JDBC driver  -- comes with the DBs
  + JFC/Swing 1.1.1 classes from Sun 
(<http://java.sun.com/products/jfc/download.html>) are
     needed to use some of the Cloudscape and InstantDB tools

DBD::JDBC requires DBI 1.13, which isn't available for the Mac. DBI 
1.08 - Mac is available at <http://pudge.net/mmp/>.  With minor 
changes to JDBC.pm, the Perl part of DBD::JDBC, it's possible to get 
this driver working on the Mac:

-- Line 25 : change    use DBI 1.13;     to     use DBI 1.08;     or 
use DBI;
-- Line 90, 95, 100, 104, 113, 121, 126, 131: change all occurences 
of $h->trace_msg(".....", 3) to
        $h->trace_msg("....."),  i.e. delete the second parameter
-- Line 291, 296, 299 : change all occurences of $drh->trace_msg(".....", 3) to
        $drh->trace_msg("....."),  i.e. delete the second parameter
-- Line 974:  comment out this line >>> return $DBD::JDBC::Types{BIT} 
if $dbi_type == SQL_BIT <<<
       (DBI 1.08 doesn't export the SQL_BIT type)
-- Line 1006: comment out this line >>> return SQL_BIT         if 
$jdbc_type == $DBD::JDBC::Types{BIT}; <<<
       (DBI 1.08 doesn't export the SQL_BIT type)


*** The JDBC-ODBC bridge

The DBD::ODBC driver, available from CPAN, currently doesn't work on 
the Mac (see the Filemaker Pro DB thread). A way around this dilemma 
may be the JDBC-ODBC bridge driver from SUN (it's the only driver 
that comes with the JDK). You will not find many (if any) infos about 
it in the JDK documentation (see the FAQ at 
http://java.sun.com/products/jdbc/faq.html), but it's there (look 
into the JDKClasses.zip archive). The name of the driver is 
sun.jdbc.odbc.JdbcOdbcDriver; the database URL you will need to 
specify is jdbc:odbc:database_identifier, where database_identifier 
generally provides a logical name that is mapped by the database 
administration software to a physical directory where the database 
tables are located (see the docs of your DB).

I haven't used this bridge with the JDBC server that comes with 
DBD::JDBC, because I don't own a ODBC capable DB.  So I can't say 
much about it: It may work or not, it may be to slow for practical 
use etc. Let me know if someone get it to work. A note for testing 
this: Because Java is available on all platforms, one could set up 
the JDBC server with the ODBC-bridge on a Windows machine and connect 
to it from a Mac via a socket (if you are in a network, of course). 
Most of the Windows based DBs are capable "speaking" ODBC (Access for 
example), I guess. It looks promising.


*** A last note

I haven't had the time to figure out all the in and outs yet  -- play 
around by yourself.  But if you ask me, I would go for the 
HypersonicSQL / InstantDB combination: use the DatabaseManager tool 
from Hypersonic and the DB-engine from InstantDB. Both DBs also 
should work fine for playing around with DBI. If you would like to 
get a full featured RDBMS, Cloudscape may be the way to go -- at 
least, it's free (and FileMaker is not ;).


The __END__ :)


Best regards

-- Thomas

# ===== Want to unsubscribe from this list?
# ===== Send mail with body "unsubscribe" to macperl-request@macperl.org