How to connect PHP to Microsoft database using sqlSrv under Linux

PHP needs to use the extension pdo_srv when using sqlSrv to connect to Microsoft's SQLSERVER database.
pdo_srv extension installation can be installed directly using pecl

The environment when writing this article is
ubuntu 16.04 LTS
php 7.0.27
unixODBC 2.3.5
FreeTDS 1.00.82
2018-03-02

pecl
The program is in the bin directory under the PHP installation directory. If your PHP directory is not in the Linux environment variables. Or your pecl program is not linked to the system bin path, and you will be prompted that the pecl program is not installed.

Installing sqlsrv requires the support of unixODBC, so if you have not installed unixODBC before installation, you need to install unixODBC first, and unixODBC requires FreeTDS to connect to SQLSERVER, because unixODBC only defines a set of common interfaces. There is no specific implementation method.

1. Install FreeTDS
a), download

http://www.freetds.org/

b), compile and install

./configure --with-tdsver=auto --prefix=/usr/local/freetds --enable-msdblib --with-gnu-ld --enable-shared --enable-static --with-unixodbc=/usr/localmake
make install

Some tutorials on the Internet say –with-tdsver=8.0. I reported an error when compiling. For specific supported values, you can see with in ./configure –help -Tdsver description

After the installation is completed, an etc directory will be generated in /usr/local/freetds. We need to use the freetds.conf file.
c), Configuration
Configure the freetds.conf file. You don’t need to configure it

The method of not configuring freetds.conf can be found in the configuration link of unixODBC

[global]#全局配置tds version = auto#更多的配置可以参考freetds官方文档，我这里这样配置已经够用了[servertag]#定义一个叫 servertag 的节点，这个节点的名称可以自定义的，不是固定#所有 # 符号的内容都可以不写在配置文件中，这里只是注释解释用#更多的细节配置看官方描述#数据库的连接IP地址host = 192.168.9.12 #数据库的端口port = 1433 #连接时默认打开的库名，【可以不配置此项】database = master 
#tds version 有全局配置，这里也可以不写tds version = auto

2. Install unixODBC

a), download

  https://sourceforge.net/projects/unixodbc/files/unixODBC/
  http://www.unixodbc.org/
  以上两个网站都可以下载到，建议到第二个官网下载吧。

b), compile and install

./configure --prefix=/usr/local/unixODBC --includedir=/usr/include  --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etcmake 
make install

c), configure
Add configuration in /etc/odbc.ini

There are tutorials on the Internet saying that the compilation and installation is in /usr/local/etc/odbc.ini
, although this file is generated after compilation, the configuration in this file does not take effect. You still need to configure

[ODBC Data Sources]#描述而已，没有测过不写行不行，理论上可行SQLSERVER2018912 = 9.12 DB

[SQLSERVER2018912]#Driver 指向的目录是FreeTDS编译生成的文件，类似一个驱动文件，稍后讲解安装方法Driver = /usr/local/freetds/lib/libtdsodbc.so
Description  = 9.12 DB，文字描述
Trace = No#Server 可以直接写 FreeTDS的freetds.conf 中配置的servertag节点名#使用freetds.conf配置时，port 也不用配置了。#因为在此直接写server IP时能配置的选项很少，而freetds.conf可以配置的细节更多#此处我已经配置了IP，freetds.conf中其实就可以不用配置。只要安装好freetds即可Server = 192.168.9.12Port = 1433#7.4或auto应该都可以。没有详细研究过TDS_Version = 7.4[Default]#全局默认配置。理论上可以不写#同上，默认使用Driver = /usr/local/freetds/lib/libtdsodbc.so

in /etc/odbc.ini. After the above configuration is completed, you can test it.
Use the isql program generated when compiling unixODBC to test. The path is /usr/bin/isql

/usr/bin/isql SQLSERVER2018912 数据库账号 密码

isql. You can use isql –help to view the
configuration. If there are no errors, you can see that you are connected to the database. You can use sql statements to query things.
After the unixODBC configuration is completed, you can install the PHP extension

3. Install the PHP sqlsrv extension

pecl install sqlsrv

After installation, you need to load the extension in php.ini, and then Restart apache to take effect.
Pay attention to the content printed on the screen after compilation

The environment when writing this article is
ubuntu 16.04 LTS
php 7.0.27
unixODBC 2.3.5
FreeTDS 1.00.82
2018-03-02

When PHP uses sqlSrv to connect to Microsoft's SQLSERVER database, the extension pdo_srv is required.
pdo_srv extension installation can be directly installed using pecl

pecl
The program is in the bin directory under the PHP installation directory. If your PHP directory is not in the Linux environment variables. Or your pecl program is not linked to the system bin path, and you will be prompted that the pecl program is not installed.

Installing sqlsrv requires the support of unixODBC, so if you have not installed unixODBC before installation, you need to install unixODBC first, and unixODBC requires FreeTDS to connect to SQLSERVER, because unixODBC only defines a set of common interfaces. There is no specific implementation method.

1. Install FreeTDS
a), download

http://www.freetds.org/

b), compile and install

./configure --with-tdsver=auto --prefix=/usr/local/freetds --enable-msdblib --with-gnu-ld --enable-shared --enable-static --with-unixodbc=/usr/localmake
make install

Some tutorials on the Internet say –with-tdsver=8.0. I reported an error when compiling. For specific supported values, you can see with in ./configure –help -Tdsver description

After the installation is completed, an etc directory will be generated in /usr/local/freetds. We need to use the freetds.conf file.
c), Configuration
Configure the freetds.conf file. You don’t need to configure it

The method of not configuring freetds.conf can be found in the configuration link of unixODBC

[global]#全局配置tds version = auto#更多的配置可以参考freetds官方文档，我这里这样配置已经够用了[servertag]#定义一个叫 servertag 的节点，这个节点的名称可以自定义的，不是固定#所有 # 符号的内容都可以不写在配置文件中，这里只是注释解释用#更多的细节配置看官方描述#数据库的连接IP地址host = 192.168.9.12 #数据库的端口port = 1433 #连接时默认打开的库名，【可以不配置此项】database = master 
#tds version 有全局配置，这里也可以不写tds version = auto

2. Install unixODBC

a), download

  https://sourceforge.net/projects/unixodbc/files/unixODBC/
  http://www.unixodbc.org/
  以上两个网站都可以下载到，建议到第二个官网下载吧。

b), compile and install

./configure --prefix=/usr/local/unixODBC --includedir=/usr/include  --libdir=/usr/lib -bindir=/usr/bin --sysconfdir=/etcmake 
make install

c), configure
Add configuration in /etc/odbc.ini

There are tutorials on the Internet saying that the compilation and installation is in /usr/local/etc/odbc.ini
, although this file is generated after compilation, the configuration in this file does not take effect. You still need to configure

[ODBC Data Sources]#描述而已，没有测过不写行不行，理论上可行SQLSERVER2018912 = 9.12 DB

[SQLSERVER2018912]#Driver 指向的目录是FreeTDS编译生成的文件，类似一个驱动文件，稍后讲解安装方法Driver = /usr/local/freetds/lib/libtdsodbc.so
Description  = 9.12 DB，文字描述
Trace = No#Server 可以直接写 FreeTDS的freetds.conf 中配置的servertag节点名#使用freetds.conf配置时，port 也不用配置了。#因为在此直接写server IP时能配置的选项很少，而freetds.conf可以配置的细节更多#此处我已经配置了IP，freetds.conf中其实就可以不用配置。只要安装好freetds即可Server = 192.168.9.12Port = 1433#7.4或auto应该都可以。没有详细研究过TDS_Version = 7.4[Default]#全局默认配置。理论上可以不写#同上，默认使用Driver = /usr/local/freetds/lib/libtdsodbc.so

in /etc/odbc.ini. After the above configuration is completed, you can test it.
Use the isql program generated when compiling unixODBC to test. The path is /usr/bin/isql

/usr/bin/isql SQLSERVER2018912 数据库账号 密码

isql. You can use isql –help to view the
configuration. If there are no errors, you can see that you are connected to the database. You can use sql statements to query things.
After the unixODBC configuration is completed, you can install the PHP extension

3. Install the PHP sqlsrv extension

pecl install sqlsrv

After installation, you need to load the extension in php.ini, and then Restart apache to take effect.
Pay attention to what is printed on the screen after compilation

The above is the detailed content of How to connect PHP to Microsoft database using sqlSrv under Linux. For more information, please follow other related articles on the PHP Chinese website!