Problem using PDO with SQL Server

[jheitz]

Hi,

I am trying to use PDO to access a SQL Server 2005 Express database. I have added the needed extensions to my php.ini file and restarted Apache. Here is an excerpt from my php.ini file:

extension=php_pdo.dll
extension=php_pdo_mssql.dll
extension=php_pdo_odbc.dll

When I restart Apache, I get no errors, so this must means the DLLs are found and loaded (BTW, I am using PHP 5.1.1).

Now, when I try to create a PDO connection to my database using the following code:

$db = new PDO(   "mssql:dbname=mydb;host=D971XB1J\SQLEXPRESS",    

    $username, $password);(

I get a timeout error:

Fatal error: Maximum execution time of 30 seconds exceeded in C:\Program Files\Apache Group\Apache2\htdocs\testpdo.php on line 13

When I try using ODBC directly instead of MSSQL, I get a different error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[IM002] SQLDriverConnect: 0 [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified' in C:\Program Files\Apache Group\Apache2\htdocs\testpdo.php:8 Stack trace: #0 C:\Program Files\Apache Group\Apache2\htdocs\testpdo.php(8): PDO->__construct('odbc:dbname=myd...', 'user', 'password') #1 {main} thrown in C:\Program Files\Apache Group\Apache2\htdocs\testpdo.php on line 8

Does anyone know what the problem can be? I would rather use the MSSQL driver, but would be happy to go for the ODBC one if I can't get it to work.

I have a working web site that uses standard ODBC connection to the SQL Server database (I mention this so you know there is no problem with the server or db themselves). I just want to change the connection to PDO.

Thanks,

Javier

[Deep_Dhyani2]

few week ago i had added GD Library in my PHP version 4.4.1. I had to paste a gd file in window root directory.

extension=php_pdo.dll
extension=php_pdo_mssql.dll
extension=php_pdo_odbc.dll

had you paste these file in window directory.

[jheitz]

No, I didn't. But I tried and makes no difference. I don't think there is a problem locating the files, as I get no error when restarting Apache (if you specify a DLL that does not exist you do get an error message from Apache)

[MarkR]

jheitz wrote:

Now, when I try to create a PDO connection to my database using the following code:

$db = new PDO(   "mssql:dbname=mydb;host=D971XB1J\SQLEXPRESS",    

    $username, $password);(

I get a timeout error...

Are you absolutely sure that's the right syntax? Are you sure there is no firewall etc, blocking the connection?

Have you tried connecting locally using SQL Query Analyser (ISQL/W) using the same details and got in successfully?

It looks like you're not using the default instance. Have you tried using the default instance instead? If you don't specify the instance name during install, it will use the default; this is almost always what you want.

Mark

[jheitz]

Thanks Mark,

SQL Server is up and running perfectly. In fact I have a working site that uses ODBC, so instance name is correct (although I tried with localhost as well just in case).

For clarity's sake, here is the ODBC connection that works fine and I am trying to migrate to PDO:

<?php
// Connect to SQL Server
$dsn="DRIVER={SQL Server};SERVER=D971XB1J\SQLEXPRESS;DATABASE=mydb";
$username="xxxxxx";
$password="yyyyyy";

// Open connection to the Database
$sqlconnect=odbc_connect($dsn, $username, $password);
?>

[MarkR]

Are you sure that this is the right syntax for PDO connecting to a non-default instance? I see nothing in the manual about the driver, maybe there is some undocumented way of connecting to this instance.

Failing this, use the client utility to create an alias for the instance under another host name maybe?

Mark

[jheitz]

Hi Mark,

I am not sure, and I guess something must be wrong, as it is not working for me. I searched on the manuals and on the web for examples and created my code using what I found... but there must be something wrong.

Anywazs, for now I am happy to stick with generic ODBC connections, although I have the BIND problem (I need to force escaping single quotes, not ideal) and I guess there is a performance difference between running generic ODBC and PDO SQL Server queries...

Thanks

[jheitz]

Hi all,

Just wanted to check if any of you have any ideas as to how to make PDO work.... Thanks

[Jizzler]

Old topic I know, but seems to be the only one the search came up with.

What I've done:

php_pdo.dll
php_pdo_odbc.dll

Note: PHP 5.2 fixes a couple problems with PDO ODBC. Make sure to update if you haven't already.

System DSN with the SQL Server driver.
Windows NT Authentication.
Client Connect: TCP/IP.
Set permissions on the DB for the IUSR (box runs IIS6).

With the default DB set, my connection is simply $db = new PDO('odbc:SQLDSN');

I've tested with transactions, prepared statements, and simple queries. All seem to work well. Especially a test I did with a large number of prepared inserts and bound variables.

From what I've read on it, (there's not much on PDO and MSSQL unfortunately), the PDO MSSQL driver is based off the older driver which was already based on the old DBLIB library. It worked well with our older MSSQL 2000 database, but after moving to MSSQL 2005 and PDO, I've found ODBC to be the best bet.

I do hope though that the PDO MSSQL is updated, expecially as PDO is updated and more features are added.