Connecting to SQL Server from Perl on Linux

One of the tasks I recently had to do at the office was to get a program written in Perl and running on Linux to connect to and work with Microsoft's SQL Server. I thought that I would document the process here on my blog, in the off chance that it might help someone, and also so that I have a reference I can go back to in the future.

So, I went through the process of installing the latest stable FreeTDS driver. If you get the tarball and install using the 'configure, make, make install' method the files are installed under the '/usr/local' directory.

To create a connection to your SQL Server you have to modify the '/usr/local/etc/freetds.conf' file and add the details of your server. For example here is what the details of a SQL Server 2008:

# SQL Server 2008
[sql2008]
host = 231.321.452.871
port = 1433
tds version = 8.0

In order to test the connection you can use the 'tsql' command found under '/usr/local/bin' by passing it the Server, Username and Password variables:

tsql -S sql2008 -U [username] -P [password]

Note that if the connection is successful, you should get a very simple prompt (something like '1>') from which you can exit using the 'quit' or 'exit' commands.

So, now that we know that we have the FreeTDS driver working, we can set about getting access to this interface programatically, i.e. from our program.

For the next step we're going to install the DBD::Sybase module from CPAN and configure it to work with FreeTDS. When I first tried this I attempted to run the following command:

sudo cpan DBD::Sybase

This however, resulted in the following error:

Please set SYBASE in CONFIG, or set the $SYBASE environment variable at Makefile.PL line 103, <in> line 45.

This was a relatively simple fix which involved going into the '/root/.cpan/build/DBD-Sybase-1.09/CONFIG' file and changing the line:

SYBASE=$ENV{SYBASE}||'/opt/sybase'

To:

SYBASE=/usr/local

After making the change, run the command 'perl Makefile.PL' and just leave all of the values set to the default and when it comes to the part where it asks you for the server, database, username and password in order to test the install, enter the credentials for your server.

The next part is to simply run the 'make' command, which will go and compile the module. Trying this gives the following errors:

dbdimp.c:786: error: ‘BLK_VERSION_150’ undeclared (first use in this function)
dbdimp.c:786: error: (Each undeclared identifier is reported only once
dbdimp.c:786: error: for each function it appears in.)
dbdimp.c:790: error: ‘BLK_VERSION_125’ undeclared (first use in this function)
dbdimp.c:794: error: ‘BLK_VERSION_120’ undeclared (first use in this function)


In order to fix these errors, simply add the following lines after any #include statements in the 'dbdimp.c' file:

#define BLK_VERSION_150 BLK_VERSION_100
#define BLK_VERSION_125 BLK_VERSION_100
#define BLK_VERSION_120 BLK_VERSION_100

Run 'make' again, and this time it should build without any errors (although not without warnings). The next step is to run the 'make test' command to test the compiled code. Again, you should note that I've never gotten the code to pass all of the tests, my theory is that some of the tests are specific to a Sybase database and fail with SQL Server.

Once 'make test' finishes, run 'make install', which simply copies the compiled files accross to the correct place in the filesystem. Now, to test that what we've just done works, take the following code, paste it into a file, update it for you database and see if you get the correct return values:

#!/usr/bin/perl

use DBI;

$dsn = 'DBI:Sybase:server=sql2008';

my $dbh = DBI->connect($dsn, '[username]', '[password]');
die "Unable to connect to server $DBI::errstr" unless $dbh;

$dbh->do('USE [database]');

$query = 'SELECT * FROM [table]';
$sth = $dbh->prepare ($query) or die 'prepare failed\n';
$sth->execute() or die "unable to execute query $query error $DBI::errstr";

$rows = $sth->rows ;
print "$rows rows returned by query\n";

while ( @first = $sth->fetchrow_array ) {
foreach $field (@first) {
print "$field, ";
}
print "\n";
}

That's it! Hopefully you're able to access SQL Server through Perl running on Linux now. For more information please have a look at the links below:
http://www.perlmonks.org/?node_id=392385
http://www.n3ncy.net/UNIX/FreeBSD/FreeTDS.htm
http://lists.ibiblio.org/pipermail/freetds/2006q3/020583.html

Getting QPID running on Ubuntu

After reading Joshua Kramers article in the November 2009 issue of Linux Journal, I decided to try out AMQP and see how easy it was to set up and get going.

Firstly, the article doesn't mention:

* You need dependencies, namely valgrind, libboost-dev, uuid-dev
* the version of g++ that comes with Ubuntu 9.04 (g++-4.3) doesn't work with qpid-0.5, so you need to change the symlink to point to the older g++4.2 version
* the python tests fail due to an obscure line which goes something like:
if isinstance(sock, socket.SSLType):
AttributeError: 'module' object has no attribute 'SSLType'
* When trying to install the python modules I had a probem with the setup.py script and ended up commenting out the 'scripts=["amqp-doc"]' argument to the setup method due to the fact that it complained about not being able to find the script.
* Trying to run the 'Listing1.py' code resulted in it not being able to find the AMPQ spec, even after I had set the environment variable $AMPQ_SPEC to point to the correct file. Eventually I just hardcoded the value by editing the '/usr/local/lib/python2.6/dist-packages/qpid/spec.py' file and setting 'amqp_spec = "/usr/local/share/qpid/specs/amqp.0-10-qpid-errata.xml"' instead of the previous version which was ' amqp_spec = os.environ["AMQP_SPEC"]'

I haven't been able to find anything else on this error, with the exception of other frustrated qpid users.

Processes as trees

Just found this nice little command which displays all running processes as a tree structure. Actually, the command is just good ol' ps, but with the axf arguments. i.e.

# ps axf
...
...
8923 ?        Ss     0:00 /bin/sh /command/svscanboot
8925 ?        S      0:00  \_ svscan /service
10888 ?        S      0:00  |   \_ supervise qmail-send
13089 ?        S      0:00  |   |   \_ qmail-send
13097 ?        S      0:00  |   |       \_ qmail-lspawn ./Maildir/
13099 ?        S      0:00  |   |       \_ qmail-rspawn
13100 ?        S      0:00  |   |       \_ qmail-clean
10889 ?        S      0:00  |   \_ supervise log
13088 ?        S      0:00  |   |   \_ /usr/local/bin/multilog t /var/log/qmail
10890 ?        S      0:00  |   \_ supervise qmail-smtpd
21951 ?        S      0:00  |   |   \_ /usr/local/bin/tcpserver -v -R -l smtp1.smartmailpro.co.nz -x /etc/tcp.smtp.cdb -c 20 -u 502 -g 501 0 25 /var/qmail/bin
10891 ?        S      0:00  |   \_ supervise log
13091 ?        S      0:00  |       \_ /usr/local/bin/multilog t /var/log/qmail/smtpd
8926 ?        S      0:00  \_ readproctitle service errors: .................................................................................................

nVIDIA CUDA

Started a new project in my spare time. Trying to add CUDA functionality to rarcrack, a password recovery program for rar files after finding an old encrypted rar file which I forgot the password to. RAR uses 128-bit AES as far as I know, should be interesting to see what this CUDA technology is all about and how easy it is to use.

CentOS, Nagios and SELinux

I've just finished installing Nagios on our CentOS box. I kept on running into errors when trying to get Nagios to start. The errors weren't very specific, merely stating

Starting nagios:CONFIG ERROR! Start aborted. Check your Nagios configuration.

This confused the crap out of me, mostly because of the lack of information in the error messages. So after a few hours of googling, I found that the problem was with SELinux. This is the fourth or fifth time that this piece of software has gotten in the way of installs. It's no wonder then that the first piece of advice when telling someone on a forum/mailing list is to give the advice to set 'SELINUX=disabled'.

This ofcourse ends up voiding any security advantage that SELinux may have had, but apparently everyone seems to think that Linux is secure enough.

Linux and Remote Desktop

Recently we've installed a remote server in North America. In order to set up some Windows boxes, we figured it would be easier to download the DVD images from the servers themselves. This posed a problem however, for several reasons:

1. XenServer requires that any Linux installs be specially compiled for Xen. This is due to the way that the Xen guys wrote the code for the HyperVisor. From my understanding (and without going through the lkml) the code was not very 'clean' and/or did not conform to the kernel coding guidelines. This resulted in the Xen code not being available in the standard kernel that comes with most distributions.

2. Figuring out how to add an ISO repository and actually have its contents come up when installing a new VM is harder than it probably should be on Xen. When trying to figure this out, I found plenty of examples of how to add a new iSCSI/CIFS repository to the XenServer, but nothing on how to set up an ISO repository on the local storage.

3. Apparently there's also a way of uploading a Windows XP VM, but this was more complicated to get going than adding the local ISO repository.

Due to these problems it was decided to take one of the existing distributions (Debian Lenny) and install a desktop environment and enable Remote Desktop functionality through the use of one of the below listed technologies. After pissing about for two/three days with these protocols I can honestly say that the NX protocol and the binaries available off the NoMachine site are the easiest to configure and the best working solutions. This is kind of disappointing for me, since I would have loved to see a FLOSS solution out do a propriatary one, but as a programmer you have to give credit where credit is due.

When it comes to Remote Desktop functionality for Linux, there are a few options regarding protocols:

* XDMCP - Basically the original X idea of remote displays hooked up to a mainframe. Couldn't get it to work for the life of me. Also, found a known, unfixed bug whereby enabling remote login makes the gdm listen only to IPv6 addresses
* VNC - Hard to set up and slow
* RDP - See VNC
* NX (FreeNX) - Awesome. Although FreeNX is a real bastard to set up

UPDATE: Found out that Google released their own NX Server called Neatx.

Zend CE Server on CentOS

I just finished installing Zend Server Community Edition on CentOS. One of the problems encountered along the way was that starting Apache would give an error saying that it did not have the necessary permissions to access '/usr/local/zend/lib/apache2/libphp5.so'. This is due to SELinux not allowing the httpd process access to this file. After searching around for a solution, I found that most of the forum posts gave the advice to either switch SELinux to permissive mode or to disable it altogether. To me, this was a bit like throwing the baby out with the bathwater, so after reading up a bit on SELinux, I figured an easier (and more secure) solution was to just add this file to the 'httpd_modules' context. So, here your are:

chcon -t httpd_modules_t /usr/local/zend/lib/apache2/libphp5.so

This command adds the Zend php binary to the 'httpd_modules' context, allowing Apache to load it and start up normally.