Linux下Laravel訪問MSSQL的方式小結

[yehlu]

https://www.itread01.com/content/1545402274.html

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中文的情況下
UnixODBC+SQLSRV
為什麼有能吃的開源方案還要用MS的殘缺方案?因為他自家的東西應該相容性更好,用起來更安心.

新增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
安裝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 試一下
[[email protected] 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,
},
]
>>>