Skip to main content

Connecting to JDBC data source from OS X perl

Here's another blog post on a similar topic. I recently had to figure it out and I documented it already, so I thought I would share it here too so I don't forget.

Here are instructions on connecting to a JDBC data source from OS X perl. I used DBD::JDBC.

The process mainly involves setting up a local Java server that provides the front end for a JDBC driver. The Java code implements the JDBC connection to the data source. The perl code talks to the Java server on localhost via DBI so it can access the database.

Here are the steps:

1. Check out any documentation about required setup for your JDBC data source. For example, you may need to install an SSL certificate from the server to your client. You may also need to ensure that your server permits database access at all from your client or IP address.

2. Download your JDBC driver and put any .jar files into a lib directory. For example, VJDBC is a JDBC driver that enables you to establish JDBC connections over Java RMI. It comes with vjdbc.jar.

3. Install the perl modules Convert::BER, DBI, and DBD::JDBC. You can either install the modules from CPAN or install ActiveState perl. If you install from CPAN, you'll need to install Xcode first so you have a C compiler. If you use ActiveState, use PPM to install the required modules. Note that you'll still need to download the DBD::JDBC tarball from CPAN and install it manually because it doesn't show up in ppm.

4. Copy dbd_jdbc.jar from the DBD::JDBC tarball into the same lib directory as the other .jar files you've collected.

5. Download the latest version of log4j and copy log4j.jar into the same lib directory as above. You should now have at least 3 jar files: dbd_jdbc, log4j-1.2.16, vjdbc.

6. Set your CLASSPATH variable to point to your jar files. If you want this variable to be always set, add it to your .profile.

7. Startup the Java server with this command:

java -Djdbc.drivers=com.sourcefire.vjdbc.VirtualDriver -Ddbd.port=9001 com.vizdom.dbd.jdbc.Server

Note that I set the port to 9001. This is completely arbitrary and can be whatever you want it to be. But whatever you set this to, make sure you indicate this port in your perl code.

You'll probably see something like this:

log4j:WARN No appenders could be found for logger (com.vizdom.dbd.jdbc.Server).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.

You're seeing this because the code is looking for a log4j config file. Copy log4j.properties to the same directory you started the Java server so this error message won't appear.

After this, your host is now accepting JDBC connections on port 9001.

8. Here's some sample code that should work:

#!/usr/bin/perl

use strict;
use DBI;

my $user = "user";
my $pass = "password";
my $host = "10.10.10.1";
my $port = 9001;
my $url = "jdbc:vjdbc:rmi://$host:2000/VJdbc,eqe";  # Get this URL from JDBC data src

my %properties = ('user' => $user, 
                  'password' => $pass, 
                  'host.name' => $host, 
                  'host.port' => $port);

my $dsn = "dbi:JDBC:hostname=localhost;port=$port;url=$url";
my $dbh = DBI->connect($dsn, undef, undef, 
          { PrintError => 0, RaiseError => 1, jdbc_properties => \%properties })
          or die "Failed to connect: ($DBI::err) $DBI::errstr\n";
my $sql = qq/select * from table/;
my $sth = $dbh->prepare($sql);
$sth->execute();
my @row;
while (@row = $sth->fetchrow_array) {
 print join(", ", @row), "\n";
}

One last point: your URL and JDBC properties hash are configured with the IP address of the actual JDBC data source. But your data source is configured to talk with localhost. So you establish a JDBC connection to the Java server on localhost on port 9001, and the Java server in turn establishes a connection to the actual data source using the URL.

Comments

  1. Child protection against kidnapping is a very specialized top security companies in London
    aspect of security that requires experience in dealing with children. Children bring unique challenges depending on how they are brought up, and because they are quite unpredictable. Some are outgoing and friendly while others are usually uncomfortable in the presence of strangers. Some take only a few hours or days to get used to their bodyguards, while some take months. Our security officers are trained to deal with all children, no matter their unique set of behaviors.

    ReplyDelete
  2. Thank you so much for sharing this. Would love to see more of these in the future. GBWAMOD

    ReplyDelete
  3. Your post is helping me a lot. Its really nice and epic. Thanks a lot for the useful info on this topic. You did it so much well. I love to see more about GBWhatsApp. Keep sharing and updating. Also share more posts with us. Thank you

    ReplyDelete

Post a Comment

Popular posts from this blog

Building a Hadoop cluster

I've recently had to build a Hadoop cluster for a class in information retrieval . My final project involved building a Hadoop cluster. Here are some of my notes on configuring the nodes in the cluster. These links on configuring a single node cluster and multi node cluster were the most helpful. I downloaded the latest Hadoop distribution then moved it into /hadoop. I had problems with this latest distribution (v.21) so I used v.20 instead. Here are the configuration files I changed: core-site.xml: fs.default.name hdfs://master:9000 hadoop.tmp.dir /hadoop/tmp A base for other temporary directories. hadoop-env.sh: # Variables required by Mahout export HADOOP_HOME=/hadoop export HADOOP_CONF_DIR=/hadoop/conf export MAHOUT_HOME=/Users/rpark/mahout PATH=/hadoop/bin:/Users/rpark/mahout/bin:$PATH # The java implementation to use. Required. export JAVA_HOME=/System/Library/Frameworks/JavaVM.framework/Versions/CurrentJDK/Home hdfs-site

Creating a Hackintosh

I've always wanted to create a "Hackintosh", i.e. a standard PC that runs OS X. My PC is over 5 years old so it was time for a refresh. I figured this was the best time to give the Hackintosh a go. Hardware CPU:  Intel Quad Core i7 4790 3.6 Ghz Motherboard:  GIGABYTE GA-Z97-HD3 Audio:  ALC 887 Network: Realtek 8111F-VL Network Card:  4 Antennas 802.11ac WiFi BCM94360CD Wireless Network Card Graphics Card:  nVidia 750 GTX Memory:  Corsair Vengeance DDR3-1600 32 GB (4x8 GB) Hard Drive : Seagate ST3000DM001 3 TB SATA3 7200 rpm DVD:  Samsung SH-224DB 24X BIOS Changes The first step was to change the BIOS settings to support OS X. Disabling VT-d is the only setting that is clearly required; the others are questionable but were done by others so I thought they were worth trying. F7  to load Optimized Defaults M.I.T. Advanced Frequency Settings Extreme Memory Profile (X.M.P.): Enabled Miscellaneous Settings PCIe Slot Configuration:  Gen

Connecting to SQL Server from OS X perl

I've been spending my coding time in the offhours working on Perl instead of Ruby. My coding time in general has been very limited, which is part of the reason for the length of time between updates. :) My latest project is to pull data out of a Microsoft SQL Server database for analysis. I'm using perl for various reasons: I need a crossplatform environment, and I need certain libraries that only work on perl. Some of the target users for my code run on Windows. I know that Ruby runs on Windows but it's not the platform of choice for Ruby developers. The vast majority seem to develop either on OS X or Linux. So Ruby on Windows isn't at the maturity that ActiveState perl is on Windows. In fact, I don't even run native perl anymore on my MacBook Pro. I've switched over to ActiveState perl because I don't need to compile anything every time I want to install new CPAN libraries. And because it's ActiveState, I'm that much more confident it will w