Perl 操作DB2数据库

hao0089

#!/usr/bin/perl -w  
#################################################################################################
# Author: George Feng  added on Sep 13th
# Usage: This script is used to Access DB2  
# Version: 0.1
# License: GPL v2
#
#################################################################################################
#load modules
use Spreadsheet::ParseExcel;  
use DBI;
use DBD::DB2::Constants;
use DBD::DB2;
# Configure DB2 Connection Info
my $db_name = "citm";
my $username = "admin";
my $password = "password";
# connect
my $dbh = DBI->connect("dbi:DB2:$db_name", $username, $password);
if ($dbh)
{
print "-->CITM Database \"$db_name\" connected successfully...\n\n";
} else
{
print "-->Error: Can not connect to $db_name!!!\n\n";   
}

# execute INSERT
sub _execute_insert(){
print "--->start insert record to db ...\n";
my $cols=$_[0];
my $values=$_[1];
my $insert="INSERT INTO tablename ".$cols." VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ";
print "----> sql satament is $insert \n";
$sth = $dbh->prepare($insert);
#bind paras
$count=1;
while($count<=(@$values-1)){
$sth->bind_param($count,@$values[$count]);
$count++;
}
if($sth->execute() ) {
return($sth);
} else {
print "Execution of insert failed: \n";
return(0);
}
}


# execute SELECT query
$sth = $dbh->prepare("SELECT * FROM tablename");
$sth->execute();
# iterate through resultset
# print values
while(my $hash_ref = $sth->fetchrow_hashref) {
print "name:$hash_ref->{'name'}";
print ",owner:$hash_ref->{'owner'}";
print ",sex:$hash_ref->{'sex'}";
print ",birth:$hash_ref->{'birth'}";
print ",death:$hash_ref->{'death'}";
print "\n";
}

$sth = $dbh->prepare("SELECT * FROM tablename");
$sth->execute();
while(my $array_ref = $sth->fetchrow_arrayref) {
print "name:$array_ref->[0]";
print ",owner:$array_ref->[1]";
print ",sex:$array_ref->[2]";
print ",birth:$array_ref->[3]";
print ",death:$array_ref->[4]";
print "\n";
}

$sth = $dbh->prepare("SELECT * FROM pet");
$sth->execute();
print "fetchrow_array output style----\n";
while(my @row_array = $sth->fetchrow_array) {
print "name:$row_array[0]";
print ",owner:$row_array[1]";
print ",sex:$row_array[2]";
print ",birth:$row_array[3]";
print ",death:$row_array[4]";
print "\n";
}
# execute DELETE
my $c = $dbh->do('DELETE FROM tablename');
# clean up
$dbh->disconnect();

有兴趣的可以：
http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0512greenstein/?S_TACT=105AGX52&S_CMP=techccid