尝试连接/查询Microsoft Azure SQL数据库时，DBLIB PDO驱动程序一直出现故障

Question

So I'm using Linux (ubuntu) and I'm trying to connect to a Microsoft Azure SQL database.

Server information:

• The server address is a1a1a1a1a1.database.windows.net (a1a1a1a1a1 is a placeholder for my server name)

• The database name is MyDatabase

• The table is called [dbo].[Sloth]

Freetds.conf settings:

[global]
    dump file = /tmp/freetds.log
    debug flags = 0xffff
    text size = 64512

[a1a1a1a1a1.database.windows.net]
    host = a1a1a1a1a1.database.windows.net
    port = 1433
    tds version = 8.0
    client charset = UTF-8

When I run

php -r "phpinfo();" | grep "PDO drivers"

in terminal it returns

PDO drivers => dblib, mysql

so to the best of my knowledge all the configuration and driver installations are how they should be.

So now for the errors:

ERROR 1

If I initialize the PDO like so:

$conn = new \PDO ( "dblib:dbname = MyDatabase;host=a1a1a1a1a1.database.windows.net;", $Username, $Password);

it connects, but if I remove the spaces around the '=' character for dbname like so:

$conn = new \PDO ( "dblib:dbname=MyDatabase;host=a1a1a1a1a1.database.windows.net;", $Username, $Password);

it returns these errors from the PDOException and freetds.log

SQLSTATE[HY000] General SQL Server error: Check messages from the SQL Server (severity 16)

(dbutil.c:86):msgno 40508: "USE statement is not supported to switch between databases. Use a new connection to connect to a different Database."

ERROR 2

When I run the following code

try {
    $conn = new \PDO ( "dblib:dbname = MyDatabase;host=a1a1a1a1a1.database.windows.net;", $Username, $Password);
    $conn->setAttribute( \PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION );
    $statement = $conn->prepare("SELECT * FROM dbo.Sloth");
    $result = $statement->execute();
}
catch ( PDOException $e ) {
    print( "Error connecting to SQL Server." );
    die(print_r($e));
}

it breaks at the $result = ... line with these errors from the PDOException and freetds.log

SQLSTATE[HY000]: General error: 208 General SQL Server error: Check messages from the SQL Server [208] (severity 16) [(null)] 

(dbutil.c:86):msgno 208: "Invalid object name 'dbo.Sloth'."

but when I run the query

SELECT * FROM dbo.Sloth

in the Azure Database Management Portal it returns all the correct rows.

Any help with these issues will be much appreciated!

EDIT

Through some tests I've discovered that both problems stem from the fact that the PDO isn't connecting to the database but is instead connecting to the "master". This would explain ERROR 2's Invalid Object Name error.

Any thoughts as to why the code in ERROR 1 doesn't connect to MyDatabase?

Answer 1

SOLUTION

I was using PHP 5.3.10 which had the following bug still in it, https://bugs.php.net/bug.php?id=64338

Turns out the PDO driver was making a USE statement which Microsoft Azure SQL Database doesn't like.

I upgraded to PHP 5.4.21 and everything worked properly.