0

I have the following code that is working fine.

$dsn = 'sqlsrv:server = tcp:my_server_host,my_port; Database = my_database';
$connection = new PDO($dsn, $user, $password);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $connection->query('SELECT * FROM my_table');

But the problem is, on the production server the pdo_sqlsrv driver is not installed, and instead pdo_odbc is installed.

So how to convert the above code so that it uses the pdo_odbc driver?

I have tried with the following code.

$dsn = 'odbc:mssql;Server=my_server_host.net;Port:my_port;Database=my_database';
$connection = new PDO($dsn, $user, $passowrd);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$result = $connection->query('SELECT * FROM my_table');

But I am getting the following error.

PDOException: SQLSTATE[IM002] SQLDriverConnect: 0 [unixODBC][Driver Manager]Data source name not found and no default driver specified

Note: My production server is not on Windows.

Ahmad
  • 2,099
  • 10
  • 42
  • 79
  • Why not install the driver on the server? Then you don't have to refactor (all) your code. – Thom A Sep 09 '21 at 07:20
  • I don't have access so cannot install the driver. And I heard that pdo_odbc is the fastest and recommended. All others are deprecating. – Ahmad Sep 09 '21 at 07:22
  • 1
    As far as I know ODBC requires a driver for each database brand it tries to access. Could it be that that is missing on your server? – KIKO Software Sep 09 '21 at 07:24
  • `pdo_sqlsrv` is certainly not deprecated according to [documentation](https://learn.microsoft.com/en-us/sql/connect/php/pdo-sqlsrv-driver-reference?view=sql-server-ver15). – Thom A Sep 09 '21 at 07:25
  • So it is not possible to connect to mssql using only pdo_odbc? – Ahmad Sep 09 '21 at 07:26
  • You can, but even the [documentation](https://www.php.net/manual/en/ref.pdo-dblib.php) recommends using `sqlsrv`. – Thom A Sep 09 '21 at 07:29
  • If I can then it is possible to covert the above code? – Ahmad Sep 09 '21 at 07:30
  • Does this answer your question? [Connect PHP to MSSQL via PDO ODBC](https://stackoverflow.com/questions/20163776/connect-php-to-mssql-via-pdo-odbc) – KIKO Software Sep 09 '21 at 07:32
  • @KIKOSoftware No, because I have used that code and it gives me the error mentioned in my question. – Ahmad Sep 09 '21 at 07:33
  • @Larnu, it is recommended only if you are using Windows. – Ahmad Sep 09 '21 at 07:34
  • Have you installed the [Microsoft SQL Server ODBC Driver](https://www.microsoft.com/en-us/download/details.aspx?id=28160)? – Thom A Sep 09 '21 at 07:36
  • I wouldn't say it's *not* not recommended if you're using a different operating system though, @Ahmad . If you have the right tool for the job, why try and find a different one..? – Thom A Sep 09 '21 at 07:37
  • @Larnu, not sure about that. But my network administrator told me that pdo_odbc is installed on the server. – Ahmad Sep 09 '21 at 07:37
  • But is the ODBC driver for SQL Server installed (as @KIKOSoftware asked you earlier). If not that's why it's not working. (and the error very much suggest it is not installed). But then, if you're installing that, you might just want to install `sqlsrv`. ;) – Thom A Sep 09 '21 at 07:38
  • @Larnu, On the documentation page you have linked, it is stated as `If it is not possible to use SqlSrv, you can use the PDO_ODBC driver to connect to Microsoft SQL Server and Sybase databases`. So it means with simple pdo_odbc, we can create a connection? – Ahmad Sep 09 '21 at 07:42
  • So the server you are using doesn't support `sqlsrv`? You said before you just didn't want to install it, not that you couldn't because it doesn't support it. Are you using PHP5 then? But again, yes you *can* use `pdo_odbc` you need to install the driver for it. Clearly you haven't. But if you're installing that, it makes sense to install `sqlsrv`. – Thom A Sep 09 '21 at 07:43
  • You're taking the documentation too literal. If I say that, instead of using a bike, you can use a car to drive home, you would still need to know how to drive a car, make sure it has petrol, four wheels, etc. In other words: If you want to use ODBC you need to read up on it, and learn how it works. PDO_ODBC does not work in the same way as PDO_SQLSRV does. – KIKO Software Sep 09 '21 at 07:46
  • I am not saying my server is not supporting sqlsrv. But the network administrator is not willing to install it. Therefore I want to change my code accordingly – Ahmad Sep 09 '21 at 07:48
  • Ok, then they need to install the ODBC driver *for* SQLServer that I linked earlier. I would also ask them, personally, to explain why they won't install the specific driver for the specific RDBMS you are using. – Thom A Sep 09 '21 at 07:49
  • System admin gives the reason for not installing the pdo_sqlsrv is that it requires pecl packages and they don't want to start using pecl packages. – Ahmad Sep 10 '21 at 06:34

0 Answers0