Linux下Laravel访问MSSQL的方式小结

PHP访问MSSQL的两个方案

开源的:FreeTDS+DBLIB 和 闭源的:MS ODBC+SQLSRV.
两种方案都是前一个为Linux系统下驱动,后一个为PHP扩展.

FreeTDS+DBLIB

yum install freetds php72w-pdo_dblib

为什么有MS自家的驱动我们还要用开源的呢?因为M$居然不支持SQL 2008 R2之前的SQL SRV…

vim /etc/freetds.conf

加入要访问的SQL SRV的讯息

[sql01]
        host = 192.168.0.1
        port = 1433 #如果用的不是默认实例,自己去SQL SRV配置里去查端口.
        tds version = 8.0  #不同的SQL SRV 要用不同的版本连,2005用8.0
        client charset = UTF-8 #这个很重要,对于SQLSRV的collation中文的情况下

此处tds版本选择存在疑问,参考下列文档:
https://www.freetds.org/userguide/ChoosingTdsProtocol.html#ftn.idm68026320

UnixODBC+SQLSRV

为什么有能吃的开源方案还要用MS的残缺方案?因为他自家的东西应该兼容性更好,用起来更安心.

1. 新增MS的源,然后安装相应的包

$ curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
$ yum update
$ yum remove unixODBC #不移没事,移了安心,怕有冲突
$ ACCEPT_EULA=Y yum install msodbcsql mssql-tools 
$ yum install unixODBC-devel

2. 安装MS的PHP Driver for SQLSRV
   2.1. 从这里下载https://github.com/Microsoft/msphpsql/releases/tag/v5.3.0
   2.2. 解包安装,然后修改php.ini 以启用扩展

  	tar xvzf sqlsrv-5.3.0.tgz
  	cd sqlsrv-5.3.0/
  	phpize
  	./configure --with-php-config=/usr/bin/php-config
  	make
  	sudo make install
  	echo extension=pdo_sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/30-pdo_sqlsrv.ini
  	echo extension=sqlsrv.so >> `php --ini | grep "Scan for additional .ini files" | sed -e "s|.*:\s*||"`/20-sqlsrv.ini

2.3. 或者可以用 remi 源 用 yum install php-sqlsrv php-pdo_sqlsrv ,或者还可以用 pecl install sqlsrv pecl install pdo_sqlsrv
2.4. 重启Apache或Nginx

Laravel 访问MSSQL

在config/database.php 里配置要访问的SQLSRV,或者先配置.env然后再配置database.php.

'xxxx' => [
            'driver' => 'sqlsrv',   //这里不能错
            'host' => env('DB_HOST1', 'localhost'),
            'port' => env('DB_PORT1', '1433'),
            'database' => env('DB_DATABASE1', 'forge'),
            'username' => env('DB_USERNAME1', 'forge'),
            'password' => env('DB_PASSWORD1', ''),
            'charset' => 'utf8',
            'prefix' => '',
        ],

问题1:同时安装了两套方案的情况下,Laravel用什么连接SQL 2005?

答案是:FREETDS+DBLIB

问题2: 只装了微软方案的情况下连接SQL08 R2之前版本会怎么样?

SQLSTATE[08001]: [unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746"

如何让Laravel 同时支持两套方案,以连接不同版本的SQLSRV.

此行为纯属强迫症,可能没有多少实际意义!

修改DatabaseManager.php

namespace Illuminate\Database;
....
public function supportedDrivers() //Laravel 支持的Drivers
    {
        return ['mysql', 'pgsql', 'sqlite', 'sqlsv','dblib'];
    }
    public function availableDrivers()//Laravel 可用Drivers
    {
        return array_intersect(
            $this->supportedDrivers(),PDO::getAvailableDrivers()
       //     str_replace('dblib', 'sqlsrv', PDO::getAvailableDrivers()) 不要要dblib替换掉
        );
    }

修改SqlServerConnetor.php

namespace Illuminate\Database\Connectors;
.....
  protected function getDsn(array $config)
    {
            if($config['driver']=="dblib"){//用dblib支持sql08及以前版本,ms的pdo-sqlsrv支持08R2以后版本
            return $this->getDblibDsn($config);
        /*if (in_array('dblib', $this->getAvailableDrivers())) {
            return $this->getDblibDsn($config);
        } else*/if ($this->prefersOdbc($config)) {
            return $this->getOdbcDsn($config);
        }
         };
        return $this->getSqlSrvDsn($config);
    }

修改ConnectionFactory.php

namespace Illuminate\Database\Connectors;
....有两处,只写一处
switch ($config['driver']) {
            case 'mysql':
                return new MySqlConnector;
            case 'pgsql':
                return new PostgresConnector;
            case 'sqlite':
                return new SQLiteConnector;
            case 'sqlsrv':
                return new SqlServerConnector;
            case 'dblib'://用dblib支持sql2005及以前版本,ms的pdo-sqlsrv支持08以后版本
                return new SqlServerConnector;
        }

database.php里driver指定为dblib或sqlsrv

'oooo' => [
            'driver' => 'dblib',
            'host' => env('DB_HOST1', 'localhost'),
            'port' => env('DB_PORT1', '1433'),
            'database' => env('DB_DATABASE1', 'forge'),
            'username' => env('DB_USERNAME1', 'forge'),
            'password' => env('DB_PASSWORD1', ''),
            'charset' => 'utf8',
            'prefix' => '',
        ],

用php artisan tinker 试一下

[root@web02 ldap]# php artisan tinker
Psy Shell v0.9.9 (PHP 7.2.11 — cli) by Justin Hileman
>>> DB::connection('dblib')->select("select count(*) FROM table;")
=> [
     {#2998
       +"computed": 2338,
     },
   ]
>>>

Laravel 5.8及以后如何連接SQL2005及以前版本

修改 SqlServerConnector.php 中getDsn:

if ( ($config['dblib'] ?? null)  === true){ //sql2005及以前必須使用dblib...
            return $this->getDblibDsn($config);
        }

在config\database.php 中加個指定dblib的參數

 'hrdb' => [
            'driver' => 'sqlsrv',
            'url' => env('DATABASE_URL'),
           'host' => env('DB_HOST', 'localhost'),
            'port' => env('DB_PORT', '1433'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'dblib' => true,
        ],