DBD::DB2 使用

DBD::DB2模块安装

ppm install (For Version 5.8)
ppm install (For Version 5.10)

创建数据库表

#!/usr/bin/perl -w

use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die "Can't connect to sample database: $DBI::errstr";
#$rcount = $dbh->do ("CREATE TABLE PT_addr_book(name char(30), phone char(10))");
$rcount = $dbh->do ("create table MAPS( map_id INT, map_name VARCHAR(13), area INT, photo_format CHAR(3), picture BLOB)");
print "Returns: $rcount\n";

添加

#!/usr/bin/perl -w

use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die "Can't connect to sample database: $DBI::errstr";
#$rcount = $dbh-> do ("Insert into PT_ADDR_BOOK values
# ('Gregory Whales', '9142712020'),
# ('Robert Moses', '2127652345')");
#print "Returns: $rcount \n";

##$stmt = "INSERT INTO PT_addr_book values ('JOHN SMITH','9145556677')";
##$sth = $dbh->prepare($stmt);
##$sth->execute();
##print "We inserted row into addr_book\n";
##$sth->finish();

$name ="STEVE BROWN";
$phone = "7184358769";
$stmt = "INSERT INTO PT_addr_book values (?,?)";
$sth = $dbh->prepare($stmt);
$sth->bind_param(1,$name);
$sth->bind_param(2,$phone);
$sth->execute();
print "We inserted row into addr_book\n";
$sth->finish();

查找

#!/usr/bin/perl -w

use DBI;
use DBD::DB2::Constants;
$dbh = DBI->connect("dbi:DB2:sample","","") or
die "Can't connect to sample database: $DBI::errstr";
#$stmt = "SELECT max(phone) from PT_addr_book";
#$sth = $dbh->prepare($stmt);
#$sth->execute();
##associate variable with output columns...
#$sth->bind_col(1,\$max_sal);
#while ($sth->fetch) {
# print "The biggest phone is: $max_sal\n";
#}


$stmt = "SELECT name, phone from PT_ADDR_BOOK";
$sth = $dbh->prepare($stmt);
$sth->execute();
#associate variables with output columns...
#$sth->bind_col(1,\$name);
#$sth->bind_col(2,\$phone);
#print "NAME PHONE \n";
#print "------------------------- -----------\n";
$~ = "WRITEHEADER";
write;
#while ($sth->fetch) {
while (($name, $phone) = $sth->fetchrow()){
# print $name ;
# print $phone; print "\n";
$~ = "WRITELETTER";
write;
}
$~ = "WRITEEND";
write;
print "DONE \n";
$sth->finish();

format WRITEHEADER =
NAME PHONE
------------------------- -----------
.
format WRITELETTER =
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<@<<<<<<
$name,$phone
.
format WRITEEND =
------------------------- -----------
.

添加LOB数据

#!/usr/bin/perl -w
use DBI;
use DBD::DB2::Constants;
%conattr = ( AutoCommit => 1,
# Turn Autocommit On
db2_info_applname => 'Maps Module', );
# Identify this appl
$dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or die "$DBI::errstr";
$dbh->do("SET CURRENT SCHEMA ADMINISTRATOR");
$sql = "INSERT INTO MAPS(map_id, map_name, area, photo_format, picture) VALUES(?,?,?,?,?)";

$sth = $dbh->prepare($sql);
$picture_file = "image.jpg"; # File containing our picture
$sth->bind_param(1, 100); # map_id
$sth->bind_param(2, "Pearson"); # map_name
$sth->bind_param(3, 416); # area
$sth->bind_param(4, "JPG"); # photo_format
$sth->bind_param(5, $picture_file, {db2_file => 1});
$rows_affected = $sth->execute();
printf("%d rows affected", $rows_affected);
$sth->finish();
$dbh->disconnect;

查找LOB数据

#!/usr/bin/perl
use DBI;
use DBD::DB2::Constants;
%conattr =
(
AutoCommit => 1,
# Turn Autocommit On
db2_info_applname => 'Maps Module',
# Identify this appl
LongReadLen => 80000
# Don't retrieve LOBs
);
# Connect to our database
$dbh = DBI->connect("dbi:DB2:sample","", "",\%conattr) or
die "$DBI::errstr";
# Set the current schema to 'ADMINISTRATOR'
$dbh->do("SET CURRENT SCHEMA ADMINISTRATOR");
$sql = "SELECT picture FROM maps WHERE map_name ='Pearson'";
# Prepare the statement
$sth = $dbh->prepare($sql);
# Open output file
$out_file = "mypic.jpg";
open(OUTPUT, ">$out_file") or die "Cannot open $out_file because $!";
binmode OUTPUT;
$sth->execute;
@row = $sth->fetchrow;
print OUTPUT $row[0];
@row = "";
close(OUTPUT);
print "Picture in the file $out_file\n";
$sth->finish();
$dbh->disconnect;