尝试使用unixODBC/FreeTDS连接到PHP中的MS SQL Server的iODBC错误 [英] iODBC error trying to connect to MS SQL Server in PHP with unixODBC/FreeTDS

问题描述

我正在尝试从Mac上的PHP(最终是在具有FreeTDS和unixODBC的Ubuntu服务器上，通过PHP连接到远程MS SQL Server数据库，但是即使我似乎一切都设置正确，我仍收到iODBC错误，我不确定如何解决它们.

I am trying to connect to a remote MS SQL Server db from PHP on Mac (eventually on an Ubuntu server( with FreeTDS and unixODBC, but even though I seem to have everything set up properly, I'm getting iODBC errors, and I'm not sure how to get around them.

我正在使用MacPorts，因此我的配置是:

I'm using MacPorts, so my config is:

/opt/local/etc/freetds.conf ::

/opt/local/etc/freetds.conf::

[bti_db]
host = 123.45.67.89 (IP address changed to protect the innocent)
port = 14333
tds version = 8.0

/opt/local/etc/odbcinst.ini:

/opt/local/etc/odbcinst.ini:

[FreeTDS]
Description = TDS Driver (Sybase/MSSQL)
Driver = /opt/local/lib/libtdsodbc.so
Setup = /opt/local/lib/libtdsS.so
FileUsage = 1

/opt/local/etc/odbc.ini:

/opt/local/etc/odbc.ini:

[bti_dsn]
Driver = FreeTDS
Description = My Database
Trace = no
Servername = bti_db
Database = btidata

但是，每当我尝试使用'bti_dsn'与odbc_connect()连接时

However, whenever I try to connect with odbc_connect() using the 'bti_dsn'

$conn = odbc_connect('bti_dsn;, $user, $pw);

我收到此错误:

警告:odbc_connect()[function.odbc-connect]:SQL错误:[iODBC] [Driver Manager]找不到数据源名称，也未指定默认驱动程序.无法加载驱动程序，SQLConnect中的SQL状态IM002

Warning: odbc_connect() [function.odbc-connect]: SQL error: [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQL state IM002 in SQLConnect

在我的phpinfo()的ODBC部分中，我看到ODBC库定义为iodbc，并且PHP使用'--with-iodbc =/usr'进行了编译，因此我猜测配置是我的问题.如何解决这个问题，以便它使用我已设置的unixODBC/FreeTDS?

In the ODBC section my phpinfo(), I see ODBC Library defined as iodbc, and PHP is compiled with '--with-iodbc=/usr', so I'm guessing that config is my problem. How can I get around this so that it uses unixODBC/FreeTDS I have set up?

谢谢.

推荐答案

iODBC 默认安装为Mac OS X的；从Jaguar(10.2.x)开始.在Mac上不需要UnixODBC，如果您不是一个认真的专家，它可能会导致很多错误.对于在Mac OS X上将 PHP与iODBC结合使用，有专门的指南.为了获得最佳结果，您可能还需要升级到最新的适用于Mac OS的iODBC X .

iODBC is installed by default as part of Mac OS X; has been since Jaguar (10.2.x). There's no need for UnixODBC on Macs, and it can lead to lots of errors if you're not a serious expert. There is a specific guide to using PHP with iODBC on Mac OS X. For best results, you may also want to upgrade to the latest iODBC for Mac OS X.

/opt/local/etc不应通过.profile或其他方式不添加到您的$PATH中.

/opt/local/etc should not be added to your $PATH, through .profile or otherwise.

PHP肯定在UnixODBC之前找到了iODBC，但这不是问题. UnixODBC和iODBC通常是(并且完全是)等同于API的ODBC驱动程序管理器.如果您真的很在意这一部分，则可以更改$DYLD_LIBRARY_PATH(Mac OS X的Linux $LD_LIBRARY_PATH版本)，但是如果PHP是与iODBC框架(而不是dylib)链接在一起的，则不会有所作为.

PHP is definitely finding iODBC before UnixODBC, but this should not be a problem; UnixODBC and iODBC are generally (and are meant to be fully) API-equivalent ODBC driver managers. If you're really concerned about that part, you can change the $DYLD_LIBRARY_PATH (Mac OS X's version of Linux's $LD_LIBRARY_PATH) -- but if PHP was linked against the iODBC Frameworks, as opposed to the dylibs, this won't make any difference.

(请注意，$DYLD_LIBRARY_PATH还必须包含/opt/local/lib，否则您的FreeTDS驱动程序将无法加载.)

(Note that $DYLD_LIBRARY_PATH also must include /opt/local/lib or your FreeTDS driver won't load.)

对于特定错误，您的报告-如果您未使用Mac的默认ODBC配置文件(系统级别位于/Library/ODBC/odbc[inst].ini；用户级别位于~/Library/ODBC/odbc[inst].ini，则PHP需要设置几个环境变量) ...如果存在~/.odbdc[inst].ini文件，则应将它们混合到~/Library/ODBC/文件中，并用符号链接代替.

For the specific error your report -- PHP needs to have a couple of environment variables set, if you're not using the Mac's default ODBC configuration files (System level are in /Library/ODBC/odbc[inst].ini; User level are in ~/Library/ODBC/odbc[inst].ini ... if there are ~/.odbdc[inst].ini files present, they should be blended into the ~/Library/ODBC/ files and replaced by symlinks to the same).

如果您不想使用iODBC或不想使用这些默认文件，则必须将$ODBCINI设置为定位已定义DSN的odbc.ini文件的目标，而$ODBCINSTINI以odbcinst.ini文件为目标，该文件注册了要使用的驱动程序.

If you don't want to use iODBC, or don't want to use those default files, you have to set $ODBCINI to target the odbc.ini file where you've defined your DSN, and $ODBCINSTINI to target the odbcinst.ini file which registers the driver you want to use.

假设您要执行上述所有操作，则应将此类行添加到您的*.php文件中(最好通过require或include语句进行优化，以最大程度地减少将来的编辑)

Assuming you want to do all of the above, lines like these should be added to your *.php files (optimally via a require or include statement to minimize future editing) --

putenv("DYLD_LIBRARY_PATH=/path/to/odbcsdk/lib;$DYLD_LIBRARY_PATH");
putenv("ODBCINSTINI=/path/to/odbcinst.ini");
putenv("ODBCINI=/path/to/odbc.ini");

对于DYLD_LIBRARY_PATH设置我不太清楚，因为您没有指定UnixODBC库的位置.但是，如果可以使用iODBC作为驱动程序管理器，并且只希望加载FreeTDS库，则以下方法应该可以工作-

I can't be exact about the DYLD_LIBRARY_PATH setting, because you didn't specify where your UnixODBC libraries are. However, if you are OK with iODBC being the driver manager, and just want your FreeTDS libraries to load, the following should work --

putenv("DYLD_LIBRARY_PATH=/opt/local/lib;$DYLD_LIBRARY_PATH");
putenv("ODBCINSTINI=/opt/local/etc/odbcinst.ini");
putenv("ODBCINI=/opt/local/etc/odbc.ini");

我希望这会有所帮助.

P.S.在您的DSN定义中，这一行-

P.S. In your DSN definition, this line --

Driver = FreeTDS

-应该重写.应该将人类友好的驱动程序名称括在大括号({FreeTDS})中，或者将驱动程序库的完整路径(/opt/local/lib/libtdsodbc.so)用作值.

-- should be rewritten. Either the human-friendly driver name should be wrapped in braces ({FreeTDS}), or the full-path to the driver library (/opt/local/lib/libtdsodbc.so) should be the value instead.

Driver = {FreeTDS}
Driver = /opt/local/lib/libtdsodbc.so

我假设您在odbcinst.ini中也有类似以下索引条目的内容-

I'm presuming that you also have something like the following index entry in your odbcinst.ini --

[ODBC Drivers]
FreeTDS = Installed

-和odbc.ini中的以下索引条目-

-- and something like the following index entry in your odbc.ini --

[ODBC Data Sources]
bti_dsn = FreeTDS

...但是现在我注意到您的$ conn行可能只需要更正.查看odbc_connect的参数.

...but now I notice that your $conn line may just need correction. Look at the arguments to odbc_connect.

$conn = odbc_connect('bti_dsn;, $user, $pw);

那应该看起来更像是-

$conn = odbc_connect("bti_dsn", "$user", "$pw");

这篇关于尝试使用unixODBC/FreeTDS连接到PHP中的MS SQL Server的iODBC错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！