Linux environment PHP5.5 above connection SqlServer2008

Linux version: 64-bit CentOS 6.4

Nginx Version: nginx1.8.0

PHP Version: php5.5.28

SQL Server version: 2008

FreeTDS version: 0.95

For Linux environment Installation nginx+php reference Linux environment Nginx installation and debugging as well as 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

Note: Be sure to download the latest version from the official website FreeTDS-0.95 ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz

If the official website is too slow suggestions from I uploaded here as soon as download: http://download.csdn.net/detail/21aspnet/9000357

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

Note that this is the--with-tdsver=7.3 here, this is very important, you need to choose the correct configuration according to your database version, because now mostly SQLserve2008 so need to choose 7.3.

About this question online some say is 7.1, also some say is 7.2, even some say is 8.0, can see the text at the end of reference post, but those said all have problems.

The cause of this configuration confusion is that many people use FreeTDS-0.91, after my test FreeTDS-0.91 only support 7.1, if it is more than 7.2 configuration then all will become 5.0.

In fact, refer to the official website of the document to know this problem, but because many people download the old version of FreeTDS-0.91, even if set to--with-tdsver=7.2 above also no use.

Summary: FreeTDS-0.91 only supports 7.1, the rest will default to 5.0. Only the latest FreeTDS-0.95, which is the best configuration for Sqlserver2008.

#./configure--prefix=/usr/local/freetds--with-tdsver=7.3--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

Verifying the FreeTDS version

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

Test Database Connectivity

#/usr/local/freetds/bin/tsql-h Database Server ip-p port number-u user name-p password

About freetds/etc/freetds.conf Configuration Items

Many other posts have been written to configure/usr/local/freetds/etc/freetds.conf, in fact this does not need to be configured . If the configuration is also possible, the configuration of PHP can invoke this configuration item, otherwise you need to specify the database server information in the PHP code.

It is also important to note that the freetds.conf under the/usr/local/freetds/etc/is different from the freetds.conf in front/usr/local/freetds/lib/.

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

The default is this:

[CPP]View Plain Copy

2. # $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden EXP $
4. #
6. # This file was installed by FreeTDS if no file by the same
8. # name is found in the installation directory.
10. #
12. # For information about the layout of this file and its settings,
14. # See the freetds.conf manpage ' man freetds.conf '.
18. # Global settings is overridden by those in a database
20. # Server Specific section
22. [Global]
24. # TDS Protocol version
26. ; TDS Version = 4.2
30. # Whether to write a tdsdump file for diagnostic purposes
32. # (Setting this to/tmp was insecure on a multi-user system)
34. ; dump file =/tmp/freetds.log
36. ; Debug flags = 0xFFFF
40. # Command and Connection Timeouts
42. ; Timeout = 10
44. ; Connect Timeout = 10
48. # If you get out-of-memory errors, it could mean that your client
50. # is trying to allocate a huge buffer for a TEXT field.
52. # Try Setting ' text Size ' to a more reasonable limit
54. Text size = 64512
58. # A Typical Sybase server
60. [EgServer50]
62. Host = symachine.domain.com
64. Port = 5000
66. TDS Version = 5.0
70. # A Typical Microsoft server
72. [EgServer70]
74. Host = ntmachine.domain.com
76. Port = 1433
78. TDS Version = 7.0

If you want to use a configuration item, simply modify [EgServer70] to:

[CPP]View Plain Copy

1. [EgServer70]
2. Host = 192.168.1.235 This is the database server IP
3. Port = 1433
4. TDS Version = 7.1

Other do not move, about [EgServer70] 's name is also arbitrary, this is to PHP call, and PHP code 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:

[CPP]View Plain Copy

1. Extension = "mssql.so"
2. 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]View Plain Copy

1. <?php
2. Header ("content-type:text/html; Charset=utf-8 ");
3. $msdb =mssql_connect ("EgServer70", "blog.csdn.net.unix21", "password");
4. if (! $msdb) {
5. echo "Connect SQL Server Error";
6. Exit
7. }
8. mssql_select_db ("Database name", $MSDB);
9. $result = Mssql_query ("SELECT Top 5 * from TableName", $MSDB);
10. while ($row = Mssql_fetch_array ($result)) {
11. Print_r ($row);
12. }
13. Mssql_free_result ($result);
14. ?>

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

(2). Mssql_connect Non-configurable version

[PHP]View Plain Copy

2. <?php
4. Header ("content-type:text/html; Charset=utf-8 ");
6. $msdb =mssql_connect ("Database IP", "blog.csdn.net.unix21", "password");
8. $msdb =mssql_connect ("Database ip:1433", "blog.csdn.net.unix21", "password");
10. $msdb =mssql_connect ("Database ip:49151", "blog.csdn.net.unix21", "password");
12. if (! $msdb) {
14. echo "Connect SQL Server Error";
16. Exit
18. }
20. mssql_select_db ("Database name", $MSDB);
22. $result = Mssql_query ("SELECT Top 5 * from TableName", $MSDB);
24. while ($row = Mssql_fetch_array ($result)) {
26. Print_r ($row);
28. }
30. Mssql_free_result ($result);
32. ?>

(3). PDO version

[PHP]View Plain Copy

2. <?php
4. Header ("content-type:text/html; Charset=utf-8 ");
6. try {
8. $hostname = "Database IP";
10. $port = 1433;
12. $dbname = "database name";
14. $username = "blog.csdn.net.unix21";
16. $PW = "password";
18. $DBH = new PDO ("dblib:host= $hostname: $port;d bname= $dbname", "$username", "$PW");
20. } catch (Pdoexception $e) {
22. echo "Failed to get DB handle:". $e->getmessage (). "\ n";
24. Exit
26. }
30. $stmt = $dbh->prepare ("SELECT Top 5 * from tablename");
32. $stmt->execute ();
34. while ($row = $stmt->fetch ()) {
36. Print_r ($row);
38. }
40. Unset ($DBH); Unset ($stmt);
44. ?>

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

Linux environment PHP5.5 above connection SqlServer2008