在 CentOS 7 下安装 SQL Server 2017 及创建 PHP 测试程序

关键字

CentOS, SQL Server, CentOS 7, SQL Server 2017, PHP, msodbcsql, mssql-tools, unixODBC-devel, PECL,PEAR, sqlsrv, pdo_sqlsrv

背景概述

SQL Server 是微软公司 Windows 上闭源数据库,当微软拥抱了开源,SQL Server 也被移植到 Linux,有时,我们还会需要通过 PHP 连接 SQL Server 来进行应该开发。

本文旨在帮助读者在 CentOS 7 下顺利安装 SQL Server 及 PHP 连接驱动,本安装过程应已经过笔者反复测试,以确保成功。

如果按此文安装时遇到任何问题,请留言或通过【桃花岛】官方联系我们。

软件依赖

  • CentOS 7
  • PHP
  • SQL Server
  • msodbcsql
  • mssql-tools
  • unixODBC-devel
  • PECL
  • PEAR
  • sqlsrv
  • pdo_sqlsrv

具体步骤

下载 SQL Server 2017 仓库配置,yum 安装

curl https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo | sudo tee /etc/yum.repos.d/mssql-server-2017.repo
sudo yum update
sudo yum install mssql-server

启动安装程序

[root@carbon ~]# sudo /opt/mssql/bin/mssql-conf setup

选择安装 Express 版本,输入用户名密码

Choose an edition of SQL Server:
  1) Evaluation (free, no production use rights, 180-day limit)
  2) Developer (free, no production use rights)
  3) Express (free)
  4) Web (PAID)
  5) Standard (PAID)
  6) Enterprise (PAID)
  7) Enterprise Core (PAID)
  8) I bought a license through a retail sales channel and have a product key to enter.

Details about editions can be found at
https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409

Use of PAID editions of this software requires separate licensing through a
Microsoft Volume Licensing program.
By choosing a PAID edition, you are verifying that you have the appropriate
number of licenses in place to install and run this software.

Enter your edition(1-8): 3
The license terms for this product can be found in
/usr/share/doc/mssql-server or downloaded from:
https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409

The privacy statement can be viewed at:
https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409

Enter the SQL Server system administrator password: 
Confirm the SQL Server system administrator password: 
Configuring SQL Server...

The licensing PID was successfully processed. The new edition is [Express Edition].
ForceFlush is enabled for this instance. 
ForceFlush feature is enabled for log durability.
Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service.
Setup has completed successfully. SQL Server is now starting.

安装完毕!SQL Server 已启动。

安装 PHP 和其他依赖包

sudo su
wget https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
wget http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh remi-release-7.rpm epel-release-latest-7.noarch.rpm

yum install yum-utils
yum-config-manager --enable remi-php73
yum update
yum install php php-pdo php-xml php-pear php-devel re2c gcc-c++ gcc

编译带 PECL 的 PHP 驱动需要最新的 GCC 7

sudo yum install centos-release-scl
sudo yum-config-manager --enable rhel-server-rhscl-7-rpms
sudo yum install devtoolset-7
scl enable devtoolset-7 bash

SELinux 默认运行在 Enforcing 模式,为允许 Apache 连接数据库,需执行:

sudo setsebool -P httpd_can_network_connect_db 1

安装驱动和客户端

安装 msodbcsql17 驱动和 mssql-tools 工具集

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

初步测试

执行

sqlcmd -S localhost -U sa -P yourpassword -Q "SELECT @@VERSION"

返回

---------------------------------------------------------------------------
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64) 
        Jul 12 2019 17:43:08 
        Copyright (C) 2017 Microsoft Corporation
        Express Edition (64-bit) on Linux (CentOS Linux 7 (Core)) 

(1 rows affected)

PECL 安装 PHP 访问 SQL Server 的 PDO 驱动

  • PECL:全称 PHP Extension Community Library,C语言扩展,被加载进PHP以提供额外的功能,你需要具有管理员权限,C编译器和工具链来安装这些扩展
  • PEAR:全称 PHP Extension and Application Repository,它含有一些 PHP 写的代码和库,可以直接下载安装,并直接在你的代码中调用。

PECL 采用 PEAR 打包

编译 sqlsrv,其中版本号 5.6.1 可能会更新,下列命令注意修改

pecl download sqlsrv
tar xvzf sqlsrv-5.6.1.tgz
cd sqlsrv-5.6.1/
phpize
./configure --with-php-config=/usr/bin/php-config
make
sudo make install

编译 pdo_sqlsrv, 其中版本号 5.6.1 可能会更新,下列命令注意修改

pecl download pdo_sqlsrv
tar xvzf pdo_sqlsrv-5.6.1.tgz 
cd pdo_sqlsrv-5.6.1
phpize
 ./configure --with-php-config=/usr/bin/php-config
make
sudo make install

安装验证

运行 sqlcmd 客户端创建 SampleDB 数据库并插入三条数据

sqlcmd -S localhost -U sa -P your_password -Q "CREATE DATABASE SampleDB;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE SCHEMA TestSchema;"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "CREATE TABLE TestSchema.Employees (Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY, Name NVARCHAR(50), Location NVARCHAR(50));"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "INSERT INTO TestSchema.Employees (Name, Location) VALUES (N'Jared', N'Australia'), (N'Nikita', N'India'), (N'Tom', N'Germany');"
sqlcmd -S localhost -U sa -P your_password -d SampleDB -Q "SELECT * FROM TestSchema.Employees;"

创建 PhpSamples 目录,编写 sqlsrv_connect.php 和 sqlsrv_crud.php 文件

mkdir PhpSamples
cd PhpSamples

sqlsrv_connect.php 文件

<?php
    $serverName = "localhost";
    $connectionOptions = array(
        "Database" => "SampleDB",
        "Uid" => "sa",
        "PWD" => "your_password"
    );
    //Establishes the connection
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if($conn)
        echo "Connected!"
?>

sqlsrv_crud.php 文件

<?php
$serverName = "localhost";
$connectionOptions = array(
    "Database" => "SampleDB",
    "Uid" => "sa",
    "PWD" => "your_password"
);
//Establishes the connection
$conn = sqlsrv_connect($serverName, $connectionOptions);

//Insert Query
echo ("Inserting a new row into table" . PHP_EOL);
$tsql= "INSERT INTO TestSchema.Employees (Name, Location) VALUES (?,?);";
$params = array('Jake','United States');
$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) inserted: " . PHP_EOL);

sqlsrv_free_stmt($getResults);

//Update Query

$userToUpdate = 'Nikita';
$tsql= "UPDATE TestSchema.Employees SET Location = ? WHERE Name = ?";
$params = array('Sweden', $userToUpdate);
echo("Updating Location for user " . $userToUpdate . PHP_EOL);

$getResults= sqlsrv_query($conn, $tsql, $params);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) updated: " . PHP_EOL);
sqlsrv_free_stmt($getResults);

//Delete Query
$userToDelete = 'Jared';
$tsql= "DELETE FROM TestSchema.Employees WHERE Name = ?";
$params = array($userToDelete);
$getResults= sqlsrv_query($conn, $tsql, $params);
echo("Deleting user " . $userToDelete . PHP_EOL);
$rowsAffected = sqlsrv_rows_affected($getResults);
if ($getResults == FALSE or $rowsAffected == FALSE)
    die(FormatErrors(sqlsrv_errors()));
echo ($rowsAffected. " row(s) deleted: " . PHP_EOL);
sqlsrv_free_stmt($getResults);


//Read Query
$tsql= "SELECT Id, Name, Location FROM TestSchema.Employees;";
$getResults= sqlsrv_query($conn, $tsql);
echo ("Reading data from table" . PHP_EOL);
if ($getResults == FALSE)
    die(FormatErrors(sqlsrv_errors()));
while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
    echo ($row['Id'] . " " . $row['Name'] . " " . $row['Location'] . PHP_EOL);

}
sqlsrv_free_stmt($getResults);

function FormatErrors( $errors )
{
    /* Display errors. */
    echo "Error information: ";

    foreach ( $errors as $error )
    {
        echo "SQLSTATE: ".$error['SQLSTATE']."";
        echo "Code: ".$error['code']."";
        echo "Message: ".$error['message']."";
    }
}
?>

运行测试,返回如下:

[root@carbon PhpSamples]# php sqlsrv_connect.php
Connected!
[root@carbon PhpSamples]# php sqlsrv_crud.php
Inserting a new row into table
1 row(s) inserted:
Updating Location for user Nikita
1 row(s) updated:
Deleting user Jared
1 row(s) deleted:
Reading data from table
2 Nikita Sweden
3 Tom Germany
4 Jake United States

问题汇总

问题:pecl install sqlsrv 失败的问题

解决:

安装必要的包

相关文章

暂无

发表评论

电子邮件地址不会被公开。 必填项已用*标注