CODEWITHSUNDEEP
phpsqlubuntuazurepdo
B1NARY
B1NARY

Reputation: 163

DBLIB PDO driver keeps failing when trying to connect/query Microsoft Azure SQL database

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

Server information:

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?

Upvotes: 0

Views: 5537

Answers (1)

B1NARY
B1NARY

Reputation: 163

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.

Upvotes: 1

Related Questions