Hacking J-Bird (Version 0.5.2): Accessing J-Bird from Perl using DBD::JDBC

#! /bin/sh

# This script runs the proxy server that is used by DBD::JDBC to access
# databases.  It provides access to the Mckoi database that is used
# by J-Bird.  

# 1) Edit the fields below to reflect the location of the dbd_jdbc.jar file
#    and the home of J-Bird.
# 2) Edit other aspects of the script to your liking.
# 3) Run the script.
# 4) There seems to be no way of stopping the proxy other than using ctrl-C
#    to halt it.

##### Edit the following to suit your needs

			# the DBD-JDBC module jar file
DBD_JDBC_JAR="$HOME/usr/src/DBD-JDBC-0.64/dbd_jdbc.jar"
			# the directory in which J-Bird is installed
JBIRD_HOME="$HOME/J-Bird"
			# the port on which the DBD-JDBC server listens
PORT=37776
			# DBD-JDBC server verbosity: silent, brief, verbose,
			# tedious, abusive
TRACE=brief

##################### Shouldn't need to edit below ##########################

MCKOI_HOME="$JBIRD_HOME/downloads/drivers/Mckoi"
MCKOI_CP="$MCKOI_HOME/mckoidb.jar:$MCKOI_HOME/gnu-regexp.jar"

export CLASSPATH="$DBD_JDBC_JAR:$MCKOI_CP"

DRIVER=com.mckoi.JDBCDriver 

cd "$JBIRD_HOME"

java -Djdbc.drivers=$DRIVER 					\
     -Ddbd.port=$PORT						\
     -Ddbd.trace=$TRACE						\
     com.vizdom.dbd.jdbc.Server

#! /usr/bin/perl

# Example script showing use of DBD::JDBC to access J-Bird.
# This script prints in tab-delimited format region name, locality,
# date, common name and observer for all ticks stored in J-Bird.

# The script is written under the assumption that the DBD::JDBC proxy
# is run from the J-Bird directory.  The script dbd-jdbc-proxy.sh
# runs the proxy from the J-Bird directory as this script expects.

use strict;
no strict "vars";
no strict "subs";
no strict "refs";

use DBI;

			# Name of Mckoi database config file used
			# by J-Bird. Relative path should be OK for everyone 
			# because the DBD::JDBC proxy is run from the J-Bird
			# directory.

my $mckoi_config = "./mckoidb.conf";

my $user = "jbird";
my $pw = "chirp";

			# Host and port on which DBD::JDBC proxy is running
			# No need to change these if no change as been
			# made to dbd-jdbc-proxy.sh.

my $host = "127.0.0.1";
my $port = "37776";
			# Characterset being used on your computer
my $charset = "ASCII";

	# Query region name, locality, date, common name, observer name
	# for all ticks
 
my $query = <<EOQ;
SELECT regions.RegName, trips.Locality, trips.ObsDate, commonnames.Common, 
		observers.ObsName
	FROM commonnames, ticks, trips, regions, observers
	WHERE commonnames.SpecNo = ticks.SpecNo 
		AND ticks.TripNo = trips.TripNo 
		AND trips.Region = regions.RegNo
		AND ticks.Observer = observers.ObsNo
EOQ

	# alternative simple query

# my $query = "SELECT * FROM ticks";

################## You shouldn't need to edit anything below here ############

			# URL for J-Bird database

my $url = "jdbc:mckoi:local://$mckoi_config";

			# DBD::JDBC DSN

my $dsn="dbi:JDBC:hostname=$host;port=$port;url=$url;jdbc_character_set=$charset";

			# ACTION

my $dbh = DBI->connect($dsn, $user, $pw);
my $sth = $dbh -> prepare($query);
$sth -> execute;
while (my @row = $sth -> fetchrow_array) {
	print join("\t", @row), "\n";
}
$sth -> finish;
$dbh -> disconnect;