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