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

Question

Here's the phpinfo output: 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.

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

[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.

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

Solution 1

Using 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 here for More Information

Solution 2

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?

Troubleshooting SQL Server

You may wish to use the SQLSRV_ERR_ALL flag when calling sqlsrv_errors() for as much detail as possible, and use a die() or simply print_r/echo the 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_connect documentation page.

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

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

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

$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).

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

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.

Troubleshooting file-writing

• you need to have a look at your fopen - you can use a mode instead of w to make sure that the file isn't truncated when you open it
• You should also check to see whether or not the returned $myfile3 is false as 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_reporting configuration is set to E_ALL, but you could also check the PHP error logfile). You can also use file_exists and 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.txt in both cases)

Troubleshooting your PHP

Have a look at the Microsoft documentation to 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 may not 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.

Solution 3

From the docs, this would give you more information: 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?

Comments

[Leith]

OP doesn't include anything to indicate using PDO... also FormatErrors appears to be a custom function (lifted from comments in stackoverflow.com/questions/28468160/…) and will cause an undefined function error if used. Also doesn't address anything to do with the file writing issues.