perl DBI DBD和java 的JDBC对比学习

最新推荐文章于 2021-02-25 01:38:28 发布

cll3830995

最新推荐文章于 2021-02-25 01:38:28 发布

阅读量434

点赞数

文章标签：数据库

一、概念理解：perl DBI 学习总结

DBI和DBD的不同关系模型：

perl >> perl-DBI >>DBD

DBI的设计的作用是使用你忽略不同厂商的库的细节。它为你想要使用的SQL查询提供简单的接口。DBI不知道怎样和不同的数据库部分进行交流，但是它知道怎样找到和加载DBD（Database Driver）模块。在DBD模块里，有不同厂商的库，并且DBD知道怎样去和真正的数据库交流。对于不同的数据库，有一个DBD模块。

当你想让DBI做一个查询的时候，它会发送这个查询给适当的DBD模块，DBD将和真正的数据库交互。当它得到返回结构时，它将结果传送给DBI。然后DBI将结果给你。所以，你的程序只需要和DBI进行交互，而不是和真正的数据库。
看到这里会发现，DBI就是类似java连接数据库的JDBC的玩意儿。

二、DBI安装：DBI-1.634.tar.gz   DBD-mysql-4.032.tar.gz
DBI 最新下载包链接： http://search.cpan.org/dist/DBI/
DBD 最新下载包链接： http://search.cpan.org/dist/DBD-mysql/
capn：是per档案库，我把它理解为perl 资料大全库
#tar -zxvf DBI-1.604.tar.gz
#cd DBI-1.604
#perl Makefile.PL
#make
#make test
#make install
三、DBD的安装：
DBD的安装需要mysql_config，mysql_config在安装包MySQL-devel-XXXXXX.i386.rpm
以下是安装的截屏，中间有删节：

[root@red55 package]# cd DBD-mysql-4.032
[root@red55 DBD-mysql-4.032]# ls
ChangeLog dbdimp.h LICENSE MANIFEST META.yml README.pod
constants.h eg Makefile.PL MANIFEST.SKIP myld t
dbdimp.c lib Makefile.PL.embedded META.json mysql.xs TODO
[root@red55 DBD-mysql-4.032]# perl Makefile.PL
Can't exec "mysql_config": 没有那个文件或目录 at Makefile.PL line 73.

Cannot find the file 'mysql_config'! Your execution PATH doesn't seem
not contain the path to mysql_config. Resorting to guessed values!
mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';

You can also optionally set the user to run 'make test' with:

perl Makefile.PL --testuser=username

Can't exec "mysql_config": 没有那个文件或目录 at Makefile.PL line 481.
Can't find mysql_config. Use --mysql_config option to specify where mysql_config is located
Failed to determine directory of mysql.h. Use
perl Makefile.PL --cflags=-I rpm -ivh /tmp/package/MySQL-devel-5.6.25-1.linux_glibc2.5.i386.rpm
Preparing... ########################################### [100%]
1:MySQL-devel ########################################### [100%]
[root@red55 ~]# find / -name mysql_config
/usr/bin/mysql_config

[root@red55 DBD-mysql-4.032]# perl Makefile.PL
PLEASE NOTE:
For 'make test' to run properly, you must ensure that the
database user 'root' can connect to your MySQL server
and has the proper privileges that these tests require such
as 'drop table', 'create table', 'drop procedure', 'create procedure'
as well as others.
mysql> grant all privileges on test.* to 'root'@'localhost' identified by 's3kr1t';
You can also optionally set the user to run 'make test' with:
perl Makefile.PL --testuser=username
I will use the following settings for compiling and testing:
cflags (mysql_config) = -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing
embedded (guessed ) =
ldflags (mysql_config) =
libs (mysql_config) = -L/usr/lib -lmysqlclient -lpthread -lm -lrt -ldl
mysql_config (guessed ) = mysql_config
nocatchstderr (default ) = 0
nofoundrows (default ) = 0
ssl (guessed ) = 0
testdb (default ) = test
testhost (default ) =
testpassword (default ) =
testport (default ) =
testsocket (default ) =
testuser (guessed ) = root
To change these settings, see 'perl Makefile.PL --help' and
'perldoc DBD::mysql::INSTALL'.
Checking if your kit is complete...
Looks good
Warning: prerequisite Test::Deep 0 not found.
Warning: prerequisite Test::Simple 0.90 not found. We have 0.62.
Multiple copies of Driver.xst found in: /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/ /usr/lib/perl5/vendor_perl/5.8.8/i386-linux-thread-multi/auto/DBI/ at Makefile.PL line 771
Using DBI 1.634 (for perl 5.008008 on i386-linux-thread-multi) installed in /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/
Writing Makefile for DBD::mysql
[root@red55 DBD-mysql-4.032]# make
cp lib/DBD/mysql.pm blib/lib/DBD/mysql.pm
cp lib/DBD/mysql/GetInfo.pm blib/lib/DBD/mysql/GetInfo.pm
cp README.pod blib/lib/DBD/README.pod
cp lib/DBD/mysql/INSTALL.pod blib/lib/DBD/mysql/INSTALL.pod
cp lib/Bundle/DBD/mysql.pm blib/lib/Bundle/DBD/mysql.pm
gcc -c -I/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -DVERSION=\"4.032\" -DXS_VERSION=\"4.032\" -fPIC "-I/usr/lib/perl5/5.8.8/i386-linux-thread-multi/CORE" dbdimp.c
dbdimp.c: In function ‘dbd_discon_all’:
dbdimp.c:2291: 警告：未使用的变量 ‘imp_xxh’
dbdimp.c: In function ‘mysql_st_prepare’:
dbdimp.c:2747: 警告：未使用的变量 ‘limit_flag’
/usr/bin/perl -p -e "s/~DRIVER~/mysql/g" /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI/Driver.xst > mysql.xsi
/usr/bin/perl /usr/lib/perl5/5.8.8/ExtUtils/xsubpp -typemap /usr/lib/perl5/5.8.8/ExtUtils/typemap mysql.xs > mysql.xsc && mv mysql.xsc mysql.c
Warning: duplicate function definition 'do' detected in mysql.xs, line 242
Warning: duplicate function definition 'rows' detected in mysql.xs, line 754
gcc -c -I/usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBI -I/usr/include/mysql -g -fstack-protector -m32 -fasynchronous-unwind-tables -fPIC -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBD_MYSQL_INSERT_ID_IS_GOOD -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -Wdeclaration-after-statement -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -I/usr/include/gdbm -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -DVERSION=\"4.032\" -DXS_VERSION=\"4.032\" -fPIC "-I/usr/lib/perl5/5.8.8/i386-linux-thread-multi/CORE" mysql.c
mysql.xs: In function ‘XS_DBD__mysql__db_do’:
mysql.xs:260: 警告：未使用的变量 ‘statement_ptr’
mysql.xs: In function ‘XS_DBD__mysql__st_more_results’:
mysql.xs:691: 警告：未使用的变量 ‘retval’
Running Mkbootstrap for DBD::mysql ()
chmod 644 mysql.bs
rm -f blib/arch/auto/DBD/mysql/mysql.so
/usr/bin/perl myld gcc -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m32 -march=i386 -mtune=generic -fasynchronous-unwind-tables -L/usr/local/lib dbdimp.o mysql.o -o blib/arch/auto/DBD/mysql/mysql.so \
-L/usr/lib -lmysqlclient -lpthread -lm -lrt -ldl \

chmod 755 blib/arch/auto/DBD/mysql/mysql.so
cp mysql.bs blib/arch/auto/DBD/mysql/mysql.bs
chmod 644 blib/arch/auto/DBD/mysql/mysql.bs
Manifying blib/man3/DBD::mysql.3pm
lib/DBD/mysql.pm:891: Unknown command paragraph "=encoding utf8"
Manifying blib/man3/DBD::README.3pm
Manifying blib/man3/DBD::mysql::INSTALL.3pm
lib/DBD/mysql/INSTALL.pod:1: Unknown command paragraph "=encoding utf8"
Manifying blib/man3/Bundle::DBD::mysql.3pm
[root@red55 DBD-mysql-4.032]# make test
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::Command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/arch')" t/*.t
t/00base.............................ok 2/6# Driver version is 4.032
t/00base.............................ok
t/05dbcreate.........................# DBI connect('','root',...) failed: Access denied for user 'root'@'localhost' (using password: NO) at t/05dbcreate.t line 13

t/version............................Bareword "done_testing" not allowed while "strict subs" in use at t/version.t line 16.
Execution of t/version.t aborted due to compilation errors.
# Looks like your test died before it could output anything.
t/version............................dubious
Test returned status 255 (wstat 65280, 0xff00)
Failed Test Stat Wstat Total Fail Failed List of Failed
-------------------------------------------------------------------------------
t/10connect.t 255 65280 ?? ?? % ??
t/30insertfetch.t 255 65280 ?? ?? % ??
t/35prepare.t 255 65280 ?? ?? % ??
t/40blobs.t 255 65280 ?? ?? % ??
t/40nulls_prepare.t 255 65280 ?? ?? % ??
t/40server_prepare_error.t 255 65280 ?? ?? % ??
t/41blobs_prepare.t 255 65280 ?? ?? % ??
t/53comment.t 255 65280 ?? ?? % ??
t/60leaks.t 255 65280 ?? ?? % ??
t/70takeimp.t 255 65280 ?? ?? % ??
t/87async.t 2 512 ?? ?? % ??
t/90no-async.t 255 65280 ?? ?? % ??
t/rt50304-column_info_parentheses 255 65280 ?? ?? % ??
t/rt83494-quotes-comments.t 255 65280 ?? ?? % ??
t/rt85919-fetch-lost-connection.t 255 65280 ?? ?? % ??
t/rt86153-reconnect-fail-memory.t 255 65280 ?? ?? % ??
t/rt88006-bit-prepare.t 255 65280 ?? ?? % ??
t/version.t 255 65280 ?? ?? % ??
43 tests skipped.
Failed 18/62 test scripts, 70.97% okay. 0/6 subtests failed, 100.00% okay.
make: *** [test_dynamic] 错误 255
[root@red55 DBD-mysql-4.032]# make install
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.so
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/mysql.bs
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/Bundle/DBD/mysql.pm
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/README.pod
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql.pm
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql/INSTALL.pod
Installing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql/GetInfo.pm
Installing /usr/share/man/man3/DBD::mysql.3pm
Installing /usr/share/man/man3/DBD::mysql::INSTALL.3pm
Installing /usr/share/man/man3/Bundle::DBD::mysql.3pm
Installing /usr/share/man/man3/DBD::README.3pm
Writing /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/auto/DBD/mysql/.packlist
Appending installation info to /usr/lib/perl5/5.8.8/i386-linux-thread-multi/perllocal.pod

四、最后就是测试DBD安装是否成功，使用如下连接代码：

[mysql@red55 perl 17:35:46]$ vi test.pl
#!/bin/perl
use DBI;
# Connect to target DB
my $dbh = DBI->connect("DBI:mysql:database= test;host=localhost", "system","123456", {'RaiseError' => 1});
# Insert one row
my $rows = $dbh->do("INSERT INTO stu1 (id, username) VALUES (7, 'xiang')");
# query
my $sqr = $dbh->prepare("SELECT username FROM stu1");
$sqr->execute();
while(my $ref = $sqr->fetchrow_hashref()) {
print "$ref->{'username'}\n";
}
$dbh->disconnect();
"test.pl" 19L, 423C 已写入
[mysql@red55 perl 17:36:25]$perl test.pl
xiebin
wenqing
xiang
xiang
xiang

大功告成，连接成功。DBD安装好了
五、看到网上也有同学采用如下命令安装： yum install perl-DBD-MySQL。采用这个命令安装会出现如下错误：
Transaction Check Error:
file /etc/my.cnf from install of mysql-5.0.77-4.el5_5.4.i386 conflicts with file from package MySQL-server-5.6.25-1.linux_glibc2.5.i386
原因是：perl-DBD-MySQL对应了5.0版本的mysql，而系统安装5.6的版本。解决办法是像上面一样，手动安装DBD。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30393770/viewspace-1813513/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30393770/viewspace-1813513/