3

I am developing a small web application on my XAMPP (5.6.8) localhost that is selecting data from two remote databases, one is MySQL and one is MSSQL.

I'm unsure how to connect to both of these databases within a single conn.php file - if it's even possible?

I am able to use HeadiSQL & SQL Server Management Studio to login to each database, run queries and retrieve data, so I know it's not a user permissions issue. Note: I am not the admin of either of these databases.

I have one conn.php file that I include throughout my web application. The contents of this file are below.

When I include the MSSQL connection lines I receive the following error message on my web app;

localhost is currently unable to handle this request. HTTP ERROR 500

When I delete the MSSQL connection lines my web app loads as normal. Should I be connecting to the MSSQL database in a different way, this is clearly the issue.

conn.php

<?php
// Create MySQL connection
$conn = new mysqli('123.123.123.123', 'db_user', 'password', 'db_name'); 
if ($conn->connect_errno > 0) {
    die('Unable to connect to database [' . $conn->connect_error . ']');
} 
// end MySQL connection

//Create MSSQL connection
$serverName = "456.456.456.456";
$connectionInfo = array( "Database"=>"db_name", "UID"=>"db_user", "PWD"=>"password");
$conn2 = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn2 ) {
     echo "Connection established to smartcard.<br />";
}else{
     echo "Connection could not be established.<br />";
     die( print_r( sqlsrv_errors(), true));
}
// end MSSQL connection
?>

I've also created a separate conn_1.php page which contains only the MSSQL connection code. When I visit this page in my browser I see the following error;

localhost is currently unable to handle this request. HTTP ERROR 500

Any help is appreciated.

jonboy
  • 2,729
  • 6
  • 37
  • 77
  • 1
    Does the question title have anything to do with your problem? From what you say, your SQL Server code is failing to connect regardless of what do with MySQL. You're using the wrong IP address, you're missing the instance name or your server is not properly configured—as I said, nothing to do with MySQL. – Álvaro González Sep 29 '16 at 10:03

1 Answers1

0

mysqli and sqlsrv are entirely different extensions. They don't share codebase, target DBMS or even interface—neither do they say variables or resources. There're two basic troubleshooting steps you should try:

  1. Connect to SQL Server from another client (e.g. SQL Server Management Studio) to determine whether your credentials and the server settings are correct.

  2. Run your SQLSRV code independently, possibly from a short test script, to discard side effects from other parts of the code.

I had written some advice when I initially misread your question. I'll keep an adapted version of it because I think it's helpful anyway.

It can be hard to handle two different database APIs with different design. It's normally easier to switch to a cross-DBMS library like PDO. (The sqlsrv extension provides a PDO driver.)

If that's not an option, you can write a custom wrapper. You can define an interface with the functionality you need, e.g.:

interface MyDatabaseConnection {
    public function connect();
    public function prepare(array $params);
}
interface MyDatabaseStatement{
    public function execute($sql);
    public function fetchRow();
}

... and write classes that implement it for both DBMS.

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
  • Thanks for your advice @Alvaro - I have tried your two suggestions, see my updated answer. I have no experience in custom wrappers. If I was to switch to PDO, how would my MSSQL connection code look? Thanks again. – jonboy Sep 29 '16 at 11:30
  • @johnny_s A "500 Internal Server Error" status code (or a blank page) means that your script is throwing an error but you haven't configured PHP to display error messages. That's something you need to address before you go further because it's hard to code properly without the aid of error messages. The error reporting thumb rule is to show in development and log in production. As a starting point, I suggest you edit the system-wide `php.ini` file in the computer where you develop and tweak `error_reporting` and `display_errors` ([details here](http://stackoverflow.com/a/5680885/13508)). – Álvaro González Sep 29 '16 at 11:35
  • As about PDO, you can find the documentation at [http://php.net/pdo](http://php.net/pdo). Once there, check the general documentation and also browse to [Microsoft SQL Server Functions (PDO_SQLSRV)](http://php.net/manual/en/ref.pdo-sqlsrv.php). – Álvaro González Sep 29 '16 at 11:36