Php sql server on linux

Linux based PHP install connecting to MsSQL Server

What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server. The PHP will only ever run on a Linux box. I’ve been trawling for the simplest answer for a while now.

6 Answers 6

Php 5.6

sudo apt-get install php5.6-sybase freetds-common libsybdb5 

AWS / Centos / Redhat

sudo yum install php56-mssql 

After that, you can connect to the MsSql database through PHP with something like this:

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); > catch (PDOException $e) < echo "There was a problem connecting. " . $e->getMessage(); > $query = "SELECT * FROM TestSchema.Employees"; $statement = $pdo->prepare($query); $statement->execute(); $results = $statement->fetchAll(PDO::FETCH_ASSOC); var_dump($results); 

You can troubleshoot MsSQL with something like this on the command line:

tsql -H your.server.name -p 1433 -U yourusername -P yourpassword -D yourdatabasename 

To install tsql you can run this:

Installation of SQL binaries for testing on any PHP version

sudo apt install freetds-bin 

Php 7+

Microsoft has native drivers we can use. Full instructions are here (Redhat / Ubuntu / Others).

That link also contains required steps to install Ms SQL server on your dev machine (working on Ubuntu, doesn’t work on AWS, but you can just spin up an RDS instance there). It also contains basic instructions on how to create test tables and data in the database, and PHP connectivity code.

You can also install the components for a newer version of PHP like this:

sudo apt-get install php7.2-sybase freetds-common libsybdb5 

The above did not work for me in docker with PHP 5.6 but this did the trick -> github.com/docksal/docksal/issues/473#issuecomment-374855050

you must to install mssql driver for php on linux.
this is a best tutorial for you.

sudo apt-get install php5-sybase php5-odbc freetds-common 

Edit freetds.conf then connect MSSQL server with this PHP.

After searching and trying out the many suggestions here and in other posts, and spending a lot of time, a colleague suggested trying ADO.

As we already had an ADO enabled PHP install, it literally took less than 10 minutes to get up and running.

If your serious about connecting from Linux PHP to MS-SQL, consider ADO.

My apologies if I don’t understand the protocal here, but I’m pretty sure I gave pretty good answer. The question was: ‘What is the best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server.’ My answer is: The best way to connect via PHP on a Linux box to a Remote Microsoft SQL Server is to use the PHP ADO library. There are many examples. Here’s the first one I found: stackoverflow.com/questions/10881599/php-adodb-mssql-connection

It’s interesting to note that every answer here proposes one solution to the problem rather than answering the question asked which was which is the «best» solution. Unfortunately the OP forgot to tell us what the criteria were for «best».

Nobody so far has mentioned odbc. While this entails both configuring the odbc driver for connecting to the database and enabling the php extension, this method provides the best isolation of the php code from the underlying database making it easier to port the code later.

The microsoft provided drivers for php should give good compatibility but only support v7 up of php.

You may prefer to stick with the functionality provided by your Linux distro to ensure you have a supportable product and/or automated patch management. An alternative to odbc here may be the freetds driver but you didn’t tell us anything about which Linux distro this is.

Источник

Php sql server on linux

This week we tested the public preview of Microsoft SQL Server for Linux using PHP 7 with our component zendframework/zend-db.

Microsoft announced the availability of a public preview of SQL Server for Linux on the 16th of November, 2016. This new version of SQL Server has some interesting features such as:

• transparent data encryption;
• always encrypted;
• row level security;
• in-memory tables;
• columnstore indexing;
• native JSON support;
• support for in-database analytics with R-integration.

Moreover, the performance of the new DBMS seems to be very impressive. Microsoft published a case study with 1.2 million requests per second with In-Memory OLTP on a single commodity server.

We tested the last preview of SQL Server (CTP1.2 — 14.0.200.24) using a Vagrant box with Ubuntu 16.04 and 4 GB RAM.

Install SQL Server on Linux

We followed the instructions list on the Microsoft website to install SQL Server for PHP on Ubuntu 16.04.

To ensure optimal performance of SQL Server, the Ubuntu box should have at least 4 GB of memory.

The first step is to add the GPG key for the Microsoft repositories.

$ sudo su $ curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - $ curl https://packages.microsoft.com/config/ubuntu/16.04/mssql-server.list > /etc/apt/sources.list.d/mssql-server.list $ exit 

Then we can update the repository list and install the mssql-server package, using the following commands:

$ sudo apt-get update $ sudo apt-get install mssql-server 

Now we can run the setup for sqlserver. We will be required to accept the EULA and choose a password for the System Administrator (SA).

sudo /opt/mssql/bin/sqlservr-setup 

After the installation, we will have SQL Server running on Linux!

Install the command line utility for SQL Server

Now that we have the DBMS running, we need a tool to access it. Microsoft provides a command line tool named sqlcmd . This program is very similar to the MySQL client tool, quite familiar to PHP developers.

To install sqlcmd , we need to run the following commands:

$ sudo su $ curl https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-tools.list $ exit $ sudo apt-get update $ sudo apt-get install msodbcsql mssql-tools unixodbc-dev $ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile $ echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc $ source ~/.bashrc 

msodbcsql

Though the Microsoft documentation does not indicate it, we also installed msodbcsql ; without it, we ran into dependency issues.

If the installation was successful, we can start using the command line tool. For instance, we can ask for the SQL Server vesion using the following instruction:

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

where yourpassword should be replaced with the SA password that you choose during the SQL Server setup.

This command will return output like the following:

---------------------------------------------------------------- Microsoft SQL Server vNext (CTP1.2) - 14.0.200.24 (X64) Jan 10 2017 19:15:28 Copyright (C) 2016 Microsoft Corporation. All rights reserved. on Linux (Ubuntu 16.04.1 LTS) 

Install the SQL Server extension for PHP

Next, we need to install the PHP extension for SQL Server. This can be done using PECL.

PECL

If you do not have PECL installed on Ubuntu, you can install it with the following command:

$ sudo apt-get install php-dev 

To install the sqlsrv and pdo_sqlsrv extensions for PHP, we need to execute the following commands:

$ sudo apt-get install unixodbc-dev gcc g++ build-essential $ sudo pecl install sqlsrv pdo_sqlsrv 

Finally, we need to add the directives extension=pdo_sqlsrv.so and extension=sqlsrv.so to our PHP configuration (generally php.ini ). In our case, running Ubuntu (or any other Debian-flavored distribution) we have the PHP configuration files stored in /etc/php/7.0/mods-available . We can create sqlsrv.ini and pdo_sqlsrv.ini containing the respective configurations. As a last step, we need to link these configurations to our specific PHP environments. For this, you can have two choices:

• For Ubuntu, you can use the phpenmod command.
• Alternately, you can symlink to the appropriate directory.

For our purposes, we are using PHP 7.0, from the CLI SAPI, so we can do either of the following:

# Using phpenmod: $ sudo phpenmod -v 7.0 -s cli sqlsrv pdo_sqlsrv # Manually symlinking: $ sudo ln -s /etc/php/7.0/mods-available/sqlsrv.ini /etc/php/7.0/cli/conf.d/20-sqlsrv.ini $ sudo ln -s /etc/php/7.0/mods-available/pdo_sqlsrv.ini /etc/php/7.0/cli/conf.d/20-pdo_sqlsrv.ini 

Integration tests with zend-db

We used the above information to add support for SQL Server to the zendframework/zend-db vagrant configuration, used by developers to test against the various database platforms we support.

We updated the Vagrantfile, enabling integration tests for MySQL, PostgreSQL and SQL Server on Linux, to read as follows:

$install_software = > /etc/postgresql/9.5/main/pg_hba.conf service postgresql restart # INSTALL MySQL debconf-set-selections /etc/apt/sources.list.d/mssql-server.list apt-get -yq update apt-get -yq install mssql-server printf "YES\nPassword123\nPassword123\ny\ny" | /opt/mssql/bin/sqlservr-setup curl -s https://packages.microsoft.com/config/ubuntu/16.04/prod.list > /etc/apt/sources.list.d/mssql-tools.list apt-get -yq update ACCEPT_EULA=Y apt-get -yq install msodbcsql mssql-tools unixodbc-dev echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> /home/vagrant/.bash_profile echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> /home/vagrant/.bashrc source /home/vagrant/.bashrc SCRIPT $setup_vagrant_user_environment = /dev/null; then echo "cd /vagrant" >> /home/vagrant/.profile fi SCRIPT Vagrant.configure(2) do |config| config.vm.box = 'bento/ubuntu-16.04' config.vm.provider "virtualbox" do |v| v.memory = 4096 v.cpus = 2 end config.vm.network "private_network", ip: "192.168.20.20" config.vm.provision 'shell', inline: $install_software config.vm.provision 'shell', privileged: false, inline: '/vagrant/.ci/mysql_fixtures.sh' config.vm.provision 'shell', privileged: false, inline: '/vagrant/.ci/pgsql_fixtures.sh' config.vm.provision 'shell', privileged: false, inline: '/vagrant/.ci/sqlsrv_fixtures.sh' config.vm.provision 'shell', inline: $setup_vagrant_user_environment end 

This vagrant configuration installs Ubuntu 16.04 with 4 GB of RAM and the following databases (user and password are reported in parenthesis):

• MySQL 5.7.17 (root/Password123)
• PostgreSQL 9.5 (postgres/postgres)
• SQL Server 14.0.200.24 (sa/Password123)

We can use this virtual machine to run PHPUnit for the zend-db integration tests. If you want to test this vagrant box, you can clone the zend-db repository and run the following command:

This creates a VM running with IP 192.168.20.20 .

More information

In this post, we’ve demonstrated how to install the public preview release of SQL Server on Ubuntu. Microsoft also provides support for other Linux distributions, such as Red Hat and Suse.

You can also install this new SQL Server preview on Windows, macOS, Azure or using a preconfigured Docker container.

Get more information on SQL Server for Linux from the official website. Find specific information on how to use SQL Server with PHP here.

Источник