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!
This question is related to
php
sql-server
windows
pdo
Figured this out. Pretty simple:
new PDO("sqlsrv:server=[sqlservername];Database=[sqlserverdbname]", "[username]", "[password]");
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);
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();
}
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);
}
$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());
}
Source: Stackoverflow.com