Linux environment PHP5.5 above connection SqlServer2008 "All-network most classic error-free version"

Linux version: 64-bit CentOS 6.4

Nginx Version: nginx1.8.0

PHP Version: php5.5.28

sqlserver:2008

For Linux environment installation nginx+php refer to "Installationand commissioning of the Linux environment and PHP installation" .

In general, Php+mysql is the most classic combination, running in a Linux environment is very good if Php+sqlserver is running in the Windows environment.

Today, the Linux environment requires PHP to call SQL Server, using a day, and finally put this problem thoroughly studied, the other similar articles on the internet I mostly see, in fact, there are some because too long do not apply, some have errors, there are several key issues not clear, see this article other can be ignored, Said really step on the pit really tired, there is no need, according to this article to do is, so this wencai claims to be the most classic error-free version of the network, in fact, so the main hope that you save time.

1. First you need to compile and install FreeTDS

Description: FreeTDS official website Download is relatively slow, you can upload from the same here quickly download: http://download.csdn.net/detail/21aspnet/8997397

# wget ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz
# TAR-ZXVF Freetds-0.91.tar.gz
# CD freetds-0.91

Need to pay attention to is here--with-tdsver=7.1, this is very important, online some say is 7.2, some say is 8.0, about this question can see at the end of the article reference post.

#./configure--prefix=/usr/local/freetds--with-tdsver=7.1--enable-msdblib
# Make && make install

Install and you will see this message:

Configure FreeTDS

# CD: /

# echo "/usr/local/freetds/lib/" >/etc/ld.so.conf.d/freetds.conf
# Ldconfig

Verify FreeTDS

This step is very important, it can be continued, or the subsequent steps are meaningless.

First look at the version information

# /usr/local/freetds/bin/tsql-c

If the previous configuration--with-tdsver=7.1 if the write is 7.2 and 8.0 or the other will be here the TDS version is 5.0, for reasons see post in the reference information later.

# /usr/local/freetds/bin/tsql-h database server ip:xxx-p 1433-u user name-p password

or directly

# /usr/local/freetds/bin/tsql-h database server ip-p 1433-u user name-p password

About freetds/etc/freetds.conf

Important: Many other posts need to be configured/usr/local/freetds/etc/freetds.conf, in addition to note that the freetds.conf under etc is different from the previous freetds.conf.

In fact, the default has been generated good etc under the freetds.conf, in fact, this is redundant, but the configuration is not a problem.

If configured here, then the PHP page can use the configuration here, or PHP page designation is the same.

The default is this:

[global]
        # TDS protocol version
;	tds version = 4.2

	# Whether to write a TDSDUMP file for diagnostic purposes
	# (setting this to /tmp is insecure on a multi-user system)
;	dump file = /tmp/freetds.log
;	debug flags = 0xffff

	# Command and connection timeouts
;	timeout = 10
;	connect timeout = 10
	
	# If you get out-of-memory errors, it may mean that your client
	# is trying to allocate a huge buffer for a TEXT field.  
	# Try setting ‘text size‘ to a more reasonable limit 
	text size = 64512

# A typical Sybase server
[egServer50]
	host = symachine.domain.com
	port = 5000
	tds version = 5.0

# A typical Microsoft server
[egServer70]
	host = ntmachine.domain.com
	port = 1433
	tds version = 7.0

  

	
   


  

Modify [EgServer70] for this:

[egServer70]
host = 192.168.1.235 This is the database server IP
port = 1433
tds version = 7.1

Other do not move, about [EgServer70] name is also arbitrary, this is to PHP call, consistent.

3. Add PHP extensions for MSSQL and PDO Pdo_dblib

Note: These 2 kinds of extensions can achieve the same purpose, choose one.

(1). Increase the PHP extension MSSQL

# cd/usr/php-5.5.28/ext/mssql/
Using Phpize to add extensions to PHP dynamically under Linux
# /usr/local/php/bin/phpize
# ./configure--with-php-config=/usr/local/php/bin/php-config--with-mssql=/usr/local/freetds/
# make && make install

(2). Increase the pdo_dblib of the PHP extension PDO
# cd/usr/php-5.5.28/ext/pdo_dblib/
Using Phpize to add extensions to PHP dynamically under Linux
# /usr/local/php/bin/phpize
# ./configure--with-php-config=/usr/local/php/bin/php-config--with-pdo-dblib=/usr/local/freetds/
# make && make install

(3). Add. So in the php.ini configuration file
# PHP.ini under the Cd/usr/local/php/lib

Increase:

Extension = "mssql.so" extension = "pdo_dblib.so"

If you only need one of the 2 extensions above, naturally just add one of the. so extension to php.ini.

(4). Restart PHP FastCGI

# Killall PHP-FPM
#/ETC/INIT.D/PHP-FPM

The PHP-FPM cannot be restarted if the extension is not properly generated.

This time in the phpinfo can see the extension to add success information.

4. Calling SQL Server using PHP

(1). Mssql_connect Configuration Version

<? php
header ("Content-type: text / html; charset = utf-8");
$ msdb = mssql_connect ("egServer70", "blog.csdn.net.unix21", "password");
if (! $ msdb) {
echo "connect sqlserver error";
exit;
}
mssql_select_db ("database name", $ msdb);
$ result = mssql_query ("SELECT top 5 * FROM tablename", $ msdb);
while ($ row = mssql_fetch_array ($ result)) {
  print_r ($ row);
}
mssql_free_result ($ result);
?>

Note: The above egServer70 is the previous freetds/etc/freetds.conf configuration.

(2). Mssql_connect Non-configurable version

<? php
header ("Content-type: text / html; charset = utf-8");
// $ msdb = mssql_connect ("Database IP", "blog.csdn.net.unix21", "password");
// $ msdb = mssql_connect ("Database IP: 1433", "blog.csdn.net.unix21", "password");
$ msdb = mssql_connect ("Database IP: 49151", "blog.csdn.net.unix21", "password");
if (! $ msdb) {
echo "connect sqlserver error";
exit;
}
mssql_select_db ("database name", $ msdb);
$ result = mssql_query ("SELECT top 5 * FROM tablename", $ msdb);
while ($ row = mssql_fetch_array ($ result)) {
  print_r ($ row);
}
mssql_free_result ($ result);
?>

(3). PDO version

<? php
header ("Content-type: text / html; charset = utf-8");
   try {
     $ hostname = "Database IP";
     $ port = 1433;
     $ dbname = "Database name";
     $ username = "blog.csdn.net.unix21";
     $ pw = "password";
     $ dbh = new PDO ("dblib: host = $ hostname: $ port; dbname = $ dbname", "$ username", "$ pw");
   } catch (PDOException $ e) {
     echo "Failed to get DB handle:". $ e-> getMessage (). "\ n";
     exit;
   }
 
   $ stmt = $ dbh-> prepare ("SELECT top 5 * FROM tablename");
   $ stmt-> execute ();
   while ($ row = $ stmt-> fetch ()) {
     print_r ($ row);
   }
   unset ($ dbh); unset ($ stmt);

?> 

Show Data:

Above I have been verified.

Reference Information :

1. This post explains the first important question why--with-tdsver=7.1

http://blog.csdn.net/dlutxie/article/details/6851429

2. Official information

http://php.net/manual/zh/ref.pdo-dblib.php

http://php.net/manual/zh/function.mssql-connect.php

3. I used to refer to these, but there are mistakes in some places, the Internet is mainly about these posts, others are quoted from each other.

http://zyan.cc/php_sqlserver_freetds/

HTTP://MY.OSCHINA.NET/ROBANLEE/BLOG/168626?P={{CURRENTPAGE+1}}

http://blog.csdn.net/rgb_rgb/article/details/8762769

Http://blog.sina.com.cn/s/blog_87b9bbc70101avmh.html

Http://stackoverflow.com/questions/13180746/mssql-connect-unable-to-connect-to-server-without-freetds-conf

http://blog.csdn.net/chenjiebin/article/details/7278304
http://blog.163.com/[email protected]/blog/static/9540383720112157055119/
Http://www.linuxidc.com/Linux/2012-09/70192.htm
http://blog.csdn.net/helonsy/article/details/7207497

http://my.oschina.net/u/217063/blog/220337

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Linux environment PHP5.5 above connection SqlServer2008 "All-network most classic error-free version"