Perl on BSD talking to MS-SQL Server
13/09/13 10:27 Filed in: Programming
A lot of broadcast monitoring equipment seems to use Microsoft SQL Server as backend for storing alerts and other data. As we use a FreeBSD based monitoring box it proved necessary to find a way of accessing these servers via ODBC.
The solution is relatively simple, but confusingly documented. The following worked example should help make the documentation clearer.
We chose to install libiodbc and DBD
Select the IODBC and MSDBLIB options
If you setup a
The you can access the database via the tsql command. Eg:
To access the DBNAME database located on the MS SQL server on 10.0.1.3; SQL commands are executed when the GO command is entered on a line by itself.
To get it all to work with Perl:
Simple really. Note that the contents of
We chose to install libiodbc and DBD
cd /usr/ports/databases/libiodbc
make install
cd /usr/ports/databases/freetds
make config
Select the IODBC and MSDBLIB options
cd /usr/ports/databases/p5-DBD-ODBC
make install
If you setup a
/usr/local//etc/freetds.conf file like:
[DBNAME]
host = 10.0.1.3
port = 1433
tds version = 8.0
The you can access the database via the tsql command. Eg:
tsql -S DBNAME -U username -P password
To access the DBNAME database located on the MS SQL server on 10.0.1.3; SQL commands are executed when the GO command is entered on a line by itself.
To get it all to work with Perl:
#!/usr/bin/perl
use DBI;
my $server=’10.0.1.3’;
my $port=1433;
my $db=‘DBNAME’;
my $user=‘username’;
my $pw=‘password’;
my $DSN = “Driver=/usr/local/lib/libtdsodbc.so;’”
“Server=$server;Port=$port;Database=$db;UID=$user;PWD=$pass;”.
“TDS_VERSION=8.0;”;
my $dbh = DBI->connect("dbi:ODBC:$DSN", {RaiseError=>1, odbc_cursortype=>2});
Simple really. Note that the contents of
/usr/local//etc/freetds.conf are only important for tsql.