perl连接sqlserver

下载相关软件 unixODBC、freetds和DBD-ODBC?
需要下载3个文件：?
? ? Linux系统的ODBC：unixODBC-2.3.1.tar.gz ( http://www.unixodbc.org)?
? ? 连接SQLServer或Sybase的驱动：freetds-0.82.tar.gz ( http://www.freetds.org)?
? ? perl的ODBC模块 ：cd DBD-ODBC-1.43.tar.gz ( http://cpan.perl.org)?
安装和配置

1、安装unixODBC

# tar vxzf unixODBC-2.3.1.tar.gz
# cd unixODBC-2.3.1
# ./configure --prefix=/usr/local/unixODBC
# make
# make install

2、安装freetds

# tar vxzf freetds-0.82.tar.gz
# cd freetds-0.82
# ./configure --prefix=/usr/local/freetds --with-unixodbc=/usr/local/unixODBC --with-tdsver=8.0 --enable-msdblib

# make
# make install

3、安装DBI 模块

安装DBI前需要安装：
http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/Test-Simple-0.98.tar.gz
http://search.cpan.org/CPAN/authors/id/M/MS/MSCHWERN/ExtUtils-MakeMaker-6.56.tar.gz
否则会报：
Warning: prerequisite ExtUtils::MakeMaker 6.48 not found. We have 6.30.
Warning: prerequisite Test::Simple 0.9 not found. We have 0.62.
Using DBI 1.625 (for perl 5.008008 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/
Using DBI 1.625 (for perl 5.008008 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBI/
Writing Makefile for DBD::ODBC
Warning: not all required environment variables are set.

Warning: Will not be able to run tests as you have not defined
all of DBI_DSN,DBI_USER and DBI_PASS environment variables.

?

$ tar vxzf DBD-ODBC-1.616.tar.gz
$ cd DBD-ODBC-1.616
$ perl Makefile.PL
$ make
$ make install
测试dbi安装是否成功：
$ ?perl -e 'use DBI 1.625;'
如果出现"DBI version 1.616 required--this is only version 1.30 at -e line 1."错误，需要更新dbi；
如果出现 "DBI cannot be found in @INC"错误，表明还没有安装

4、安装DBD-ODBC?

? ?在安装之前要先设置一下环境变量
# export ODBCHOME=/usr/local/unixODBC

#export LD_LIBRARY_PATH=/usr/local/unixODBC/lib
之后开始编译安装
# tar vxzf cd DBD-ODBC-1.43.tar.gz
# cd ?DBD-ODBC-1.43
# perl Makefile.PL
# make
# make install
测试DBD-ODBC模块是否安装成功：
$perl -e 'use DBD::ODBC;'
$perl -MDBD::ODBC -e 'print $DBD::ODBC::VERSION;'
$perl -MDBI -e 'DBI->installed_versions;'
http://www.easysoft.com/developer/interfaces/odbc/64-bit.html#easysoft




所有的软件到现在都安装完成了，接下来就是配置了?

5、配置freetds

#pwd
/usr/local/freetds/etc
#ls
freetds.conf ?locales.conf ?pool.conf
# vi freetds.conf
增加如下内容：
[WIN-SGTEST]
host=192.168.251.218
port=1433
tds version=8.0
--其中的[WIN-SGTEST]代表在客户端使用的服务器名称，host代表SQLServer服务器的IP地址，port代表端口
测试连接；
# /usr/local/freetds/bin/tsql -H 192.168.251.218 -p 1433 -U sa -P 11
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> use sg0157
2> go
1> select count(1) from tb1
2> go
1729
(1 row affected)
1>?

[root@sggp bin]# ./tsql -H 192.168.1.127 -p 1433 -U sa -P 11
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20017 (severity 9):
??????? Unexpected EOF from the server
??????? OS error 115,"Operation now in progress"
Error 20002 (severity 9):
??????? Adaptive Server connection failed
There was a problem connecting to the server
[root@sggp bin]# ./tsql -C
Compile-time settings (established with the "configure" script)
??????????????????????????? Version: freetds v0.92.79
???????????? freetds.conf directory: /usr/local/freetds/etc
???? MS db-lib source compatibility: yes
??????? Sybase binary compatibility: no
????????????????????? Thread safety: yes
????????????????????? iconv library: yes
??????????????????????? TDS version: 5.0
????????????????????????????? iODBC: no
?????????????????????????? unixodbc: yes
????????????? SSPI "trusted" logins: no
?????????????????????????? Kerberos: no
[root@sggp bin]#

?

6、配置unixODBC

# cd /usr/local/unixODBC/etc
--向ODBC添加SQLServer驱动
# vi odbcinst.ini
#####加入如下内容
[FreeTDS]
Driver=/usr/local/freetds/lib/libtdsodbc.so
Setup=/usr/local/freetds/lib/libtds.so
FileUsage=1


# vi odbc.ini
[Server1];DSN名称
Driver = FreeTDS ? ;与上边的FreeTDS相对应
Server = ##隐去的服务器地址##
Port = ##隐去的端口号##
TDS_Version = 8.0 ? ;这个东西必须加，否则会给你报个什么unexpect EOF~~~这个东西小弟找了快2小时。。。


[Server1]
Driver = FreeTDS
Server = 192.168.251.218
Database = sg0157
Port = 1433
TDS_Version = 8.0


# odbcinst -q -d?
[PostgreSQL]
[MySQL]
[PostgreSQL64]
[MySQL64]
#?






保存并退出，测试ODBC的连接
# /usr/local/unixODBC/bin/isql -v Server1 sa 11
+---------------------------------------+
| Connected! ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
| sql-statement ? ? ? ? ? ? ? ? ? ? ? ? |
| help [tablename] ? ? ? ? ? ? ? ? ? ? ?|
| quit ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?|
| ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? |
+---------------------------------------+
SQL>?
SQL> select count(1) from "xxx".tb1
+------------+
| ? ? ? ? ? ?|
+------------+
| 1729 ? ? ? |
+------------+
SQLRowCount returns 1
1 rows fetched
SQL>?
SQL> quit
测试通过.




开始测试perl能否正确使用ODBC ，在使用perl连接ODBC之前要做些准备工作，把ODBC的共享库都复制到/usr/lib目录
# cp /usr/local/unixODBC/lib/libodbc.* /usr/lib

8、perl脚本测试能否成功

Crontab 中运行需要在perl脚本的最前面加入以下代码： set ENV{'LD_LIBRARY_PATH'}='/usr/local/freetds/lib:/usr/local/unixODBC/lib:'; set ENV{'ODBCHOME'}='/usr/local/unixODBC'; set ENV{'SYBASE'}='/usr/local/freetds'; # vi test.pl #!/usr/bin/perl use DBI; my $dbh=DBI->connect('dbi:ODBC:Server1','sa','11'); my $sth=$dbh->prepare("select * from "xxx".tb1"; $sth->execute(); while (@data=$sth->fetchrow_array()){ print "$data[0] $data[1] $data[2]n"; } #!/usr/bin/perl use DBI; $dbh=DBI->connect('dbi:ODBC:Server1','11'); my $sth=$dbh->prepare("select * from tb1"); $sth->execute(); while (@data=$sth->fetchrow_array()){ print "$data[0] $data[1] $data[2]n"; } ? 保存退出 # perl test.pl 如果可以看到数据库中的记录，就说明可以正常使用ODBC perl了。 #!/usr/bin/perl # lookinc - where to look for modules $"="n"; print "@INCn"; my $dbh=DBI->connect("DBI:ODBC:ODBC-SQL-test","用户名","密码") or die "Can't connect to LocalSQL"; [root@pc02 oracle]# perl t01.pl install_driver(ODBC) failed: Can't locate DBD/ODBC.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .) at (eval 3) line 3. Perhaps the DBD::ODBC perl module hasn't been fully installed,or perhaps the capitalisation of 'ODBC' isn't right. Available drivers: DBM,ExampleP,File,Gofer,Proxy,Sponge. ?at t01.pl line 3 [root@pc02 oracle]#? [root@pc02 oracle]# perl t01.pl? install_driver(ODBC) failed: Can't load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/ODBC/ODBC.so' for module DBD::ODBC: libodbc.so.2: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/DynaLoader.pm line 230. ?at (eval 3) line 3 Compilation failed in require at (eval 3) line 3. Perhaps a required shared library or dll isn't installed where expected ?at t01.pl line 3 [root@pc02 oracle]#?