Connect to SQL Server through PDO using SQL Server Driver

Question

I am trying to connect to an existing SQL Server database using PDO with the drivers provided by Microsoft.

I have seen examples using odbc, dblib, mssql, etc., however I believe the connection string with these drivers should use 'sqlsrv'?

Are there any good examples of how to properly do this? If I should be doing this via some other method please let me know. Thanks!

score 66 · Accepted Answer · answered Oct 05 '12 at 14:31

66

Well that's the best part about PDOs is that it's pretty easy to access any database. Provided you have installed those drivers, you should be able to just do:

$db = new PDO("sqlsrv:Server=YouAddress;Database=YourDatabase", "Username", "Password");

answered Oct 05 '12 at 14:31

MatthewMcGovern

3,466
1
19
19

What drivers do I need to enable on the server? Enabling only the pdo_odbc will work? – Ahmad Sep 06 '21 at 08:01

score 12 · Answer 2 · edited May 23 '17 at 12:18

12

Mind you that in my experience and also of other (PHP - Why is new SQLSRV driver slower than the old mssql driver?) that using PDO_SQLSRV is way slower than through PDO_ODBC.

If you want to use the faster PDO_ODBC you can use:

//use any of these or check exact MSSQL ODBC drivername in "ODBC Data Source Administrator"
$mssqldriver = '{SQL Server}'; 
$mssqldriver = '{SQL Server Native Client 11.0}';
$mssqldriver = '{ODBC Driver 11 for SQL Server}';

$hostname='127.0.0.1';
$dbname='test';
$username='user';
$password='pw';
$dbDB = new PDO("odbc:Driver=$mssqldriver;Server=$hostname;Database=$dbname", $username, $password);

edited May 23 '17 at 12:18

Community

1
1

answered Jun 28 '15 at 22:49

Jan

2,165
1
18
13

May you check my question related http://serverfault.com/questions/722670/s1090unixodbcdriver-managerinvalid-string-or-buffer-length – Marco Marsala Sep 16 '15 at 10:18
This didn't work for me with the curly braces. Works great as `$mssqldriver = 'ODBC Driver 11 for SQL Server';` – Jeff Puckett Jul 06 '16 at 16:15
on a new server the old sqlsrv driver would not connect. this saved me a BUNCH of time. Thank you! – Jeff Bluemel Mar 06 '18 at 19:17

score 9 · Answer 3 · answered Mar 25 '16 at 01:07

This works for me, and in this case was a remote connection: Note: The port was IMPORTANT for me

$dsn = "sqlsrv:Server=server.dyndns.biz,1433;Database=DBNAME";
$conn = new PDO($dsn, "root", "P4sw0rd");
$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$sql = "SELECT * FROM Table";

foreach ($conn->query($sql) as $row) {
    print_r($row);
}

score 8 · Answer 4 · answered Oct 05 '12 at 14:31

8

Figured this out. Pretty simple:

 new PDO("sqlsrv:server=[sqlservername];Database=[sqlserverdbname]",  "[username]", "[password]");

answered Oct 05 '12 at 14:31

Drew

2,601
6
42
65

hassan javaid · Answer 5 · 2017-07-13T10:57:40.573

2

try
{

    $conn = new PDO("sqlsrv:Server=$server_name;Database=$db_name;ConnectionPooling=0", "", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

}
catch(PDOException $e)
{

    $e->getMessage();

}

edited Jul 13 '17 at 10:57

answered Apr 29 '17 at 05:45

hassan javaid

82
11

score 2 · Answer 6 · answered May 19 '19 at 10:57

$servername = "";
$username = "";
$password = "";
$database = "";
$port = "1433";
try {
    $conn = new PDO("sqlsrv:server=$servername,$port;Database=$database;ConnectionPooling=0", $username, $password,
        array(
            PDO::ATTR_PERSISTENT => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    echo ("Error connecting to SQL Server: " . $e->getMessage());
}

Connect to SQL Server through PDO using SQL Server Driver

6 Answers6

Linked

Related