Perl DBI

These instructions allow access to up-to-date DBI functionality through the extremely well supported JDBC driver for OpenBase. This set of instructions shows you how to connect to an OpenBase server using the OpenBase JDBC Drivers on MacOS X.

All the software to do this, with the exception of the OpenBase software, is freely available, either from OpenBase (in the case of the JDBC Driver), or from CPAN.

Introduction

DBD::JDBC is a java server application, and a Perl DBD module which allows a Perl module to execute SQL queries and commands through the JDBC interface by means of a JDBC Proxy.

The connection sequence is:

Perl::DBI
| | DBD::JDBC
| |
Java JDBC Proxy Server
| |
JDBC 1.x OpenBase Driver
| |
OpenBase Database Server

Using the PERL DBI interface with OpenBase on Linux

The following implementation instructions are written for bash on linux: Download the DBD::JDBC module or the CPAN Perl module from http://www.cpan.org .

Follow the installation instructions, and copy the dbd_j dbc. j ar file to your java CLASSPATH from its directory.

cp /home/user/DBD-JDBC/dbd_jdbc.jar /path/to/lib/dbd_jdbc.jar

Install the OpenBase JDBC v1.x driver from OpenBase's installation directory to your CLASSPATH.

cp /home/OpenBase/Developer/Interfaces/JDBC1.x/JDBC_Driver/ OpenBaseJDBC.jar /path/to/lib/OpenBaseJDBC.jar

Set your CLASSPATH to include the new jar-files.

export CLASSPATH='$CLASSPATH:/path/to/lib/dbd_jdbc.jar:/path/to/ lib/OpenBaseJDBC.jar'

Start the Java JDBC Proxy Server (change the port as you wish - this is the DBI side of the proxy, not the database one)

/path/to/bin/java -Djdbc.drivers=com.openbase.jdbc.ObDriver
Ddbd.port=12014
Ddbd.trace=tedious com.vizdom.dbd.jdbc.Server &

Create the Perl database handle. (Note, the following is all Perl code. Substitute your values where necessary.)

#!/usr/bin/perl
use DBI;
# note that the URL section of the DSN is a traditional JDBC URL. # the hostname:port is of the JDBC Proxy, not your database server
my $dsn =
"dbi:JDBC:hostname=localhost:12014;url=jdbc:openbase://localhost/ dbname;user=DBUser;pass=DBPass";
my $dbuser = "DBUser";
my $dbpass = "DBPass";
my $dbh = DBI->connect( $dsn, $dbuser, $dbpass );

With that DBH, you have a database handle capable of up-to-date DBI features (data binding, hashref returns, arrayref returns, and so on).

AUTHOR:
Cal S. Goodman < ac.vitiutni|lac#ac.vitiutni|lac >
Intuitiv / Mission Management Information Systems Inc.

CREDIT:
The DBD::JDBC module is written by Gennis Emerson of Vizdom Software, Inc. Without his work, this interface would not have been possible.

Using the PERL DBI interface with OpenBase on Mac OS X 10.2+

You will need to download the OpenBaseJDBC.jar and three PERL modules and install them on the host running the OpenBase server. You will also need to install the PERL modules on the client machines you are using to connect to the OpenBase Database server.

The OpenBaseJDBC.jar file can be obtained from the OpenBase website. Simply download it from the OpenBase website and install it in /Library/Java/Extensions. This is only needed on the host running the OpenBase Database server.

The three PERL modules can be obtained from the Comprehensive Perl Archive (CPAN). The simplest way to obtain these modules is to do the following:

1. Start the the Terminal application (/Applications/Utilities/Terminal) on the host running the OpenBase database server.

2. su to root.
Note: You must have the root account enabled

3. Type "cpan" (without the quotes).
If you have not previously done this, a configuration script will automatically begin to run in order to set this application up for you.

4. Select which of the 50+ CPAN sites to download from.

5. Accept any remaining default settings for the configuration.

After the configuration is complete, you will see the "cpan>" in bold text in the terminal window. You are now ready to download the PERL modules.

1. Enter the command "install <perlmodule>" to download and install a PERL module. This is a case sensitive process.

2. Type "install Bundle: : DBI" (without the quotes) at the cpan> prompt and press return.

Your computer will automatically begin the download and installation process. The module will automatically be put in the correct location on your computer and should already be included in your PATH environmental variable, meaning you should not need to chande anything once the installation is complete.

The download, configuration, and installation have been completed when you see the "cpan>" prompt again.

The next PERL module you need is the Convert: : BER module. Repeat the above process for this module. Upon completion, the next module is the DBD::JDBC module. Repeat the process for this module.

Note: The installation for this module may fail. If this happens, repeat the process except add the force option to the installation.

Do this by entering "force install DBD: :JDBC" at the cpan> prompt.

Once you have completed the download, configuration, and installation of these three modules, enter "q" at the "capn>" prompt to exit this application.

Your internet connection and server load will determine how quickly this entire process will take to complete.

Once you have completed the above process, you need to find a .jar file called dbd_jdbc.jar that was dowloaded with DBD::JDBC modules. It was probably installed in the /Library/Perl/5.8.1/DBD directory. If not, use the find command to locate it. Once you have located it, move it to the /Libaray/Java/Extensions directory (mv /path/to/dbd_jdbc.jar /Library/Java/Extensions/).

Once you have done this, you will need to start the JDBC listener server on the host running the OpenBase Database Server. To do this, enter the following command in the terminal window and press return.

/usr/bin/java
-Djdbc.drivers=com.openbase.jdbc.ObDriver -Ddbd.port=<port_number> com.vizdom.dbd.jdbc.Server

If all has been done correctly, you should now be able to connect to your OpenBase server using the PERL DBI interface. Be sure your OpenBase server is configured to allow network connections. You may need to stop and restart your OpenBase server to ensure this. If you are using a firewall on your OpenBase server, be sure to configure it to allow connections on the port you have specified in the above command string.

To verify you can connect to your OpenBase server, you can execute the following script. You will need to edit it to suit your particular configuration and ensure it is executable on your system. It should display all the PERL DBI drivers it finds on your system and list any databases it finds if it is executed on the host where your OpenBase Server is running. If it does, you should be able to connect to your OpenBase Database server from any other host using a properly installed and configured PERL DBI interface.

#!/usr/bin/perl -w
use DBI; use strict;
my @drivers = DBI->available_drivers();
die "No drivers found\n" unless @drivers;
foreach my $driver(@drivers){
print "Driver $driver\n";
my @data_sources = DBI->data_sources($driver); foreach my $data_source(@data_sources){ print "\nData Source is $data_source\n";
exit;

To connect to your OpenBase Database Server using the PERL DBI, use the following example, after modifying it for your system:

#!/usr/bin/perl -w
use DBI; use strict;
my $dbhDriver = "DBI:JDBC";
my $dbHost = "hostname=<enter your host name or IP address here>"; my $dbPort= "port=<enter the port you selected>";
my $dbURL = "url=jdbc:openbase:\/\/<enter your host name or IP address
here>\/<enter a table name here>";
my $connect_statement = "$dbhDriver:$dbHost;$dbPort;$dbURL"; my $DB_username = "<account name for database selected>"; my $DB_password = "<appropriate password for this account>";
my $dbh = DBI->connect($connect_statement, $DB_username, $DB_password)
or die "Cannot Connect to DB: ",$DBI::errstr,"\n";
<followed by appropriate SQL commannds>
exit;

The O'Reilly book "Programming the Perl DBI" is a valuable resource for learning and understanding this flexible, highly responsive, and free means for using your OpenBase server in your applications.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License