Interkoneksi PERL dengan MS SQL Server di Ubuntu.
Requirement:
1. Update Module perl DBI
2. Install FreeTDS
3. Install Module perl DBD::Sybase
1. Update Module perl DBI
Ada dua opsi untuk melakukan update, yaitu dengan melakukan instalasi manual (compile dari source) dan instalasi melalui CPAN Console).
- Instalasi melalui source
Download source di http://www.cpan.org/authors/id/T/TI/TIMB/DBI-1.48.tar.gz
Install module tersebut sesuai dengan panduan yang ada di README
- Instalasi melalui CPAN Console
Jalankan command ini sebagai root
perl -MCPAN -e shell
Jika prompt sudah menunjukkan “cpan>” jalankan command
install DBI
System akan melakukan update module DBI. Jika prompt “cpan>” muncul kembali, jalankan command install DBI, jika outputnya “DBI is up to date.” Maka module DBI sudah terupdate.
2. Install FreeTDS.
- Upload file source di /usr/local/src
- Jalankan command berikut
tar xzvf freetds-stable.tgz
cd freetds-0.82
./configure --with-tdsver=7.0 --prefix=/usr/local/freetds
make
make install
- Set environment variable sebagai berikut
export export SYBASE=/usr/local/freetds
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/usr/local/freetds/lib
- Tambahkan dua baris tersebut pada file /etc/profile, agar variable tersebut bisa tetap ada pada saat mesin restart
Notes: Jika ada error compiler belum diinstall, maka terlebih dahulu install paket “build-essential” dengan command: apt-get install build-essential
3. Install Module perl DBD::Sybase
- Upload file source di /usr/local/src
- Jalankan command berikut
tar xzvf DBD-Sybase-1.07.tar.gz
cd DBD-Sybase-1.07
perl Makefile.PL
- Setelah proses selesai, edit file “dbdimp.c” yaitu tambahkan code berikut diatas fungsi “void syb_init(dbistate)”
#undef CS_VERSION_150
#undef CS_VERSION_125
#undef CS_VERSION_120
#undef CS_DATE_TYPE
#undef CS_BIGINT_TYPE
- Setelah pengeditan selesai, jalankan command berikut
make
make install
Testing Script.
1. Untuk melakukan testing koneksi dengan MS SQL Server
#!/usr/bin/perl
use DBI;
my $user = "user";
my $passwd = "password";
my $dbh = DBI->connect("DBI:Sybase:server=", $user, $passwd, {PrintError => 0});
unless ($dbh) {
die "Unable for connect to server $DBI::errstr";
}
my $sth;
$sth = $dbh->prepare("select \@\@servername");
if ($sth->execute) {
while(@dat = $sth->fetchrow) {
print "SERVER IS: @dat\n";
}
}
exit(0);
Output:
ipunk@xxx:~/sql_server$ perl query.pl
SERVER IS: MSSQLSERVER
ipunk@xxx:~/sql_server$
2. Melakukan query di database
#!/usr/bin/perl
#
# test the db2 dbi driver
#
use DBI ;
our $tbl_interaction; our $tbl_participant; our $sql_nice;
our $rsConnect;our @datatampil;
my $user = "user";
my $passwd = "password";
my $dbh = DBI->connect("DBI:Sybase:server=", $user, $passwd, {PrintError => 0});
$dbh->do("dbase name");
$tbl_interaction = tblInteraction . "01";
$tbl_participant = tblParticipant . "01";
$sql_nice = "select DISTINCT(a.dtInteractionLocalStartTime) as StartTime,a.dtInteractionLocalStopTime as ".
"StopTime, b.nvcPhoneNumber as MSISDN,b.nvcAgentID as ".
"LoginID FROM tblInteraction01 a, tblParticipant01 b ".
"WHERE a.iInteractionID=b.iInteractionID ".
"and a.dtInteractionLocalStartTime between '2009-03-01 00:00:00' and '2009-03-31 23:59:59' ";
$rsConnect = $dbh->prepare($sql_nice)
or die "Couldn't prepare statement: $sql_nice -->" . $dbh->errstr;
# execute sql source
$rsConnect->execute
or die "Couldn't execute statement: $sql_nice -->" . $rsConnect->errstr;
@datatampil = $rsConnect->fetchrow_array();
while(@datatampil = $rsConnect->fetchrow_array())
{
print "Starttime : @datatampil[0]";
print "Stoptime : @datatampil[1]";
print "Connected Number : @datatampil[2]";
print "LoginID : @datatampil[3]";
}
Sumber:
Posts
