如何在Windows中使用Perl连接MS SQL数据库? [英] How to connect ms sql database using perl in Windows?

问题描述

我想通过perl语言连接ms sql数据库.我完成了以下步骤.

I want to connect ms sql database through perl language. The following steps are I made.

• 我已经以"SampleDb"的名称创建了一个数据库表
• 已配置ODBC，并且我已将sql服务器驱动程序的名称设置为"SampleDb"
• 现在我尝试按以下方式进行连接，

my $dbs = "dbi:ODBC:DRIVER={SQL Server};SERVER={SampleDb}";
my $dbh = DBI->connect($dbs, "username", "password");

但是现在我遇到了以下错误

But Now I got the following error

DBI connect('DRIVER={SQL Server};SERVER={SampleDB}','username',...) failed: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (SQL-08001) [state was 08001 now 01000]
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (SQL-01000)

该如何解决?或者 如何使用Perl通过ODBC连接ms sql?

How to solve this ? or How to Connect ms sql using perl through ODBC ?

推荐答案

此问题已得到回答(并接受!!).只是提供了使用ODBC从Linux上运行的perl连接MSSQL服务器的详细步骤.

This question has already been answered (and accepted !!). Just providing detailed steps for connecting MSSQL server from perl running on Linux using ODBC.

在学习perl内容之前，需要在Linux机器上安装和配置odbc环境.

Before you get into perl stuff, you need to install and configure odbc environment on the Linux box.

安装以下软件包:

Fedora:
    unixODBC-devel.i686 
    unixODBC.i686
    AND
    freetds.i686 
    freetds-devel.i686 
    freetds-doc.i686
Ubuntu:
    unixodbc 
    unixodbc-dev
    AND
    freetds-bin 
    freetds-common  
    freetds-dev
    tdsodbc

在安装这些软件包之后，我们必须能够使用freetds来通过MS SQL服务器测试身份验证网络身份验证.

After these packages are installed we MUST be able to use freetds to test authentication network authentication with the MS SQL server.

root@ubuntu:/home# tsql -S <db_host_name> -p 1433 -U perluser -P password
locale is "en_IN"
locale charset is "UTF-8"
1>

如果以上操作失败，请检查该用户的MSSQL db权限.还要检查MSSQL日志.在成功运行以上命令之前，请勿继续.另外，上述命令的失败与unixodbc或freetds配置无关.

If above fails, then check the MSSQL db permission for this user. Also check MSSQL logs. Do not proceed until you run above command successfully. Also, failure of above command has nothing to do with the unixodbc OR freetds configuration.

配置odbc.ini和odbcinst.ini和freetds.conf文件.

/etc/odbc.ini将包含DSN信息:

root@ubuntu:/home# cat /etc/odbc.ini 
[odbc-test]
Description     = test
Driver          = ms-sql
Servername      = ms-sql
Database        = <db_name>
UID             = perluser
Port            = 1433

• 请注意，Driver字段引用名为[ms-sql]的/etc/odbcinst.ini上下文.
• Servername字段引用我也命名为[ms-sql]的/etc/freetds.conf上下文.
• Database字段是可选的，但由于要连接到特定的数据库，因此我正在使用它.
• Please note that, the Driver field refers to /etc/odbcinst.ini context named [ms-sql].
• The Servername field refers to the /etc/freetds.conf context that I also named [ms-sql].
• Database field is optional but I am using it as I want to connect to a particular Database.

/etc/odbcinst.ini文件包含驱动程序信息:

/etc/odbcinst.ini file contains the Driver information:

root@ubuntu:/home# cat /etc/odbcinst.ini 
[ms-sql]
Description     = TDS Conection
Driver          = /usr/lib/odbc/libtdsodbc.so
Setup           = /usr/lib/odbc/libtdsS.so
UsageCount      = 1
FileUsage       = 1

• odbcinst.ini文件仅将odbc.ini文件定向到适当的驱动程序.
• Fedora的Driver和Setup条目分别是/usr/lib/libtdsodbc.so和/usr/lib/libtdsS.so.
• 另外，对于64位Linux机器，它分别是Driver64和Setup64而不是Driver和Setup.
• The odbcinst.ini file simply directs the odbc.ini file to the appropriate driver.
• The Driver and Setup entries for Fedora is /usr/lib/libtdsodbc.so and /usr/lib/libtdsS.so respectively.
• Also, for 64-bit Linux box, it would be Driver64 and Setup64 instead of Driver and Setup respectively.

配置/etc/freetds/freetds.conf(对于Fedora，为/etc/freetds.conf).它包含TDS信息.这是我的:

Configure /etc/freetds/freetds.conf ( /etc/freetds.conf for Fedora). It contains TDS information. Here is mine:

root@ubuntu:/home# tail  /etc/freetds/freetds.conf 
[ms-sql]
        host = <db_host_name>
        port = 1433
        tds version = 7.0
        dump file = /var/log/freetds.log

• 请注意，上下文名称[ms-sql]上方是odbc.ini中Servername的值.
• Please note that, above context name [ms-sql] is the value of Servername in odbc.ini.

通过使用isql命令连接MSSQL服务器来测试配置:

Test the configuration by connecting the MSSQL server using isql command:

root@ubuntu:/home# isql -v odbc-test perluser password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

• isql命令的odbc-test部分是在odbc.ini文件中定义的.
• 您必须收到上面的输出，内容为Connected!.如果不这样做，则说明您的配置存在问题，请再次执行上述所有步骤.
• The odbc-test portion of the isql command was defined in the odbc.ini file.
• You HAVE to receive above output which says Connected!. If you don’t, then there is some problem in your configuration and go through all the above steps again.

现在，

• 安装Perl DBI 模块.
• 安装 DBD::ODBC 模块.

然后在您的perl代码中使用ODBC，如下所示:

Then use the ODBC in your perl code as below:

my $dbh = DBI->connect ('dbi:ODBC:odbc-test', 'perluser', 'password');

希望这会有所帮助.

这篇关于如何在Windows中使用Perl连接MS SQL数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！