Use Perl on Solaris to talk to Microsoft SQL Server without root privileges
OR
How to install DBI, DBD::Sybase, and FreeTDS on Solaris without root privileges
1. GCC
A. Download a prebuilt compiler
I grabbed the gcc_small-3.4.2-sol8-sparc-local.gz package from sunfreeware. It has a dependancy on libiconv-1.8-sol8-sparc-local.gz, so grab that too.
gunzip gcc_small-3.4.2-sol8-sparc-local.gz
gunzip libiconv-1.8-sol8-sparc-local.gz
B. Extract the packages
You must be root to use pkgadd, but it turns out pkgtrans will extract a pkg for you.
pkgtrans libiconv-1.8-sol8-sparc-local . SMCiconv # here, SMCiconv is the name of the package. If you don't know, just leave it off the command and you will get a menu
pkgtrans gcc_small-3.4.2-sol8-sparc-local
C. Copy files into place
This extracts the packages to directories with the package name. Within those directories are 2 files and a subdirectory. pkginfo and pkgmap contains some package information. The reloc directory contains the files we want.
cp -ri SMCiconv/reloc/* .
cp -ri SMCiconv/reloc/* .
I like to use the -i command so that I know if files in these packages are overwriting each other. I’m copying them to ., the current directory, you can just as easily copy them to some install path, but I don’t mind keeping a bin, doc, etc, info, lib, libexec, share, include, man in my home directory. Remember we aren’t using any special permissions so /var/tmp or $HOME are probably the only places to which we can write files.
D. Configure the package
So now we have ~/bin/gcc… but take a look, it isn’t executable! It turns out this is what information the pkgmap file holds.
grep "^1 f" SMC*/pkgmap | awk '{print "chmod "$5" "$4}' | sh #set file modes from pkgmap.
Another important step done by pkgadd from the pkgmap is creating symbolic links.
grep "^1 s " SMC*/pkgmap | awk '{print $4}' | awk -F= '{print "ln -s "$2" "$1}' | sh
2. Get a development environment
From this point on, I like to have a develop environment defined in my shell. I source a shell script which looks like this.
PREFIX=$HOME
PATH=$PREFIX/bin:/usr/ccs/bin:/usr/bin:/bin:/usr/sbin:/sbin:/usr/local/bin:/usr/sfw/bin:/usr/local/bin:/usr/local/sbin:/usr/openwin/bin
PERLLIB=$PREFIX/lib/perl
LD_LIBRARY_PATH=$PREFIX/lib
MANPATH=$PREFIX/man:/usr/share/man:/usr/man:/usr/local/man:/usr/sfw/man
PERLLIB=$PREFIX/lib/perl:$PREFIX/lib/perl/sun4-solaris-64int
export PATH
export LD_LIBRARY_PATH
export MANPATH
export PERLLIB
3. Freetds
If you have ever built source packages which use the autotools system, this is straightforward. Just be sure to set your PREFIX.
Download freetds from www.freetds.org.
gunzip -dc freetds-stable.tgz | tar -xf - # Extract the tarball
pushd freetds-0.63 ; ./configure --prefix=$HOME && make && make install ; popd
4. DBI
Download DBI-1.50.tar.gz from dbi.perl.org. It depends on Test-Simple, so get that from CPAN.
gunzip -dc Test-Simple-0.62.tar.gz | tar -xf -
pushd Test-Simple-0.62
perl Makefile.PL PREFIX=$HOME/src INSTALLSITELIB=$HOME/lib/perl && make install
popd
gunzip -dc DBI-1.50.tar.gz | tar -xvf -
pushd DBI-1.50
PREFIX=$HOME/src CC=gcc LD=gcc PERLLIB=$PERLLIB perl Makefile.PL
perl -pi -e "s/ cc$/ gcc/;s/-xO3 -xdepend/-O3/;s/ -KPIC/ -fPIC/;s@INSTALLSITELIB = .*\$@INSTALLSITELIB = $HOME/lib/perl@;s@INSTALLSITEARCH = .*\$@INSTALLSITEARCH = $HOME/lib/perl/sun4-solaris-64bit@;s@PREFIX = .*@PREFIX = $HOME@" Makefile #massage solaris's stupid perl.
make install
popd
Using Sun’s perl to build perl modules can be a challenge, but a few touchups to the Makefile allow us to accomplish what we want.
5. DBD::Sybase
CPAN docs are excellent: http://search.cpan.org/~mewp/DBD-Sybase-1.07/Sybase.pm
Download DBD::Sybase http://www.peppler.org/downloads/DBD-Sybase-1.07.tar.gz
gunzip -dc DBD-Sybase-1.07.tar.gz | tar -xf -
pushd DBD-Sybase-1.07
perl Makefile.PL PREFIX=$HOME/src INSTALLSITELIB=$HOME/lib/perl CC=gcc LD=gcc # press enter at all the prompts.
perl -pi -e "s/ cc$/ gcc/;s/-xO3 -xdepend/-O3/;s/ -KPIC/ -fPIC/;" Makefile #massage solaris's stupid perl.
make install
6. Use it!
Using it involves configuring your etc/freetds.conf file and writing some perl.
The only special part of this is using a sunfreeware package without root access. This opens up many exellent possibilities for using precompiled packages. Getting a compiler opens up even more, and you don’t even have to nag your system administrator. Of course system administrators probably hate developers like me who used to be system administrators.
trivial example usage:
#!/usr/bin/perl -w
use strict;
use DBI;
$ENV{DSQUERY}="myserver"; #this is a [server] defined in freetds.conf
print 'Enter your username: ';
chomp (my $username = <>) ;
print 'Enter your password: ';
`stty -echo`;
my $password = <>;
chomp $password;
`stty echo`;
my $dbh = DBI->connect('dbi:Sybase:database=mydb', $username, $password) or die DBI->errstr;
my $sth = $dbh->prepare('select * from Table');
$sth->execute;
while ( my @row = $sth->fetchrow_array ) {
for my $c (@row) {
if ( ! defined $c ) {
$c="";
}
}
print join(",",@row)."\n";
}
$sth->finish;
$dbh->disconnect;
