Connecting to SQL Server via CodeIgniter

Question

I'm trying to setup a Windows dev environment: Windows 8.1 with IIS 8.5 running SQL Server 2008RC2 and PHP 5.3.24

CodeIgniter 2.1.4

I can connect to the database just fine via PDO in a normal PHP script. But when I try to connect via CodeIgniter I get this error:

"Unable to connect to your database server using the provided settings.
Filename: C\inetpub\wwwroot\ci\system\database\DB_Driver.php
Line number 124"

If I open PHP Manager in the IIS interface I can see that the two required drivers are enabled:

php_sqlsrv_53_ts.dll
php_pdo_sqlsrv_53_ts.dll

Both of those show up in my ini file.

In my CodeIgniter database config file I have the driver set to: sqlsrv (if I use mssql I get a blank screen).

I have the /system/database/drivers/sqlsrv drivers in my CI install.

The only thing that is odd is in phpinfo() under Configure Command it shows --without-mssql and --without-pdo-mssql but I know it's working outside of CI.

phpinfo() also shows the ini file path at C:\WINDOWS but it also shows the loaded ini path as C:\Program Files (x86)\PHP\v5.3\php.ini

I tried moving it but no luck.

My /application/config/database config file:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'localsql';
$db['default']['password'] = 'password';
$db['default']['database'] = 'elements';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

Am I missing anything specific?

Answer 1

First, change

$db['default']['database'] = ‘elements;
$db['default']['dbdriver'] = ‘sq’lsrv;

to

$db['default']['database'] = 'elements';
$db['default']['dbdriver'] = 'sqlsrv';

in your config file.

Second, driver sqlsrv is buggy. Open /system/database/drivers/sqlsrv/sqlsrv_driver.php

To allow pconnect in your configuration, change line 89 from

$this->db_connect(TRUE);

to

return $this->db_connect(TRUE);

If you want to use affected_rows correctly, then change line 274 from

return @sqlrv_rows_affected($this->conn_id);

to

return @sqlsrv_num_rows($this->result_id);

I saw multiple suggestions of how to fix affected_rows posted elsewhere, but changing _execute to not use Scrollable will break stored sessions if you're also using sqlsrv for session validation.