php 如何在 Laravel 中使用 SQL Server 连接？

How to use SQL Server connection in Laravel?

提问by ghiscoding

I got a working project made in Laravel 3 that I have to switch to MsSQL Server (not my call though, sniff...) and I don't understand the Laravel configuration on this database type...

我有一个在 Laravel 3 中制作的工作项目，我必须切换到 MsSQL Server（虽然不是我的电话，嗅探......），但我不了解此数据库类型的 Laravel 配置......

I changed the default inside database.phpto this 'default' => 'sqlsrv'then I configured the host, database, username, password in the sqlsrvarray but then I get this error message:

我将内部的默认值更改database.php为这个，'default' => 'sqlsrv'然后我在sqlsrv数组中配置了主机、数据库、用户名、密码，但随后我收到此错误消息：

This extension requires the Microsoft SQL Server 2012 Native Client ODBC Driver to communicate with SQL Server`

此扩展需要 Microsoft SQL Server 2012 Native Client ODBC 驱动程序才能与 SQL Server 通信

After some research I found that we need PDO of SQLSRV, which I already have as version 5.4 and in my phpinfoI get this returned pdo_sqlsrv support : enabledso it seems all correct but at the same time it seems to ask for ODBC SQLSRV connections, does I really have to use that?

经过一些研究，我发现我们需要 SQLSRV 的 PDO，我已经在 5.4 版中使用了它，并且在我的版本中我phpinfo得到了它的返回，pdo_sqlsrv support : enabled所以它看起来都是正确的，但同时它似乎要求 ODBC SQLSRV 连接，我真的需要用那个？

I would rather connect directly from the Laravel database connection... but even if I need it and I created some ODBC connection to the server, how do I put them inside my configuration? Here is what I have in my configuration (of course the my...is replace by my real details) :

我宁愿直接从 Laravel 数据库连接进行连接......但即使我需要它并且我创建了一些到服务器的 ODBC 连接，我如何将它们放入我的配置中？这是我的配置中的内容（当然，my...它被我的真实详细信息替换）：

'sqlsrv' => array(
    'driver'   => 'sqlsrv',
    'host'     => 'myServerIP', 
    'database' => 'myDatabase',
    'username' => 'myUsername',
    'password' => 'myPassword',
    'prefix'   => '',
 ),

Any help would be greatly appreciated, the only thing I found close to my problem was this link from the Laravel forum: Laravel query on SQL Serverbut then it just stop without giving any configurations.

任何帮助将不胜感激，我发现唯一接近我的问题的是来自 Laravel 论坛的这个链接：Laravel query on SQL Server但它只是停止而不提供任何配置。

采纳答案by ghiscoding

Ahh after a lot more research, I found out that my driver of SQL Server Native Client 10.0was out of date and not sufficient to connect to a MsSQL Server 2008with PDO, you need to have at least version 11.0in order to connect to an SQL Server 2005+

啊经过大量研究，我发现我的驱动程序SQL Server Native Client 10.0已过时并且不足以连接到MsSQL Server 2008带有 PDO 的驱动程序，您需要至少有版本11.0才能连接到 SQL Server 2005+

If you want to validate your driver version, you can go through Control Panel >> Administrative Tools >> Data Sources (ODBC)and then click on the Driverstab to find out which SQL Server Native Clientyou have already installed. If you want to update your driver, depending on your OS configuration you may choose the proper link below...

如果您想验证您的驱动程序版本，您可以通过Control Panel >> Administrative Tools >> Data Sources (ODBC)然后单击Drivers选项卡以找出SQL Server Native Client您已安装的驱动程序版本。如果您想更新您的驱动程序，根据您的操作系统配置，您可以选择下面的正确链接...

SQL Server 2012 Native Client
x86 (32 bits) Package http://go.microsoft.com/fwlink/?LinkID=239647&clcid=0x409
x64 (64 bits) Package http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

SQL Server 2012 Native Client
x86（32 位）包http://go.microsoft.com/fwlink/?LinkID=239647&clcid=0x409
x64（64 位）包http://go.microsoft.com/fwlink/?LinkID= 239648&clcid=0x409

回答by Mahmoud Zalt

That's what I did to connect my Laravel app to MS SQL Server:

这就是我将 Laravel 应用程序连接到 MS SQL Server 所做的工作：

1. sudo apt-get update

2. sudo apt-get install?php5-sybase

1. sudo apt-get 更新

2. sudo apt-get install?php5-sybase

if you try to install php5-mssql it will install php5-sybase anyway, it is “Sybase?/?MS?SQL?Server?module?for?php5”.

如果您尝试安装 php5-mssql，它无论如何都会安装 php5-sybase，它是“Sybase?/?MS?SQL?Server?module?for?php5”。

this will make Laravel?use?the?dblib?driver?instead?of?sqlsrv.

这将使 Laravel?use?the?dblib?driver?instead?of?sqlsrv。

check out this file for reference Illuminate\Database\Connectors\SqlServerConnector.php.

查看此文件以供参考 Illuminate\Database\Connectors\SqlServerConnector.php。

1. open this file:?/etc/freetds/freetds.conf

3. 打开这个文件：? /etc/freetds/freetds.conf

under [global]section and under tds?version?=?4.2

下[global]节和下tds?version?=?4.2

add tds?version?= 8.0? ? ? ? ?? (without ; semi column at the beginning of the line)

添加tds?version?= 8.0？? ? ? ?? （没有 ; 行首的半列）

and add client?charset?=?UTF-8just under the above line ? ? ? ? ? ?(without ; semi column at the beginning of the line)

并添加客户端？charset?=?UTF-8就在上面那条线下？? ? ? ? ? （没有 ; 行首的半列）

This?lets?the?driver?encode?all?the?data?in?utf-8?and?avoid?the?strange?characters?in?data

这？让？驱动程序？编码？所有？数据？输入？utf-8？和？避免？奇怪？字符？输入？数据

the file will look like this:

该文件将如下所示：

[global]

? ? ? ? #?TDS?protocol?version

;? ? ? ?tds?version?=?4.2

? ? ? ? tds?version?=?8.0

? ? ? ? client?charset?=?UTF-8

1. create file locales.confin this directory /etc/freetdsand past the following inside it: ? ? (this allow?correct?parsing?of?dates?with?SQL?Server).

4. 在此目录/etc/freetds 中创建文件locales.conf 并在其中传递以下内容：? （这允许？正确？解析？日期？使用？SQL？服务器）。

---

---

[default]

? ? date?format?=?%Y-%m-%d?%I:%M:%S.%z

[en_US]

? ? date?format?=?%b?%e?%Y?%I:%M:%S:%z%p

? ? language?=?us_english

? ? charset?=?iso_1

---

---

1. edit the php.inifile

5. 编辑php.ini文件

first find the file with?php -i | grep php.ini

首先找到文件？php -i | grep php.ini

then open the file and search for?mssql.charset? ? ? (it will probably be like disabled like this ? ? ? ? ;?mssql.charset?=?"ISO-8859-1"? ? )

然后打开文件并搜索？mssql.charset? ? ? （它可能会像这样被禁用 ? ? ? ? ;?mssql.charset?=?"ISO-8859-1"? ? )

make it?mssql.charset?=?"UTF-8"? ?<<make sure you remove the ; semi column to enable it>>

做了？mssql.charset?=?"UTF-8"? ?<< 确保删除 ; 半列启用它>>

save and exit

保存并退出

1. now restartyour server (nginx?or?apache or php-fpm)

2. of course you know that you need to set the database config to use sqlsrv by default 'default' => 'sqlsrv',and add your credentials.

6. 现在重新启动您的服务器（nginx？或？apache 或 php-fpm）

7. 当然，您知道您需要将数据库配置设置为默认使用 sqlsrv'default' => 'sqlsrv',并添加您的凭据。

---

---

OTHER METHODS:

其他方法：

you can use ODBC driver to connect

您可以使用 ODBC 驱动程序连接

here's a nice package?https://github.com/ccovey/odbc-driver

这是一个不错的包裹？https://github.com/ccovey/odbc-driver

回答by insign

For Linux, Laravel 5and php 7.1:

对于 Linux、Laravel 5和 php 7.1：

1. Add connection as says here
2. sudo apt install php7.1-sybase
3. Try again :)

1. 添加连接，说这里
2. sudo apt install php7.1-sybase
3. 再试一次 ：）

回答by Job Ferrari

GOT THE SOLUTION. TRY THIS!

得到了解决方案。尝试这个！

1. Click Protocols for the Instance you created

   Refer :https://i.stack.imgur.com/O6Dyn.png

2. Enable the IP settings

   Refer :https://i.stack.imgur.com/64j11.png

3. Right click on the IP settings>properties>IP Addresses>IP All>IP Port: Set it to 1433 as default SQL Server database port.

   Refer: https://i.stack.imgur.com/ZQ7MR.png

1. 单击您创建的实例的协议

   参考：https: //i.stack.imgur.com/O6Dyn.png

2. 启用 IP 设置

   参考：https: //i.stack.imgur.com/64j11.png

3. 右键单击 IP 设置>属性>IP 地址>IP 全部>IP 端口：将其设置为 1433 作为默认 SQL Server 数据库端口。

   参考：https: //i.stack.imgur.com/ZQ7MR.png

Hope it will help. Thank you!

希望它会有所帮助。谢谢！