Centos6.6 安装odbc使用微软的driver连接sqlserver

安装

RedHat Enterprise Server 6

Copy
sudo su
curl https://packages.microsoft.com/config/rhel/6/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo yum install unixODBC-devel

RedHat Enterprise Server 7

Copy
sudo su
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install msodbcsql
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo yum install unixODBC-devel

编译pdo-odbc

解压php-5.6.17.tar.gz源码。

tar zxf php-5.6.17.tar.gz
cd php-5.6.17
cd ext
cd pdo-odbc
/usr/local/bin/phpize
./configure --with-pdo-odbc=unixODBC,/usr/ --with-php-config=/usr/local/bin/php-config
make
sudo make install

重点是php的源码包里有一个ext文件夹，好多扩展其实已经放到里边了

配置

查看/etc/odbcinst.ini中，应该有了sql server的连接驱动

[PostgreSQL]
Description=ODBC for PostgreSQL
Driver=/usr/lib/psqlodbcw.so
Setup=/usr/lib/libodbcpsqlS.so
Driver64=/usr/lib64/psqlodbcw.so
Setup64=/usr/lib64/libodbcpsqlS.so
FileUsage=1

[MySQL]
Description=ODBC for MySQL
Driver=/usr/lib/libmyodbc5.so
Setup=/usr/lib/libodbcmyS.so
Driver64=/usr/lib64/libmyodbc5.so
Setup64=/usr/lib64/libodbcmyS.so
FileUsage=1

[ODBC Driver 13 for SQL Server]
Description=Microsoft ODBC Driver 13 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.0
UsageCount=1

注意这个配置文件中，方括号里边的就是驱动名称，用来在odbc.ini中选择驱动用的。

odbc.ini例子

[MyDSN]
Driver=ODBC Driver 13 for SQL Server
Description=My Test ODBC Database Connection
Trace=Yes
Server=test.sqlserver.com
Port=1433
Database=dbname

连接测试

isql MyDSN username passwod


+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

php code example

<?php

 putenv('ODBCSYSINI=/etc/');
 putenv('ODBCINI=/etc/odbc.ini');

    $username = "xxxx";
    $password = "xxxxxx";
    try {
      $dbh = new PDO("odbc:MyDSN",
                    "$username",
                    "$password"
                   );
    } catch (PDOException $exception) {
      echo $exception->getMessage();
      exit;
    }
    echo var_dump($dbh);
    unset($dbh);
?>

/usr/local/bin/php test.php 


#Result
object(PDO)#1 (0) {
}