php 如何使用PHP7连接到sql server？（我错过了什么？）

How to connect to sql server using PHP7? (What am I missing?)

提问by Mark

Here's the phpinfo output: version.php

这是 phpinfo 输出： version.php

Here's the code:

这是代码：

$serverName = "X.X.X.X";
$connection = array( "UID"=>"UserID", "PWD"=>"Password123", "Database"=>"database_name");
$conn = sqlsrv_connect( $serverName, $connection);

if ($conn === false) {
    $myfile3 = fopen("log.txt", "w");
    fwrite($myfile3, sqlsrv_errors());
    fclose($myfile3);

};

$tsql = "SELECT top 10 pName from products";
$stmt = sqlsrv_query( $conn, $tsql);
$row = sqlsrv_fetch_array($stmt);

$myfile4 = fopen("log.txt", "w");
fwrite($myfile4,  $row[0]);
fclose($myfile4);
sqlsrv_free_stmt( $stmt);
sqlsrv_close( $conn);

Nothing is written to the log file. Even if I hard code text in the fwrite($myfile3, "hard coded text"); place, nothing is written out.

日志文件中没有写入任何内容。即使我在 fwrite($myfile3, "hardcoded text"); 中硬编码文本；地方，什么都没有写出来。

Here's the extensions section in the php.ini file

这是 php.ini 文件中的扩展部分

[ExtensionList]
;extension=php_mysql.dll
extension=php_mysqli.dll
extension=php_mbstring.dll
extension=php_gd2.dll
extension=php_gettext.dll
extension=php_curl.dll
extension=php_exif.dll
extension=php_xmlrpc.dll
extension=php_openssl.dll
extension=php_soap.dll
extension=php_pdo_mysql.dll
extension=php_pdo_sqlite.dll
extension=php_imap.dll
extension=php_tidy.dll
extension=php_sqlsrv_7_nts_x64.dll
;extension=php_sqlsrv_7_ts_x64.dll

Lastly, I know I don't need all of these, but these are 4 dlls I have in the ext folder.

最后，我知道我不需要所有这些，但这些是我在 ext 文件夹中的 4 个 dll。

php_sqlsrv_7_nts_x64.dll
php_sqlsrv_7_nts_x86.dll
php_sqlsrv_7_ts_x64.dll
php_sqlsrv_7_ts_x86.dll

采纳答案by viral barot

Using pdo:

使用 pdo：

$serverName = "(local)\sqlexpress";  

/* Connect using Windows Authentication. */  
try  
{  
  $conn = new PDO( "sqlsrv:server=$serverName ; Database=AdventureWorks", "", "");  
  $conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
}  
catch(Exception $e)  
{   
  die( print_r( $e->getMessage() ) );   
} 

Procedural way:

程序方式：

$serverName = "(local)\sqlexpress";  
$connectionOptions = array("Database"=>"AdventureWorks");  

/* Connect using Windows Authentication. */  
$conn = sqlsrv_connect( $serverName, $connectionOptions);  
if( $conn === false )  
die(sqlsrv_errors());  

Click herefor More Information

单击 此处了解更多信息

回答by Leith

First you need to isolate your problem - is this something wrong with your file writing or something wrong with connecting to SQL server, or something wrong with your PHP setup?

首先，您需要隔离您的问题 - 这是您的文件编写有问题还是连接到 SQL 服务器有问题，还是您的 PHP 设置有问题？

Troubleshooting SQL Server

SQL Server 疑难解答

You may wish to use the SQLSRV_ERR_ALLflag when calling sqlsrv_errors()for as much detail as possible, and use a die()or simply print_r/echothe result to the screen (instead of writing to a file) so you can see if there's something wrong there - have a look at the examples on the sqlsrv_connectdocumentation page.

您可能希望SQLSRV_ERR_ALL在调用sqlsrv_errors()尽可能多的细节时使用该标志，并使用 adie()或简单的print_r/echo结果到屏幕（而不是写入文件），以便您可以查看那里是否有问题 - 看看sqlsrv_connect文档页面上的示例。

Next I would try to make sure that you're specifying the port correctly, e.g.:

接下来，我会尝试确保您正确指定了端口，例如：

$serverName = 'X.X.X.X, 1433';

$serverName = 'X.X.X.X, 1433';

Or specify the SQL Server instance, e.g.:

或者指定 SQL Server 实例，例如：

$serverName = 'X.X.X.X\sqlexpress';

$serverName = 'X.X.X.X\sqlexpress';

Also make sure that your user, password and database name are all correct, and that the user you've specified has the correct permissions on that database. And of course that it's running on port 1433 (or change the above to whatever port you have it running on).

还要确保您的用户、密码和数据库名称都正确，并且您指定的用户对该数据库具有正确的权限。当然，它在端口 1433 上运行（或者将上面的内容更改为运行它的任何端口）。

After that, I would consider adding these properties priorto your sqlsrv_connect()call to elevate as much as possible into errors coming back for troubleshooting the connection:

之后，我会考虑在您sqlsrv_connect()调用之前添加这些属性，以尽可能多地将错误提升为错误，以便对连接进行故障排除：

sqlsrv_configure('WarningsReturnAsErrors', true);
sqlsrv_configure('LogSubsystems', SQLSRV_LOG_SYSTEM_ALL);
sqlsrv_configure('LogSeverity', SQLSRV_LOG_SEVERITY_ALL);

See if that turns up anything more useful from SQL server directly.

看看这是否会直接从 SQL 服务器中找到更有用的东西。

Troubleshooting file-writing

文件写入故障排除

• you need to have a look at your fopen- you can use amode instead of wto make sure that the file isn't truncated when you open it
• You should also check to see whether or not the returned $myfile3is falseas per the documentation, in case the file open itself is failing.
  • If it is, then you may have a permissions problem in the directory that you're trying to create the file. If the file is already created ahead of time for you (no need to worry about creating it) you also may not have write permissions on that file. If the open is failing it should generate an E_WARNING(which should display on the page since your error_reportingconfiguration is set to E_ALL, but you could also check the PHP error logfile). You can also use file_existsand is_readable()to help check permissions.
  • When you say hard-coding a write value doesn't do any writes, this leads me to think that this is the actual problem.
• I would also consider using different log files for each part (not log.txtin both cases)

• 您需要查看您的fopen- 您可以使用amode 而不是w确保打开文件时文件不会被截断
• 您也应该检查返回的是否$myfile3是false按文件，如果打开该文件本身失败。
  • 如果是，那么您尝试创建文件的目录中可能存在权限问题。如果文件已经为您提前创建（无需担心创建它），您也可能没有该文件的写权限。如果打开失败，它应该生成一个E_WARNING（它应该显示在页面上，因为您的error_reporting配置设置为E_ALL，但您也可以检查 PHP 错误日志文件）。您还可以使用file_exists和is_readable()来帮助检查权限。
  • 当您说对写入值进行硬编码不会进行任何写入时，这让我认为这是实际问题。
• 我也会考虑为每个部分使用不同的日志文件（不是log.txt在两种情况下）

Troubleshooting your PHP

PHP 故障排除

Have a look at the Microsoft documentationto ensure that your DLLs match the right PHP7 dll you're using on your version of Windows, although that doesn't specify the Operating system requirements for v4.0 of the driver: they maynot support Windows server 2008, although I would be surprised. They also don't show which versions of SQL server they support connecting to, so if you're on an old version of SQL server that might be a problem as well.

查看Microsoft 文档以确保您的 DLL 与您在 Windows 版本上使用的正确 PHP7 dll 匹配，尽管这并未指定 v4.0 驱动程序的操作系统要求：它们可能不支持 Windows server 2008，虽然我会感到惊讶。他们也没有显示他们支持连接到哪个版本的 SQL 服务器，所以如果你使用的是旧版本的 SQL 服务器，这也可能是一个问题。

回答by Peter Darmis

As an alternative you could try to install PDO_SQLSRVdriver instead. This is an example for connecting to SQL-Server using Windows authentication.

作为替代方案，您可以尝试安装PDO_SQLSRV驱动程序。这是使用 Windows 身份验证连接到 SQL-Server 的示例。

$serverName = "(local)\sqlexpress";    

/* Connect using Windows Authentication. */  
try  
{  
$conn = new PDO( "sqlsrv:server=$serverName ; Database=database_name", "", "");  
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );  
}  
catch(Exception $e)  
{   
die( print_r( $e->getMessage() ) );   
} 

For more information take a look at

有关更多信息，请查看

• msdn - PDO-SQLSRV
• How can I install pdo_sqlsrv on my windows 2008 Server 2008 R2
• msdn - PHP Driver for SQL Server Support for LocalDB

• msdn - PDO-SQLSRV
• 如何在 Windows 2008 Server 2008 R2 上安装 pdo_sqlsrv
• msdn - 用于 SQL Server 支持 LocalDB 的 PHP 驱动程序

回答by bstricks

From the docs, this would give you more information: http://php.net/manual/en/function.sqlsrv-connect.php

从文档中，这将为您提供更多信息：http: //php.net/manual/en/function.sqlsrv-connect.php

$connectionInfo = array( "Database"=>"dbName");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
     echo "Connection established.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}

If that doesn't give you an error maybe you can't write to the file due to permissions?

如果这没有给您带来错误，也许您由于权限而无法写入文件？