| 您的位置:
Linux时代 > 技术文档 > 数据库 >
通过Perl 编程访问DB2数据库
日期：2006-10-24 作者：Marina Greenstein 来自：IBM DW中国
在本文中，我们将学习如何编写简单的 Perl 程序来提取或操作 IBM® DB2® Universal Database™（DB2 UDB）中存储的数据。我们将从一个简单的任务入手：从数据库中选择一行数据放入一个 Perl 程序中；接着逐步介绍一些高级主题，包括处理大对象和调用存储过程。
简介
Perl（Practical Extraction and Report Language）是一种功能强大而又非常简单易用的编程语言，在很多操作系统上都可以使用。Perl 是免费的。我们可以（以源代码或二进制的格式）下载这个语言，并可以免费地使用它。
Perl 日渐成为一种广受欢迎的语言。它包含了 C 编程语言的特性，以及 UNIX® 中的一些命令，例如 awk 和 sed。Perl 是一种解释语言，可以在单独的应用程序中使用，也可以与 Apache 一起来构建 Web 应用程序。
我们可以使用 Perl 快速操作来自文件或 RDBMS 的大型数据集。DBI 是在 Perl 脚本中连接 RDBMS 的标准，它是在 1994 年开始引入的。在 http://dbi.perl.org/ 站点上可以找到 DBI 驱动程序的源代码及其文档。
IBM 在 1992 年为 Perl 开发了 DB2 的驱动程序，并随着 DBI 规范的发展周期性地对其进行更新。这个驱动程序的最新版本（在撰写本文时）是 0.78。在 http://www.ibm.com/software/data/db2/perl/ 上可以找到主要的 DBD::DB2（这是 Perl 语言中采用的命名机制）驱动程序信息。
本文将展示如何编写简单的 Perl 程序来提取或操作 DB2 UDB 中存储的数据。我们将从一个简单的任务入手：从数据库中选择一行数据放入一个 Perl 程序中；接着逐步介绍一些高级主题，包括处理大对象和调用存储过程。
开始
图 1 展示了 Perl 环境如何与数据库环境进行交互：
图 1. Perl 环境
正如可以从这个图中看出的，Perl 程序使用了一个标准的 API 来与 DBI（Perl 的数据库接口模块）进行通信。Perl DBI 模块只能支持动态 SQL。它定义了一组方法、变量和约定来提供一个与实际使用的数据库独立的一致数据库接口。DBI 为 API 提供了一个一致的接口，它可以适用于程序员想使用的任何数据库。DBD::DB2 是一个 Perl 模块，当与 DBI 一起使用时，它就可以让 Perl 与 DB2 UDB 进行通信。
因此，为了运行访问 DB2 数据库的 Perl 脚本，需要在系统上安装以下组件：
- Perl 语言环境
- DBI 驱动程序（可以用于任何 RDBMS）
- DBD::DB2 驱动程序
- DB2 Runtime Client
- C 编译器
- DB2 数据库服务器的连接信息
在 http://www.ibm.com/software/db2/perl/ 的 Web 站点上可以看到所有的安装配置指南。
连接到 DB2 数据库上
为了让 Perl 程序访问 DB2 数据库，需要建立到数据库的连接。为了让 Perl 加载 DBI 模块，需要在 Perl DB2 应用程序中包含下面的内容：
use DBI;
当使用 DBI->connect 语句（语法如下）来创建一个数据库句柄 时，DBI 模块会自动加载 DBD::DB2 驱动程序。
清单 1. 创建数据库句柄
use DBI;
$dbh = DBI->connect (“dbi:DB2:dbalias", $UserId, $password);
|
其中：
| $dbh |
—— 表示 connect 语句所返回的数据库句柄 |
| dbalias |
—— 表示分类进 DB2 数据库目录中的 DB2 别名 |
| $userID |
—— 表示用来连接数据库的用户 ID |
| $password |
—— 表示这个用户 ID 的密码 |
清单 2 展示了一个简单的 Perl 程序，它建立到数据库 SAMPLE 的连接，并返回今天的日期。这个程序执行一条动态生成的 DB2 SQL 语句，从数据库中获取 CURRENT DATE。它使用 DBI -> bind_col 方法将数据库中的值传递到一个本地变量中，稍后我们就会讨论这个问题。
清单 2. 连接到数据库上并执行语句
#!/usr/local/bin/perl -w
use DBI;
use strict;
# Open a connection
my $dbh = DBI->connect("dbi:DB2:sample", “DB2ADMIN", “db2admin“, {RaiseError => 1});
# use VALUES to retrieve value from special register
my $stmt = "Values CURRENT DATE";
my $sth = $dbh->prepare($stmt);
$sth->execute();
# associate variables with output columns...
my $col1;
$sth->bind_col(1,\$col1);
while ($sth->fetch) { print "Today is: $col1\n"; }
$sth->finish();
$dbh->disconnect();
|
错误处理 —— SQLCODE 和 SQLSTATE
为了返回与某个 Perl DBI 数据库句柄或语句句柄相关的 SQLSTATE，可以调用 state 方法。例如，要返回与数据库句柄 $dbhandle 相关的 SQLSTATE ，可以在应用程序中使用下面的 Perl 语句：
my $sqlstate = $dbhandle->state;
为了返回与某个 Perl DBI 数据库句柄或语句句柄相关的 SQLCODE，可以调用 err 方法。例如，要返回与数据库句柄 $dbhandle 相关的 SQLCODE，可以在应用程序中使用下面的 Perl 语句：
my $sqlcode = $dbhandle->err;
errstr 方法返回与某个 Perl DBI 数据库句柄或语句句柄相关的 SQLCODE 的消息。我推荐使用这个方法，因为这个方法可以给出有关 SQL 语句失败的更多信息。
清单 3 中的例子展示了这个方法的用法：
清单 3. 返回错误消息的 errstr 方法
$dbh = DBI->connect("dbi:DB2:sample",“USERID",“password") or
die “Can't connect to sample database: $DBI::errstr";
$sth = $dbh->prepare(“SQL statement“) or die "Cannot prepare: " $dbh->errstr;
$sth->>execute() or die "Cannot execute: " $sth->errstr;
|
现在让我们进一步看一下第一个实验。它展示了一个 Perl 程序，这个程序使用作为参数传入的用户 ID 和密码来连接数据库 SAMPLE。当传递了有效的 ID 和密码时，它会返回一条消息说明连接成功了。下面是 lab1.pl 的代码：
清单 4. lab1.pl
#!/usr/local/bin/perl –w
use DBI;
$db2user = $ARGV[0];
$pasw = $ARGV[1];
# Open a connection
$dbh = DBI->connect("dbi:DB2:sample", $db2user, $pasw) or
“Can't connect to sample database: $DBI::errstr";
print "Connection is successful !!\n";
|
图 2 给出了使用有效身份验证和无效身份验证来执行这个程序的结果：
图 2. 执行 lab1.pl
执行 SQL 语句
下面让我们来编写一个程序，在数据库 SAMPLE 中创建一个表 PT_ADDR_BOOK。要执行在编写应用程序时就已经知道的 SQL 语句，可以使用 $dbh->do 方法。这个方法的语法如下所示：
my $cnt = $dbh->do(SQL statement);
其中 $cnt 是这条 SQL 语句所影响的行数。
使用这个方法，我们的程序创建了一个 DB2 表，如清单 5 所示：
清单 5. 用来创建 DB2 表的程序
#!/usr/local/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))");
print “Returns: $rcount\n";
|
可以使用相同的 do 方法向 PT_addr_book 表中插入几行数据。请注意，所插入行的值在编写这个程序时都是已知的，因此可以硬编码在代码中。
清单 6. 使用 do 方法插入几行数据
#!/usr/local/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";
|
正如从这个例子中可以看到的，这条 SQL 语句会影响两行数据。通过在 DB2 CLP 中对这个表运行 SELECT 语句，可以确定有两行数据已经插入了这个表中。
清单 7. test_do_exs.pl
$perl test_do_exs.pl
Returns : 2
$db2 “select * from PT_ADDR_BOOK"
NAME PHONE
------------------------------ ----------
Gregory Whales 9142712020
Robert Moses 2127652345
2 record(s) selected.
|
下面让我们从 下载 一节中实验 2 的练习开始，编写并执行一个简单的 Perl 程序来更新 PR_ADDR_BOOK 表。
INSERT、UPDATE 和 DELETE 语句 —— 没有占位符
执行一条在编写应用程序时还未确定的 SQL 语句（动态 SQL）需要使用一种不同的技术，可以使用 $dbh->prepare 方法来实现。动态 SQL 可以通过在程序执行过程中自己修改列、表和谓词（操作）的能力来实现。动态 SQL 需要由 UDB 优化器来准备 执行，目的是为这条语句创建一个访问计划。如果动态 SQL 没有参数标记（占位符），它就可以在这个步骤之后立即执行。清单 8 给出了 $dbh->prepare 和 $sth->execute 方法的语法，可以在 Perl 程序中用来执行没有占位符的嵌入式 SQL 语句：
清单 8. 动态 SQL 的准备和执行
$stmt = “SQL Statement without placeholder“;
$sth = $dbh->prepare($stmt);
$sth->execute();
|
请注意，$dbh->prepare 方法的结果是 SQL 语句句柄。
清单 9 中给出的 Perl 程序展示了如何使用这些方法将一行数据插入到 PT_ADDR_BOOK 表中：
清单 9. 插入一行数据
#!/usr/local/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 = "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();
|
具有参数标记的 SQL 语句
现在让我们来看一下如何执行在编写应用程序时还不确定并且具有参数标记（即占位符）的 SQL 语句 —— 即真正的动态 SQL 语句。请注意，我们推荐使用这种 SQL 语句，因为它们具有性能和安全性方面的优点。从性能的角度来看，动态 SQL 语句只需要准备一次就可以执行多次，可以重用相同的数据库访问计划。从安全性的角度来看，占位符可以确保只有一个值可以插入到这条语句中，从而可以避免出现 SQL 语句错误。
为了让 Perl 程序将动态 SQL 语句转换成可执行的格式，需要首先使用 prepare 方法，然后使用 bind_param 方法来绑定参数。只有这样才可以执行这条语句。要绑定每个参数，需要指定参数标记的个数，以及包含这些参数标记值的本地 Perl 变量的名字。清单 10 给出了让 Perl 程序执行动态 SQL 语句所使用的方法的语法：
清单 10. 动态 SQL 的方法
$stmt = “SQL Statement with parameter marker“;
$sth = $dbh->prepare($stmt);
$sth->bind_param(1,$parm,\% attr);
$sth->execute();
|
清单 11 展示了对表 PT_ADDR_BOOK 执行 INSERT 操作的方法。现在，所插入列的值不再是在 SQL 语句中硬编码的了，而是使用参数标记动态传递给这条语句的；这些参数标记已经使用 $sth->bind_param 方法与这条语句进行了绑定。只有完成这些设置之后，才开始执行这条语句。
清单 11. 对表 PT_ADDR_BOOK 执行 INSERT 操作
#!/usr/local/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";
$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();
|
从数据库中检索数据 —— 单个结果
为了将数据库中的值放到 Perl 程序中使用，需要按照下面的步骤执行：
- 准备 SELECT 语句。
- 执行所准备的语句。
- 使用 $sth->bind_col 方法把某列的值（或函数）关联到一个本地 Perl 变量上。
- 使用 $sth->fetch 方法将一个值取到本地变量中。
下面的伪码展示了如何从数据库中检索单个值：
清单 12. 从数据库中检索单个值
$stmt = “SQL SELECT Statement to be executed“;
$sth = $dbh->prepare($stmt);
$sth->execute();
$result = $sth->bind_col(col, \variable [, \%attr ]);
while ($sth->fetch){
process result of the fetch;
}
|
下面这个例子展示了如何为一个聚合函数 max 从 EMPLOYEE 表中检索 salary 的值：
清单 13. 为聚合函数检索值
#!/usr/local/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(salary) from EMPLOYEE";
$sth = $dbh->prepare($stmt);
$sth->execute();
#associate variable with output columns...
$sth->bind_col(1,\$max_sal);
while ($sth->fetch) {
print "The biggest salary is: $max_sal\n";
}
|
下面是执行这个 Perl 程序后的结果：
清单 14. 聚合结果值
$perl test_return_value.pl
The biggest salary is: 52750.00
|
从数据库中检索数据 —— 多个结果
从数据库中返回一个结果集（即多个结果）到 Perl 程序中的技术与前面所采用的技术非常类似。在准备并执行返回多个结果的 SQL 语句并将返回值（列）绑定到本地变量上之后，可能需要使用 $sth->fetch 方法来检索这些值。为了展示这个方法是如何工作的，下面让我们从 PT_ADDR_BOOK 表中检索出 phone 和 name 列的内容。清单 15 给出了使用一个循环来使用 $sth_fetch 方法的用法：
清单 15. 从数据库中检索多个值
$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";
while ($sth->fetch) {
print $name ;
print $phone; print "\n"; }
print "DONE \n";
$sth->finish();
|
执行这段代码的结果如下：
清单 16. 从数据库中检索多个值
C:\Dev_POT\PERL\Labs>perl multi.pl
NAME PHONE
------------------------- -----------
JOHN SMITH 9145556677
STEVE BROWN 7184358769
DONE
|
也可以使用 $sth->fetchrow 方法从结果集中检索数据。$sth ->fetch 方法会将每个值作为单独一项返回，而 $sth ->fetchrow() 方法则将一行作为一个数组返回，每列的值都是该数组中的一个元素。
可以使用 fetchrow 方法编写功能相同的 Perl 程序，如下所示：
清单 17. 从数据库中检索多个值
$stmt = "SELECT name, phone from PT_ADDR_BOOK";
$sth = $dbh->prepare($stmt);
$sth->execute();
print "NAME PHONE \n";
print "------------------------- -----------\n";
while (($name, $phone) = $sth->fetchrow())
{ print "$name $phone\n"; }
|
我们可以从 实验 3 开始编写并执行一个 Perl 程序，它可以创建一个 DB2 表，向该表中插入一些数据，并返回插入该表中的行数。
调用存储过程
下面让我们来创建一个多个步骤的场景，从而详细了解一下如何从 Perl 程序中调用 DB2 的存储过程。首先，创建一个简单的存储过程 SP_GET_LOC，它从表 ORG 中返回某个给定部门的位置（步骤 1）。
清单 18. 步骤 1：创建存储过程
create procedure sp_get_loc (in deptin int, out loc varchar(13))
begin
select location into loc from org where deptnumb = deptin;
end @
|
请注意，这个存储过程有一个输入参数和一个输出参数。当我们在 DB2 的命令行处理器（CLP）中运行这个过程时，它对部门 10 会返回位置 NEW YORK。
清单 19. 步骤 2：运行存储过程
$db2 "call sp_get_loc(10,?)"
Value of output parameters
--------------------------
Parameter Name : LOC
Parameter Value : New York
Return Status = 0
|
下面让我们来编写一个简单的 Perl 程序来调用存储过程 SP_GET_LOC（步骤 3，请参看 清单 20）。我们的动态 SQL 语句实际上与在 DB2 CLP 中执行的语句相同，形式为 SP_GET_LOC(?,?)，不过在 CLP 中是传递硬编码的 10（部门编号），在动态 SQL 语句中将使用一个参数标记。采用这种方法，就可以对 ORG 表的部门编号列的任何值查询位置了。
在构造 SQL 语句之后，剩下的步骤就与其他具有参数标记的动态语句完全相同了。可以使用 $sth = $dbh->prepare 方法进行准备。使用 $sth->bind_param 方法将部门编号绑定为输入参数，使用 $sth->bind_param_inout 方法将返回的位置绑定为输出参数，然后再执行动态 SQL 语句。
该程序如下：
清单 20. 步骤 3：调用存储过程
#!/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";
# Prepare our call statement
$sth = $dbh->prepare( "CALL SP_GET_LOC(?,?)" );
# Bind input parameter for department number
$sth->bind_param(1, 10);
# Bind output parameter - location
$sth->bind_param_inout (2, \$location, 13, db2_param_type=>SQL_PARAM_OUTPUT});
# Call the stored procedure
$sth->execute();
printf("Stored procedure returned location: %s\n", $location);
$sth->finish();
$dbh->disconnect;
|
如果执行这个程序，就会看到给定部门（本例中为 10）的 LOCATION：
清单 21. 步骤 4：执行调用 DB2 存储过程的 Perl 程序
$perl test_call_sp.pl
Stored Procedure returned location: New York
|
大对象的操作
使用 Perl 来处理文件比使用其他更复杂的语言（例如 C 或 Java®）都要简单。下面我们就会看到如何将数据直接从文件中导入 DB2 的大对象数据（LOB）列中。插入大对象数据的最有效的方法是，将这个文件直接绑定到 DB2 表中一个与 LOB 类型的列相关联的输入参数上。Perl 驱动程序可以直接从文件中读取数据，并将数据传递给数据库服务器。要将一个文件绑定到某个输入 LOB 参数上，可以在执行 INSERT 操作过程中使用 bind_param 方法来指定 { db2_file => 1} 参数属性。
在我们的数据库模式 POT 下面有一个表 MAP。这个表有一个 picture 列，它被声明成 BLOB 类型的，用来存放某个地区的图像。下面是创建该表所使用的 DDL：
清单 22. 创建 MAP 表使用的 DDL
create table POT.MAPS
( map_id INT,
map_name VARCHAR(13),
area INT ,
photo_format CHAR(3),
picture BLOB) ;
|
另外，我们还有一个文件 pearcson.jpg，其中包含了 Pearson Airport 的地图。
图 3. 示例地图
现在让我们来编写一个程序，向表 POT.MAP 中插入一行数据，即将 JPG 文件的图像插入到 PICTURE 中。首先，需要构造一个具有 5 个参数标记的动态 SQL 语句。然后，需要对这条语句进行准备。在将参数绑定到准备好的语句上之前，需要指定包含要插入的图像的文件名，并将其赋给一个本地 Perl 变量（$picture_file）。现在可以将所有参数全部绑定到需要插入到 MAP 表中的值上。请注意，我们为最后一个参数指定了 db2_file =>1 属性。最后一个步骤是执行这条 INSERT 语句。这个程序的代码如下：
清单 23. 插入 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 POT");
$sql = "INSERT INTO MAPS(map_id, map_name, area, photo_format, picture)
VALUES(?,?,?,?,?)";
$sth = $dbh->prepare($sql);
$picture_file = "pearson.jpg"; # File containing our picture
$sth->bind_param(1, 100); # map_id
$sth->bind_param(2, "Pearson airport"); # 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 数据
可以使用标准的 fetch 方法来检索 LOB 数据，例如 fetchrow_array 或 fetchrow_arrayref。DBI 让我们可以使用 LongReadLen 连接属性来设置每次 fetch 可以检索的最大字节数。对于 LOB 列来说，缺省值为 32,700 个字节。要实现这种功能，需要执行以下步骤：
- 构造 SQL 语句从 MAP 表中选择 picture 列的数据。
- 准备 SQL 语句。
- 为保存所检索到的图像使用的文件分配一个名字。
- 打开该文件。
- 执行这条 SQL 语句。
- 使用 fetch 方法将结果取到文件中。
下面是展示如何从数据库中检索 LOB 数据的代码：
清单 24. 从数据库中读取 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 'POT'
$dbh->do("SET CURRENT SCHEMA POT");
$sql = "SELECT picture FROM maps WHERE map_name ='Pearson airport'";
# 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;
|
在运行这个程序之后，图像就保存到 mypic.jpg 文件中了。
清单 25. 从数据库中读取 LOB 数据
$perl test_lobread.pl
Picture in the file mypic.jpg
|
请使用 实验 4 中的练习来编写并执行一个 Perl 程序，它从一个表中检索出二进制大对象，并将其保存到一个文件中。
结束语
本文是为那些具有关系数据库经验并且希望学习如何编写 Perl 程序来访问 DB2 数据库的 Perl 程序员编写的。在本文中，我们已经学习了如何连接数据库，如何通过 INSERT、UPDATE 和 DELETE 语句来操作数据库的内容。还学习了如何从数据库中检索数据，并介绍了一些高级主题，包括调用存储过程和操作大数据对象（LOB 和 BLOB）。现在我们应该已经准备好使用自己刚掌握的 Perl DB2 编程技巧来开发自己的程序了。
原文链接：http://www-128.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512greenstein/index.html
本文被浏览次
| 
